Re: [HACKERS] vacuum slowness

6.5beta has a much faster vacuumer when indexes are used. Please try
that when you can.

>
> Tom Lane writes:
> > Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > I just deleted all 50,000 rows from a table that has one int4 and one
> text
> > > field.
> >
> > > Why does vacuum take so long? If all the rows are superceeded, so no
> > > rows actually have to be moved, should it take so long for vacuum to
> > > run?
> >
> > Do you have any indexes on the table? I've noticed (and complained in
> > the past ;-)) that vacuuming a table takes unreasonably long if there
> > are a lot of dead index entries to be cleaned. It seems faster to drop
> > and recreate the index in a case like that.
>
> Hi everyone,
>
> I am working on a large project right now which involves the use of a
> table that has thousands of inserts and updates performed each day. (At
> the end of the day, about 20000 inserts have occured, and each inserted
> row gets modified 2 or 3 times) Vacuums take absolutely ages and
> unfortunately the system must run continuously 24 hours per day so I can't
> afford to have the table locked for ages while it is being vacuumed.
>
> I've played around with vacuum quite a bit, and I've found that if I do
> one huge vacuum every so often, it takes longer than if I do lots of
> vacuum's during the day, this way the tables are kept more 'compacted' and
> there is less moving around of data required, and so it runs a bit faster.
>
> As the number of days of new data stored increases, the size of the tables
> grows to the point where a vacuum can take 10 minutes or so, and this is
> unacceptable considering it occurs in a few seconds without indexes. To
> get around this, once every day, I grab entries which are in the active
> table that are older than two days, and move them into an archive table
> which never changes. This way, I can keep the active table small and do
> vacuums within a minute or so, allowing me to keep my software from
> waiting too long. I'd really like to avoid doing this though, because it
> causes complications - lately I've found that vacuuming is becoming a
> major hassle which I'd rather not have to do at all :)
>
> What I was wanting to know if there was a way of temporarily disabling
> indexes while the vacuum is occuring, and then update it all in one hit
> once the update is completely finished. This would be equivalent to
> dropping and recreating them, but I don't want to do that in case
> something dies during the vacuum and my tables are left without indexes on
> them.
>
> Or perhaps telling Postgres to do a partial vacuum, with a time limit set
> to say 20 seconds and it will do it in stages over the period of a day.
> This way the database can still run and we can keep the dbms cleaned. From
> what I understand, the new MVCC support in 6.5 will be able to do vacuum's
> in the background, or is this for the future?
>
> Also, I had a look at the src/commands/vacuum.c code, and had a bit of a
> read through it. One thing I wasn't sure about is the method it uses to
> move the rows around while it is doing the index. Lets say that we have
> 100 rows, and the first one is deleted and so is empty. Does every single
> row get moved back one, or does only one row get moved to fill in the
> empty gap?
>
> Is the vacuum code moving tons of rows around the table, causing the
> indexes to be updated lots of times and slowing things down?
>
>
> If someone could give me some hints about how to best handle my tables to
> get good vacuum times I would really appreciate it.
>
>
> btw, keep up the good work everyone, I've been following this mailing list
> and developing with Postgres since the days of pre-6.0 and I'm very
> impressed with all the great improvements that have been made to Postgres
> over the years!
>
> Thanks,
> Wayne
>
> ------------------------------------------------------------------------------
> Wayne Piekarski Tel: (08) 8221 5221
> Research & Development Manager Fax: (08) 8221 5220
> SE Network Access Pty Ltd Mob: 0407 395 889
> 222 Grote Street Email: wayne(at)senet(dot)com(dot)au
> Adelaide SA 5000 WWW: http://www.senet.com.au
>
>

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026