postgresql performance

http://archives.postgresql.org/pgsql-performance/2009-06/msg00319.php

I see a lot of io wait time there.  My guess is that your DB is flooded with
synchronous writes.

IF you want to optimize the hardware for this you have a couple options.
I'm assuming your RAID 0 is not hardware RAID.

1.  Use 8.3+ and asynchronous commit  (set synchronous_commit=false).  This
is safe data wise, but if your DB crashes you might lose the last second of
transactions or so that the app thought were comitted.  For a DB forum, this
is probably very acceptable.   Performance should significantly gain as the
writes/sec will go down a lot.
(pgsql 8.3 이상 버전에서는 asynchronous commit(set synchronous_commit=false)이 쓰기에서 이점을 가진다.
단, DB가 crash 되었을때 일부 데이터를 잃을수도 있지만 대부분 무난하다고 본다.)

 

2. put your data on one partition and your WAL log on another.
(wal log와 pg data 디렉토리를 다른 파티션에 두어라.)

 

3. Get a battery backed hardware raid with write-back caching.
(H/W raid 에 있는 write-back caching 기능을 이용하라.)

 

4. If you are using ext3 on linux, make sure you mount with data=writeback
on the file system that your wal logs are on.
data=ordered will cause the WHOLE file sytem to be flushed for each fsync,
not just the tiny bit of WAL log.
(만약 리눅스의 ext3 를 쓰고 있다면 wal logs 쪽이 파일시스템 마운트 옵션이 writeback으로 되어 있는지 확인해라.
마운트 옵션이 ordered 이면 원인이 된다 단지 WAL log의 조그마한 비트가 아니라, 전체 파일시스템에서 각 fsync를 위해 플러시 될것이다.)

In short, if you combined 1,2, and 4, you'll probably have significantly
more capacity on the same server.
So make sure your WAL log is in a different file system from your OS and data,
mount it optimally, and
consider turning synchronous_commit off.

If you're using RAID 0, I doubt the data is so precious that
synchronous_commit being true is important at all.