From: Steve Crawford <scrawford@pinpointresearch.com>
To: Campbell, Lance <lance@uiuc.edu>
Cc: posgres support <pgsql-admin@postgresql.org>
Subject: Re: Enhancement request
Date: Fri, 30 Nov 2007 12:42:22 -0800
Message-ID: <475075AE.1080108@pinpointresearch.com> (raw)
In-Reply-To: <B10E6810AC2A2F4EA7550D072CDE8760197E2C@SAB-FENWICK.sab.uiuc.edu>
References: <B10E6810AC2A2F4EA7550D072CDE8760197E2C@SAB-FENWICK.sab.uiuc.edu>
Campbell, Lance wrote:
>
> Could you please add to your to do list a schema parameter for vacuum?
>
>
>
> Example:
>
>
>
> VACUUM SCHEMA xyz;
>
>
>
> PostgreSQL would get a list of all of the tables found in the schema.
> It would then loop through vacuuming each table in the schema.
>
>
>
> I found today that I did not have max_fsm_pages set high enough in
> order to vacuum the entire database. I vacuum daily to avoid
> problems. After realizing that I cannot vacuum the entire database
> now, I then had to do it at the table level. I had to go through all
> 13 of my schemas and list each table I found into an SQL vacuum
> script. This was a real waist of time. I will eventually restart
> PostgreSQL with an increased value for max_fsm_pages so this will not
> be an issue.
>
If I understand how PG works, I don't believe this is a problem. Just
run vacuum verbose analyze to determine the required value, set
max_fsm_pages to that value, restart and vacuum. I believe the issue
with too-small max_fsm_pages is that as vacuum locates reusable space,
it simply runs out of room to save that information thus PG bloats the
table instead of making use of available space located by vacuum.
Setting max_fsm_pages to a proper value and running vacuum will address
_future_ bloat - and you may even eventually fill all the bloat back in.
But the only certain way to remove the bloat that has occurred due to
insufficient max_fsm_pages is to vacuum full (or cluster as appropriate
- cluster can be many times faster than vacuum full).
Having said that, I would also find this feature occasionally useful but
would think something akin to pg_dump's options would be more useful
with both -n and -t allowing wildcards.
Cheers,
Steve
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scrawford@pinpointresearch.com, lance@uiuc.edu
Subject: Re: Enhancement request
In-Reply-To: <475075AE.1080108@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox