Testing for Performance Regression with pgBench 9.0

In order to test for performance regressions (or improvements) it's necessary to install two versions of PostgreSQL on the same machine. Otherwise, you have no comparable statistics. For example, you might install 8.4.3 and 9.0alpha5, or you might install 9.0alpha4 and 9.0alpha5. You also might run against the same test version in two modes: with HS/SR and without, for example.

Since pgbench is such a simple test, you'll need to run several different runs to see different aspects of performance. It's also a good idea to run each at least 3 times, since pgbench has some randomness to it.

Always run the same version of pgBench against both databases, probably the newer version.

Some factors:

Where to run pgBench: Ideally, you want to run it from a separate machine from the one holding the database. That way, you don't have pgBench taking CPU away from the database.

Number of Threads and Clients to Use: This depends on the number of cores on the machine(s) you're testing. For each core available to the database, I suggest 1 thread and 2 clients. Note: do not use multi-threaded pgBench on non-threadsafe systems; you will get unreliable results.

PostgreSQL Configuration: use what you'd consider a normal performance configuration for the machine being tested. Use (as much as possible) the same configuration for both.

Time vs. Transactions: results which run pgbench for a specific amount of time are easier to compare. You also know how long they'll take you.

Initializing Databases: if you are going to use the same database for several test runs in a row, it's important that you "prime" it by running pgbench against it for at least 20 minutes first, or the first couple of tests will be misleadingly fast. Alternately, you can initialize a new database for each test run.

Time to Run Ideally, you'd do each pgbench run for at least an hour for useful results. However, this interferes with running a lot of different tests for people who don't do this full-time or have a dedicated testing server. Make sure to run it for at least 10 minutes, though, to get results you can even measure. Possibly run the most interesting results in a 1-hour test. All tests below run for 10 minutes.

What follows are some examples of tests. The command line given would be appropriate for a machine with 2 cores available to the database and thread-safe.

Memory vs. Disk Performance

You want to test pgbench at the 3 levels of performance related to disk: in buffer, mostly in cache, and all on disk. You manipulate this by changing the scale factor, following these two formulas, assuming a dedicated database server.

scale / 75 = 1GB database

In Buffer Test: 0.1 X RAM

Mostly Cached: 0.9 X RAM

Mostly on Disk: 4.0 X RAM

Note that the mostly-on-disk test may require you to have a considerable amount of disk space available for your database.

Examples: the following assume a 2-core machine with 2GB of RAM, running for 10 minutes:

Buffer test:

pgbench -i -s 15 bench1

pgbench -c 4 -j 2 -T 600 bench1

Mostly Cache Test:

pgbench -i -s 70 bench2

pgbench -c 4 -j 2 -T 600 bench2

On-Disk Test:

pgbench -i -s 600 bench3

pgbench -c 4 -j 2 -T 600 bench3

Measuring the amount of time required to initialize the three databases will also provide interesting results.

Read vs. Write Performance

It is also interesting to test relative speed of different write patterns. For this set of tests, use either the Mostly Cache or On-Disk size database, or something in-between.

The tests below assume the same machine above. All start with:

pgbench -i -s 70 bench2

Read-Write Test

pgbench -c 4 -j 2 -T 600 bench2

Read-Only Test

pgbench -c 4 -j 2 -T 600 -S bench2

Simple Write Test

pgbench -c 4 -j 2 -T 600 -N bench2

Connections and Contention

For this series of tests, we want to test how PostgreSQL behaves with different levels of connection activity. In this case, it's very relative to how many cores you have. Again, we're assuming the same 2-core, 2GB machine.

Unfortunately, you can only do this test effectively from another machine which has at least as many cores as the database server.

All tests start with:

pgbench -i -s 30 bench

Single-Threaded

pgbench -c 1 -T 600 bench

Normal Load

pgbench -c 8 -j 2 -T 600 bench

Heavy Contention

pgbench -c 64 -j 4 -T 600 bench

Heavy Connections without Contention

pgbench -c 64 -j 4 -T 600 -N bench

Heavy Re-connection (simulates no connection pooling)

pgbench -c 8 -j 2 -T 600 -C bench

Prepared vs. Ah-hoc Queries

pgBench 9.0 also allows you to test the effect of prepared queries on performance. Assumes the same database server as above.