Checkpoints and slow queries

(I am resending this question after waiting for several hours because my
previous mail got stalled probably because I didn't confirm my email
address after subscribing. So resending the mail. Sorry if this is causing
a double post.)

*Problem description:*
After a few days of running in my test environment, a query timed out
(query timeout=4mins). Also in general the queries were taking a lot longer
than expected. The workload in my database is a write intensive workload.
And the writes happen in a burst every 5 minutes. There are a whole bunch
of insert and update queries that run every 5 minutes. When I analyzed the
situation (by enabling more postgres logs), I noticed that postgres
checkpoints were triggering approximately every 5 minutes and based on my
online research I suspected the i/o overhead of checkpoints was affecting
the query performance. The checkpoint related settings were:
checkpoint_segments = 30
checkpoint_timeout = 15min

I modified these settings to the following:
checkpoint_segments = 250
checkpoint_timeout = 1h
checkpoint_completion_target = 0.9

After I tweaked these settings, checkpoints were happening only once in an
hour and that improved the query performance. However, when the checkpoint
happens every hour, the query performance is still very poor. This is still
undesirable to my system.

I also tried editing dirty_background_ratio and dirty_expire_centisecs in
/etc/sysctl.conf. All dirty related kernel settings:

># sysctl -a | grep dirty

vm.dirty_background_ratio = 1

vm.dirty_background_bytes = 0

vm.dirty_ratio = 20

vm.dirty_bytes = 0

vm.dirty_writeback_centisecs = 500

vm.dirty_expire_centisecs = 500

This also didn't improve the situation.
My question is how to track down the reason for the poor performance during
checkpoints and improve the query performance when the checkpoints happen?

- *EXPLAIN ANALYZE:*
- http://explain.depesz.com/s/BNva - An insert query inserting just
129 rows takes 20 seconds.
- http://explain.depesz.com/s/5hA - An update query updating 43926
rows takes 55 seconds.
- *History:* It gets slower after a few days of the system running.

*Table Metadata*:

- The tables get updated every 5 minutes. Utmost 50000 rows in a table
get updated every 5 minutes. About 50000 rows get inserted every 1 hour.
- There are 90 tables in the DB. 43 of these are updated every 5
minutes. 8/90 tables receive a high update traffic of 50000 updates/5mins.
Remaining tables receive an update traffic of 2000 updates/5min. 43/90
tables are updated every 1 hour.

*PostgreSQL version: *PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu,
compiled by gcc (GCC) 4.6.x-google 20120601 (prerelease), 64-bit

*How you installed PostgreSQL: *Compiled from source and installed.

*Changes made to the settings in the postgresql.conf file:*

name | current_setting | source

------------------------------+------------------------+----------------------

application_name | psql | client

checkpoint_completion_target | 0.9 | configuration file

checkpoint_segments | 250 | configuration file

checkpoint_timeout | 1h | configuration file

client_encoding | SQL_ASCII | client

client_min_messages | error | configuration file

constraint_exclusion | on | configuration file

DateStyle | ISO, MDY | configuration file

default_statistics_target | 800 | configuration file

default_text_search_config | pg_catalog.english | configuration file

effective_cache_size | 4GB | configuration file

lc_messages | C | configuration file

lc_monetary | C | configuration file

lc_numeric | C | configuration file

lc_time | C | configuration file

listen_addresses | localhost | configuration file

log_autovacuum_min_duration | 20s | configuration file

log_checkpoints | on | configuration file

log_connections | on | configuration file

log_destination | syslog | configuration file

log_disconnections | on | configuration file

log_line_prefix | user=%u,db=%d | configuration file

log_lock_waits | on | configuration file

log_min_duration_statement | 1s | configuration file

log_min_messages | error | configuration file

log_temp_files | 0 | configuration file

log_timezone | PST8PDT,M3.2.0,M11.1.0 | environment
variable

maintenance_work_mem | 64MB | configuration file

max_connections | 12 | configuration file

max_locks_per_transaction | 700 | configuration file

max_stack_depth | 2MB | environment
variable

port | 5432 | configuration file

shared_buffers | 500MB | configuration file

ssl | off | configuration file

statement_timeout | 4min | configuration file

syslog_facility | local1 | configuration file

syslog_ident | postgres | configuration file

temp_buffers | 256MB | configuration file

TimeZone | PST8PDT,M3.2.0,M11.1.0 | environment
variable

wal_buffers | 1MB | configuration file

work_mem | 128MB | configuration file

*Operating system and version: *Scientific Linux release 6.1 (Carbon)

*What program you're using to connect to PostgreSQL: *C++ libpqxx library

- *Relevant Schema*: All tables referenced in this question have this
same schema

managed_target_stats=> \d stat_300_3_1

Table "public.stat_300_40110_1"

Column | Type | Modifiers

--------+---------+-----------

ts | integer |

target | bigint |

port | integer |

data | real[] |

Indexes:

"unique_stat_300_40110_1" UNIQUE CONSTRAINT, btree (ts, target, port)

"idx_port_stat_300_40110_1" btree (port)

"idx_target_stat_300_40110_1" btree (target)

"idx_ts_stat_300_40110_1" btree (ts)

- *Hardware*:
- CPU: Intel(R) Xeon(R) CPU E5205 @ 1.86GHz
- Memory: 6GB
- Storage Details:

There are 2 500GB disks (/dev/sda, /dev/sdb) with the following 6
partitions on each disk.

*Number Start End Size Type File system Flags*

1 512B 24.7MB 24.7MB primary boot

2 24.7MB 6473MB 6449MB primary linux-swap(v1)

3 6473MB 40.8GB 34.4GB primary ext3

4 40.8GB 500GB 459GB extended lba

5 40.8GB 408GB 367GB logical ext3

6 408GB 472GB 64.4GB logical ext3

*Disk model and details:*

Model Family: Western Digital RE3 Serial ATA family

Device Model: WDC WD5002ABYS-02B1B0

Serial Number: WD-WCASYD132237

Firmware Version: 02.03B03

User Capacity: 500,107,862,016 bytes

Device is: In smartctl database [for details use: -P show]

ATA Version is: 8

ATA Standard is: Exact ATA specification draft version not indicated

Local Time is: Sun Apr 27 05:05:13 2014 PDT

SMART support is: Available - device has SMART capability.

SMART support is: Enabled

The postgres data is stored on a software RAID10 on partition 5 of both
these disks.

[admin(at)chief-cmc2 tmp]# mdadm --detail /dev/md3

/dev/md3:

Version : 0.90

Creation Time : Wed Mar 19 06:40:57 2014

Raid Level : raid10

Array Size : 358402048 (341.80 GiB 367.00 GB)

Used Dev Size : 358402048 (341.80 GiB 367.00 GB)

Raid Devices : 2

Total Devices : 2

Preferred Minor : 3

Persistence : Superblock is persistent

Update Time : Sun Apr 27 04:22:07 2014

State : active

Active Devices : 2

Working Devices : 2

Failed Devices : 0

Spare Devices : 0

Layout : far=2

Chunk Size : 64K

UUID : 79d04a1b:99461915:3d186b3c:53958f34

Events : 0.24

Number Major Minor RaidDevice State

0 8 5 0 active sync /dev/sda5

1 8 21 1 active sync /dev/sdb5

- *Maintenance Setup*: autovacuum is running with default settings. Old
records are deleted every night. I also do 'vacuum full' on a 12 tables
that receive large number of updates every night at 1AM. I have noticed
that these 'vacuum full' also time out. (I am planning to post a separate
question regarding my vacuuming strategy).
- *WAL Configuration*: The WAL is in the same disk.