Alvaro Herrera píše v Čt 24. 09. 2009 v 14:41 -0400: > Jaromír Talíř wrote: > > > we are facing strange situation with exclusively locked table during > > normal lazy vacuum. There is one big table (66GB) that is heavily > > inserted and updated in our database. Suddenly (after backup and delete > > of almost all records) we are not able to run VACUUM over this table > > because after 50 minutes of work it allocate AccessExclusiveLock on this > > table and all other connections start to timeout. > > What version are you running? We are running 8.3.5 on Ubuntu LTS 8.04. Here is confirmation of lock from sql: "SELECT a.datname, c.relname, l.mode, l.granted, a.usename, age(now(), a.query_start) as "age", l.pid, a.current_query FROM pg_stat_activity a JOIN pg_locks l ON (l.pid = a.procpid) LEFT OUTER JOIN pg_class c ON (l.relation = c.oid) WHERE l.pid!=pg_backend_pid() ORDER BY a.query_start" datname | relname | mode | granted | usename | age | pid | current_query ---------+------------+--------------------------+---------+----------+-----------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- fred | action_xml | ShareUpdateExclusiveLock | t | postgres | 00:50:49.708796 | 15799 | VACUUM ANALYZE VERBOSE action_xml ; fred | action_xml | AccessExclusiveLock | t | postgres | 00:50:49.708796 | 15799 | VACUUM ANALYZE VERBOSE action_xml ; fred | action_xml | RowExclusiveLock | f | rifd | 00:00:54.987454 | 28815 | INSERT INTO Action_XML VALUES ( ... Here is log of VACUUM VERBOSE. At the end we have to kill it because we cannot afford to block normal connections: fred=# VACUUM ANALYZE VERBOSE action_xml ; INFO: vacuuming "public.action_xml" INFO: scanned index "action_xml_pkey" to remove 4722451 row versions DETAIL: CPU 2.62s/3.41u sec elapsed 41.56 sec. INFO: "action_xml": removed 4722451 row versions in 4722024 pages DETAIL: CPU 113.50s/40.13u sec elapsed 1162.88 sec. INFO: index "action_xml_pkey" now contains 5993747 row versions in 250663 pages DETAIL: 4722451 index row versions were removed. 234178 index pages have been deleted, 221276 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "action_xml": found 8091937 removable, 6006252 nonremovable row versions in 8397120 pages DETAIL: 12739 dead row versions cannot be removed yet. There were 80712079 unused item pointers. 8397120 pages contain useful free space. 0 pages are entirely empty. CPU 284.46s/109.26u sec elapsed 2994.64 sec. Cancel request sent -- Jaromir Talir technicky reditel / Chief Technical Officer ------------------------------------------- CZ.NIC, z.s.p.o. -- .cz domain registry Americka 23, 120 00 Praha 2, Czech Republic mailto:jaromir.talir@xxxxxx http://nic.cz/ sip:jaromir.talir@xxxxxx tel:+420.222745107 mob:+420.739632712 fax:+420.222745112 -------------------------------------------
<<attachment: smime.p7s>>
