Re: Posgresql 14 and CarbonBlack on RHEL8?

[Tom Lane]

Ron Johnson  writes:
> When running stress tests on the systems (in prod, during the maintenance
> window), 171K events/second are generated on the RHEL8 servers, and CB
> needs (according to top(1)) 325% of CPU to handle that, and still dropping
> 92% of them.
> The RHEL6 system doesn't bat an eye at running the exact same test (36 cron
> jobs running psql executing SELECT statements).

Is JIT enabled on the newer system?  If so try turning it off, or else
raise the associated cost settings.  We've seen lots of reports of
workloads where, by default, the planner is too aggressive about
applying JIT.

regards, tom lane

Posgresql 14 and CarbonBlack on RHEL8?

[Ron Johnson]

(CarbonBlack is cross-platform AV software sold by VMware.)

Currently we're running PG 9.6.24 on RHEL 6.10 with CB (version unknown to
me) in production, and testing PG 14.11 on RHEL 8.9 with CB 2.15.2
(hopefully going into production next month).

Both old and new VMs are 32 CPU with 128GB RAM.
Nothing but PG, CB and itsm software runs on these systems.

When running stress tests on the systems (in prod, during the maintenance
window), 171K events/second are generated on the RHEL8 servers, and CB
needs (according to top(1)) 325% of CPU to handle that, and still dropping
92% of them.
The RHEL6 system doesn't bat an eye at running the exact same test (36 cron
jobs running psql executing SELECT statements).

The small RHEL8/PG14 non-prod systems show similar load when lots of SELECT
statements run.

Has anyone else seen this?  If so, how did you resolve it?

Re: Linked directory or explicit reference

[Ron Johnson]

On Tue, Apr 30, 2024 at 7:00 PM Senor Cervesa 
wrote:

> Hi All;
>
> When doing an initial install of PostgreSQL on RHEL 7 or 8 derived OS via
> rpm, what are pros, cons and recommendations of these 2 procedures for
> utilizing a second disk?
>
> Secondary SSD or RAID mounted at /disk2.
>
> Option #1
>
>1. install the rpm which creates basic user and home
>2. Create symlink /var/lib/pgsql/15/data --> /disk2/data
>3. initdb with no special options
>
> Or Option #2
>
>1. install the rpm which creates basic user and home
>2. initdb with --pgdata=/disk2/data
>Probably using included 'postgresql-12-setup' script
>
> I also link /var/lib/pgsql/data  --> ../15/data so automation can
> reference postgresql.conf without knowing version (legacy stuff).
>

In my experience,The PgBackRest restore feature does not like symlinks.


> The install is automated with a bash script which handles several options
> including whether there is a second disk for DB. Scripting the install with
> or without the second disk is straight forward but I'm concerned with
> either scenario causing unforeseen differences.
>
> I don't think there's a benefit to using tablespace here but I have no
> experience with it. The systemd service is configured with a dependency on
> the disk mount so I don't think there are different risks for starting
> postgres with missing data directory.
>
> I've run postgres in both scenarios and not had any issues. I'm interested
> in comments from others on their experience using these or other options.
>
Is the mount point just "/disk2" when using "--pgdata=/disk2/data"?  I've
gotten "directory not empty" errors when the mount point is
"/Database/x.y/data".

Linked directory or explicit reference

[Senor Cervesa]

Hi All;

When doing an initial install of PostgreSQL on RHEL 7 or 8 derived OS 
via rpm, what are pros, cons and recommendations of these 2 procedures 
for utilizing a second disk?


Secondary SSD or RAID mounted at /disk2.

Option #1

1. install the rpm which creates basic user and home
2. Create symlink /var/lib/pgsql/15/data --> /disk2/data
3. initdb with no special options

Or Option #2

1. install the rpm which creates basic user and home
2. initdb with --pgdata=/disk2/data
   Probably using included 'postgresql-12-setup' script

I also link /var/lib/pgsql/data  --> ../15/data so automation can 
reference postgresql.conf without knowing version (legacy stuff).


The install is automated with a bash script which handles several 
options including whether there is a second disk for DB. Scripting the 
install with or without the second disk is straight forward but I'm 
concerned with either scenario causing unforeseen differences.


I don't think there's a benefit to using tablespace here but I have no 
experience with it. The systemd service is configured with a dependency 
on the disk mount so I don't think there are different risks for 
starting postgres with missing data directory.


I've run postgres in both scenarios and not had any issues. I'm 
interested in comments from others on their experience using these or 
other options.


Thanks,

Senor

Re: Introduction of a new field in pg_class indicating presence of a large object in a table

[Tom Lane]

"David G. Johnston"  writes:
> On Tue, Apr 30, 2024 at 11:57 AM Gaurav Pant 
> wrote:
>> I wanted to know if there is any such system table that we can use to
>> identify and map the fields containing large objects and the respective
>> tables and if it is not already there, do we have any plans to incorporate
>> the same in pg_class like we have for pg_toast?

> Large Objects are nothing like TOAST.  There is no system level association
> between large objects and tables.  Sure, the DBA can choose to store a
> large object OID in a table, but how you'd go about figuring out which
> columns contain those is going to be installation specific.

Yeah.  You might want to look at contrib/vacuumlo, but realize that
that's fairly heuristic.

> Though
> hopefully they used a bigint data type and maybe added "oid" to the column
> name...I suppose it would be interesting if one could define a FK on a
> table and point it at pg_largeobject_metadata but that I suspect would be
> the extent to which we'd do something along the lines of your request.

That would solve the opposite problem, of preventing a column from
containing any OIDs that *weren't* large object OIDs.  Given that
recording a large object OID elsewhere in the database is purely
an application decision, I don't think there's a reasonable way
for the system to track it.

regards, tom lane

Re: Introduction of a new field in pg_class indicating presence of a large object in a table

[David G. Johnston]

On Tue, Apr 30, 2024 at 11:57 AM Gaurav Pant 
wrote:

> I wanted to know if there is any such system table that we can use to
> identify and map the fields containing large objects and the respective
> tables and if it is not already there, do we have any plans to incorporate
> the same in pg_class like we have for pg_toast?
>
>
https://www.postgresql.org/docs/current/catalog-pg-largeobject.html

Large Objects are nothing like TOAST.  There is no system level association
between large objects and tables.  Sure, the DBA can choose to store a
large object OID in a table, but how you'd go about figuring out which
columns contain those is going to be installation specific.  Though
hopefully they used a bigint data type and maybe added "oid" to the column
name...I suppose it would be interesting if one could define a FK on a
table and point it at pg_largeobject_metadata but that I suspect would be
the extent to which we'd do something along the lines of your request.

David J.

Introduction of a new field in pg_class indicating presence of a large object in a table

[Gaurav Pant]

Hi All,

I hope you're all doing well!

Recently, I have across a scenario where one of our client was unable to
perform a major version upgrade of their PostgreSQL instance as it was
failing due to OOM errors. When reviewed the upgrade log files and queried
pg_largeobject_metadata, we found a significant number of large objects
(more than a million) due to which the upgrade seems to fail. As the client
had a different vendor in the past, they are unsure of why large objects
were created in the first place. However, in order to further dive deep,
the requirement is to identify the tables (and the columns) which contain
these large objects to decide on the next steps. I checked for online
resources but could not find any information that helps in this regard.

Although quite different from the above specified use case, pg_class does
contain a field: 'reltoastrelid' that helps us to identify the presence of
a toast table for any relation in PostgreSQL. I wanted to know if there is
any such system table that we can use to identify and map the fields
containing large objects and the respective tables and if it is not already
there, do we have any plans to incorporate the same in pg_class like we
have for pg_toast?

Thanks and Regards,
Gaurav Pant

Re: pg_notify contention

[Dimitris Zenios]

Hi Torsten and thanks for your quick response.

My problem with WAL stream and pg_logical_emit_message is that this will
pollute the wal files resulting in bigger files in which btw are also
archived using archive-command for disaster recovery cases.

I am thinking of an approach that instead of notifying, I save into an
unlogged table and then have a queue that processes the messages
sequentially.This will allow the original transaction to be as fast as
possible.
I still haven't figured out the correct way to do the fifo approach on the
unlogged tables.

What is your opinion on my proposed approach?

Thanks

On Tue, Apr 30, 2024 at 1:57 PM Torsten Förtsch 
wrote:

> As part of the commit operation, Postgres inserts the notification into a
> queue. Naturally, that insert is guarded by a lock and that lock is
> released only at the very end of the commit operation. This effect gets
> much worse if you also configure synchronous replication because commit
> finishes only when the desired sync level has been achieved.
>
> An alternative solution could be to receive notification through the WAL
> stream. Use pg_revclogical to connect to a replication slot configured with
> wal2json. Then you can send notifications with pg_logical_emit_message()
> for instance which even allows you to send notifications from aborted
> transactions.
>
> Setup:
> SELECT * FROM pg_create_logical_replication_slot('w2j', 'wal2json');
>
> Here is a sender:
> CREATE SCHEMA notify;
> CREATE OR REPLACE FUNCTION notify.send(
> transactional BOOLEAN,
> channel TEXT,
> message TEXT
> ) RETURNS PG_LSN AS $$
> SELECT pg_logical_emit_message(
>transactional,
>'NOTIFY_'||channel,
>jsonb_build_object(
>'txn_start', now()::TEXT,
>'pid', pg_backend_pid(),
>'message', message
>)::TEXT
>);
> $$ LANGUAGE sql VOLATILE;
>
> And something like this as a receiver / distributor:
> sudo -iu postgres /usr/lib/postgresql/$VERSION/bin/pg_recvlogical \
>  -d 'port=5432 dbname=your_database' \
>  -S w2j \
>  -f - \
>  -F 0 \
>  --start \
>  -o include-xids=1 \
>  -o include-timestamp=1 \
>  -o add-tables=notify.\* \
>  -o format-version=2 |
> jq 'select(.action=="M" and (.prefix | test("^NOTIFY_"))) |
> . as $x |
> .content |
> fromjson + {
> "channel": ($x.prefix | .[7:]),
> "xid": $x.xid,
> "txn_commit": $x.timestamp,
> "transactional": $x.transactional
> }'
>
> Note, this is not a 100% replacement. The semantics are slightly
> different. You also need a replication slot which comes with its own
> maintenance cost. But maybe it's worth a try.
>
> Have fun,
> Torsten
>
> On Tue, Apr 30, 2024 at 12:05 PM Dimitris Zenios <
> dimitris.zen...@gmail.com> wrote:
>
>> 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, 10 * :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 

Re: pg_notify contention

[Torsten Förtsch]

As part of the commit operation, Postgres inserts the notification into a
queue. Naturally, that insert is guarded by a lock and that lock is
released only at the very end of the commit operation. This effect gets
much worse if you also configure synchronous replication because commit
finishes only when the desired sync level has been achieved.

An alternative solution could be to receive notification through the WAL
stream. Use pg_revclogical to connect to a replication slot configured with
wal2json. Then you can send notifications with pg_logical_emit_message()
for instance which even allows you to send notifications from aborted
transactions.

Setup:
SELECT * FROM pg_create_logical_replication_slot('w2j', 'wal2json');

Here is a sender:
CREATE SCHEMA notify;
CREATE OR REPLACE FUNCTION notify.send(
transactional BOOLEAN,
channel TEXT,
message TEXT
) RETURNS PG_LSN AS $$
SELECT pg_logical_emit_message(
   transactional,
   'NOTIFY_'||channel,
   jsonb_build_object(
   'txn_start', now()::TEXT,
   'pid', pg_backend_pid(),
   'message', message
   )::TEXT
   );
$$ LANGUAGE sql VOLATILE;

And something like this as a receiver / distributor:
sudo -iu postgres /usr/lib/postgresql/$VERSION/bin/pg_recvlogical \
 -d 'port=5432 dbname=your_database' \
 -S w2j \
 -f - \
 -F 0 \
 --start \
 -o include-xids=1 \
 -o include-timestamp=1 \
 -o add-tables=notify.\* \
 -o format-version=2 |
jq 'select(.action=="M" and (.prefix | test("^NOTIFY_"))) |
. as $x |
.content |
fromjson + {
"channel": ($x.prefix | .[7:]),
"xid": $x.xid,
"txn_commit": $x.timestamp,
"transactional": $x.transactional
}'

Note, this is not a 100% replacement. The semantics are slightly different.
You also need a replication slot which comes with its own maintenance cost.
But maybe it's worth a try.

Have fun,
Torsten

On Tue, Apr 30, 2024 at 12:05 PM Dimitris Zenios 
wrote:

> 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, 10 * :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
>
>

pg_notify contention

[Dimitris Zenios]

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, 10 * :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