Performance question

Поиск
Список
Период
Сортировка
Искать

Performance question

От:
Bob Smith <bsmith@h-e.com>
Дата:

When I execute a query on a new connection, the performance is many 
times slower than if the query is repeated.  In other words, if I start 
psql, execute the query, then repeat it immediately, the second time it 
takes only about 20% as long to execute.  Now here's the confusing 
part, if I exit psql then start it up again, the same thing will occur 
on the new connection as well, the first execution takes 5x as long 
again.  I don't understand this, it would make sense to me that the 
second execution being faster is due to disk caching on the server, but 
then why is it slower again on every new connection?  Disk caching 
should benefit all current and new connections until the cache is 
flushed, which on this server shouldn't happen for a long time, the 
load is light and it has lots of RAM.  Is Postgres doing some kind of 
caching itself that lasts only for the life of one backend process?  If 
so, is there any way to make this caching persistent across backends?

Server particulars:

Postgres 7.2.1, Mac OS X Server 10.1.5, dual 1GHz CPUs, 1.5GB memory

Thanks!

Bob Smith
Hammett & Edison, Inc.
bsmith@h-e.com

Followup Re: Performance question

От:
Bob Smith <bsmith@h-e.com>
Дата:

OK, I seem to have fixed my own problem here again, sorry.  I restarted 
the postmaster, now _all_ queries are about 10x faster, and the first 
execution on a new connection is no longer significantly slower than 
the second.  The server (and the original postmaster) had been up for:

11:18AM  up 162 days, 17:48, 3 users, load averages: 0.57, 0.51, 0.51

Maybe I should have a cron script restart postmaster every now and 
then, like once a week?

Bob

On Wednesday, Mar 5, 2003, at 10:11 US/Pacific, Bob Smith wrote:

>
> When I execute a query on a new connection, the performance is many 
> times slower than if the query is repeated.  In other words, if I 
> start psql, execute the query, then repeat it immediately, the second 
> time it takes only about 20% as long to execute.  Now here's the 
> confusing part, if I exit psql then start it up again, the same thing 
> will occur on the new connection as well, the first execution takes 5x 
> as long again.  I don't understand this, it would make sense to me 
> that the second execution being faster is due to disk caching on the 
> server, but then why is it slower again on every new connection?  Disk 
> caching should benefit all current and new connections until the cache 
> is flushed, which on this server shouldn't happen for a long time, the 
> load is light and it has lots of RAM.  Is Postgres doing some kind of 
> caching itself that lasts only for the life of one backend process?  
> If so, is there any way to make this caching persistent across 
> backends?
>
> Server particulars:
>
> Postgres 7.2.1, Mac OS X Server 10.1.5, dual 1GHz CPUs, 1.5GB memory
>
> Thanks!
>
> Bob Smith
> Hammett & Edison, Inc.
> bsmith@h-e.com
>
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Re: Followup Re: Performance question

От:
Jodi Kanter <jkanter@virginia.edu>
Дата:
Is restarting postmaster on a regular basis necessary for performance?

----- Original Message ----- 
From: "Bob Smith" 
To: "pgsql-admin" 
Sent: Wednesday, March 05, 2003 2:20 PM
Subject: Followup Re: [ADMIN] Performance question


> 
> OK, I seem to have fixed my own problem here again, sorry.  I restarted 
> the postmaster, now _all_ queries are about 10x faster, and the first 
> execution on a new connection is no longer significantly slower than 
> the second.  The server (and the original postmaster) had been up for:
> 
> 11:18AM  up 162 days, 17:48, 3 users, load averages: 0.57, 0.51, 0.51
> 
> Maybe I should have a cron script restart postmaster every now and 
> then, like once a week?
> 
> Bob
> 
> On Wednesday, Mar 5, 2003, at 10:11 US/Pacific, Bob Smith wrote:
> 
> >
> > When I execute a query on a new connection, the performance is many 
> > times slower than if the query is repeated.  In other words, if I 
> > start psql, execute the query, then repeat it immediately, the second 
> > time it takes only about 20% as long to execute.  Now here's the 
> > confusing part, if I exit psql then start it up again, the same thing 
> > will occur on the new connection as well, the first execution takes 5x 
> > as long again.  I don't understand this, it would make sense to me 
> > that the second execution being faster is due to disk caching on the 
> > server, but then why is it slower again on every new connection?  Disk 
> > caching should benefit all current and new connections until the cache 
> > is flushed, which on this server shouldn't happen for a long time, the 
> > load is light and it has lots of RAM.  Is Postgres doing some kind of 
> > caching itself that lasts only for the life of one backend process?  
> > If so, is there any way to make this caching persistent across 
> > backends?
> >
> > Server particulars:
> >
> > Postgres 7.2.1, Mac OS X Server 10.1.5, dual 1GHz CPUs, 1.5GB memory
> >
> > Thanks!
> >
> > Bob Smith
> > Hammett & Edison, Inc.
> > bsmith@h-e.com
> >
> >
> > ---------------------------(end of 
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

Re: Followup Re: Performance question

От:
Steve Crawford <scrawford@pinpointresearch.com>
Дата:
On Wednesday 05 March 2003 11:29 am, Jodi Kanter wrote:
> Is restarting postmaster on a regular basis necessary for performance?
...snip...

Certainly not in my experience running postgres on Linux 2.4.x. I keep 
individual postgres connections up for many months at a time (not just the 
server - connections to the server) and haven't personally experienced a 
problem.

Cheers,
Steve

Re: Followup Re: Performance question

От:
Andrew Sullivan <andrew@libertyrms.info>
Дата:
On Wed, Mar 05, 2003 at 02:29:14PM -0500, Jodi Kanter wrote:
> Is restarting postmaster on a regular basis necessary for performance?

If it were, we'd throw it overboard for sure.

The one thing we _never_ restart is the postmaster.  We see no
degradation in performance from this.

My suspicion is that, if you find restarting the postmaster helps, it
might be because of a long-running transaction which is hanging
around and keeping VACUUM from doing its job.  Killing the postmaster
will certainly get rid of such transactions, but there are probably
less dire ways to do it ;-)

A


-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
                              M2P 2A8
                                         +1 416 646 3304 x110

FAQ