Load testing of postgresql 8.4 for OLTP application
suitability showed that throughput of the
database significantly degraded over time from thousands of write
transactions per second to almost zero. Write transactions are in given
case insert/update/delete database transactions. The load driver used
for testing the database executed SQL queries in parallel threads and
used prepared statement and connection pooling. Postgres performance
degraded in a couple of minutes after the first run of the test, and
the
problem was reproducible with only 2 parallel client threads.
Subsequent test executions showed degraded throughput since the
beginning. The degradation has been detected only in case of write
transactions - select transactions were not affected. After some time
or
after server restart the problem is reproducible - test achieves high
throughput and then degrades again. Linux top does not show any
postgres
processes performing any significant work, CPU usage during the test
after degradation is <1%, io waits are also normal.
Machine used for the test is:
Red Hat Enterprise Linux AS release
4 (Nahant Update 6)
8 CPU @ 2GHz
16GB RAM
WAL and data are on
separate SSD drives
Server is initially configured as dedicated OLTP transaction
processing:
Options changed from default:
max_connections =
150
shared_buffers = 4GB
wal_buffers = 16MB
checkpoint_segments
= 80
maintenance_work_mem = 2GB
Modified kernel params:
kernel.shmmax =
8932986880
kernel.shmall = 2180905
kernel.sem = 500 64000 200
256
Disabling and tuning autovacuum did not give any results.
Any suggestions?
----------------------------
Täna teleka ette ei jõua? Pane film salvestama!
minuTV.ee
www.minutv.ee
