Regression Testing with pgbench
From PostgreSQL wiki
pgbench ships with PostgreSQL and can be used to test several aspects of database performance. As the primary benchmarking tool that is included with the main distribution, it provides the easiest way to run simple performance tests to confirm whether a new release is faster or slower than an earlier one. This is particularly useful for regression testing, to confirm that a new version wasn't slowed down by changes since the previous one.
If you are building your PostgreSQL from source, note that the default "make" target does not compile pgbench or other modules in the contrib/ directory tree. You'll need to do that yourself like this:
cd contrib make make install
(Alternately you could just go into contrib/pgbench and do the make/install from that directory)
You can confirm you're getting the right version like this example:
$ pgbench --version pgbench (PostgreSQL) 8.5alpha2
One critical thing to be aware of is that many testing builds of PostgreSQL, including the pre-release RPM versions, are compiled with assertion code turned on. Assertions help find bugs, but they will substantially slow down pgbench tests, particularly if you've increased shared_buffers to a large value. After you've started your database, you can check whether asserts are on using the SHOW command; this is what you should see:
postgres=# show debug_assertions; debug_assertions ------------------ off (1 row)
You will not get useful regression data unless this setting is off.
postgresql.conf configuration changes
The default settings for the postgresql.conf give pretty low performance for pgbench. Two settings you might increase usefully are:
* shared_buffers: Setting this definitely helps pgbench performance on reads and writes. Using at least 256MB gets you a good sized portion of the improvement possible here. * checkpoint_segments: If you're running the default or other write-heavy tests, you want this to be much larger to get more useful results. At least 16 will improve a lot over the baseline.
Many other parameters that impact general performance, including effective_cache_size and work_mem, have no impact on the simple tests pgbench runs.
Here's a script that creates a database cluster with useful starting parameters, presuming you've set PGDATA and PGLOG to where you want the cluster and startup log file to go, respectively:
initdb SHARED_BUFFERS="256MB" WAL_BUFFERS="16MB" CHECKPOINT_SEGMENTS="16" echo \# Changes for pgbench testing >> $PGDATA/postgresql.conf echo shared_buffers=$SHARED_BUFFERS >> $PGDATA/postgresql.conf echo wal_buffers=$WAL_BUFFERS >> $PGDATA/postgresql.conf echo checkpoint_segments=$CHECKPOINT_SEGMENTS >> $PGDATA/postgresql.conf pg_ctl start -l $PGLOG
Creating pgbench sample database
The simplest approach to get useful regression testing is to use a small database (one that fits in shared_buffers) that has a scale greater than the number of cores on your system, then run the select-only test on that.
- Set shared_buffers='256MB'
- Create a database with a scale of 10 (approximately 160MB):
createdb pgbench pgbench -i -s 10 pgbench
Here's a sample with 2 jobs and 4 clients, suitable for a 2-core system, that runs for 30 seconds:
pgbench -T 30 -j 2 -S -c 4 pgbench
Note that clients must be a multiple of cores, so with "-j 2" we can't test performance with a single client.
Here's a script that tries a number of client loads:
CORES="2" CLIENTS="2 4 8 16 32" for c in $CLIENTS; do pgbench -T 30 -j $CORES -S -c $c pgbench done
Note that if you run the pgbench SELECT test right after initialization step, you'll be testing with a "warm cache": one where all of the data you need is in RAM already. If you do something to clear the PostgreSQL and OS caches, such as reboot, pgbench is going to be slow afterwards, because it will will read every single block from the accounts table in particular back into RAM again in a way that involves all seeks rather than sequential reads. For regression testing purposes, you might as well just create a new database every time you reboot so that you're only testing cached performance instead. Uncached behavior is much harder to systematically compare multiple runs with.
You can use pgbench-tools to script large numbers of tests and record the results for analysis in a database.