Thread: auto-vacuum conflicting with long running analyze

I am currently attempting to import the world street map data as 
currently available from openstreetmap.org and am wondering if I will 
come across a possible problem that is mentioned on their website, which 
appears to be relevant for 8.1

From http://wiki.openstreetmap.org/index.php/Mapnik they state -

<quote>

After several hours the import may quit with an error like:-

ANALYZE planet_osm_line; failed: ERROR:  deadlock detected
DETAIL:  Process 28511 waits for AccessExclusiveLock on relation 1064115 
of database 18309; blocked by process 12776.
Process 12776 waits for ShareLock on transaction 572766655; blocked by 
process 28511.

Error occurred, cleaning up

This seems to be a fault in PostgreSQL and is caused when an auto-vacuum 
is attempted during the ANALYZE. The solution is to disable all 
auto-vacuums on the database. The data is not updated after the import 
so the vacuum process does nothing useful. In the postgresql.conf file 
set the option:

autovacuum = off

Then restart the database server

# /etc/init.d/postgresql-8.1 restart

Note: In Debian/Ubuntu you also need to update 
/etc/cron.d/postgresql-common to comment out the two pg_maintenance 
tasks which ...

</quote>

I can see that an analyze will be run at the end of the import and given 
that the uncompressed xml file being imported is about 77GB I can see a 
large dataset with a long running analyze to finish of the task.

Given that the analyze will obviously take a long time, is this scenario 
likely to happen with 8.3.1? or has it been fixed since 8.1.x?


Would this be the issue fixed in 8.1.1? -
Prevent autovacuum from crashing during ANALYZE of expression index

Or is it in various autovacuum improvements from 8.3.0 ?


As it is a long slow process so it will be a while before I can verify 
for sure.



-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

Shane Ambler wrote:

> Given that the analyze will obviously take a long time, is this scenario  
> likely to happen with 8.3.1? or has it been fixed since 8.1.x?

In 8.3, autovacuum cancels itself if it sees it is conflicting with
another query.

> Would this be the issue fixed in 8.1.1? -
> Prevent autovacuum from crashing during ANALYZE of expression index

Unrelated.

> Or is it in various autovacuum improvements from 8.3.0 ?

Yes.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:
> Shane Ambler wrote:
> 
>> Given that the analyze will obviously take a long time, is this scenario  
>> likely to happen with 8.3.1? or has it been fixed since 8.1.x?
> 
> In 8.3, autovacuum cancels itself if it sees it is conflicting with
> another query.
> 
>> Would this be the issue fixed in 8.1.1? -
>> Prevent autovacuum from crashing during ANALYZE of expression index
> 
> Unrelated.
> 
>> Or is it in various autovacuum improvements from 8.3.0 ?
> 
> Yes.
> 

Sounds good. When it has run through to verify I will get them to update 
their notes on that.


-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

"Alvaro Herrera" <alvherre@commandprompt.com> writes:

> Shane Ambler wrote:
>
>> Given that the analyze will obviously take a long time, is this scenario  
>> likely to happen with 8.3.1? or has it been fixed since 8.1.x?
>
> In 8.3, autovacuum cancels itself if it sees it is conflicting with
> another query.

But that's because it could *block* other queries. Not because it should be
causing deadlocks. Unless you're doing analyze within a transaction I don't
see why a autovacuum would be causing deadlocks. There are useful reasons to
run analyze within a transaction but I suspect you're not actually doing it
for any of those reasons.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!

Shane Ambler <pgsql@Sheeky.Biz> writes:
> Would this be the issue fixed in 8.1.1? -
> Prevent autovacuum from crashing during ANALYZE of expression index

More likely this one:

2007-06-14 09:53  alvherre
* src/backend/commands/: vacuum.c (REL8_1_STABLE), vacuum.c(REL8_2_STABLE), vacuum.c: Avoid having autovacuum run
multipleANALYZEcommands in a single transaction, to prevent possibledeadlock problems.  Per request from Tom Lane.
 
        regards, tom lane