Thread: lazy vacuum blocks analyze
My colleague hit interesting problem. His transaction hanged for a several days (PG8.3). We found that transaction (ANALYZE) command) waited on relation lock which had been acquired by lazy vacuum. Unfortunately, lazy vacuum on large table (38GB) takes veeeery long time - several days. The problem is that vacuum and analyze use same lock. If I understood correctly comment in analyze_rel() function it is not necessary. I think that it is very serious issue and dead space map does not help much in this case, because affected table is heavily modified. If there is not another problem I suggest to use two different locks for vacuum and analyze. Zdenek
Zdenek Kotala wrote: > If there is not another problem I suggest to use two different locks for > vacuum and analyze. By itself that won't work -- see vac_update_relstats. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes: > My colleague hit interesting problem. His transaction hanged for a > several days (PG8.3). We found that transaction (ANALYZE) command) > waited on relation lock which had been acquired by lazy vacuum. > Unfortunately, lazy vacuum on large table (38GB) takes veeeery long time > - several days. > The problem is that vacuum and analyze use same lock. If I understood > correctly comment in analyze_rel() function it is not necessary. > I think that it is very serious issue and dead space map does not help > much in this case, because affected table is heavily modified. > If there is not another problem I suggest to use two different locks for > vacuum and analyze. We would have to invent another lock type just for ANALYZE. It does not seem worth it. regards, tom lane
Alvaro Herrera píše v st 06. 05. 2009 v 15:11 -0400: > Zdenek Kotala wrote: > > > If there is not another problem I suggest to use two different locks for > > vacuum and analyze. > > By itself that won't work -- see vac_update_relstats. It says: * Note another assumption: that two VACUUMs/ANALYZEs on a table can't * run in parallel, nor can VACUUM/ANALYZE run in parallel with a * schema alteration such as adding an index, rule, or trigger. Otherwise * our updates of relhasindex etc might overwrite uncommitted updates. But what "two VACUUMs/ANALYZEs on a table" exactly means? It is not clear here if VACUUMxANALYZE parallel run is allowed or not. I also don't see explanation why it is not allowed? From code I don't see any problem here. Zdenek