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 indexUnrelated.
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 multiple
ANALYZE commands in a single transaction, to prevent possible
deadlock problems. Per request from Tom Lane.
regards, tom lane