Date: Thu, 5 Jul 2012 22:06:13 -0700 (PDT)
From: Akash <akash.kodibail@onmobile.com>
To: pgsql-admin@postgresql.org
Message-ID: <1341551173972-5715621.post@n5.nabble.com>
In-Reply-To: <4FF5354E.8090105@ringerc.id.au>
References: 
 <67A07D932F8DCC41989A050EDE427A210CF9B5@JETWINSRVRPS02.onmobile.com>
	<4FF5354E.8090105@ringerc.id.au>
Subject: Re: Delay in completion of aggregation inserts when run in a single
	commit - PG 9.1.2
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

@@Craig

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

No locks on any of the tables when running.

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).

Below are some changes I have in postgresql.conf, some based on your
suggestions:

1) constraint_exclusion = partition, which I had already made, supported by
an trigger on every insert with around 100 checks for each table (date
constraints).
2)log_destination = 'stderr'
3)logging_collector = on
4)autovacuum = off, we are doing daily vacuum analyze on all tables.
5)shared_buffers = 32MB
6)max_prepared_transactions = 100
7)work_mem = 16MB
8)maintenance_work_mem = 64MB
9)wal_writer_delay = 1000ms
10)checkpoint_segments = 16

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.

Also a thing I noticed is: When I run the query:

select relname, age(relfrozenxid) from pg_class order by age(relfrozenxid)
desc;

                     relname                      |    age
--------------------------------------------------+------------
 pg_toast_28344_index                             | 2147483647
 pg_toast_28351_index                             | 2147483647
 pg_toast_33106_index                             | 2147483647
 pg_toast_33099_index                             | 2147483647
 pg_toast_32128_index                             | 2147483647
 pg_toast_28232_index                             | 2147483647
 pg_toast_33092_index                             | 2147483647
 promo_seq                                        | 2147483647
 pg_toast_33085_index                             | 2147483647
 pg_toast_32135_index                             | 2147483647
 pg_toast_33120_index                             | 2147483647
...... There are many more. 

Regards,
Akash.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Delay-in-completion-of-aggregation-inserts-when-run-in-a-single-commit-PG-9-1-2-tp5715391p5715621.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.