Message-ID: <4FF6778B.8040704@ringerc.id.au>
Date: Fri, 06 Jul 2012 13:28:43 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:13.0) Gecko/20120605 Thunderbird/13.0
MIME-Version: 1.0
To: Akash <akash.kodibail@onmobile.com>
CC: pgsql-admin@postgresql.org
Subject: Re: Re: Delay in completion of aggregation inserts when run
	in a single commit - PG 9.1.2
References: 
 <67A07D932F8DCC41989A050EDE427A210CF9B5@JETWINSRVRPS02.onmobile.com>
	<4FF5354E.8090105@ringerc.id.au>
	<1341551173972-5715621.post@n5.nabble.com>
In-Reply-To: <1341551173972-5715621.post@n5.nabble.com>
Content-Type: multipart/alternative;
	boundary="------------000400060602020708030200"

This is a multi-part message in MIME format.
--------------000400060602020708030200
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------000400060602020708030200
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 07/06/2012 01:06 PM, Akash wrote:<br>
    </div>
    <blockquote cite="mid:1341551173972-5715621.post@n5.nabble.com"
      type="cite">
      <pre wrap="">@@Craig

I could only wait for 6 hours. I cancelled the process after that.

No locks on any of the tables when running.</pre>
    </blockquote>
    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 <i>most
      certainly are</i> 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.<br>
    <br>
    There could be no locks taken by transactions other than the
    transaction doing the run, though.<br>
    <br>
    <blockquote cite="mid:1341551173972-5715621.post@n5.nabble.com"
      type="cite">
      <pre wrap="">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).
</pre>
    </blockquote>
    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?<br>
    <br>
    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?<br>
    <br>
    <blockquote cite="mid:1341551173972-5715621.post@n5.nabble.com"
      type="cite">
      <pre wrap="">
4)autovacuum = off, we are doing daily vacuum analyze on all tables.</pre>
    </blockquote>
    No!<br>
    <br>
    Unless your database as <i>no</i> 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.<br>
    <br>
    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.<br>
    <br>
    <br>
    <blockquote cite="mid:1341551173972-5715621.post@n5.nabble.com"
      type="cite">
      <pre wrap="">5)shared_buffers = 32MB</pre>
    </blockquote>
    That's tiny and will severely constrain Pg's resources.<br>
    <br>
    I notice you didn't set effective_cache_size either.<br>
    <blockquote cite="mid:1341551173972-5715621.post@n5.nabble.com"
      type="cite">
      <pre wrap="">
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.</pre>
    </blockquote>
    Well, your tables and indexes are probably bloating horribly because
    autovaccum is turned off, so that's not surprising.<br>
    <br>
    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.<br>
    <br>
    --<br>
    Craig Ringer<br>
  </body>
</html>

--------------000400060602020708030200--