I'm running pgFouine right now on my server

19 April 2012   0 comments   Linux

Powered by Fusion×

pgFouine in action on my server
pgFouine is a PostgreSQL log analyzer. You basically, configure your Postgres server to be very verbose about all statements. Then, you simply run the pgfouine.php command against the log file and it spits out a page like this:


Running all this verbose logging will obviously slow down the database server a bit so I'm only going to be running this temporarily. The overhead is actually pretty small but it's also piling on quite a few bytes in terms of the size of the log file.

So, at the time of writing, it's been about 1 day running and it has captured about 70,000 queries (by the time you look at the file it might have gone up significantly). I haven't started actually looking at it in detail yet but it's clear that there's some use of the LIKE operator that Postgres spends most of its time on.

You can configure your pgFouine to filter on specific databases. I have not done so because I'm at the moment just interested in what the whole database server is getting up to. Most of these guilty queries comes from the Crosstips site. Maybe it's time to optimize the worst performing queries there a bit.


After running for 24 hours, I did some low-hanging fruit optimization to the biggest culprits and reset the logs. The first 24 hours report is still here: www.peterbe.com/pgfouine.1.html


I've stopped logging all queries now. The results are still there. I'm quite pleased with the results so far.


Your email will never ever be published

Related posts

Secs sell! How frickin' fast this site is! (server side) 05 April 2012
Are WebSockets faster than AJAX? ...with latency in mind? 22 April 2012
Related by keywords:
A great thing about Squid: Calamaris 04 July 2008