Configuring the database server for pgbench
All of the queries used for the built-in tests are simple: They are at most using a primary key index to look a row up, but no joins or more complicated query types. But the statements executed will heavily stress writes and the buffer cache's ability to keep up.
Accordingly, parameters you might adjust break down into three major categories:
- Important to note and possibly tune:
shared_buffers,checkpoint_segments,autovacuum,wal_buffers,checkpoint_completion_target. - Impacts test results significantly:
wal_sync_method,synchronous_commit,wal_writer_delay. Adjust on systems where a change is appropriate to get good and/or safe results. - Does not matter:
effective_cache_size,default_statistics_target,work_mem,random_page_cost, and most other parameters. The various bgwriter settings do have an impact, just usually not a positive one. Unlike some real workloads where it might help, making the background writer more aggressive usually...
