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

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

Ok.

> 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

Ok.

> 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.