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