Skip site navigation (1) Skip section navigation (2)

Degrading PostgreSQL 8.4 write performance

From: Kabu Taah <kabuutah(at)hot(dot)ee>
To: pgsql-performance(at)postgresql(dot)org
Subject: Degrading PostgreSQL 8.4 write performance
Date: 2011-06-17 12:48:47
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
                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
 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
 after server restart the problem is reproducible - test achieves high 
throughput and then degrades again. Linux top does not show any
 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
WAL and data are on
separate SSD drives 

Server is initially configured as dedicated OLTP transaction

Options changed from default:
max_connections =
shared_buffers = 4GB
wal_buffers = 16MB
= 80
maintenance_work_mem = 2GB

Modified kernel params:
kernel.shmmax =
kernel.shmall = 2180905
kernel.sem = 500 64000 200


Disabling and tuning autovacuum did not give any results. 

Any suggestions?


Täna teleka ette ei jõua? Pane film salvestama!


pgsql-performance by date

Next:From: Merlin MoncureDate: 2011-06-17 13:29:31
Subject: Re: Performance advice for a new low(er)-power server
Previous:From: Shaun ThomasDate: 2011-06-17 12:43:46
Subject: Re: seq scan in the case of max() on the primary key column

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group