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

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