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
Import Notes
Resolved by subject fallback
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. Doesthis
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
Import Notes
Resolved by subject fallback
"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
Import Notes
Resolved by subject fallback
"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
Import Notes
Reply to msg id not found: ClaudioLapidus'smessageofFri27Jun2003093641-0300
"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