MIME-Version: 1.0
From: Dimitris Zenios <dimitris.zenios@gmail.com>
Date: Tue, 30 Apr 2024 13:04:44 +0300
Message-ID: <CAGSzHa0WS6pQM6fvrC1Uq74AON9sZgZj0LOkDe=_yrsVMfcx=w@mail.gmail.com>
Subject: pg_notify contention
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000f5879306174d81fa"
Archived-At: <https://www.postgresql.org/message-id/CAGSzHa0WS6pQM6fvrC1Uq74AON9sZgZj0LOkDe%3D_yrsVMfcx%3Dw%40mail.gmail.com>
Precedence: bulk

--000000000000f5879306174d81fa
Content-Type: text/plain; charset="UTF-8"

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

--000000000000f5879306174d81fa
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi,=C2=A0<div><br></div><div>I am measuring a very simple =
case of pg_notify in an after update trigger. The trigger is the following:=
<br><br>CREATE<br>=C2=A0 =C2=A0 OR REPLACE FUNCTION audit_event() RETURNS T=
RIGGER AS<br>$$<br>BEGIN<br>=C2=A0 =C2=A0 PERFORM pg_notify(&#39;user&#39;,=
 &#39;hello world&#39;);<br>=C2=A0 =C2=A0 RETURN NULL;<br>END;<br>$$<br>=C2=
=A0 =C2=A0 LANGUAGE plpgsql;</div><div><br></div><div>and configured on the=
 table with the following sql</div><div><br></div><div>CREATE CONSTRAINT TR=
IGGER trigger_test AFTER UPDATE ON pgbench_accounts FOR EACH ROW EXECUTE PR=
OCEDURE audit_event()<br><br>I am running two benchmarks. One with the pg_n=
otify on the trigger enabled and one with the notify commented out.<br><br>=
The command is the following:<div>pgbench -f /tmp/update_bench.sql =C2=A0-c=
 10 -j 10 -t 100 benchdb<br><br>And the content of the update_bench.sql are=
 the following</div><div><br></div><div>\set aid random(1, 100000 * :scale)=
<br>\set delta random(-5000, 5000)<br>BEGIN;<br>UPDATE pgbench_accounts SET=
 abalance =3D abalance + :delta WHERE aid =3D :aid;<br>END;<br><div><br></d=
iv><div>Results are</div><div><br></div><div>- With pg_notify disabled</div=
><div><br></div><div>pgbench (16.1)<br>starting vacuum...end.<br>transactio=
n type: /tmp/update_bench.sql<br>scaling factor: 1<br>query mode: simple<br=
>number of clients: 10<br>number of threads: 10<br>maximum number of tries:=
 1<br>number of transactions per client: 100<br>number of transactions actu=
ally processed: 1000/1000<br>number of failed transactions: 0 (0.000%)<br>l=
atency average =3D 11.744 ms<br>initial connection time =3D 15.616 ms<br>tp=
s =3D 851.531991 (without initial connection time)<br></div><div><br></div>=
<div>- With pg_notify enabled</div><div><br></div><div>pgbench (16.1)<br>st=
arting vacuum...end.<br>transaction type: /tmp/update_bench.sql<br>scaling =
factor: 1<br>query mode: simple<br>number of clients: 10<br>number of threa=
ds: 10<br>maximum number of tries: 1<br>number of transactions per client: =
100<br>number of transactions actually processed: 1000/1000<br>number of fa=
iled transactions: 0 (0.000%)<br>latency average =3D 56.927 ms<br>initial c=
onnection time =3D 11.182 ms<br>tps =3D 175.664989 (without initial connect=
ion time)<br></div><div><br></div><div>There is a huge drop in TPS from 851=
 to 175.</div><div><br></div><div>I also noticed that if I run the test wit=
h a single connection -c 1 then the results are nearly identical which make=
s me assume that this is a contention that occurs between multiple connecti=
ons.</div><div><br></div><div>Thanks</div><div><br></div></div></div></div>

--000000000000f5879306174d81fa--