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

From Craig Ringer
Subject Re: Re: Delay in completion of aggregation inserts when run in a single commit - PG 9.1.2
Date
Msg-id 4FF6778B.8040704@ringerc.id.au
Whole thread Raw
In response to Re: Delay in completion of aggregation inserts when run in a single commit - PG 9.1.2  (Akash <akash.kodibail@onmobile.com>)
List 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

pgsql-admin by date:

Previous
From: Akash
Date:
Subject: Re: Delay in completion of aggregation inserts when run in a single commit - PG 9.1.2
Next
From: Samuel Stearns
Date:
Subject: Re: Duplicate Index Creation