Database tuning at Duke

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxx    Baltimore, MD


--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux