From: Andrus <kobruleht2@hot.ee>
To: Richard Huxton <dev@archonet.com>
To: PFC <lists@peufeu.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hash join on int takes 8..114 seconds
Date: Fri, 21 Nov 2008 21:10:21 +0200
Message-ID: <8D7F64CF7F614E13AC554D6CF86F39B2@andrusnotebook> (raw)
In-Reply-To: <op.ukzdioivcigqcu@soyouz>
References: <gg1sv3$27fr$1@news.hub.org>
<49253FE1.3070706@archonet.com>
<0205611B528C4C2CBA99AFC64E57D6E8@andrusnotebook>
<op.ukw669uvcigqcu@soyouz>
<75A918DFCF25470FAF96B3FE3D25A38B@andrusnotebook>
<4926E2B2.9050202@archonet.com>
<8A78204AA6D44C04B053E6412B244E13@andrusnotebook>
<op.ukzdioivcigqcu@soyouz>
>> How to vacuum full pg_shdepend automatically so that other users can
>> work at same time ?
>
> Your table is horribly bloated.
> You must use VACUUM FULL + REINDEX (as superuser) on it, however
> unfortunately, it is blocking.
> Therefore, you should wait for sunday night to do this, when noone will
> notice.
Shops are closed late night for a short time, including sunday night.
This time may be shorter than time required to complete VACUUM command.
I discovered vacuum_cost_delay=2000 option. Will this remove blocking issue
and allow vacuum full to work ?
> Meanwhile, you can always VACUUM it (as superuser) and REINDEX it.
I expect that autovacuum does this automatically.
> And while you're at it, VACUUM FULL + reindex the entire database.
> To avoid such annoyances in the future, you should ensure that autovacuum
> runs properly ; you should investigate this. If you use a cron'ed VACUUM
> that does not run as superuser, then it will not be able to VACUUM the
> system catalogs, and the problem will come back.
autovacuum is turned on in postgresql.conf file
log file shows a lot of messages every day that database is vacuumed.
I assume that it is running as user postgres.
I do'nt understand how autovacuum can avoid this: it does not perform vacuum
full so pg_shdepend ja my tables become
bloated again and again.
Andrus.
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-performance@postgresql.org
Cc: kobruleht2@hot.ee, dev@archonet.com, lists@peufeu.com
Subject: Re: Hash join on int takes 8..114 seconds
In-Reply-To: <8D7F64CF7F614E13AC554D6CF86F39B2@andrusnotebook>
* 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