FAQ
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
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.

Search Discussions

  • Joe Slag at Jul 21, 2000 at 7:42 pm

    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.
  • The Hermit Hacker at Jul 21, 2000 at 8:22 pm

    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.
  • Anthony E . Greene at Jul 21, 2000 at 8:22 pm

    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/>
  • Andrew McMillan at Jul 21, 2000 at 10:05 pm

    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
  • Ghaverla at Aug 17, 2000 at 4:49 pm
    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
  • Tom Lane at Aug 19, 2000 at 2:04 am

    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
  • Ghaverla at Aug 19, 2000 at 4:27 am

    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
  • Tom Lane at Aug 19, 2000 at 4:39 am

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJul 21, '00 at 7:37p
activeAug 19, '00 at 4:39a
posts9
users7
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase