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, compiled with 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 was 8x 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 are some 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
