On Wed, Dec 7, 2016 at 11:31 AM, Torsten Förtsch <tfoertsch123@xxxxxxxxx> wrote:
On Wed, Dec 7, 2016 at 11:21 AM, Torsten Förtsch <tfoertsch123@xxxxxxxxx> wrote:Hi,I need to tune my database for a high update rate of a single small table. A little simplified it looks like this:CREATE TABLE temp_agg(topic TEXT PRIMARY KEY,tstmp TIMESTAMP,cnt BIGINT,sum NUMERIC)The table has 500 rows.A transaction looks simplified like this:1) select * from temp_agg where topic=$1 for update2) if date_trunc('second', tstmp)=date_trunc('second', $3) then:2a) update temp_agg set cnt=cnt+1, sum=sum+$2 where topic=$12b) set local synchronous_commit to off3) if a new second has started:3a) insert into other_table select * from temp_agg where topic=$13b) update temp_agg set tstmp=date_trunc('second', $3) cnt=1, sum=$2 where topic=$13c) emit a notification (pg_notify(...)) with the new datasorry, hit the wrong button.As a first test my program starts 10 writers each of which serves 50 topics. The timestamps are generated in a way that on average 10 timestamps per second per topic are produced. That means on average the 2) branch is hit 10 times more often.These 10 writers then flood the database. At first I see a really good transaction rate of more than 6500 tx/sec. But after a while it suddenly drops to less than 1/10 of that. I also monitored the number of processes waiting for locks. As can be seen in this picture, this is a locking problem.
Here is a snapshot of not granted locks:locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
----------+----------+----------+--------+--------+--------- ---+---------------+---------+ -------+----------+----------- ---------+-------+------------ ---------+---------+----------
object | 0 | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | 1262 | 0 | 0 | 15/6381185 | 11468 | AccessExclusiveLock | f | f
object | 0 | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | 1262 | 0 | 0 | 13/6375058 | 11465 | AccessExclusiveLock | f | f
object | 0 | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | 1262 | 0 | 0 | 9/6373397 | 11463 | AccessExclusiveLock | f | f
object | 0 | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | 1262 | 0 | 0 | 11/6380027 | 11464 | AccessExclusiveLock | f | f
object | 0 | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | 1262 | 0 | 0 | 3/447139 | 11133 | AccessExclusiveLock | f | f
object | 0 | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | 1262 | 0 | 0 | 7/6375244 | 11461 | AccessExclusiveLock | f | f
object | 0 | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | 1262 | 0 | 0 | 16/6381714 | 11467 | AccessExclusiveLock | f | f
object | 0 | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | 1262 | 0 | 0 | 10/6380578 | 11460 | AccessExclusiveLock | f | f
object | 0 | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | 1262 | 0 | 0 | 8/6374490 | 11459 | AccessExclusiveLock | f | f
object | 0 | <NULL> | <NULL> | <NULL> | <NULL> | <NULL> | 1262 | 0 | 0 | 12/6377255 | 11462 | AccessExclusiveLock | f | fWhat are these object locks here? How can I prevent contention?This is pgdg postgres 9.5
1262 is 'pg_database'::regclass::oid
I don't know for sure but things I would worry about given the performance profile are:
1. NUMA swap insanity
1. NUMA swap insanity
2. Accumulation of dead tuples leading to what should be very short operations taking longer.
No idea of that is helpful but where I would probably start
Thanks,Torsten
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
