Search Postgresql Archives

Re: Unexpectedly high disk space usage

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

 



Lists <lists@xxxxxxxxxxxxxxxxxx> writes:
>   pg_catalog.pg_attribute                                  | 36727480320

Ouch.

> Our current process is that every night in the middle of the night, a 
> script connects to each database on each server and runs a query to get 
> all tables in each database and, for each, run
> "VACUUM ANALYZE $table"
> for each table in the database.
> (note: there is a database for the "postgres" user on each DB server) 
> The script is a remnant from PG 8.x days, so am I missing something 
> fundamental about 9.x? I will note that autovacuum is off ...

So you've turned off autovacuum, and are carefully not vacuuming the
system catalogs.  That's your problem all right.  Is there a
particularly good reason why this script isn't a one-liner "VACUUM"?
Are you sure that once-a-day vacuuming is sufficient, even if it was
covering the system catalogs?  If you've managed to bloat pg_attribute
to 36GB, I suspect you've got enough churn (perhaps from temp tables)
that you really need the catalogs vacuumed more often.

My advice is dump, reload, and *don't* turn off autovacuum.

> ... because it 
> occasionally causes transactions and queries to hang when an update 
> causes a vacuum mid-day, effectively taking us offline randomly.

I suspect this claim is based on ancient and no longer very relevant
experience.

			regards, tom lane


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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux