Обсуждение: Performance question

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

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
>

Is restarting postmaster on a regular basis necessary for performance?

----- Original Message -----
From: "Bob Smith" <bsmith@h-e.com>
To: "pgsql-admin" <pgsql-admin@postgresql.org>
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
>

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

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
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110