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
VERBOSEaction_xml ; 
 fred    | action_xml | AccessExclusiveLock      | t       | postgres | 00:50:49.708796 | 15799 | VACUUM ANALYZE
VERBOSEaction_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@nic.cz  http://nic.cz/
sip:jaromir.talir@nic.cz tel:+420.222745107
mob:+420.739632712       fax:+420.222745112
-------------------------------------------