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

Re: Re: Delay in completion of aggregation inserts when run in a single commit - PG 9.1.2

From: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
To: Akash <akash(dot)kodibail(at)onmobile(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Re: Delay in completion of aggregation inserts when run in a single commit - PG 9.1.2
Date: 2012-07-06 05:28:43
Message-ID: 4FF6778B.8040704@ringerc.id.au (view raw or flat)
Thread:
Lists: pgsql-admin
On 07/06/2012 01:06 PM, Akash wrote:
> @@Craig
>
> I could only wait for 6 hours. I cancelled the process after that.
>
> No locks on any of the tables when running.
That's a very ambiguous statement. I'm assuming you are saying "our code 
does not take any explict locks on those tables using LOCK TABLE or 
SELECT ... FOR SHARE / FOR UPDATE" - because there /most certainly are/ 
locks on those tables whenever you're running a query against them. Even 
a simple SELECT takes out a lock to prevent the table from being dropped 
while the query runs.

There could be no locks taken by transactions other than the transaction 
doing the run, though.

> Yes, It was progressing. Of the 15 tables, when I cancelled after 6 hours,
> 3rd table was getting populated (when checked in pg_stat_activity).
OK. If you turn auto_explain on and use it to get a plan for the slow 
queries, do those plans differ from the plans produced when running the 
same queries standalone?

Did you examine iostat? Did you look at what work the postgres process 
was doing to see if it was busy with cpu or I/O (iowait) or if it was 
idle waiting for something else to happen?

> 4)autovacuum = off, we are doing daily vacuum analyze on all tables.
No!

Unless your database as /no/ write activity other than these batch 
loads, you should have autovacuum on. The more frequently autovaccum 
runs the better a job it will do of preventing bloat, maintaining table 
stats, etc.

If you have tables that are only ever modified by a bulk-load script 
that then immediately vacuums them, set the autovac parameters for that 
table so it's excluded, don't turn autovaccum off entirely.


> 5)shared_buffers = 32MB
That's tiny and will severely constrain Pg's resources.

I notice you didn't set effective_cache_size either.
> After these changes process is moving forward, but somehow I feel that, It
> is only matter of time, I will reach the next choking point. Since I am
> seeing the that process completion is taking a little longer each time I run
> it.
Well, your tables and indexes are probably bloating horribly because 
autovaccum is turned off, so that's not surprising.

If you drop and recreate, or TRUNCATE, the tables between load runs you 
might be OK with autovac off for those tables, but what you're 
describing makes me think otherwise.

--
Craig Ringer

In response to

pgsql-admin by date

Next:From: Samuel StearnsDate: 2012-07-09 00:00:46
Subject: Re: Duplicate Index Creation
Previous:From: AkashDate: 2012-07-06 05:06:13
Subject: Re: Delay in completion of aggregation inserts when run in a single commit - PG 9.1.2

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