Обсуждение: vacuum in Postgresql 8.0.x slowing down the database

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

vacuum in Postgresql 8.0.x slowing down the database

От
Vinubalaji Gopal
Дата:
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

Re: vacuum in Postgresql 8.0.x slowing down the database

От
"Joshua D. Drake"
Дата:
On Wed, 26 Mar 2008 12:49:56 -0800
Vinubalaji Gopal <vgopal@abaca.com> wrote:

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

The slowness is likely attributed to Vacuum's use of I/O. When vacuum
is running what does iostat -k 10 say?

Joshua D. Drake



--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director |  PostgreSQL political pundit


Вложения

Re: vacuum in Postgresql 8.0.x slowing down the database

От
Vinubalaji Gopal
Дата:
On Wed, 26 Mar 2008 13:02:13 -0700
"Joshua D. Drake" <jd@commandprompt.com> wrote:


> The slowness is likely attributed to Vacuum's use of I/O. When vacuum
> is running what does iostat -k 10 say?

Seems to be higher than normal - here is the output with vacuum run
without the other queries and the default vacuum taking ~1 hr:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          13.30    0.00    4.50   25.91    0.00   56.29
Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
md2            3356.94      2005.59     12945.45      20076     129584


avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          16.20    0.00    6.32   24.89    0.00   52.59

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
md2             461.70       667.20      1512.00       6672      15120


I don't know if the output helps much since the vacuum took some time
and I lost more than half of my iostat -k screen output. (I scrolled up
- but got only some of the data)

If vacuum does affect the io what are the ways to reduce the io during
vacuum (the delay and cost parameter did not help that much - should I
consider reducing the cost even further)? Should I consider
partitioning the table?

Thank you.

--
Vinu