Re: Degrading PostgreSQL 8.4 write performance

On Fri, Jun 17, 2011 at 7:48 AM, Kabu Taah <kabuutah@hot.ee> wrote:
> 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.

There are a ton of potential causes of this.  The problem could be in
your code, the database driver, etc.  The first step is to try and
isolate a query that is not running properly and to benchmark it with
explain analyze.   Being able to reproduce the problem in pgbench
would explain a lot as well.

merlin