Re: [HACKERS] vacuum slowness - Mailing list pgsql-hackers

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

> 
> Tom Lane writes:
> > Bruce Momjian <maillist@candle.pha.pa.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@senet.com.au
> Adelaide SA 5000                              WWW:     http://www.senet.com.au
> 
> 


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