Обсуждение: db slowness + upgrade prospects

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

db slowness + upgrade prospects

От
"Ed L."
Дата:
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?

Ed

Re: db slowness + upgrade prospects

От
Scott Marlowe
Дата:
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?

Re: db slowness + upgrade prospects

От
Martijn van Oosterhout
Дата:
Check the sizes of your relations, it may be that your indexes are
getting large. Under certain situations, older versions of postgresql
would have indexes grow forever. Try REINDEX on various tables. Also,
the output of vacuum should give you hints as to large tables/indexes.

Hope this helps,

On Mon, Jul 25, 2005 at 03:17:03PM -0600, 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?
>
> Ed
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Вложения