Re: high transaction rate

On Wed, Dec 7, 2016 at 11:31 AM, Torsten Förtsch <tfoertsch123(at)gmail(dot)com>
wrote:

> On Wed, Dec 7, 2016 at 11:21 AM, Torsten Förtsch <tfoertsch123(at)gmail(dot)com>
> 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 update
>>
>> 2) if date_trunc('second', tstmp)=date_trunc('second', $3) then:
>> 2a) update temp_agg set cnt=cnt+1, sum=sum+$2 where topic=$1
>> 2b) set local synchronous_commit to off
>>
>> 3) if a new second has started:
>> 3a) insert into other_table select * from temp_agg where topic=$1
>> 3b) update temp_agg set tstmp=date_trunc('second', $3) cnt=1, sum=$2
>> where topic=$1
>> 3c) emit a notification (pg_notify(...)) with the new data
>>
>> sorry, 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 | f
>
> What 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
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.
http://www.efficito.com/learn_more