Horton

Thread

  1. RE: dramatic slowdown in selects after pg has been runni ng for a while

    WOLF, PATRICK <patrick.wolf@aerojet.com> — 2000-07-21T19:36:35Z

    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.

  2. Re: dramatic slowdown. . .fixed by vacuum

    Joe Slag <jslag@visi.com> — 2000-07-21T19:42:19Z

    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.

  3. Re: Re: dramatic slowdown. . .fixed by vacuum

    Anthony E . Greene <agreene@pobox.com> — 2000-07-21T20:20:22Z

    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/>

  4. Re: Re: dramatic slowdown. . .fixed by vacuum

    Marc G. Fournier <scrappy@hub.org> — 2000-07-21T20:20:23Z

    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.

  5. Re: Re: dramatic slowdown. . .fixed by vacuum

    Andrew McMillan <andrew@catalyst.net.nz> — 2000-07-21T21:35:47Z

    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

  6. Third email on compilining 7.0.2 on Solaris 2.5.1

    ghaverla@freenet.edmonton.ab.ca — 2000-08-17T16:49:22Z

    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

  7. Re: Third email on compilining 7.0.2 on Solaris 2.5.1

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-19T02:04:25Z

    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

  8. Re: Third email on compilining 7.0.2 on Solaris 2.5.1

    ghaverla@freenet.edmonton.ab.ca — 2000-08-19T04:27:41Z

    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

  9. Re: Third email on compilining 7.0.2 on Solaris 2.5.1

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-19T04:39:48Z

    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