Обсуждение: Vacuum (table performance)
Hello Here we have a couple of tables with very high rotation: on one of them, rows get inserted all the time from 2 up to 15 per second, depending on application level of activity. Each row has a mean life of about 3 hours, when it gets copied to another table and deleted, along with a bunch of similar ones. This is done by a pgpsql function which is invoked on a regular basis. Following each execution of this function we run a vacuum on the table. However, we are seeing increasing execution times, not for the function but for the vacuum itself. Currently we are getting about 9 minutes for the vacuum, with a table of 45K records. This creeped up from a few seconds past week, when the table was reinitialized. However table size is approximately constant all the time. Are there other mechanisms to do online maintenance of a table? I must stress the word 'online' since this is a real time application which must be up all the time. thanks in advance cl. _________________________________________________________________ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail
"Claudio Lapidus" <clapidus@hotmail.com> writes:
> ... we are seeing increasing execution times, not for the
> function but for the vacuum itself.
Does a REINDEX of the table fix it?
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > >"Claudio Lapidus" <clapidus@hotmail.com> writes: > > ... we are seeing increasing execution times, not for the > > function but for the vacuum itself. > >Does a REINDEX of the table fix it? > Hmm, I'm looking at the documentation and it says that REINDEX acquires an exclusive lock on the table. Does this mean that during the reindex operation the table is unavailable for read/write by other processes? An alternative suggested right there is to drop and recreate an index, where -it says- CREATE INDEX would get a write lock on the table. Does this mean that during the create index operation the whole table is unavailable for write by other processes? thanks again cl. _________________________________________________________________ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
On Wed, Jun 25, 2003 at 08:16:42PM -0300, Claudio Lapidus wrote: > > Tom Lane <tgl@sss.pgh.pa.us> writes: > > > >"Claudio Lapidus" <clapidus@hotmail.com> writes: > >> ... we are seeing increasing execution times, not for the > >> function but for the vacuum itself. > > > >Does a REINDEX of the table fix it? > > Hmm, I'm looking at the documentation and it says that REINDEX acquires an > exclusive lock on the table. Does this mean that during the reindex > operation the table is unavailable for read/write by other processes? Yeah. > An alternative suggested right there is to drop and recreate an index, > where -it says- CREATE INDEX would get a write lock on the table. Does this > mean that during the create index operation the whole table is unavailable > for write by other processes? An alternative approach would be to create a second index indentical to the one in place and drop the first one. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Las cosas son buenas o malas segun las hace nuestra opinion" (Lisias)
"Claudio Lapidus" <clapidus@hotmail.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Does a REINDEX of the table fix it?
> Hmm, I'm looking at the documentation and it says that REINDEX acquires an
> exclusive lock on the table. Does this mean that during the reindex
> operation the table is unavailable for read/write by other processes?
Yes. At the moment, I am just asking this as a means of helping to
pinpoint where the problem is.
regards, tom lane
> > An alternative suggested right there is to drop and recreate an index, > > where -it says- CREATE INDEX would get a write lock on the table. Does >this > > mean that during the create index operation the whole table is >unavailable > > for write by other processes? > >An alternative approach would be to create a second index indentical to >the one in place and drop the first one. > OK, but what's the difference in creating a second index and dropping the original one first and recreate it? If I understood correctly the doc, I think the table will be unavailable for write in either case, is this right? cl. _________________________________________________________________ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus
"Claudio Lapidus" <clapidus@hotmail.com> writes: > Tom Lane <tgl@sss.pgh.pa.us> writes: >> >>"Claudio Lapidus" <clapidus@hotmail.com> writes: >> > ... we are seeing increasing execution times, not for the >> > function but for the vacuum itself. >> >>Does a REINDEX of the table fix it? >> > > Hmm, I'm looking at the documentation and it says that REINDEX > acquires an exclusive lock on the table. Does this mean that during > the reindex operation the table is unavailable for read/write by other > processes? > > An alternative suggested right there is to drop and recreate an index, > where -it says- CREATE INDEX would get a write lock on the table. Does > this mean that during the create index operation the whole table is > unavailable for write by other processes? > > thanks again > cl. At the very least you are going to need a write lock with the existing implementation of PostgreSQL. Unfortunately, indexes with the current version of PostgreSQL grow and grow indefinitely. Tables with a lot of turnover are especially problematic. However, with the new release that will be entering beta "real soon now" this will no longer be the case. Tom Lane has fixed the indexing code so that a vacuum will clean out the index as well as the tablespace. My guess is that Mr. Lane is fishing for a little bit of "beta testing" :). Jason
Tom Lane <tgl@sss.pgh.pa.us> wries: > >> Does a REINDEX of the table fix it? > Yes, positively it does. After reindexing all five indices of the table, vacuum times have dropped from minutes to seconds. Looking at pg_class, it is apparent that there was a cut in the number of tuples of about 30% for all indices, but the number of pages dropped by about 90%! Other people mentioned that this probably would get fixed in a forthcoming release. Is there anything we can do in the meantime? thanks cl. _________________________________________________________________ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus
"Claudio Lapidus" <clapidus@hotmail.com> writes: > Other people mentioned that this probably would get fixed in a > forthcoming release. Is there anything we can do in the meantime? REINDEXing on a regular basis is about all you can do. -Doug
"Claudio Lapidus" <clapidus@hotmail.com> writes:
> Other people mentioned that this probably would get fixed in a forthcoming
> release. Is there anything we can do in the meantime?
This is fixed (or at least greatly improved) in 7.4. Don't suppose
you'd care to run a test version and see if it actually prevents index
bloat for your usage pattern?
In earlier releases there is very little you can do about it; the only
solutions are regular reindexing or modifying your application to keep
the range of index keys from changing. The latter is generally
impractical...
regards, tom lane