Skip site navigation (1) Skip section navigation (2)

Re: idle process & autovacuum

From: Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: idle process & autovacuum
Date: 2010-07-30 15:10:01
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
On 10-07-30 10:52 AM, Gauthier, Dave wrote:
> Hi:
> I have a perl/dbi script that's loading a lot of data into a DB 
> (probably about 8,000,000 records).  pg_stat_activity shows the 
> script's activity alongside another process with current_query as...
> autovacuum: ANALYZE public.instance_pins
> The total instance_pins table cardinality is about 60,000,000.  The 
> script inserts records but never updates or deletes anything.  And 
> before the script runs, the table is truncated.  The script's process 
> seems to be stalled in the meantime.

The analyze that is running on that table will block the truncate of 
that until the analyze completes.  You can check pg_locks for to see if 
there are any ungranted locks relating to the script you are running.

> This is a new phenomena as of yesterday. The only change is that I 
> added an index on that table.  But I created the new index yesterday 
> (create index...) and it completed OK.
> What does this message mean in that context?  Is this indicative of 
> someting being misconfigured in the DB?

The message means that the data in instance_pins has changed enough for 
autovacuum to run an analyze on the table.  It's doing this to 
re-generate statistics for the table for the query planner to use.

Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

In response to

pgsql-general by date

Next:From: Merlin MoncureDate: 2010-07-30 15:50:03
Subject: Re: select a list of column values directly into an array
Previous:From: Gauthier, DaveDate: 2010-07-30 14:52:34
Subject: idle process & autovacuum

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group