Since I started this thread I should finish it with the final
resolution of my problem.
Turns out it wasn't really the postmaster having problems on it's own,
it was the server itself. The symptom of queries taking longer on the
first execution on each new connection, plus each day the same query
taking about 3x as long as the day before, persisted for several days
after I first noticed it. Restarting the postmaster would clear things
up for a while, but by the following day the performance was in the
toilet again. Finally I re-booted the whole server and since then the
problem has not come back. So there was something going on in the
server OS, my guess is something to do with shared memory, that was
causing postgres to get increasingly worse performance after the
postmaster process had been running for a few hours. The server is a
Mac running OS X Server 10.1.5 with Postgres 7.2.1, I've been told
repeatedly that a lot of my troubles will go away if I upgrade Postgres
to 7.3 and the server OS to 10.2; as soon as I get out from under the
current project I'll be doing those upgrades.
The bottom line is, the others who responded saying postmaster restarts
are unnecessary are probably correct. On the other hand restarting
your whole server may sometimes be needed, but this is likely an
isolated problem involving OS X Server and not something chronic with
Postgres.
Bob
On Thursday, Mar 6, 2003, at 09:08 US/Pacific, Andrew Sullivan wrote:
> 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
>
>
> ---------------------------(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
>