Thread: RE: dramatic slowdown in selects after pg has been runni ng for a while
RE: dramatic slowdown in selects after pg has been runni ng for a while
From
patrick.wolf@Aerojet.com (WOLF, PATRICK)
Date:
Try running vacuum on the table or the database. Here's an excerpt from the
man on vacuum:
Description
VACUUM serves two purposes in Postgres as both a means to reclaim storage
and also a means to collect information for the optimizer.
VACUUM opens every class in the database, cleans out records from rolled
back transactions, and updates statistics in the system
catalogs. The statistics maintained include the number of tuples and number
of pages stored in all classes.
VACUUM ANALYZE collects statistics representing the disbursion of the data
in each column. This information is valuable when several
query execution paths are possible.
Running VACUUM periodically will increase the speed of the database in
processing user queries.
Patrick C. Wolf
Test Manager
Aerojet
Socorro Plant
-----Original Message-----
From: Joe Slag [mailto:jslag@visi.com]
Sent: Friday, July 21, 2000 1:05 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] dramatic slowdown in selects after
pg has been running for a while
I'm evaluating pg for use in my company, and have run into a
bit of a snag.
One of the tests I've been running is a loop of 10,000
"select *
from foo" statements from a perl program, where foo is:
Table "foo"
Attribute | Type | Modifier
-----------+---------+----------
bar | integer |
zag | text |
When I initially ran this test on my workstation (500 mhz
PIII, 128 meg
ram, debian 2.2 w/2.2.16 kernel) the whole process took
around
10 seconds. After getting results from my select test, I
did 10,000
updates (which took an average of 37 seconds), and then
deleted the rows I'd
updated (from psql).
Now, when I rerun the "select" test (against the same data
that was
there before the updates), it takes forever - results have
varied from 300-some seconds to over 700.
To make sure that the whole pg process wasn't screwed up, I
created another
similar table and ran my 10,000 select script against it -
and results are
back down to 10 seconds. So, it seems that somewhere in the
process of
running a bunch of updates to "foo" (and deleteing them)
things have
become screwed up.
What could be slowing selects against this table down, and
how would
I proceed to investigate the matter further? I've been
reading through
the pg docs, and haven't seen much performance monitoring
other than
"explain" (which says exactly the same thing about both the
fast and
slow tables). Is there a log somewhere, or a command that
would further
show me what's going on?
TIA
Joe Slag
Wagpaw, inc.
On Fri, Jul 21, 2000 at 12:36:35PM -0700, WOLF, PATRICK wrote:
> Try running vacuum on the table or the database. Here's an excerpt from the
> man on vacuum:
>
[snip]
>
>
Thanks to all who responded. I vacuumed out foo, and sure enough
the select time is down to 10 seconds again.
I see in the docs the suggestion:
We recommend that active production databases be VACUUMM-ed nightly
Is this how people tend to do their vacuuming? Does anyone do programmatic
vacuums instead of / in addition to a nightly run? Is vacuuming mainly
necessary after big deletes, or are there other common situations
requiring it?
Joe Slag
Wagpaw, Inc.
On Fri, 21 Jul 2000, Joe Slag wrote: > On Fri, Jul 21, 2000 at 12:36:35PM -0700, WOLF, PATRICK wrote: > > Try running vacuum on the table or the database. Here's an excerpt from the > > man on vacuum: > > > [snip] > > > > > > Thanks to all who responded. I vacuumed out foo, and sure enough > the select time is down to 10 seconds again. > > I see in the docs the suggestion: > > We recommend that active production databases be VACUUMM-ed nightly > > Is this how people tend to do their vacuuming? Does anyone do programmatic > vacuums instead of / in addition to a nightly run? Is vacuuming mainly > necessary after big deletes, or are there other common situations > requiring it? UPDATEs are a combination of 'INSERT new tuple/mark old as DELETEd', so for each UPDATE, you are adding one more tuple to the table but not removing anything. VACUUM removes that DELETEd tuple.
On 21 Jul 2000 21:42 Joe Slag wrote: >I see in the docs the suggestion: > > We recommend that active production databases be VACUUMM-ed nightly > >Is this how people tend to do their vacuuming? Does anyone do programmatic >vacuums instead of / in addition to a nightly run? Is vacuuming mainly >necessary after big deletes, or are there other common situations >requiring it? I run vacuum as part of a cron job each month. My databases are small and 90% of the activity is SELECTs. -- Anthony E. Greene <agreene@pobox.com> <http://www.pobox.com/~agreene/> PGP Key: 0x6C94239D/7B3D BD7D 7D91 1B44 BA26 C484 A42A 60DD 6C94 239D Linux. The choice of a GNU Generation. <http://www.linux.org/>
Joe Slag wrote:
>
> I see in the docs the suggestion:
>
> We recommend that active production databases be VACUUMM-ed nightly
>
> Is this how people tend to do their vacuuming? Does anyone do programmatic
> vacuums instead of / in addition to a nightly run? Is vacuuming mainly
> necessary after big deletes, or are there other common situations
> requiring it?
I run vacuum more often than daily on a few tables which have frequent
updates and where I can handle the table lock for the necessary length
of time.
Cheers,
Andrew
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew@cat-it.co.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
Well, I am slowly making headway on my own, but it would be nice to know if this hasn't already been gone through and a patch was forthcoming. :-) I added a line to config.h in the section on vsnprintf which did a #include <varargs.h> This got past the initial compile problem I was having. How other architectures can include a vsnprintf() function without including varargs.h I don't know. Anyway, the compile goes quite a ways and dies, again on a varargs problem. A module assumes va_start takes 2 arguments, when my header files say only 1. I guess I have more fun to come. I'm still hoping someone has gone through this, but if not I'll come up with some diffs later. Gord Matter Realisations http://www.materialisations.com/ Gordon Haverland, B.Sc. M.Eng. President 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7 780/481-8019 ghaverla @ freenet.edmonton.ab.ca
ghaverla@freenet.edmonton.ab.ca writes:
> I added a line to config.h in the section on vsnprintf
> which did a #include <varargs.h>
> This got past the initial compile problem I was having.
> How other architectures can include a vsnprintf() function
> without including varargs.h I don't know. Anyway, the
> compile goes quite a ways and dies, again on a varargs
> problem. A module assumes va_start takes 2 arguments,
> when my header files say only 1.
ANSI C says va_start() takes 2 args. <varargs.h> (on those platforms
that have it at all) defines an older, non-ANSI-compliant version of
the va_foo macros. What you want to be including is <stdarg.h>.
Dunno why src/include/c.h is not including that automatically on your
platform, but that's what to look at.
I've suspected for some time that the conditional include of <varargs.h>
near the bottom of c.h is dead code, if not actively pernicious. But
without access to a platform where
#if defined(sun) && defined(__sparc__) && !defined(__SVR4)
applies, I can't be sure whether to rip it out or not.
regards, tom lane
On Fri, 18 Aug 2000, Tom Lane wrote: > ghaverla@freenet.edmonton.ab.ca writes: [ ... ] > ANSI C says va_start() takes 2 args. <varargs.h> (on those platforms > that have it at all) defines an older, non-ANSI-compliant version of > the va_foo macros. What you want to be including is <stdarg.h>. > Dunno why src/include/c.h is not including that automatically on your > platform, but that's what to look at. I found that out some time today. > I've suspected for some time that the conditional include of <varargs.h> > near the bottom of c.h is dead code, if not actively pernicious. But > without access to a platform where > #if defined(sun) && defined(__sparc__) && !defined(__SVR4) > applies, I can't be sure whether to rip it out or not. Well, I have a Sun Ultra Enterprise 2 (2 166 MHz UltraSPARCs) running Solaris 2.5.1. If that qualifies as one of these platforms, ... It certainly is a sun with a sparc. I thought 2.5.1 was supposed to be SvR4, but the Open-BSD people tell me it's a dinosaur (I had some compile fun with Open-SSH). If there are some tests you want me to run, I can spend some time playing with it. Gord Matter Realisations http://www.materialisations.com/ Gordon Haverland, B.Sc. M.Eng. President 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7 780/481-8019 ghaverla @ freenet.edmonton.ab.ca
ghaverla@freenet.edmonton.ab.ca writes:
>> Dunno why src/include/c.h is not including that automatically on your
>> platform, but that's what to look at.
> I found that out some time today.
FWIW, in current sources <stdarg.h> is included *unconditionally* by
c.h, so your problem should go away in 7.1. (This may mean that any
remaining not-quite-ANSI platforms will crash and burn ...) In the
meantime you might check how things work for you if you change c.h to
include <stdarg.h> unconditionally.
>> I've suspected for some time that the conditional include of <varargs.h>
>> near the bottom of c.h is dead code, if not actively pernicious. But
>> without access to a platform where
>> #if defined(sun) && defined(__sparc__) && !defined(__SVR4)
>> applies, I can't be sure whether to rip it out or not.
> Well, I have a Sun Ultra Enterprise 2 (2 166 MHz UltraSPARCs)
> running Solaris 2.5.1. If that qualifies as one of these
> platforms, ...
Evidently not, else your hand addition of <varargs.h> wouldn't have
changed anything since it'd already have been included. I'm guessing
that your compiler predefines __SVR4.
regards, tom lane