Server tuning is a topic that consumes many books, blog posts and wiki pages.
Below is some practical advice for getting low-hanging fruit out of the way if you’re new to tuning Postgres and just want something that will likely work well-enough on low volume systems. I’d say looking at this list and making changes on a new system should take 10 minutes or less.
Run pgtune
Greg Smith open sourced a utility for making a first pass at tuning Postgres for a local system with pgtune. This tool is easy to run – just copy it to a target system and then point it at your existing Postgres config. It puts its changes into a new file at the very bottom.
Use XFS
Filesystem choice matters. Greg Smith goes into some detail on why ext3 is a terrible performance choice for a database filesystem in his talk Righting Your Writes. At this point, XFS is the filesystem that should be your default choice. If you want to explore ext4 or zfs (if that’s an option for you), that may be worth looking at. It is “safe” however to choose XFS. Depending on your disk situation, recreating your filesystem might take a bit longer than 10 minutes, but hopefully this will save you time and bad performance in the future!
Increase your readahead buffer
On Linux, the readahead buffer (brief explanation) is set way to small for most database systems. Increase this to about 1 MB with blockdev -setra 2048 [device].
For further performance analysis
I wrote this performance checklist a while back for assessing a system’s health. I’d say a review of all the things on that list would take probably half a day. Following up and making the changes could take a day or more. These kinds of analysis are worth exploring periodically to ensure you haven’t missed important changes in your environment or your application over time.
Why XFS?
The main reason is incremental sync when making filesystem changes. This is worth reading through Greg’s slides linked above for a longer explanation — he does a great job walking through how this works and how it is different than ext3.
The main reason is incremental sync when making filesystem changes. This is worth reading through Greg’s slides linked above for a longer explanation — he does a great job walking through how this works and how it is different than ext3.
… and in those slides, Greg says “XFS and ext4 allow granular sync”. And being that ext4 is the default filesystem for most peoples systems and what most are using, what compelling reason is there to single out XFS or switch to it?
Critical bugs in ext4 and bad performance.
http://lwn.net/Articles/476263/
http://www.phoronix.com/scan.php?page=article&item=linux_311_filesystems&num=2
EXT4 is faster than XFS in the phoronix.com benchmark you cite.
If you read the article through to the end, you’ll see:
“but in at least some tests [ext4] doesn’t appear to be always ensuring the data is syncing to the disk as with the other file-systems, which could potentially put your data at risk in case of a kernel issue or power loss.”
So, sure, the benchmarks with a single SSD show ext4 is somewhat faster, which is fine for laptops. For a database system where you’ve got multiple disks, multiple threads, and widely varying filesizes, it is extremely unclear what the performance advantage of ext4 is in the general case (DO YOUR OWN BENCHMARKS), and there are significant bugs still being found in ext4 that warrant caution.
There have also been critical bugs in XFS! 🙂
If I couldn’t be bothered to benchmark, I’d go with ext4 due to its wider use and greater familiarity.
I noticed pgtune hasn’t been updated since 9.1. Does it support 9.2 or 9.3?
It will, but may miss out on a few features. I’ll have a look in the new year and see what’s relevant.
This post was very helpful, thank you for writing it down. I’d also like to echo denpanosekai’s questions, do you know if pgtune will work with 9.3?
Oh, also you set the readahead buffer to 2048 but you say 1MB. Am I missing something? Isn’t that 2MB?
Hi! Readahead is measured in 512 byte blocks, so 2048 * 512 bytes = 1 MB 🙂
No. It’s the 512 byte sector count. The article is correct.