Consider adding buffers the background writer finds reusable to the free list
I have tried implementing it and taken the readings for Select when all the data is in either OS buffers
or Shared Buffers.
The Patch has simple implementation for "bgwriter or checkpoint process moving the unused buffers (unpinned with "ZERO" usage_count buffers) into "freelist".
Results (Results.html attached with mail) are taken with following configuration.
Current scenario is 1. Load all the files in to OS buffers (using pg_prewarm with 'read' operation) of all tables and indexes. 2. Try to load all buffers with "pgbench_accounts" table and "pgbench_accounts_pkey" pages (using pg_prewarm with 'buffers' operation). 3. Run the pgbench with select only for 20 minutes. Platform details: Operating System: Suse-Linux 10.2 x86_64 Hardware : 4 core (Intel(R) Xeon(R) CPU L5408 @ 2.13GHz) RAM : 24GB Server Configuration: shared_buffers = 6GB (1/4 th of RAM size)
Pgbench configuration: transaction type: SELECT only scaling factor: 1200 query mode: simple number of clients: <varying from 8 to 64 > number of threads: <varying from 8 to 64 > duration: 1200 s
Comments or suggestions?
I am still collecting data for update and other operations performance results with different database configuration.