Degrading PostgreSQL 8.4 write performance

Поиск
Список
Период
Сортировка
От Kabu Taah
Тема Degrading PostgreSQL 8.4 write performance
Дата
Msg-id 20110617124857.1A82B432167@Bounce1.estpak.ee
обсуждение исходный текст
Ответы Re: Degrading PostgreSQL 8.4 write performance  (Merlin Moncure <mmoncure@gmail.com>)
Re: Degrading PostgreSQL 8.4 write performance  (Greg Smith <greg@2ndQuadrant.com>)
Re: Degrading PostgreSQL 8.4 write performance  ("Pierre C" <lists@peufeu.com>)
Список 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 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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Svetlin Manavski
Дата:
Сообщение: Re: seq scan in the case of max() on the primary key column
Следующее
От: Merlin Moncure
Дата:
Сообщение: Re: Degrading PostgreSQL 8.4 write performance