Re: [HACKERS] Problems with autovacuum and vacuum
Hello, Filip 2011/1/1 Filip Rembiałkowski > > 2010/12/30 JotaComm > >> Hello, >> >> Last week I had a serious problem with my PostgreSQL database. My >> autovacuum is OFF, but in September it started to prevent the transaction >> wraparoud; however last week the following message appeared continuously in >> my log: >> >> WARNING: database "production" must be vacuumed within 4827083 >> transactions >> HINT: To avoid a database shutdown, execute a full-database VACUUM in >> "production". >> >> This message appeared for five to six hours; after that, the message >> disappeared from log. Any idea about what could have happened? >> >> > > probably another "wraparaund-forced" autovacuum worker did the job, so the > warnings disappeared > > > >> Every day the vacuum is executed on some tables; and on Sundays it's >> executed on all tables. But as the autovacuum is running since September, >> and it runs for a long time, the vacuum was blocked because autovacuum had >> been running on the same table. How should I procede in this case? >> > > > hmm. single vacuum process runs for more than 3 months on a table with > 10 rows? > this is ... less than 128 rows/second, not good. > > I would rather terminate this old process, and start a VACUUM VERBOSE when > the database is less loaded. > > > How many INS/UPD/DEL you have on this table? > About 15 millions rows inserted by day. > > > > PS. When you fix this, enable autovacuum, to avoid more problems... > > > > Regards, João Paulo -- JotaComm http://jotacomm.wordpress.com
Re: [HACKERS] Problems with autovacuum and vacuum
2010/12/30 JotaComm > Hello, > > Last week I had a serious problem with my PostgreSQL database. My > autovacuum is OFF, but in September it started to prevent the transaction > wraparoud; however last week the following message appeared continuously in > my log: > > WARNING: database "production" must be vacuumed within 4827083 transactions > > HINT: To avoid a database shutdown, execute a full-database VACUUM in > "production". > > This message appeared for five to six hours; after that, the message > disappeared from log. Any idea about what could have happened? > > probably another "wraparaund-forced" autovacuum worker did the job, so the warnings disappeared > Every day the vacuum is executed on some tables; and on Sundays it's > executed on all tables. But as the autovacuum is running since September, > and it runs for a long time, the vacuum was blocked because autovacuum had > been running on the same table. How should I procede in this case? > hmm. single vacuum process runs for more than 3 months on a table with 10 rows? this is ... less than 128 rows/second, not good. I would rather terminate this old process, and start a VACUUM VERBOSE when the database is less loaded. How many INS/UPD/DEL you have on this table? PS. When you fix this, enable autovacuum, to avoid more problems...
Re: [HACKERS] Problems with autovacuum and vacuum
On Thu, Dec 30, 2010 at 12:56 PM, JotaComm wrote: > Last week I had a serious problem with my PostgreSQL database. My autovacuum > is OFF, but in September it started to prevent the transaction wraparoud; > however last week the following message appeared continuously in my log: > > WARNING: database "production" must be vacuumed within 4827083 transactions > HINT: To avoid a database shutdown, execute a full-database VACUUM in > "production". > > This message appeared for five to six hours; after that, the message > disappeared from log. Any idea about what could have happened? I'm thinking that autovacuum kicked into gear to prevent transaction wraparound. Once it did enough work to stave off disaster, the warning messages stopped appearing in the log. > Every day the vacuum is executed on some tables; and on Sundays it's > executed on all tables. But as the autovacuum is running since September, > and it runs for a long time, the vacuum was blocked because autovacuum had > been running on the same table. How should I procede in this case? I guess the obvious thing to do would be to turn on autovacuum and forget about manual vacuums. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Problems with autovacuum and vacuum
Hello, Last week I had a serious problem with my PostgreSQL database. My autovacuum is OFF, but in September it started to prevent the transaction wraparoud; however last week the following message appeared continuously in my log: WARNING: database "production" must be vacuumed within 4827083 transactions HINT: To avoid a database shutdown, execute a full-database VACUUM in "production". This message appeared for five to six hours; after that, the message disappeared from log. Any idea about what could have happened? Every day the vacuum is executed on some tables; and on Sundays it's executed on all tables. But as the autovacuum is running since September, and it runs for a long time, the vacuum was blocked because autovacuum had been running on the same table. How should I procede in this case? The table where the autovacuum is running and where the vacuum was blocked has billion of rows. I'm using the PostgreSQL 8.3.8 The configuration of the vacuum parameters are: vacuum_cost_limit = 200 vacuum_cost_delay = 0 vacuum_freeze_min_age = 1 autovacuum = off autovacuum_freeze_max_age = 2 Regards, João Paulo -- JotaComm http://jotacomm.wordpress.com
