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