Re: high transaction rate - Mailing list pgsql-general

From Torsten Förtsch
Subject Re: high transaction rate
Date
Msg-id CAKkG4_=TVn=y=yn+xJh=XTvpLbfSm5_Wpj8CCFrJSV0EtMTb2g@mail.gmail.com
Whole thread Raw
In response to Re: high transaction rate  (Torsten Förtsch <tfoertsch123@gmail.com>)
List pgsql-general
Think I found it. classid 1262 is pg_database and I seem to remember that NOTIFY takes that lock. I dropped pg_notify from my function and got immediately >3500 tx/sec.

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

Attachment

pgsql-general by date:

Previous
From: Chris Travers
Date:
Subject: Re: high transaction rate
Next
From: Juliano
Date:
Subject: repmgr new standby node without clone master data