4

Load testing of postgresql 8.4 for OLTP application suitability resulted in unpleasant situation when 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 ideas how to fight this problem?

Improve this question

2 Answers 2

Reset to default
5

Assuming it's not a VACUUM problem, it sounds like you've got an unindexed FOREIGN KEY. During the bulk load, do you see an increase in CPU or disk IO?

For giggles, can you please post the output from a VACUUM VERBOSE ANALYZE table_name? An EXPLAIN ANALYZE INSERT ... may also be useful along with as much of the relevant schema.

Improve this answer
5
  • The table does not have any FK's. During performance degradation CPU consumption does not increase. IO increases, but not significantly.
    – OlegT
    Commented Jun 21, 2011 at 11:29
  • I cannot provide vacuum/explain output right now, but I have noticed, that there are multiple tables, that cause performance degradation on bulk updates, and all they have one common thing - columns of type bytea.
    – OlegT
    Commented Jun 21, 2011 at 11:43
  • Furthermore performance degrades even if update queries do not modify the bytea column. If I drop the bytea column from the table, the throughput bottleneck almost vanishes (at some short <10s periods throughput drops 2x down, but does not go below hundreds of updates/sec anymore). So the problem seems to be whether in bytea type or in average row size (test table has 64 bytes in each bytea value). Are there any known rules of thumb when dealing with bytea or performance issues caused by bytea?
    – OlegT
    Commented Jun 21, 2011 at 11:43
  • @OlegT, there are zero constraints on the PostgreSQL side of things in this regard. If the total size of rows are >1K in size, I'd start to throw out a few ideas. At 64 bytes, there should be no problems. BYTEA is a fundamental storage blob that most data types "inherit" from (or as close as you can in C using the VARLENA structure). Can you SELECT * FROM pg_catalog.pg_locks during an UPDATE? A yet-to-be-disclosed something is the culprit. This problem seems very specific to your environment and I'd suggest you jump in to the #PostgreSQL channel for further debugging and discussion.
    – Sean
    Commented Jun 21, 2011 at 17:38
  • Last thoughts, if you don't have any FOREIGN KEYs, do you have any INDEXes on the TABLE? Any TABLE inheritance? Is autovacuum running? Is there anything special about your file system?
    – Sean
    Commented Jun 21, 2011 at 17:51
1

Please clarify this statement: Disabling and tuning autovacuum did not give any results

What did you do to tune autovacuum? What evidence do you have that the database isn't getting bloated during this test? I run 48 core monster servers that do dozens to hundreds of updates a second, and have been for years now. If this were a problem I'd have noticed. note that autovacuum had to be made much more aggressive to handle this load.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.