pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedauto vacuum, not working?
4+ messages / 4 participants
[nested] [flat]
* auto vacuum, not working?
@ 2012-01-13 12:08 Anibal David Acosta <aa@devshock.com>
2012-01-13 14:26 ` Re: auto vacuum, not working? Mario Weilguni <roadrunner6@gmx.at>
2012-01-15 12:09 ` Re: auto vacuum, not working? Greg Smith <greg@2ndQuadrant.com>
0 siblings, 2 replies; 4+ messages in thread
From: Anibal David Acosta @ 2012-01-13 12:08 UTC (permalink / raw)
To: pgsql-performance
Hi,
yesterday I delete about 200 million rows of a table (about 150GB of data),
after delete completes the autovacuum process start.
The autovacuum is running for about 11 hours but no space is released
Autovacuum parameters are with default values in postgresql.conf
The postgres version is 9.0.3
The pg activity reports:
select (now()-query_start) as duration, waiting, current_query from
pg_stat_activity where current_query ilike '%auto%'
10:42:19.829 f "autovacuum: VACUUM ANALYZE
public.myTable"
How can I release the space used by deleted rows? Without block the table.
Thanks!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: auto vacuum, not working?
2012-01-13 12:08 auto vacuum, not working? Anibal David Acosta <aa@devshock.com>
@ 2012-01-13 14:26 ` Mario Weilguni <roadrunner6@gmx.at>
2012-01-13 14:50 ` Re: auto vacuum, not working? Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 1 reply; 4+ messages in thread
From: Mario Weilguni @ 2012-01-13 14:26 UTC (permalink / raw)
To: pgsql-performance
Am 13.01.2012 13:08, schrieb Anibal David Acosta:
>
> Hi,
>
> yesterday I delete about 200 million rows of a table (about 150GB of
> data), after delete completes the autovacuum process start.
>
> The autovacuum is running for about 11 hours but no space is released
>
> Autovacuum parameters are with default values in postgresql.conf
>
> The postgres version is 9.0.3
>
> The pg activity reports:
>
> select (now()-query_start) as duration, waiting, current_query from
> pg_stat_activity where current_query ilike '%auto%'
>
> 10:42:19.829 f "autovacuum: VACUUM ANALYZE
> public.myTable"
>
> How can I release the space used by deleted rows? Without block the table.
>
> Thanks!
>
vacuum does not reclaim space, just marks tuples dead. You need vacuum full.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: auto vacuum, not working?
2012-01-13 12:08 auto vacuum, not working? Anibal David Acosta <aa@devshock.com>
2012-01-13 14:26 ` Re: auto vacuum, not working? Mario Weilguni <roadrunner6@gmx.at>
@ 2012-01-13 14:50 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 4+ messages in thread
From: Kevin Grittner @ 2012-01-13 14:50 UTC (permalink / raw)
To: Mario Weilguni <roadrunner6@gmx.at>; pgsql-performance
Mario Weilguni <roadrunner6@gmx.at> wrote:
>> yesterday I delete about 200 million rows of a table
>> How can I release the space used by deleted rows?
>> Without block the table.
> vacuum does not reclaim space, just marks tuples dead. You need
> vacuum full.
VACUUM FULL will lock the table, blocking all other access, and it
can run for quite a while. If you expect to be adding 200 million
new rows to the table in the foreseeable future, a regular VACUUM
(or autovacuum) will make that space available for reuse by that
table. The space won't show in the file system; it will still be
allocated to the database but available for new rows.
-Kevin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: auto vacuum, not working?
2012-01-13 12:08 auto vacuum, not working? Anibal David Acosta <aa@devshock.com>
@ 2012-01-15 12:09 ` Greg Smith <greg@2ndQuadrant.com>
1 sibling, 0 replies; 4+ messages in thread
From: Greg Smith @ 2012-01-15 12:09 UTC (permalink / raw)
To: pgsql-performance
On 01/13/2012 07:08 AM, Anibal David Acosta wrote:
>
> How can I release the space used by deleted rows? Without block the table.
>
>
The database can only reduce the size tables by returning space to the
operating system in one situation: there is free space at the very end
of the table. In that case, if it's possible to get a brief exclusive
lock on the table, it can shrink in size.
There are some programs available that reorganize table for goals like
this, without having any long-lasting locks on the tables. pg_reorg is
the most popular example: http://pgfoundry.org/projects/reorg/
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-01-15 12:09 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-13 12:08 auto vacuum, not working? Anibal David Acosta <aa@devshock.com>
2012-01-13 14:26 ` Mario Weilguni <roadrunner6@gmx.at>
2012-01-13 14:50 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-01-15 12:09 ` Greg Smith <greg@2ndQuadrant.com>
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