slow COMMITs

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux