On 11/09/2012 04:28 PM, Lists wrote:
As I've spent a considerable amount of time trying to sort this out, I'm posting it for the benefit other users.
Having missed the earlier conversation....a couple comments:
I've experienced persistent, ongoing issues with autovacuum in a mixed read/write environment with midrange hardware (16 core Xeon, 128 GB RAM, 200 GB SATA3 6 Gb SSDs for disk I/O on RHEL6) using the defaults in the 9.1 RPMs provided by Postgres. (yum.postgresql.org)
Don't do that. Defaults are good for ensuring that PostgreSQL will start on the widest reasonable variety of systems. They are *terrible* for performance and are certainly wrong for the system you describe.
The cause of this is not yet determined. It may be related to the any or all of the combination of:A) extensive use of temp tables; B) extensive use of multiple dblink() calls in a single query;C) use of transactions, especially prepared transactions and multiple savepoints;D) concurrent use of pg_dump; E) use of numerous databases on a single server, average about 50;To offset this, we turned off autovacuum, and used an old script to vacuum the tables in the middle of the night when nobody was looking. Unfortunately, the vacuum script only vacuumed the "userland" tables and tremendous amounts of disk space were being wasted, particularly in the pg_attribute tables.
Bloat in pg_attribute would correlate with A) (or any constant creation/destruction of tables). You can vacuum and/or reindex the system tables if you are connected as the superuser but you are better off preventing bloat by appropriate adjustment of your configuration settings. However note that if you do frequent bulk creation/destruction of tables you could end up bloating the attribute table between vacuum runs and may need to periodically manually shrink it.
Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
