Autovacuum - what does it actually do?

From: "Kasia Tuszynska" <ktuszynska(at)esri(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Autovacuum - what does it actually do?
Date: 2007-11-09 17:06:36
Message-ID: D7BFFE348C53EF4E8AA0698B1E395FA90AFCCB72@flybywire.esri.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello Everybody,
I am trying to establish what does Autovaccum actually do, as it is causing data load failures?

Documenatation from : http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html
States that VACCUMis needed to:
1. To recover or reuse disk space occupied by updated or deleted rows.
2. To update data statistics used by the PostgreSQL query planner.
3. To protect against loss of very old data due to transaction ID wraparound.

Documentation from: PgADMINIII help, on the auto vacuum daemon:
When enabled, the autovacuum daemon runs periodically and checks for tables that have had a large number of inserted, updated or deleted tuples.

So, which is it, perhaps I am mixing vaccum with autovacuum ?
Updated or deleted rows - at which point the hoovering of dead stuff to keep the disk space tidy makes sence or
Updated, deleted and inserted rows - the inserted rows trouble me, are the inserted rows merely analyzed for the benefit of the optimizer, or does vacuum actually do anything in their case as well?

I ask because I am running into a weird scenario with large dataloads and the autovacuum process.
My setup:
Windows 2003 server, sp2
PostgreSQL 8.2.4 installed from the gui (autovacuum on in postmaster.conf by default, no changes to postmaster.conf at all)
Type of data loaded: large spatial (non postgis) -> lots of blob data(4 gigs)

This is my scenario:
I am loading various sets of data, on several ones (that happen to be very large)it seems like the autovacuum threshold is triggered and on these select few datasets it crashes the postmaster:
With code -1073741819, which I looked up to mean an access violation to an address space, it was apparently bugged:

BUG #3427: Autovacuum crashed server

http://archives.postgresql.org/pgsql-bugs/2007-07/msg00016.php

The bug does not explain what is actually happening, what is autovacuum doing to get an access violation, why would it happen on very specific data everytime ?

Sorry for the long post,
Thank you,
Sincerely,
Kasia

Pg_log:
2007-11-07 08:32:29 LOG:  autovacuum process (PID 3668) exited with exit code -1073741819
2007-11-07 08:32:29 LOG:  terminating any other active server processes
2007-11-07 08:32:29 WARNING:  terminating connection because of crash of another server process
2007-11-07 08:32:29 DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2007-11-07 08:32:29 HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2007-11-07 08:32:29 WARNING:  terminating connection because of crash of another server process
2007-11-07 08:32:29 DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2007-11-07 08:32:29 HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2007-11-07 08:32:29 WARNING:  terminating connection because of crash of another server process
2007-11-07 08:32:29 DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2007-11-07 08:32:29 HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2007-11-07 08:32:29 WARNING:  terminating connection because of crash of another server process
2007-11-07 08:32:29 DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2007-11-07 08:32:29 HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2007-11-07 08:32:29 LOG:  all server processes terminated; reinitializing
2007-11-07 08:32:29 FATAL:  the database system is starting up
2007-11-07 08:32:29 LOG:  database system was interrupted at 2007-11-07 08:32:29 Pacific Standard Time
2007-11-07 08:32:29 LOG:  checkpoint record is at 15/431DF0C8
2007-11-07 08:32:29 LOG:  redo record is at 15/43008230; undo record is at 0/0; shutdown FALSE
2007-11-07 08:32:29 LOG:  next transaction ID: 0/99496820; next OID: 6802317
2007-11-07 08:32:29 LOG:  next MultiXactId: 1; next MultiXactOffset: 0
2007-11-07 08:32:29 LOG:  database system was not properly shut down; automatic recovery in progress
2007-11-07 08:32:29 FATAL:  the database system is starting up
2007-11-07 08:32:29 LOG:  redo starts at 15/43008230
2007-11-07 08:32:29 FATAL:  the database system is starting up

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Mija Lee 2007-11-09 17:48:52 references to variable in another schema
Previous Message Robert Bernabe 2007-11-09 06:32:32 Re: Debugging question ...