Analyzing PostgreSQL logs with pgFouine (on Ubuntu 8.04)

pgFouine is a slick, useful, and free tool for analyzing PostgreSQL query workloads. It works without any impact on the running PostgreSQL: it analyzes the PG log output. The caveat is that it needs PG configured to write the right kind of log output.

Sadly, as of version 8.3 PG has a wrinkle in how it writes its logs: multi-line queries can get jumbled together in the stderr-based log, resulting in erroneous output from pgFouine. Hopefully a future PG will be able to write its logs without this issue, but in the meantime, the answer it to use syslog logging instead of native PG logging. This isn’t a bad idea anyway, since syslogd and friends are well proven.

On our project where this need arose, we use the Ubuntu Linux distribution, currently version 8.04. Ubuntu’s PG package sets up native stderr logging; here are the steps needed to change that to syslog logging instead. These steps are about the same for other distributions (or for manual compiles), but with different paths.

The setting shown here for log_min_duration_statement will log all queries that take more than 4 seconds to complete. Depending on your server, workload, and type of workload (OLTP vs. OLAP), this might be too high or too low.

Edit your postgresql.conf file:

vi /etc/postgresql/8.3/main/postgresql.conf

log_destination = 'syslog'
log_line_prefix = 'user=%u,db=%d '
log_min_duration_statement = 4000
silent_mode = on
logging_collector = off

With PostgreSQL 8.2, set redirect_stderr instead of logging_collector:

redirect_stderr = off

Next, setup where syslog will store the data, and add “local0,none” to the ;-separated list of what goes in to var/log/message.  On my system it ended up looking like this, but of course it may vary depending on what else you’ve set up in syslog:

vi /etc/syslog.conf

# add this:

local0.*        -/var/log/postgresql/main.log

# edit this:

    *.=info;*.=notice;*.=warn;\
    auth,authpriv.none;\
    local0.none;\
    cron,daemon.none;\
    mail,news.none          -/var/log/messages

Restart syslogd to make the change take effect:

/etc/init.d/sysklogd restart

Then restart PG so it starts logging there:

/etc/init.d/postgresql-8.3 restart

Note that we are putting these new logs in the existing /var/log/postgresql directory which the Ubuntu PG package creates; if you install PG manually, create such a directory yourself, or set up syslog to write to the pg_log directory. The existing logs there will remain, holding only the messages from PG startup and shutdown (via /etc/init.d/postgresql). I find this unhelpful but harmless.

Log Rotation

By putting the files in this preexisting location, we take advantage of the log rotation already set up in /etc/logrotate.d/postgresql-common. On a busy server, you may want to adjust the rotation setting therein from weekly to daily, or add a line with “size 1000k” or so. Take a look at “man logrotate” to learn about many useful options, such as the ability to have these logs emailed to your DBA as they rotate.

pgFouine

Finally, you are ready to analyze logs. If you plan to analyze them on the same machine where you run your database (probably not a great idea), proceed (on Ubuntu) to get the PHP command line executable:

apt-get install php5-cli

Then download the pgFouine tarball, quietly curse the lack of an Ubuntu package, put it in your $PATH, and run it. Don’t be alarmed by its .php file extension; PHP is a usable (though not particularly charming) language for writing command line tools, as well as dynamic web pages.

cd /var/log/postgresql

pgfouine.php -file main.log  >somefile.html

View the HTML file in your web browser, and dig in to your worse queries. Good luck.