PostgreSQL 8.2 Performance Monitoring Patchset

BETA release warning: This patch works well enough for some purposes, but is still going through review; there aren't any currently known bugs remaining. A final version of this patch may be released after the 8.3 code moves into beta to better match the form that these patches were actually comitted in. The version here is strictly for early adopters.

Database corruption warning: Some of the features of this patch will make small modifications to your database that will cause issues if you then return to the unmodified version of PostgreSQL. It is not suitable for use on production servers and is intended only for benchmarking and pre-production performance tuning research. This patch is not in any way recommended or endorsed by The PostgreSQL Global Development Group, and it is strictly for educational use. Use it at your own risk

Now that the disclaimers are out of the way...the upcoming version 8.3 of PostgreSQL adds a variety of complex new features that will improve performance in various ways. It also includes several new ways to monitor what your database was already doing. The most exciting of these (if you're into this sort of thing) is pg_stat_bgwriter, which shows all sorts of information about checkpoints and how your buffer cache is working. Here is an example report:

pgbench=# select * from pg_stat_bgwriter;
 checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_lru | buffers_all | maxwritten_lru | maxwritten_all | buffers_client | buffers_alloc
-------------------+-----------------+--------------------+-------------+-------------+----------------+----------------+----------------+---------------
                93 |             100 |            1737158 |      343753 |       61611 |            342 |          12139 |        1520167 |       5464709

From this, it's very easy to tune things that used to be a complete mystery. For example, it's pretty obvious that I should increase bgwriter_all_maxpages on this system because it's writing the maximum allowed quite often. This might help with the fact that most of the buffers being written out under normal use are done so by the individual client backends, and that a high percentage of the buffers are being written at checkpoint time. (in fact, the maxpages parameters were at their very tiny defaults of 5 for most of the time this server was up, so this is all no surprise).

There are a few related patches in the new 8.3 code as well, like an addition to contrib/pg_buffercache that lets you see the usage_count information needed to figure out if you should be using the LRU or the all portions of the background writer more.

While it's very nice that this is all coming, it's going to be a while before these features are officially released As I had a hand in creating many of these patches, and I'm focused mainly on 8.2 performance issues right now, I have taken what I thought were the most interesting of these monitoring patches and back-ported them to PostgreSQL 8.2. This makes it fairly simple to use these new features to answer questions about how V8.2 runs on your hardware right now, with having to wait for 8.3 to go through beta testing and get released.

Steps to use this patch are:

-Download perfmon82.patch and copy it into a directory you've extracted the PostgreSQL 8.2 source code into. I would recommend that you use at least V8.2.4, as a serious performance-related bug and some data integrity issues exist in the earlier versions.

-Install the patch with

# patch -p 0 < perfmon-8.2.patch

-Configure the code to build this customized PostgreSQL into its own directory using the prefix feature. You don't want this version overwritting the real versions of your Postgres files. Setting things up this way and manually starting the server will also keep the patched server from starting automatically and potentially interfering with important work on this system. Here is an example:

mkdir /opt/patchedpg-8.2.3
./configure --prefix=/opt/patchedpg-8.2.3
make
make install

-Create a new database directory and build a database cluster in there:

mkdir /data/patchedpg
export PGDATA=/data/patchedpg
cd /opt/patchedpg-8.2.3
bin/initdb

-Edit the postgresql.conf file for the configuration you want to test. You should be able to just copy the file from your production 8.2 server if you have one. Be sure to change the port number if you've already got a server running on the standard port of 5432. You might also want to put the log files somewhere else. If you have the pg_xlog on another hard drive, you should use the usual technique to move that and symlink it into the data directory.

-Start the server with

/opt/patchedpg-8.2.3/bin/pg_ctl start

-Run the new version of psql in your patched directory, or otherwise execute your tests. Take a look at pg_stat_bgwriter, or build the updated contrib/pg_buffercache and install it in your database.

-Stop the server with

/opt/patchedpg-8.2.3/bin/pg_ctl stop

Credits for the patch's components

The individual components making up this patchset are:

bgwriter stats - Magnus Hagander
pgbench transaction timestamps - Greg Smith
Add usage counts to pg_buffercache - Greg Smith
Logging checkpoints and other slowdown causes - Greg Smith
Stats for buffer allocations and client writes - Greg Smith (based on Automatic adjustment of bgwriter_lru_maxpages by Itagaki Takahiro)

Licensing

Most of the source code used to assemble this patchset was released under the PostgreSQL License, and accordingly this particular assembly is also made public under that license:

PostgreSQL Database Management System (formerly known as Postgres, then as Postgres95)
Portions Copyright (c) 1996-2005, The PostgreSQL Global Development Group
Portions Copyright (c) 1994, The Regents of the University of California
Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.
IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES,