Thread: lazy vacuum and AccessExclusiveLock
Hello, 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. It's common knowledge that VACUUM doesn't block and it looks like it's not true. I found this little excuse in documentation (http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html): "... it does not attempt to reclaim the space used by this dead data unless the space is at the end of the table and an exclusive table lock can be easily obtained. Unused space at the start or middle of the file does not result in the file being shortened and space returned to the operating system." This seems to me that situation can appear that dead tuples are in such position that VACUUM will decide to reclaim free space and block other process! Is it true? I found old thread in archive speaking about similar problem (http://archives.postgresql.org/pgsql-performance/2008-06/msg00235.php) but with a resolution that it should be no problem any more. It looks like it is still a problem. If this is true, is there any solution how to convince vacuum not to reclaim free space in any situation? Regards, Jaromir
Attachment
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? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
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
-------------------------------------------
Attachment
Jaromír Talíř wrote: > 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. Okay, I was thinking on a fix that was applied before 8.3, so you're not being bitten by that bug. Yes, lazy vacuum does hold an exclusive lock: it does so to be able to truncate the empty pages at the end of the table. This lock is acquired only if available (vacuum will skip truncating if the table cannot be locked immediately), and it is supposed to last a short amount of time; namely the time it takes to scan the table backwards to find out how many pages to truncate. I guess in your case the amount of time is not as short as all that :-( The bug fixed was this one: revision 1.92 date: 2007-09-10 13:58:45 -0400; author: alvherre; state: Exp; lines: +6 -2; Remove the vacuum_delay_point call in count_nondeletable_pages, because we hold an exclusive lock on the table at this point, which we want to release as soon as possible. This is called in the phase of lazy vacuum where we truncate the empty pages at the end of the table. An alternative solution would be to lower the vacuum delay settings before starting the truncating phase, but this doesn't work very well in autovacuum due to the autobalancing code (which can cause other processes to change our cost delay settings). This case could be considered in the balancing code, but it is simpler this way. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> An alternative solution would be to lower the vacuum delay settings before
> starting the truncating phase, but this doesn't work very well in autovacuum
> due to the autobalancing code (which can cause other processes to change our
> cost delay settings). This case could be considered in the balancing code, but
> it is simpler this way.
I don't think autovacuum has a problem --- if someone requests a
conflicting lock, autovac will get kicked off, no? The OP's problem
comes from doing a manual vacuum. Perhaps "don't do that" is a good
enough answer.
regards, tom lane
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > An alternative solution would be to lower the vacuum delay settings before > > starting the truncating phase, but this doesn't work very well in autovacuum > > due to the autobalancing code (which can cause other processes to change our > > cost delay settings). This case could be considered in the balancing code, but > > it is simpler this way. > > I don't think autovacuum has a problem --- if someone requests a > conflicting lock, autovac will get kicked off, no? The OP's problem > comes from doing a manual vacuum. Perhaps "don't do that" is a good > enough answer. Hah, that was part of the commit message, which predates autovacuum getting kicked out in case of conflicting locks IIRC. I think the process being described is unusual enough that a manual vacuum at just the right time is warranted ... -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Hello, thanks for comments. But what is a conclusion? Using manual vacuum is considered as dangerous (with respect to locking) and the best is to avoid using it? What does it mean "kick off autovacuum"? Only "cleaning part" that needs lock will be omitted but dead tuples are freed, or whole table must wait to next autovacuum run and hope that it will not be kicked off once again? Regards, Jaromir Alvaro Herrera píše v Pá 25. 09. 2009 v 18:41 -0400: > Tom Lane wrote: > > Alvaro Herrera <alvherre@commandprompt.com> writes: > > > An alternative solution would be to lower the vacuum delay settings before > > > starting the truncating phase, but this doesn't work very well in autovacuum > > > due to the autobalancing code (which can cause other processes to change our > > > cost delay settings). This case could be considered in the balancing code, but > > > it is simpler this way. > > > > I don't think autovacuum has a problem --- if someone requests a > > conflicting lock, autovac will get kicked off, no? The OP's problem > > comes from doing a manual vacuum. Perhaps "don't do that" is a good > > enough answer. > > Hah, that was part of the commit message, which predates autovacuum > getting kicked out in case of conflicting locks IIRC. > > I think the process being described is unusual enough that a manual > vacuum at just the right time is warranted ... > -- 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 -------------------------------------------