Latency problems with simple queries

From: Adrian Schreyer <ams214(at)cam(dot)ac(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Latency problems with simple queries
Date: 2011-07-07 11:13:15
Message-ID: CAD=_bDnoTeYxGSw3b+7Fjeb6rrsZ18_nxCwPPrMDwYTNPAMkUg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I randomly get latency/performance problems even with very simple
queries, for example fetching a row by primary key from a small table.
Since I could not trace it back to specific queries, I decided to give
LatencyTOP (http://www.latencytop.org/) a go. Soon after running a
couple of queries, I saw this in latencytop whilst a query was hanging
in postgres:

Cause Maximum Percentage
Writing a page to disk 19283.9 msec 99.7

the disk configuration is as follows:

RAID controller: LSI MegaRAID 9261
tablespace is on a dedicated RAID10 volume, xlog on its own RAID1 and
another disk for temporary data.

Volumes are mounted with noatime,errors=remount-ro.

This are the sysctl.conf changes I made (machine has 48GB memory)

kernel.shmmax = 25344188416
kernel.shmall = 6187546
vm.swappiness = 0
vm.overcommit_memory = 2
vm.dirty_background_ratio = 1
vm.dirty_ratio = 2
vm.zone_reclaim_mode = 0

Maybe someone has seen this before and can give me some advice.

Adrian

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Hartveld 2011-07-07 12:12:29 Re: Streaming replication on 9.1-beta2 after pg_restore is very slow
Previous Message Peter Geoghegan 2011-07-07 08:49:02 Re: Request for help - Does anyone in Seattle need a Postgres job?