From: Akash <akash.kodibail@onmobile.com>
To: pgsql-admin@postgresql.org
Subject: Re: Delay in completion of aggregation inserts when run in a single commit - PG 9.1.2
Date: Thu, 5 Jul 2012 22:06:13 -0700 (PDT)
Message-ID: <1341551173972-5715621.post@n5.nabble.com> (raw)
In-Reply-To: <4FF5354E.8090105@ringerc.id.au>
References: <67A07D932F8DCC41989A050EDE427A210CF9B5@JETWINSRVRPS02.onmobile.com>
<4FF5354E.8090105@ringerc.id.au>
@@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-sin...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: akash.kodibail@onmobile.com
Subject: Re: Delay in completion of aggregation inserts when run in a single commit - PG 9.1.2
In-Reply-To: <1341551173972-5715621.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox