Skip site navigation (1) Skip section navigation (2)

Re: Inconsistent performance

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Joseph Bove <jbove(at)vetstar(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Inconsistent performance
Date: 2003-09-16 00:39:08
Message-ID: Pine.LNX.4.33.0309151836110.3077-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, 15 Sep 2003, scott.marlowe wrote:

> On Mon, 15 Sep 2003, Joseph Bove wrote:
> 
> > Stephan,
> > 
> > I've run explain analyze a number of times and have gotten results between 
> > 5.5 and 7.5 seconds
> > 
> > Attached is a typical output
> > 
> >   QUERY PLAN
> > -------------------------------------
> >   Aggregate  (cost=9993.92..9993.92 rows=1 width=0)
> >                    (actual time=7575.59..7575.59 rows=1 loops=1)
> > ->  Seq Scan on vetapview  (cost=0.00..9771.34 rows=89034 width=0)
> >                                         (actual time=0.06..7472.20 
> > rows=88910 loops=1)
> >   Total runtime: 7575.67 msec
> > (3 rows)
> > 
> > The only things changing are the actual time. The costs are constant.
> > 
> > The relpages from pg_class for vetapview (the table in question) is 8881.
> > 
> > At the end of this message is the exhaustive contents of postgresql.conf. 
> > The only settings I have attempted tuning are as follows:
> > 
> > tcpip_socket = true
> > max_connections = 100
> > shared_buffers = 5000
> > sort_mem = 8192
> > fsync = false
> 
> A couple of things.
> 
> 1:  Is there an index on the parts of the query used for the where clause?
> 2:  What is your effect_cache_size set to?  It needs to be set right for 
> your postgresql server to be able to take advantage of the kernel's cache 
> (i.e. use an index scan when the kernel is likely to have that data in 
> memory.)

Sorry, that should be effective_cache_size, not effect_cache_size.  It's 
set in 8k blocks and is usually about how much buffer / cache you have 
left over after the machines "settles" after being up and running for a 
while.  Fer instance, on my server, I show 784992K cache, and 42976K buff 
under top, so, that's 827968k/8k=103496 blocks.  Note that if you've 
recompiled you may have somehow set block size larger, but installations 
with postgresql block sizes ~=8k are pretty uncommon, and you'd know if 
you had done that, so it's probably 8k blocks.


In response to

pgsql-performance by date

Next:From: Brian HirtDate: 2003-09-16 00:39:53
Subject: Re: Inconsistent performance
Previous:From: scott.marloweDate: 2003-09-16 00:22:34
Subject: Re: Inconsistent performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group