Kevin Grittner wrote: > We have a 406GB table where 304GB is in one table. The next two tables > are 57GB and 40GB. Inserts to these three tables are constant during the > business day, along with inserts, updates, and very few deletes to the > other tables. Database modifications are few and scattered at night and > on weekends. Virtually all queries are during the business day. The > large tables are "insert only" except for a weekend delete of the oldest > one week of data, to keep a rolling set of just over a year. (No, we > really don't want to go to weekly partitions, if it can be avoided.) > > Autovacuum is enabled with very aggressive settings, to cover small > tables, including one with about 75 rows that can be updated 100 or more > times per second. Even with these settings there is zero chance of any > table of even moderate size hitting the autovacuum threshold between our > scheduled vacuums. When we tried doing a nightly vacuum analyze starting > at the end of business day, it ran well into the next day, and the users > complained of slowness until it stopped. We changed to a weeknight vacuum > analyze of the volatile tables which aren't in the big three, and a vacuum > analyze of the entire database right after the weekly delete.
Sounds like you would be served by setting those specific tables to a lower vacuum scale factor (keeping a more normal default for the rest of the tables), and having a non-zero vacuum delay setting (to avoid excessive I/O consumption). Have you tried that? The problem you would still have with 8.2 is that while one of these tables is being vacuumed the rest won't be vacuumed at all. In 8.3 the other tables can still be vacuumed regularly with the big vacuum still running (a feature I dubbed "multiple workers", but we're still waiting to know what name the marketing guys are gonna use). > In terms of our autovacuum settings, we have several different types of > databases, and in all of them we seem to do well with these changes from > the 8.2 defaults, combined with (except for the above configuration) a > nightly database vacuum: > > autovacuum_naptime = 10s Another change in 8.3 is that the naptime is per-database, i.e. the time between two consecutive autovac runs on a database. So with a setting of 10s, if you have 10 database there will be one autovac run per second, whereas on 8.2 there would be one autovac each 10 seconds (unless you run out of worker slots). > Oh, the tiny, high-update tables occasionally bloat to hundreds or > thousands of pages because of long-running transactions, so we schedule > a daily cluster on those, just to keep things tidy. If you can afford the cluster then there's no problem. I don't expect that to change in 8.3. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC "Before you were born your parents weren't as boring as they are now. They got that way paying your bills, cleaning up your room and listening to you tell them how idealistic you are." -- Charles J. Sykes' advice to teenagers ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
