On 09/11/2011 12:02 PM, Marti Raudsepp wrote:
Which brings me to another important point: don't do lots of small write transactions, SAVEPOINTs or PL/pgSQL subtransactions. Besides being inefficient, they introduce a big maintenance burden.
I'd like to second this. Before a notable application overhaul, we were handling about 300-million transactions per day (250M of that was over a 6-hour period). To avoid the risk of mid-day vacuum-freeze, we disabled autovacuum and run a nightly vacuum over the entire database. And that was *after* bumping autovacuum_freeze_max_age to 600-million.
You do *not* want to screw with that if you don't have to, and a setting of 600M is about 1/3 of the reasonable boundary there. If not for the forced autovacuums, a database with this much traffic would be corrupt in less than a week. We've managed to cut that transaction traffic by 60%, and it greatly improved the database's overall health.
-- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 sthomas@xxxxxxxxx ______________________________________________ See http://www.peak6.com/email-disclaimer/ for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
