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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
