In the past 2 days we have been experiencing problems on our production server with very long running commit statements
whichseem to cause the entire instance to become blocked and non-responsive. These are the log messages for the 2
commitstatements (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
solvingit? 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
NetApp3170. 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
notthe addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or
copyingof this communication is strictly prohibited. If you have received this e-mail in error, please notify the
senderof the error.