Server tuning is a topic that consumes many books, blog posts and wiki pages.<\/p>\n
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.<\/p>\n
Greg Smith open sourced a utility for making a first pass at tuning Postgres for a local system with pgtune<\/a>. 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.<\/p>\n 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<\/a>. 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!<\/p>\n On Linux, the readahead buffer (brief explanation<\/a>) is set way to small for most database systems. Increase this to about 1 MB with I wrote this performance checklist<\/a> 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.<\/p>\n","protected":false},"excerpt":{"rendered":" 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 … Continue reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[604,192],"class_list":["post-5082","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-everyday-postgres","tag-performance"],"_links":{"self":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/5082","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/comments?post=5082"}],"version-history":[{"count":2,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/5082\/revisions"}],"predecessor-version":[{"id":5084,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/5082\/revisions\/5084"}],"wp:attachment":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/media?parent=5082"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/categories?post=5082"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/tags?post=5082"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Use XFS<\/h2>\n
Increase your readahead buffer<\/h2>\n
blockdev -setra 2048 [device]<\/code>.<\/p>\nFor further performance analysis<\/h2>\n