Re: [PERFORM] vacuum in Postgresql 8.0.x slowing down the database
On Wed, 26 Mar 2008 13:02:13 -0700 "Joshua D. Drake" <[EMAIL PROTECTED]> 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.300.004.50 25.910.00 56.29 Device:tpskB_read/skB_wrtn/skB_readkB_wrtn md23356.94 2005.59 12945.45 20076 129584 avg-cpu: %user %nice %system %iowait %steal %idle 16.200.006.32 24.890.00 52.59 Device:tpskB_read/skB_wrtn/skB_readkB_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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] vacuum in Postgresql 8.0.x slowing down the database
On Wed, 26 Mar 2008 12:49:56 -0800 Vinubalaji Gopal <[EMAIL PROTECTED]> 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 signature.asc Description: PGP signature
[PERFORM] vacuum in Postgresql 8.0.x slowing down the database
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
