Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a Postgres database I am hosting in a quad core server with 8 GB of RAM.

At any time, there can be 100 or so reads and writes occurring concurrently. I am experiencing high IO wait time. When I do "top", most of my cores have very high IO wait % - 50% - 70%.

I have a feeling I need to fine-tune some parameters in my Postgres. But which ones would effect IO usage the most? Would adding more cores help in reducing IO usage?

share|improve this question
    
Having IO problems, the question should tell something about the IO subsystem :) Disks, RAID controllers etc. –  dezso Apr 15 '13 at 14:03
    
Many Linux distributions use default parameters for a minimal footprint. Please specify your hardware configuration and operating system and any other information about file system or LVM. –  eppesuig Apr 15 '13 at 18:38
    
May be first you shoud tune sql , and 8 GB RAM seems to little. –  francs Apr 16 '13 at 2:19
    
In addition to the other requests for info, is this server only running PostgreSQL? Is it running other things as well? –  Chris Travers Apr 23 '13 at 8:13
add comment

1 Answer

The single best thing you can do to reduce disk I/O load is to ensure that your database fits comfortably in RAM after allocating RAM to everything else running on the system. If this is the case, your disk I/O will be primarily WAL flushes on commit, which is mostly sequential I/O.

The second thing you can do is address the shared_buffers and effective_cache_size settings. If the system is exclusively running PostgreSQL I like to set shared_buffers low, and effective_cache_size high and adjust from there. If the system is running other programs too, shared_buffers should be set much higher because it reserves RAM exclusively for PostgreSQL. Note that the OS disk cache is often faster than the PostgreSQL cache, but the Pg cache is more full-featured so there is a real balance. Measure, test, repeat.

A third setting to be aware of is work_mem. This setting should be adjusted only with care, because it is per operation. If you set it too low, joins and sorts will write to disk. If you set it too high, it will squeeze out memory that could be used for caching data. Again, look for query performance patterns and move forward.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.