Re: [GENERAL] vacuum won't even start
Jean-Christophe Praud wrote: Hi all, I've a problem on a heavy loaded database: vacuums don't work since about a week. All I got is: mybase=# vacuum verbose analyze public.mytable; INFO: vacuuming public.mytable (I stop it after hours) Looking with top and iotop, I see the process takes some cpu and disk io time during several minutes, then it seems to fall asleep. The process isn't locked according to pg_stat_activity. What are your vacuum_cost_% parameters? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] vacuum won't even start
Jean-Christophe Praud j...@steek.com writes: I've a problem on a heavy loaded database: vacuums don't work since about a week. All I got is: mybase=# vacuum verbose analyze public.mytable; INFO: vacuuming public.mytable (I stop it after hours) Looking with top and iotop, I see the process takes some cpu and disk io time during several minutes, then it seems to fall asleep. The process isn't locked according to pg_stat_activity. When vacuum wants to clean up a particular table page, it will wait until no other process is examining that page; and this wait is not visible in pg_locks. Perhaps you have got some queries referencing those tables that have stopped midway and are just sitting? Although pg_locks won't immediately show the wait, it could be useful to help identify the culprit --- look for other processes holding any type of lock on the table the vacuum is stuck on, and then go to pg_stat_activity to see how old their current query is. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] vacuum won't even start
Alvaro Herrera a écrit : Jean-Christophe Praud wrote: Hi all, I've a problem on a heavy loaded database: vacuums don't work since about a week. All I got is: mybase=# vacuum verbose analyze public.mytable; INFO: vacuuming public.mytable (I stop it after hours) Looking with top and iotop, I see the process takes some cpu and disk io time during several minutes, then it seems to fall asleep. The process isn't locked according to pg_stat_activity. What are your vacuum_cost_% parameters? I've let the default values (not even uncommented in the conf file ;) ): #vacuum_cost_delay = 0 # 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-1 credits #vacuum_cost_page_miss = 10 # 0-1 credits #vacuum_cost_page_dirty = 20# 0-1 credits #vacuum_cost_limit = 200# 1-1 credits -- JC Ph'nglui mglw'nafh Cthulhu n'gah Bill R'lyeh Wgah'nagl fhtagn!
Re: [GENERAL] vacuum won't even start
Tom Lane a écrit : Jean-Christophe Praud j...@steek.com writes: I've a problem on a heavy loaded database: vacuums don't work since about a week. All I got is: mybase=# vacuum verbose analyze public.mytable; INFO: vacuuming public.mytable (I stop it after hours) Looking with top and iotop, I see the process takes some cpu and disk io time during several minutes, then it seems to fall asleep. The process isn't locked according to pg_stat_activity. When vacuum wants to clean up a particular table page, it will wait until no other process is examining that page; and this wait is not visible in pg_locks. Perhaps you have got some queries referencing those tables that have stopped midway and are just sitting? Although pg_locks won't immediately show the wait, it could be useful to help identify the culprit --- look for other processes holding any type of lock on the table the vacuum is stuck on, and then go to pg_stat_activity to see how old their current query is. regards, tom lane Indeed, the tables I tried to vacuum have locks on them. AccessShareLock belonging to queries which seem sleeping. I tried to kill these queries but pg_cancel_backend() has no effect, and the process doesn't get the 15 signal. How can I get rid of these blocking queries without restarting the server ? They are not listed as waiting in pg_stat_activity. These queries are MOVE FORWARD on cursors, the underlying query is a rather complex one (unions, joins, functions calls) Regards, -- JC Ph'nglui mglw'nafh Cthulhu n'gah Bill R'lyeh Wgah'nagl fhtagn!
Re: [GENERAL] vacuum won't even start
Jean-Christophe Praud j...@steek.com writes: Indeed, the tables I tried to vacuum have locks on them. AccessShareLock belonging to queries which seem sleeping. I tried to kill these queries but pg_cancel_backend() has no effect, and the process doesn't get the 15 signal. How can I get rid of these blocking queries without restarting the server ? They are not listed as waiting in pg_stat_activity. Have you tried killing the connected client sessions? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] vacuum won't even start
Tom Lane a écrit : Jean-Christophe Praud j...@steek.com writes: Indeed, the tables I tried to vacuum have locks on them. AccessShareLock belonging to queries which seem sleeping. I tried to kill these queries but pg_cancel_backend() has no effect, and the process doesn't get the 15 signal. How can I get rid of these blocking queries without restarting the server ? They are not listed as waiting in pg_stat_activity. Have you tried killing the connected client sessions? regards, tom lane It works ! I had pgbouncer connections hanging for several days. Thanks for your help :) Regards, -- JC Ph'nglui mglw'nafh Cthulhu n'gah Bill R'lyeh Wgah'nagl fhtagn!
