Re: db slowness + upgrade prospects - Mailing list pgsql-general

From Scott Marlowe
Subject Re: db slowness + upgrade prospects
Date
Msg-id 1122327516.15145.36.camel@state.g2switchworks.com
Whole thread Raw
In response to db slowness + upgrade prospects  ("Ed L." <pgsql@bluepolka.net>)
List pgsql-general
On Mon, 2005-07-25 at 16:17, Ed L. wrote:
> Our inserts and updates on an older 7.3.4 cluster are very slow
> (0.3s-0.9s) for any/all tables, new and old.  I know an upgrade
> may be in order, but I have a number of other 7.3.4 legacy
> clusters, and I'd really like to understand the cause and if an
> upgrade is going to solve this problem (downtime is sensitive,
> of course).
>
> As a test case, I created the following table on both the slow
> cluster and a newly created test cluster on the same box:
>
>     create table foo(id integer, msg text)
>
> I measured less than 10 inserts/second max on the slow cluster.
> On the new cluster on the same system, I measured 5000
> inserts/second.  So I figure it's something in the system
> relations.  Autovac had been disabled for a few days at least.
> I did a db-wide vacuum/analyze, still no speedup.
>
> So two questions:  1) Any ideas on what is the cause?  2) How is
> this avoided in 7.4.8 or 8.0.3?

Did you do a vacuum full?

Have you checked for system table / index bloat?

Do you create a lot of system objects and drop them frequently (this can
cause the above mentioned system table/index bloating)

What does the output of vacuum analyze verbose say?

pgsql-general by date:

Previous
From: Kevin Murphy
Date:
Subject: speeding up a query on a large table
Next
From: Scott Marlowe
Date:
Subject: Re: pgsql client/server compatibility matrix?