Hi everybody!
My system is 2xXEON 3 GHz, 4GB RAM, RAID-10 (4 SCSI HDDs), running Postgres 8.1.0, taken from CVS REL8_1_STABLE,
compiledwith gcc-3.4 with options "-march=nocona -O2 -mfpmath=sse -msse3". Hyperthreading is disabled.
There are about 300,000 - 500,000 transactions per day. Database size is about 14 Gigabytes.
The problem is that all queries run pretty good except transaction COMMITs.
Sometimes it takes about 300-500 ms to commit a transaction and it is unacceptebly slow for my application.
I had this problem before, on 8.0.x and 7.4.x, but since 8.1 upgrade all queries began to work very fast except commit.
BTW, I ran my own performance test, a multithreaded typical application user emulator, on 7.4 and 8.1. 8.1 performance
was8x times faster than 7.4, on the same machine and with the same config file settings.
Some settings from my postgresql.conf:
shared_buffers = 32768
temp_buffers = 32768
work_mem = 12228
bg_writer_delay = 400
wal_buffers = 128
commit_delay = 30000
checkpoint_segments = 8
effective_cache_size = 262144 # postgres is the one and the only application on this machine
default_statistics_target = 250
all statistic collection enabled
autovacuum runs every 120 seconds. vacuum is run after 2000 updates, analyze is run after 1000 updates.
I've run vmstat to monitor hard disk activity. It was 50-500 Kb/sec for reading and 200-1500 Kb/sec for writing. There
aresome peak hdd reads and writes (10-20Mb/s) but commit time does not always depend upon them.
What parameters should I tune?
--
Evgeny Gridasov
Software Engineer
I-Free, Russia