Message-ID: <4AFA05F4.5050203@2ndquadrant.com>
Date: Tue, 10 Nov 2009 19:31:48 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Database tuning at Duke
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

There's two papers published recently at Duke that I just found, both of 
which use PostgreSQL as part of their research:

Automated SQL Tuning through Trial and (Sometimes) Error:  
http://www.cs.duke.edu/~shivnath/papers/dbtest09z.pdf
Tuning Database Configuration Parameters with iTuned:  
http://www.cs.duke.edu/~shivnath/papers/ituned.pdf

The second has a number of interesting graphs showing how changing two 
postgresql.conf parameters at a time interact with one another.  There's 
also a set of graphs comparing the default postgresql.conf performance 
with what you get using the guidelines suggested by an earlier version 
of http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and 
some of the documents on that section of the wiki.  Check out  page 10, 
the "M" column represents that manual tuning against the leftmost "D" 
which is the stock postgresql.conf settings.

I was a bit confused at first about the environment because of how the 
paper is organized, here's the bit that clarifies it:  "The database 
size with indexes is around 4GB. The physical memory (RAM) given to the 
database is 1GB to create a realistic scenario where the database is 4x 
the amount of RAM."  That RAM limit was constrained with a Solaris 
zone.  They multiplied the 1GB x 20% to get a standard "rule-based" 
setting of shared_buffers of 200MB (based on the guidelines on the wiki 
at the time--that suggestion is now 25%).

Note that much of the improvement shown in their better tuned versions 
there results from increases to shared_buffers (peaking at 40%=400MB) 
and work_mem beyond the recommendations given in the tuning guide.  That 
is unsurprising as those are aimed more to be reasonable starting values 
rather than suggested as truly optimal.  work_mem is particular is 
dangerous to suggest raising really high without knowing what types of 
queries are going to be run.  There's been plenty of commentary on this 
list suggesting optimal shared_buffers is closer to 50% of RAM than 25% 
for some workloads, so their results showing peak performance at 40% fit 
right in the middle of community lore.

I'm now in contact with the authors and asked them to let me know whey 
publish the entire post-optimization postgresql.conf, I'll let the list 
know when that's available.  I'm quite curious to see what the final 
settings that gave the best results looked like.

--
Greg Smith    greg@2ndQuadrant.com    Baltimore, MD