Tuning Sporks Tips

If you're using the PostgreSQL database for a project - perhaps running a web forum with phpBB - here are three handy tips to improve performance.

1. In /etc/fstab, set the noatime option on the filesystem that holds your databases. By default, Linux will record the last time a file was accessed (useful if you're doing archival backups). For a database, where small parts of the file are accessed constantly, and particularly for a database like PostgreSQL, which creates a fair number of files, this generates a significant number of unnecessary disk writes. (This tip applies to pretty much any database; I've done the same thing to good effect on the Progress servers at the office.)

2. Check the -B parameter you are using. If you haven't set -B, PostgreSQL will only allocate 512KB by default, which is absurdly small for anything but a trivial database. The number after -B is the number of 8KB buffers to allocate. Be generous. (Again, this tip applies to pretty much any database. Linux will cache previously read data for you anyway, but the database buffers are likely to be more efficient, particularly if your database is intelligent about combining writes.)

3. Upgrade to 7.3. I'm not sure how big a difference this makes, but I did it anyway. This is rather more involved, as you will need to backup your databases (use pg_dumpall) and restore them (use createdb to make the empty databases, and use psql to reload the backup file). (This tip applies to most free software. Since changes tend to be incremental when you're not marketing driven, new releases are usually better than old. The rule about letting someone else test x.0 releases for you still holds.)

These three steps will turn a disappointingly slow web forum that puts a substantial load on your server into a sprightly board that hardly makes a dent on your load average.

Go to!

(Now I just need to do something about Movable Type. Since it generates static pages, it's always fast for readers, but it's a little slow on the back end. Especially if your Secret Project X™ (remember that?) involves loading up 2045 individual posts.)

Posted by: Pixy Misa at 07:10 PM

Comments

Name: (required)

Email: (required)

URL:

Comments:

Processing 0.0, elapsed 0.0043 seconds.
16 queries taking 0.0033 seconds, 7 records returned.
Page size 4 kb.
Powered by Minx 0.8 beta.