public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dimitris Zenios <[email protected]>
To: [email protected]
Subject: pg_notify contention
Date: Tue, 30 Apr 2024 13:04:44 +0300
Message-ID: <CAGSzHa0WS6pQM6fvrC1Uq74AON9sZgZj0LOkDe=_yrsVMfcx=w@mail.gmail.com> (raw)

Hi,

I am measuring a very simple case of pg_notify in an after update trigger.
The trigger is the following:

CREATE
    OR REPLACE FUNCTION audit_event() RETURNS TRIGGER AS
$$
BEGIN
    PERFORM pg_notify('user', 'hello world');
    RETURN NULL;
END;
$$
    LANGUAGE plpgsql;

and configured on the table with the following sql

CREATE CONSTRAINT TRIGGER trigger_test AFTER UPDATE ON pgbench_accounts FOR
EACH ROW EXECUTE PROCEDURE audit_event()

I am running two benchmarks. One with the pg_notify on the trigger enabled
and one with the notify commented out.

The command is the following:
pgbench -f /tmp/update_bench.sql  -c 10 -j 10 -t 100 benchdb

And the content of the update_bench.sql are the following

\set aid random(1, 100000 * :scale)
\set delta random(-5000, 5000)
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
END;

Results are

- With pg_notify disabled

pgbench (16.1)
starting vacuum...end.
transaction type: /tmp/update_bench.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 10
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 1000/1000
number of failed transactions: 0 (0.000%)
latency average = 11.744 ms
initial connection time = 15.616 ms
tps = 851.531991 (without initial connection time)

- With pg_notify enabled

pgbench (16.1)
starting vacuum...end.
transaction type: /tmp/update_bench.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 10
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 1000/1000
number of failed transactions: 0 (0.000%)
latency average = 56.927 ms
initial connection time = 11.182 ms
tps = 175.664989 (without initial connection time)

There is a huge drop in TPS from 851 to 175.

I also noticed that if I run the test with a single connection -c 1 then
the results are nearly identical which makes me assume that this is a
contention that occurs between multiple connections.

Thanks

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: pg_notify contention
  In-Reply-To: <CAGSzHa0WS6pQM6fvrC1Uq74AON9sZgZj0LOkDe=_yrsVMfcx=w@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox