Re: lazy vacuum and AccessExclusiveLock

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(dot)talir(at)nic(dot)cz http://nic.cz/
sip:jaromir(dot)talir(at)nic(dot)cz tel:+420.222745107
mob:+420.739632712 fax:+420.222745112
-------------------------------------------