Skip site navigation (1) Skip section navigation (2)

Re: Seeking performance advice and explanation for high I/O on 8.3

From: Ivan Voras <ivoras(at)freebsd(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Seeking performance advice and explanation for high I/O on 8.3
Date: 2009-09-03 15:11:02
Message-ID: h7om9u$4r5$ (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Scott Otis wrote:

 > 2x Intel Xeon Quad Core (@2 Ghz - Clovertown,L5335)
 > 4GB RAM
 > 4x Seagate 73GB SAS HDD 10k RPM – in RAID ( stripped and mirrored )

> Would love to get some advice on how to change my conf settings / setup 
> to get better I/O performance.

> ~1500 databases w/ ~60 tables each

This tells us nothing - size and complexity of databases is more 
important than their number.

> Total I/O (these number are pretty constant throughout the day):
> Reads: ~ 100 / sec for about 2.6 Mb/sec
> Writes: ~ 400 /sec for about 46.1Mb/sec

Again, not enough information. How did you measure these? With iostat? 
Are those random reads or sequential? (i.e. what was the IO transaction 
size?) Caching can explain why you have 4x more writes than reads, but 
it's still unusual, especially with the high write transfer rate you claim.

If random, you're doing ~~ 500 IOPS on a RAID10 array of 4 10 kRPM 
drives, which is much more than you should - you're lucky you have the 
performance you do.

By the way, why do you think your setup is slow? Is your application 
slow and you think your database is the reason?

> shared_buffers = 1GB
> work_mem = 5MB
> maintenance_work_mem = 256MB


> synchronous_commit = off

Ok. Could be important if your IO is slow as yours is.

> checkpoint_segments = 6

You could try raising this to 20, but I doubt it will help you that 
much. OTOH it won't hurt.

> checkpoint_warning = 30s
> effective_cache_size = 1GB


> Most of the SQL happening is selects – very little inserts, updates and 
> deletes comparatively.

Are you sure? Your write rate is a bit big for there to be very little 
insert/update/delete activity.

In response to


pgsql-performance by date

Next:From: Kevin GrittnerDate: 2009-09-03 15:27:50
Subject: Re: Slow select times on select with xpath
Previous:From: Andy ColsonDate: 2009-09-03 15:02:56
Subject: Re: Seeking performance advice and explanation for high I/O on 8.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group