| Lists: | pgsql-novice |
|---|
| From: | Wim <wdh(at)belbone(dot)be> |
|---|---|
| To: | PgSQL Novice ML <pgsql-novice(at)postgresql(dot)org> |
| Subject: | Slow VACUUM |
| Date: | 2003-09-12 12:02:12 |
| Message-ID: | 1063368132.5540.7.camel@tyr.car.belbone.be |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-novice |
Hi guys,
I have a table that had 55 million records in it. I deleted about 15
million of them, because I had to free up disk space.
Yesterday, I started VACUUM. Now my problem is that it takes too long to
VACUUM the table: it is still running with my CPU in the red zone...
Is there a way to speed thing up?
PS I created 4 indexes on the tables. I cannot cancel the query... It
keeps running. Otherwise I thought of dropping the indexes, vacuum the
table and re-create the indexes...
Any advice from the gurus?
Cheers!
Wim De Hul
------------------------------------------------------------------------------
IP SERVICES SPECIALIST
Belgacom Carrier and Wholesale Business Unit (CBU)
------------------------------------------------------------------------------
AS6774
Ripe : WDH25-RIPE
reply-to : NOC{at}belbone{dot}be
peering : peering{at}belbone{dot}be
NOC Tel : +32 2/547.51.00
www.belgacom.be/carrier
------------------------------------------------------------------------------
When a program dies, what you need is a moment of serenity.
------------------------------------------------------------------------------
1:57pm up 3 days, 1:44, 1 user, load average: 1.27, 1.37, 1.42
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Wim <wdh(at)belbone(dot)be> |
| Cc: | PgSQL Novice ML <pgsql-novice(at)postgresql(dot)org> |
| Subject: | Re: Slow VACUUM |
| Date: | 2003-09-12 14:21:30 |
| Message-ID: | 10783.1063376490@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-novice |
Wim <wdh(at)belbone(dot)be> writes:
> PS I created 4 indexes on the tables. I cannot cancel the query... It
> keeps running. Otherwise I thought of dropping the indexes, vacuum the
> table and re-create the indexes...
Sure you can cancel it (assuming you're running any remotely up-to-date
version of Postgres). If you didn't issue the vacuum command from an
interactive psql, the easiest way is to identify the backend running the
command and send it a SIGINT ("kill -INT pid-of-backend"). Be sure to
use SIGINT and not any other signal.
If what you're trying to do is a VACUUM FULL, dropping the indexes
should help. Plain VACUUM I'm not so sure is worth the trouble.
Also you might try increasing VACUUM_MEM setting before you try again.
regards, tom lane