In the past 2 days we have been experiencing problems on our production server with very long running commit statements which seem to cause the entire instance to become blocked and non-responsive. These are the log messages for the 2 commit statements (these occurred inside of a 1 hour block) duration: 945599.193 ms execute S_2: COMMIT duration: 967507.896 ms execute S_2: COMMIT Can anyone provide some assistance in how we should diagnose this problem (tools) and some possible solutions for solving it? Thanks in advance Some relevant information about our setup: PostgreSQL 8.3.9 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.3 Sun Fire X4150, single quad core, 32 GB RAM Suse Enterprise Linux 9 SP #4 Linux version 2.6.5-7.308-smp (geeko@buildhost) (gcc version 3.3.3 (SuSE Linux)) #1 SMP Mon Dec 10 11:36:40 UTC 2007 NetApp FAS3170, RAID_DP on a 13 disk aggregate. The disks are 450 GB FC 15K RPM. The filer is busy, but not overly so, several other projects are pushing more reads/writes than this instance. Dedicated storage network on a non-blocking wire speed Extreme switch. Gigabit connectivity for both piranha and the NetApp 3170. None of the Ethernet links are exceeding 60% utilization. Our non-default configuration parameters: bgwriter_delay 100 bgwriter_lru_maxpages 100 bgwriter_lru_multiplier 2.2 checkpoint_completion_target 0.9 checkpoint_segments 60 checkpoint_timeout 900 checkpoint_warning 300 cpu_index_tuple_cost 0.0025 cpu_operator_cost 0.0008 cpu_tuple_cost 0.005 random_page_cost 2 shared_buffers 6GB temp_buffers 48 MB wal_buffers 16 MB wal_writer_delay 200ms work_mem 48 MB Information in this e-mail may be confidential. It is intended only for the addressee(s) identified above. If you are not the addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender of the error. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
