From: Robert Creager <Robert_Creager@LogicalChaos.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Matthew T. O'Connor <matthew@zeut.net>
Cc: PGPerformance <pgsql-performance@postgresql.org>
Subject: Re: Huge performance problem between 7.4.1 and 8.0.3 - CS
Date: Sun, 17 Jul 2005 23:43:29 -0600
Message-ID: <20050717234329.05ce2e52@thunder.logicalchaos.org> (raw)
In-Reply-To: <24616.1121660310@sss.pgh.pa.us>
References: <20050717213416.59dcb52e@thunder.logicalchaos.org>
<42DB2684.9020706@zeut.net>
<20050717221050.7c4ac241@thunder.logicalchaos.org>
<24616.1121660310@sss.pgh.pa.us>
When grilled further on (Mon, 18 Jul 2005 00:18:30 -0400),
Tom Lane <tgl@sss.pgh.pa.us> confessed:
> Robert Creager <Robert_Creager@LogicalChaos.org> writes:
> > I am, and it is. It's ANALYZING and VACUUM'ing tables every interval (5 mi=
> > nutes
> > - 8.0.3). Right now, for that last 4 hours, I'm not VACUUMing the 7.4.1
> > database and it's still clicking along at < .2 second queries.
>
> Have you compared physical table sizes? If the autovac daemon did let
> things get out of hand, you'd need a VACUUM FULL or CLUSTER or TRUNCATE
> to get the table size back down --- plain VACUUM is unlikely to fix it.
Table sizes, no. Entire DB size is 45Mb for 803 and 29Mb for 741. Cannot make
a direct comparison between the two as I've run against more machines now with
803 than 741, so I'd expect it to be larger.
I'm still running relatively clean on 803 with cron vacuum. The CS are jumping
from 100 to 120k, but it's not steady state like it was before, and queries are
all under 5 seconds (none hitting the logs) and are typically (glancing at test
runs) still under 1 sec, with some hitting ~2 seconds occasionally.
I've 6 runs going concurrently. Just saw (vmstat 1) a set of 8 seconds where
the CS didn't drop below 90k, but right now its at ~300 for over 30 seconds...
It's bouncing all over the place, but staying reasonably well behaved overall.
Whoop. Spoke too soon. Just hit the wall. CS at ~80k constant, queries over
10 seconds and rising (30+ now)... Looking at ps, the vacuum is currently
running. Going back in the logs, the CS and vacuum hit at about the same time.
I'm going to go back to 741 with the same load and see what happens by tomorrow
morning... I'll change the cron vac to hit the 741 db.
Cheers,
Rob
--
23:29:24 up 4 days, 3:04, 6 users, load average: 0.02, 0.07, 0.08
Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Robert_Creager@LogicalChaos.org, tgl@sss.pgh.pa.us, matthew@zeut.net
Subject: Re: Huge performance problem between 7.4.1 and 8.0.3 - CS
In-Reply-To: <20050717234329.05ce2e52@thunder.logicalchaos.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox