Thread: lazy vacuum blocks analyze

lazy vacuum blocks analyze

From
Zdenek Kotala
Date:
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  



Re: lazy vacuum blocks analyze

From
Alvaro Herrera
Date:
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


Re: lazy vacuum blocks analyze

From
Tom Lane
Date:
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


Re: lazy vacuum blocks analyze

From
Zdenek Kotala
Date:
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