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.
-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
