October 2008 – Kyle Cordes

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.

Multicast your DB backups with UDPCast

At work we have a set of database machines set up so that one is the primary machine, making backups once per day, and several other machines restore this backup once per day, for development, ad hoc reporting, and other secondary purposes. We started out with an obvious approach:

back up on server1, to a file on server1
SCP or rsync the file from server1 to server2
restore the DB on server2

… but over time as the data has grown the inefficiency of such an approach become equally obvious: the backup data goes back and forth across the network and to/from disk repeatedly. These steps only count the backup data, not the live storage in the DBMS:

On to the disk on server1 (putting additional load on the primary DB machine)
Off the disk on server1 (putting additional load on the primary DB machine)
On to the disk on server2
Off the disk on server2

This is also wasteful from a failure-recovery point of view, since the place we are least likely to need the backup is on the machine whose failure would lead us to need a backup.

Pipe it over the network instead

The project at hand uses PostgreSQL on Linux, so I’ll show example PG commands here. The principles apply equally well to other DBs and platforms of course, though some DBMSs or platforms might not offer backup and restore commands that stream data. (I’m looking at you, MS SQL Server!)

What we need is a pipe that goes over the network. One way to get such a pipe is with ssh (or rsh), something like so, run from server1:

pg_dump -Fc dbnameonserver1 | ssh server2 pg_restore -Fc -v -O -x -d dbnameonserver2

This variation will simultaneously store the backup in a file on server1:

pg_dump -Fc dbnameonserver1 | tee dbname.dump | ssh server2 pg_restore -Fc -v -O -x -d dbnameonserver2

This variation (or something close, I last run this several days ago) will store the backup in a file on server2 instead:

pg_dump -Fc dbnameonserver1 | ssh server2 "tee dbname.dump | pg_restore -Fc -v -O -x -d dbnameonserver2"

To reduce the CPU load from this, adjust SSH to use less CPU-intensive encryption, or avoid that entirely with rsh (but only if you have a trusted / local network).

Multicast / Broadcast it over the network instead

The above commands are good for point-to-point streaming backup / restore, but the scenario I have in mind has one primary machine and several (3, 4, or more) secondary machines. One answer is to run the above process repeatly, once for each secondary machine, but that sends the whole backup over the network N times. Inefficiency! (==Blashphemy?)

To avoid that, simply use UDPCast. It’s a trivial install on Debian / Ubuntu:

apt-get install udpcast

(Be warned though: there is at least one annoying bug in the old (2004) UDPCast offered off-the-shelf in Debian / Ubuntu as of 2008. You might need to the latest UDPCast source from its web site above.)

Run this on the server1:

pg_dump -Fc dbnameonserver1 | udp-sender --min-wait 5 --nokbd

Run this on the server2 .. serverN:

udp-receiver --nokbd | pg_restore -Fc -v -O -x -d dbnameonserverN

With this approach, the backup data will be multicast (or broadcast, if multicast does not work and if all the machines are on the same segment), only traversing the network once no matter how many receiving machines are set up. udp-receiver has a –pipe option, but I found that I occasionally get corruption with huge (50GB+) transfer, when using –file or –pipe. So I recommend this, to save a copy on the receiving end:

udp-receiver --nokbd | tee mydatabase.dump | pg_restore -Fc -v -O -x -d dbnameonserverN

Or perhaps you want to just receive and store the backup on a file server, with this:

udp-receiver --nokbd >mydatabase.dump

To make all this happen automatically, you’ll set the sender to start at the same time as the receivers in “cron” on the relevant machines. Use NTP to keep their clocks in sync, and adjust the udp-sender and udp-receiver options as needed to get the whole process to start smoothly in spite of minor timing variations (–min-wait t, –max-wait t).

As with the previous suggestion for rsh, the data will travel unencrypted over your network, so do this only if you trust your network (such as a LAN segment between your database servers).

Multicast / broadcast is very useful technology, and with UDPcast it is quite easy to use. UDPcast also implements a checksum/retransmit mechanism, it is not a “bare”, loss-prone UDP transmission.

Rhino + JavaScript + Swing, Look Ma No Java

A while back I was discussing the future of programming languages with a colleague, and we agreed that for all its foibles, JavaScript will continue to enjoy very wide and increasing use in the coming years. I wrote last year about Steve Yegge’s hints that JavaScript is the “next big languages”, see that post for the reasoning.

Based on all that, I set about writing a small test app to see what it’s like to program a Swing app with JS. After a day or so of work (spread over a few months), I offer my Rhino Swing Test App:

Run RSTA now via Java Web Start

Get the RSTA code (git, on GitHub)

It implements the same “flying boxes” animation demo that I presented a few years ago at the St. Louis JUG, but aside from a generic launcher class, the GUI is implemented entirely in JavaScript. To clarify, this is not web browser JavaScript; it is running in Rhino, in the JVM, using Swing classes.

The documentation for interaction between Java and JS is limited, but sufficient. For simplicity, I used Rhino as an interpreter, I did not compile to java bytecode. Nonetheless, the animation runs about as smoothly in JS as it does in Java, because the heavy lifting is done by the JDK classes.
I used Eclipse (with JavaScript support) to write this code, but of course JS makes much less code completion possible than Java, and I missed that. Typically I mildly prefer IDEs, but am also productive with a text editor. For working with a large API like Swing though, IDE support helps greatly.

Still, I recommend a look at this approach if you are fond of dynamic languages but need to build on the Java platform, and I intend to investigate server-side JS development also.

Network / System Monitoring Smorgasbord

At one of my firms (a Software as a Service provider), we have a Zabbix installation in place to monitor our piles of mostly Linux servers. Recently we look a closer look at it and and found ample opportunities to monitor more aspects, of more machines and device, more thoroughly. The prospect of increased investment in monitoring led me to look around at the various tools available.

The striking thing about network monitoring tools is that there are so many from which to choose. Wikipedia offers a good list, and the comments on a Rich Lafferty blog post include a short introduction from several of the players. (Update – Jane Curry offers a long and detailed analysis of network / system monitoring and some of these tools (PDF).)

For OS level monitoring (CPU load, disk wait time, # of processes waiting for disk, etc.), Linux exposes extensive information with “top”, “vmstat”, “iostat”, etc. I was disappointed to not find any of these tools conveniently presenting / aggregating / graphing the data therein. From my short look, some of the tools offer small subsets of that data; for details, they offer the ability for me to go in and figure out myself what data I want in and how to get it. Thanks.

Network monitoring is a strange marketplace; many of the players have a very similar open source business model, something close to this:

core app is open source
low tier commercial offering with just a few closed source addons, and support
high tier commercial offering with more closed source addons, and more support

I wonder if any of them are making any money.

Some of these tools are agent-based, others are agent-less. I have not worked with network monitoring in enough depth to offer an informed opinion on which design is better; however, I have worked with network equipment enough to know that it’s silly not to leverage SNMP.
I spent yesterday looking around at some of the products on the Wikipedia list, in varying levels of depth. Here I offer first impressions and comments; please don’t expect this to be comprehensive, nor in any particular order.

Zabbix

Our old installation is Zabbix 1.4; I test-drove Zabbix 1.6 (advertised on the Zabbix site as “New look, New touch, New features”. The look seemed very similar to 1.4, but the new feature list is nice.

We most run Ubuntu 8.04, which offers a package for Zabbix 1.4. Happily, 8.04 packages for Zabbix 1.6 are available at http://oss.travelping.com/trac.

The Zabbix agent is delightfully small and lightweight, easily installing with a Ubuntu package. In its one configuration file, you can tell it how to retrieve additional kinds of data. It also offers a “sender”, a very small executable that transmits a piece of application-provided data to your Zabbix server.

I am reasonably happy with Zabbix’s capabilities, but I have the GUI design to be pretty weak, with lots of clicking to get through each bit of configuration. I built far better GUIs in the mid-90s with far inferior tools to what we have today. Don’t take this as an attack on Zabbix in particular though; I have the same complaint about most of the other tools here.

We run PostgreSQL; Zabbix doesn’t offer any PG monitoring in the box, but I was able to follow the tips at http://www.zabbix.com/wiki/doku.php?id=howto:postgresql and get it running. This monitoring described there is quite high-level and unimpressive, though.

Hyperic

I was favorably impressed by the Hyperic server installation, which got two very important things right:

It included its own PostgreSQL 8.2, in its own directory, which it used in a way that did not interfere with my existing PG on the machine.
It needed a setting changed (shmmax), which can only be adjusted by root. Most companies faced with this need would simply insist the installer run as root. Hyperic instead emitted a short script file to make the change, and asked me to run that script as root. This greatly increased my inclination to trust Hyperic.

Compared to Zabbix, the Hyperic agent is very large: a 50 MB tar file, which expands out to 100 MB and includes a JRE. Hyperic’s web site says “The agent’s implementation is designed to have a compact memory and CPU utilization footprint”, a description so silly that it undoes the trust built up above. It would be more honest and useful of them to describe their agent as very featureful and therefore relatively large, while providing some statistics to (hopefully) show that even its largish footprint is not significant on most modern servers.

Setting all that aside, I found Hyperic effective out-of-the-box, with useful auto-discovery of services (such as specific disk volumes and software packages) worth monitoring, it is far ahead of Zabbix in this regard.

For PostgreSQL, Hyperic shows limited data. It offers table and index level data for PG up through 8.3, though I was unable to get this to work, and had to rely on the documentation instead for evaluation. This is more impressive at first glance than what Zabbix offers, but is still nowhere near sufficiently good for a substantial production database system.

Ganglia

Unlike the other tools here, Ganglia comes from the world of high-performance cluster computing. It is nonetheless apparently quite suitable nowadays for typical pile of servers. Ganglia aims to efficiently gather extensive, high-rate data from many PCs, using efficient on-the-wire data representation (XDR) and networking (UDP, including multicast). While the other tools typically gather data at increments of once per minute, per 5 minutes, per 10 minutes, Ganglia is comfortable gathering many data points, for many servers, every second.

The Ganglia packages available in Ubuntu 8.04 are quite obsolete, but there are useful instructions here to help with a manual install.

Nagios

I used Nagios briefly a long time ago, but I wasn’t involved in the configuration. As I read about all these tools, I see many comments about the complexity of configuring Nagios, and I get the general impression that it is drifting in to history. However, I also get the impression that its community is vast, with Nagios-compatible data gathering tools for any imaginable purpose.

Others

How Many Monitoring Systems Does One Company Need?

It is tempting to use more than one monitoring system, to quickly get the logical union of their good features. I don’t recommend this, though; it takes a lot of work and discipline to set up and operate a monitoring system well, and dividing your energy across more than one system will likely lead to poor use of all of them.

On the contrary, there is enormous benefit to integrated, comprehensive monitoring, so much so that it makes sense to me to replace application-specific monitors with data feeds in to an integrated system. For example, in our project we might discard some code that populates RRD files with history information and published graphs, and instead feed this data in to a central monitoring system, using its off-the-shelf features for storage and graphing.

A flip side of the above is that as far as I can tell, none of these systems offers detailed DBA-grade database performance monitoring. For our PostgreSQL systems, something like pgFouine is worth a look.

Conclusion

I plan to keep looking and learning, especially about Zenoss and Ganglia. For the moment though, our existing Zabbix, upgraded to the current version, seems like a reasonable choice.

Comments are welcome, in particular from anyone who can offer comparative information based on substantial experience with more than one of these tools.

Ease of Installation: DokuWiki, PHP, files

In the past I’ve installed MediaWiki, ruwiki, git-wiki, and several other Wiki implementations (Perl, and Java implementations), with varying degrees of effort. For example, ruwiki required considerable gymnastics to get the right Ruby libraries in place on the machine I hosted it on, MediaWiki required a database, etc. Ruby libraries, databases, JVMs, and the like are all at the top of my toolbox so in most cases it’s just a few minutes and a few commands, which seems amply easy until compared wtih…

Yesterday I set up a DokuWiki instance (which stlruby.org might migrate to), and found that its underpinnings (PHP, plain text files) make for ridiculously easy installation:

wget
tar xzf
browse to install page, set a few settings
delete install page

Yet those underpinnings are very well suited to the task at hand. A typical Wiki does not need a database underneath it. As with many things, this reminded me of a general principle: use the least complex, most readily and commonly available, easiest to administrate technology appropriate for the task at hand.

Webby – Client-side, static content management system

This afternoon I rebuilt OasisDigital.com using Webby, stripping out hand-coded HTML and replacing it with much more maintainable Markdown. The site looks about the same as before (which is to say, mediocre), but under the hood it is much easier to update. We intend to use this new ease, to move forward in improving it. There is a general principle here, which applies broading in software development also:

If you need to make a change, but that change is difficult / tedious / risky to make, first improve the underlying system that makes it so.
(OasisDigital.com is a static web site; we have dynamic contact (issue trackers, etc.) to automate our work together and with our customer, but that content is on another domain.)

Webby is a client-side, simple CMS for generating static web sites, written in Ruby. Why serve a static site (plain old files on a web server) in 2008?

It minimizes the moving parts, there is almost nothing to break or maintain.
It is very unlikely that any hosting issue will break a static site.
It is easy to serve a static site fast (though our current host, TextDrive, sometimes is not all that fast).
Security vulnerabilities are very unlikely, in the absence of any executable content.
The canonical content (in this case, mostly Markdown) is stored in plain text files, which we track, diff, and merge in git.

In an earlier foray in to Drupal, we found that Drupal has extensive and useful capabilities, as well as a vibrant community, but it also has many moving parts; too many, in my judgment, to make it a good solution for building an essentially static web site.