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

Re: Configuration Recommendations

On 04/23/2012 10:56 PM, Jan Nielsen wrote:
We are planning to rebuild our production 50GB PG 9.0 database serving
our application platform on the new hardware below. The web-applications
are 80/20 read/write and the data gateways are even mix 50/50
read/write; one of the gateways nightly exports & imports ~20% of our

With enough RAM to hold the database, but that much churn in the nightly processing, you're most likely to run into VACUUM issues here. The trigger point for autovacuum to kick off is at just around 20%, so you might see problems come and go based on the size of the changed set. You might consider making your own benchmark test out of a change like the gateway introduces. Consider doing your own manual VACUUM or maybe even VACUUM FREEZE cleanup in sync with the nightly processing if you want that to be predictable.

If there are "obviously correct" choices in PG configuration, this would
be tremendously helpful information to me. I'm planning on using pgbench
to test the configuration options.

The info at http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server is as useful a checklist for getting started as any. Note that pgbench is a very insensitive tool for testing configuration changes usefully. Results there will bounce around if you change shared_buffers and checkpoint_segments, but not much else. And even the changes that test positive with it don't necessarily translate into better real-world performance. For example, you might set shared_buffers to 8GB based on pgbench TPS numbers going up as it increases, only to find that allows way too much memory to get dirty between a checkpoint in production--resulting in slow periods on the server.

And many of the more interesting and tricky parameters to try and tweak in production, such as work_mem, don't even matter to what pgbench does. It's easy to get lost trying pgbench tests without making clear forward progress for weeks. Once you've validated the hardware seems to be delivering reasonable performance, consider running your own more application-like benchmarks instead.

Greg Smith   2ndQuadrant US    greg@xxxxxxxxxxxxxxx   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com

Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:

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

Powered by Linux