Skip site navigation (1) Skip section navigation (2)

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

From: Akash <akash(dot)kodibail(at)onmobile(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Delay in completion of aggregation inserts when run in a single commit - PG 9.1.2
Date: 2012-07-06 05:06:13
Message-ID: 1341551173972-5715621.post@n5.nabble.com (view raw or flat)
Thread:
Lists: pgsql-admin
@@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.

In response to

Responses

pgsql-admin by date

Next:From: Craig RingerDate: 2012-07-06 05:28:43
Subject: Re: Re: Delay in completion of aggregation inserts when run in a single commit - PG 9.1.2
Previous:From: Tom LaneDate: 2012-07-05 16:03:21
Subject: Re: auto vacuum errors

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group