Pgbenchtesting
Testing for Performance Regression with pgBench 9.0
This page will eventually be merged into Regression Testing with pgbench
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.
Initialize with:
- pgbench -i -s 70 bench
Unprepared, Read-Write:
- pgbench -c 4 -j 2 -T 600 bench
Prepared, Read-Write:
- pgbench -c 4 -j 2 -T 600 -M prepared bench
Unprepared, Read-Only:
- pgbench -c 4 -j 2 -T 600 -S bench
Prepared, Read-Only:
- pgbench -c 4 -j 2 -T 600 -M prepared -S bench
