Re: high transaction rate - Mailing list pgsql-general

On Wed, Dec 7, 2016 at 11:21 AM, Torsten Förtsch <tfoertsch123@gmail.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