pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedVacuum taking an age
6+ messages / 5 participants
[nested] [flat]
* Vacuum taking an age
@ 2008-01-03 12:48 Brian Modra <epailty@googlemail.com>
2008-01-03 13:37 ` Re: Vacuum taking an age Pascal Heraud <heraud@tatm.com>
2008-01-04 02:31 ` Re: Vacuum taking an age Scott Marlowe <scott.marlowe@gmail.com>
2008-01-04 02:33 ` Re: Vacuum taking an age Guido Neitzer <lists@event-s.net>
0 siblings, 3 replies; 6+ messages in thread
From: Brian Modra @ 2008-01-03 12:48 UTC (permalink / raw)
To: pgsql-admin
Hi,
I have a pretty "live" table: rows being inserted and updated more
than once 1 per second, though far, far more inserts than updates.
There are currently over 3 million rows.
It has not been vacuumed for months.
Now a vacuum on that table takes hours, and I have not let it complete
because it stays running into our daily busy time... but I've been
told its necessary because the table is slowing down.
I have begun a cron job which will do a daily analyze, and am thinking
of a weekly vacuum...
Please advise on the best way to keep this table maintained, even if
it means regularly taking the service offline early on Sunday
morning...
thanks in advance
Brian
--
Brian Modra Land line: +27 23 5411 462
Mobile: +27 79 183 8059
6 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Vacuum taking an age
2008-01-03 12:48 Vacuum taking an age Brian Modra <epailty@googlemail.com>
@ 2008-01-03 13:37 ` Pascal Heraud <heraud@tatm.com>
2 siblings, 0 replies; 6+ messages in thread
From: Pascal Heraud @ 2008-01-03 13:37 UTC (permalink / raw)
To: pgsql-admin
If you're using the version 8.1 and after, you should consider using the
auto-vacuum daemon that is the best way to do it:
http://www.postgresql.org/docs/8.1/static/maintenance.html
Pascal;
Brian Modra a écrit :
> Hi,
>
>
> I have a pretty "live" table: rows being inserted and updated more
> than once 1 per second, though far, far more inserts than updates.
>
> There are currently over 3 million rows.
>
> It has not been vacuumed for months.
>
> Now a vacuum on that table takes hours, and I have not let it complete
> because it stays running into our daily busy time... but I've been
> told its necessary because the table is slowing down.
>
> I have begun a cron job which will do a daily analyze, and am thinking
> of a weekly vacuum...
> Please advise on the best way to keep this table maintained, even if
> it means regularly taking the service offline early on Sunday
> morning...
>
> thanks in advance
> Brian
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Vacuum taking an age
2008-01-03 12:48 Vacuum taking an age Brian Modra <epailty@googlemail.com>
@ 2008-01-04 02:31 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-04 04:55 ` Re: Vacuum taking an age Brian Modra <epailty@googlemail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Scott Marlowe @ 2008-01-04 02:31 UTC (permalink / raw)
To: Brian Modra <epailty@googlemail.com>; +Cc: pgsql-admin
On Jan 3, 2008 6:48 AM, Brian Modra <epailty@googlemail.com> wrote:
> Hi,
>
>
> I have a pretty "live" table: rows being inserted and updated more
> than once 1 per second, though far, far more inserts than updates.
>
> There are currently over 3 million rows.
>
> It has not been vacuumed for months.
How many rows per second? 1? all of them? Kinda makes a difference.
If it was 1 a second updated for 3 months that's about 7million dead
rows. If it was all 3million, then that's 7million * 3million dead
rows, also know as a whole bunch of rows.
Either way, you probably have a table so terribly bloated that a
regular vacuum will not help you in terms of speeding it up. Regular
vacuums are like brushing your teeth three times a day. If you've
forgotten for three months, brushing them once isn't likely to fix all
the cavities you've got. Same thing here. You'll either need a
vacuum full or a cluster. Cluster is often faster. Or you can try
selecting everything into a temp table, truncating the real table, and
inserting the data back in. Truncation will remove all rows, dead or
otherwise. The advantage is that it's often faster to truncate /
reload than it is to vacuum full. If you have indexes, you might want
to drop them while re-inserting and then recreated them.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Vacuum taking an age
2008-01-03 12:48 Vacuum taking an age Brian Modra <epailty@googlemail.com>
2008-01-04 02:31 ` Re: Vacuum taking an age Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-01-04 04:55 ` Brian Modra <epailty@googlemail.com>
2008-01-04 18:16 ` Re: Vacuum taking an age Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 6+ messages in thread
From: Brian Modra @ 2008-01-04 04:55 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
Hi,
thanks for your reply.
The number of rows per second has been increasing rapidly, but its
averaging about 1 row per second, and a far smaller number of updates.
So maybe there are not such a huge number of dead rows. I hope that a
normal vacuum will clean it up.
Total number of rows is about 3 million.
Last night before I got your reply, I noticed that the number of
shared memory buffers was only 1000, so I increased shmmax and when I
restart the server next, its number of buffers will be 10000. The
server has 8GB of memory, so that will only be a small proportion of
its total memory.
I have not restarted postgres yet because a vacuum is still running.
Maybe I should kill that and restart postgres?
The reason I increased this is because I noticed if I did a partial
count of rows (e.g. those inserted with a timestamp after midnight
last night), then the first time takes about 17 seconds, and the
second time 1/4 second.
I started a vacuum on the table yesterday, and its still running. I
guess thats because the table is live. I am pretty sure that if I take
it offline, then the vacuum will complete relatively quickly. Am I
right? (I don't want to take it offline unless I really need to.)
On 04/01/2008, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Jan 3, 2008 6:48 AM, Brian Modra <epailty@googlemail.com> wrote:
> > Hi,
> >
> >
> > I have a pretty "live" table: rows being inserted and updated more
> > than once 1 per second, though far, far more inserts than updates.
> >
> > There are currently over 3 million rows.
> >
> > It has not been vacuumed for months.
>
> How many rows per second? 1? all of them? Kinda makes a difference.
>
> If it was 1 a second updated for 3 months that's about 7million dead
> rows. If it was all 3million, then that's 7million * 3million dead
> rows, also know as a whole bunch of rows.
>
> Either way, you probably have a table so terribly bloated that a
> regular vacuum will not help you in terms of speeding it up. Regular
> vacuums are like brushing your teeth three times a day. If you've
> forgotten for three months, brushing them once isn't likely to fix all
> the cavities you've got. Same thing here. You'll either need a
> vacuum full or a cluster. Cluster is often faster. Or you can try
> selecting everything into a temp table, truncating the real table, and
> inserting the data back in. Truncation will remove all rows, dead or
> otherwise. The advantage is that it's often faster to truncate /
> reload than it is to vacuum full. If you have indexes, you might want
> to drop them while re-inserting and then recreated them.
>
--
Brian Modra Land line: +27 23 5411 462
Mobile: +27 79 183 8059
6 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Vacuum taking an age
2008-01-03 12:48 Vacuum taking an age Brian Modra <epailty@googlemail.com>
2008-01-04 02:31 ` Re: Vacuum taking an age Scott Marlowe <scott.marlowe@gmail.com>
2008-01-04 04:55 ` Re: Vacuum taking an age Brian Modra <epailty@googlemail.com>
@ 2008-01-04 18:16 ` Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 6+ messages in thread
From: Andrew Sullivan @ 2008-01-04 18:16 UTC (permalink / raw)
To: pgsql-admin
On Fri, Jan 04, 2008 at 06:55:13AM +0200, Brian Modra wrote:
> I started a vacuum on the table yesterday, and its still running. I
> guess thats because the table is live. I am pretty sure that if I take
> it offline, then the vacuum will complete relatively quickly. Am I
> right? (I don't want to take it offline unless I really need to.)
No. The vacuum on that table is going to take a long time, period. See
upthread about not brushing teeth for 3 months.
Anyway, I can't believe a plain vacuum is going to help you here. I'd
perform CLUSTER or else dump the database and reload it. You're looking at
a service outage, I think.
A
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Vacuum taking an age
2008-01-03 12:48 Vacuum taking an age Brian Modra <epailty@googlemail.com>
@ 2008-01-04 02:33 ` Guido Neitzer <lists@event-s.net>
2 siblings, 0 replies; 6+ messages in thread
From: Guido Neitzer @ 2008-01-04 02:33 UTC (permalink / raw)
To: Brian Modra <epailty@googlemail.com>; +Cc: pgsql-admin
On 03.01.2008, at 05:48, Brian Modra wrote:
> I have a pretty "live" table: rows being inserted and updated more
> than once 1 per second, though far, far more inserts than updates.
Not that busy ;-)
> It has not been vacuumed for months.
Not good.
> Now a vacuum on that table takes hours, and I have not let it complete
> because it stays running into our daily busy time... but I've been
> told its necessary because the table is slowing down.
>
> I have begun a cron job which will do a daily analyze, and am thinking
> of a weekly vacuum...
> Please advise on the best way to keep this table maintained, even if
> it means regularly taking the service offline early on Sunday
> morning...
Two things you can consider:
1. Cluster the table with one of the indexes. This will be really
fast, but is not transaction-safe as far as I remember for 8.2.x.
2. Use autovaccum to vacuum / analyze your database all the time. That
will keep the size small and the stats up to date.
cug
--
http://www.event-s.net
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2008-01-04 18:16 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-03 12:48 Vacuum taking an age Brian Modra <epailty@googlemail.com>
2008-01-03 13:37 ` Pascal Heraud <heraud@tatm.com>
2008-01-04 02:31 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-04 04:55 ` Brian Modra <epailty@googlemail.com>
2008-01-04 18:16 ` Andrew Sullivan <ajs@crankycanuck.ca>
2008-01-04 02:33 ` Guido Neitzer <lists@event-s.net>
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