Skip site navigation (1) Skip section navigation (2)

vacuum in Postgresql 8.0.x slowing down the database

From: Vinubalaji Gopal <vgopal(at)abaca(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: vacuum in Postgresql 8.0.x slowing down the database
Date: 2008-03-26 20:49:56
Message-ID: 20080326124956.49e1c13d@vinu-ubuntu.abaca (view raw or flat)
Thread:
Lists: pgsql-performance
Hey all,
   I had posted sometime back asking for the best way to perform vacuum
with a lower priority - I did tune it up to a lower priority
and still noticed that the other database queries are slowing down
with a vacuum on one big table. I also tried to upgrade Postgresql to
8.0.15 as suggested and I still could reproduce the problem.

It happens when I try to vacuum one particular table which has 9.5
million rows and all the other inserts/selects are slowing down by a
factor of 10 times. I am using
vacuum_cost_delay = 100
vacuum_cost_limit = 200

Even if I cancel the vacuum operation or let the vacuum complete - the
slowdown continues to persist till I restart my application.

Whats the best way to analyze the reason Postgresql is slowing down? I
had a look at pg_locks (did not know what to look for) and also tried
starting postgresql with the debug option using: postmaster -d 5
-D /var/pgdata (saw a lot of output including the query being performed
but could not gather anything useful) 

The big table has never been reindexed and has a primary, unique key
with btree index and one foreign key constraint.

--
Vinu

Responses

pgsql-performance by date

Next:From: PFCDate: 2008-03-26 21:58:16
Subject: Re: how can a couple of expensive queries drag my system down?
Previous:From: Scott MarloweDate: 2008-03-26 20:31:32
Subject: Re: how can a couple of expensive queries drag my system down?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group