tuning postgresql

| No Comments | No TrackBacks
Shoaib Mir is talking about performance of postgresql, and how to tune it. He started with application analysis. How do you work out if it's reading large amounts of data, or writing, or doing complicated queries. 

You need to find which queries are taking a long time to run. Install a analysis tool, such as EPQA or pgfouine (Shoaib prefers EPGA). THen you need to use 'EXPLAIN ANALYZE' to debug them. However the output of EXPAIN ANALYZE gives hard to understand output. Sucks to be you. Don't use EXPLAIN ANALYZE with DML query in production (insert update and delete).

You need to lok at memory. Is the size of the database small enough to fit in memory, or do you need faster disks. What is the Buffer cache usage (pg_buffercache or OS cache).

Storage setup. He recommends RAID 10 over RAID 5 for write heavy activity. Suggests dedicated storage. Run iostat output and look for raits and queue sizes. Perhaps consider trending iostat output. Check if RAID controller is using battery backed cache, and monitor the health of the battery. Turn on the write cache.

Use tablespaces to put indexes and tables on separate storage areas, and transaction logs on separate storage.

He recommends XFS for the filesystem, as it has better journalling than ext3. He says to start with shared_buggers set to 25% of your RAM to start with, then use pg_buffercache to find the optimal value. He talked about a work_mem parameter that is used for sorting operations. If the a disk sort is being used you need a larger work_mem. It's a session variable that can be used for different sessions, so you don't have to set this at server start.

maintenance_work_mem is another session based parameter. You can raise it when you want to create an index, and then lower. 

He mentioned that bonniee++ has some database orientated checks. Suggests using check_postgres.pl for monitoring database health monitoring.

No TrackBacks

TrackBack URL: http://geoff-blog.cromp.id.au/cgi-bin/movabletype/mt-tb.cgi/116

Leave a comment

About this Entry

This page contains a single entry by Geoff Crompton published on January 25, 2011 5:09 PM.

secure gateway was the previous entry in this blog.

CGroups for resource management is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.



Powered by Movable Type 4.23-en