Re: degenerate performance on one server of 3

On 6/4/09 4:31 AM, "Erik Aronesty" <erik(at)q32(dot)com> wrote:

>> read the entry on pg_stat_all_tables
>
> yeah, it's running ... vacuum'ed last night
>
> it's odd, to me, that the performance would degrade so extremely
> (noticeably) over the course of one year on a table which has few
> insertions, no deletions,and daily updates of an integer non null
> column (stock level).
>
> is there some way to view the level of "bloat that needs full" in each
> table, so i could write a script that alerts me to the need of a
> "vacuum full" without waiting for random queries to "get slow"?
>
> looking at the results of the "bloat query", i still can't see how to
> know whether bloat is getting bad in an objective manner.
>
> http://pgsql.tapoueh.org/site/html/news/20080131.bloat.html
>
> on the machines that perform well 30MB of bloat seems to be fine, and
> i don't knwo what the badly performing table's bloat was, since i
> already vac'ed it.
>

Updates require space as well, the full MVCC process requires that the
values for all open transactions exist, so updates are not overwrites, but
copies (of the whole tuple in the worst case, of just the column(s) in the
best).

For heavily updated tables, adjusting the table (and maybe index) fillfactor
will help prevent bloat, by adding a constant amount of extra space for temp
data for updates.

See ALTER TABLE and CREATE TABLE (and the Index variants).

ALTER TABLE foo SET (fillfactor=90);

This will leave on average, 10% of every 8k block empty and allow updates to
columns to more likely live within the same block.

Indexes have default fillfactor set to 90, I believe.

> .......
>
> there is one table i have with 2GB of bloat ... but it's performance
> (since all querys are on a clustered index) is more than adequate.
> also, it's so big i'm afraid my server would be down for 24 hours on
> that on vacuum
>
> it's a rolling "cookie table" with millions of random-id'ed entries
> that expire after a few months ... i think i'm going to copy the most
> recent 6 months worth of rows to a new table, then just drop the old
> one..... seems easier to me.than the scary unknown of running "vaccum
> full", and then i won't have to worry about the system being down on a
> table lock.

Creating a new table as a select from the old and renaming, OR doing a
CLUSTER and REINDEX is almost always faster than VACUUM FULL for such large
tables. But there are different implications on how long other queries are
locked out of access to the table. CLUSTER will generally lock out other
queries for a long time, but the end result (especially combined with a
reasonable fillfactor setting) ends up best for long term performance and
reduction in bloat.

> Seems like "VACUUM FULL" could figure out to do that too depending on
> the bloat-to-table-size ratio ...
>
> - copy all rows to new table
> - lock for a millisecond while renaming tables
> - drop old table.
>
> Locking a whole table for a very long time is scary for admins.
>

You can do the above manually in a single transaction, however any updates
or inserts during that time may be lost.

>
> - erik
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>