Re: high transaction rate

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

Thanks,
Torsten