long running commits

From: "Vaughn, Adam (IMS)" <VaughnA(at)imsweb(dot)com>
To: "'pgsql-admin(at)postgresql(dot)org'" <pgsql-admin(at)postgresql(dot)org>
Cc: "Depuy, Scott (IMS)" <DepuyS(at)imsweb(dot)com>, "Meagher, Kevin (IMS)" <MeagherK(at)imsweb(dot)com>
Subject: long running commits
Date: 2011-02-25 19:50:06
Message-ID: 6B760A41CEE02F46BADF29B4A874D6010F11C13A63@FALCON.omni.imsweb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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(at)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.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Kevin Grittner 2011-02-25 22:45:04 Re: long running commits
Previous Message Serge Dubrouski 2011-02-25 16:22:51 Streaming replication and Timelines