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

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