public inbox for [email protected]
help / color / mirror / Atom feedFrom: Imseih (AWS), Sami <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: Doc update for pg_stat_statements normalization
Date: Fri, 24 Feb 2023 20:54:00 +0000
Message-ID: <[email protected]> (raw)
Replacing constants in pg_stat_statements is on a best effort basis.
It is not unlikely that on a busy workload with heavy entry deallocation,
the user may observe the query with the constants in pg_stat_statements.
From what I can see, this is because the only time an entry is normalized is
during post_parse_analyze, and the entry may be deallocated by the time query
execution ends. At that point, the original form ( with constants ) of the query
is used.
It is not clear how prevalent this is in real-world workloads, but it's easily reproducible
on a workload with high entry deallocation. Attached are the repro steps on the latest
branch.
I think the only thing to do here is to call this out in docs with a suggestion to increase
pg_stat_statements.max to reduce the likelihood. I also attached the suggested
doc enhancement as well.
Any thoughts?
Regards,
--
Sami Imseih
Amazon Web Services
### pg_stat_statements.max is min allowed
postgres=# show pg_stat_statements.max ;
pg_stat_statements.max
------------------------
100
(1 row)
### create 200 tables
do $$
begin
for i in 1 .. 200
loop
execute 'drop table if exists foo'||i;
execute 'create table foo'||i||'(id int, c2 text)';
end loop;
end; $$ ;
### create a pgbench script to insert into the tables.
for (( i=1; i<=200; i++ ))
do
echo "INSERT INTO foo"$i" (id, c2) values (1, 'somedata');" >> /tmp/pgbench.sql
done
### run pgbench
pgbench -c 50 -f /tmp/pgbench.sql -T 1200
### observe pg_stat_statements
postgres=# select query from pg_stat_statements where query like '%foo%' and query not like '%$1%';
query
-----------------------------------------------------
INSERT INTO foo31 (id, c2) values (1, 'somedata')
INSERT INTO foo20 (id, c2) values (1, 'somedata')
INSERT INTO foo191 (id, c2) values (1, 'somedata')
INSERT INTO foo170 (id, c2) values (1, 'somedata')
INSERT INTO foo167 (id, c2) values (1, 'somedata')
INSERT INTO foo32 (id, c2) values (1, 'somedata')
INSERT INTO foo36 (id, c2) values (1, 'somedata')
INSERT INTO foo43 (id, c2) values (1, 'somedata')
INSERT INTO foo181 (id, c2) values (1, 'somedata')
INSERT INTO foo88 (id, c2) values (1, 'somedata')
(10 rows)
postgres=# select query from pg_stat_statements where query like '%foo%' and query like '%$1%' limit 5;
query
--------------------------------------------
INSERT INTO foo33 (id, c2) values ($1, $2)
INSERT INTO foo59 (id, c2) values ($1, $2)
INSERT INTO foo50 (id, c2) values ($1, $2)
INSERT INTO foo42 (id, c2) values ($1, $2)
INSERT INTO foo91 (id, c2) values ($1, $2)
### observe the # of deallocations
postgres=# select * from pg_stat_statements_info ;
dealloc | stats_reset
---------+-------------------------------
113371 | 2023-02-24 06:24:21.912275-06
(1 row)
Attachments:
[text/plain] report.txt (1.9K, ../[email protected]/3-report.txt)
download | inline:
### pg_stat_statements.max is min allowed
postgres=# show pg_stat_statements.max ;
pg_stat_statements.max
------------------------
100
(1 row)
### create 200 tables
do $$
begin
for i in 1 .. 200
loop
execute 'drop table if exists foo'||i;
execute 'create table foo'||i||'(id int, c2 text)';
end loop;
end; $$ ;
### create a pgbench script to insert into the tables.
for (( i=1; i<=200; i++ ))
do
echo "INSERT INTO foo"$i" (id, c2) values (1, 'somedata');" >> /tmp/pgbench.sql
done
### run pgbench
pgbench -c 50 -f /tmp/pgbench.sql -T 1200
### observe pg_stat_statements
postgres=# select query from pg_stat_statements where query like '%foo%' and query not like '%$1%';
query
-----------------------------------------------------
INSERT INTO foo31 (id, c2) values (1, 'somedata')
INSERT INTO foo20 (id, c2) values (1, 'somedata')
INSERT INTO foo191 (id, c2) values (1, 'somedata')
INSERT INTO foo170 (id, c2) values (1, 'somedata')
INSERT INTO foo167 (id, c2) values (1, 'somedata')
INSERT INTO foo32 (id, c2) values (1, 'somedata')
INSERT INTO foo36 (id, c2) values (1, 'somedata')
INSERT INTO foo43 (id, c2) values (1, 'somedata')
INSERT INTO foo181 (id, c2) values (1, 'somedata')
INSERT INTO foo88 (id, c2) values (1, 'somedata')
(10 rows)
postgres=# select query from pg_stat_statements where query like '%foo%' and query like '%$1%' limit 5;
query
--------------------------------------------
INSERT INTO foo33 (id, c2) values ($1, $2)
INSERT INTO foo59 (id, c2) values ($1, $2)
INSERT INTO foo50 (id, c2) values ($1, $2)
INSERT INTO foo42 (id, c2) values ($1, $2)
INSERT INTO foo91 (id, c2) values ($1, $2)
### observe the # of deallocations
postgres=# select * from pg_stat_statements_info ;
dealloc | stats_reset
---------+-------------------------------
113371 | 2023-02-24 06:24:21.912275-06
(1 row)
[application/octet-stream] 0001-doc-update-regarding-pg_stat_statements-normalizatio.patch (1.4K, ../[email protected]/4-0001-doc-update-regarding-pg_stat_statements-normalizatio.patch)
download | inline diff:
From 1ecedf4e61171d2f02d513f7f83eed46ddbee5e3 Mon Sep 17 00:00:00 2001
From: "Imseih (AWS)" <[email protected]>
Date: Fri, 24 Feb 2023 14:22:39 -0600
Subject: [PATCH 1/1] doc update regarding pg_stat_statements normalization.
It is quite possible that possible willl not normalize
(replace constants with $1, $2..) when there is a high
rate of deallocation. This commit calls this out in
docs.
---
doc/src/sgml/pgstatstatements.sgml | 9 +++++++++
1 file changed, 9 insertions(+)
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index efc36da602..4857f1b89e 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -516,6 +516,15 @@
<structname>pg_stat_statements</structname> entry.
</para>
+ <para>
+ A query text may be observed with constants in
+ <structname>pg_stat_statements</structname>, especially when there is a high
+ rate of entry deallocations. To reduce the likelihood of this occuring, consider
+ increasing <varname>pg_stat_statements.max</varname>.
+ The <structname>pg_stat_statements_info</structname> view provides entry
+ deallocation statistics.
+ </para>
+
<para>
In some cases, queries with visibly different texts might get merged into a
single <structname>pg_stat_statements</structname> entry. Normally this will happen
--
2.37.1 (Apple Git-137.1)
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: Doc update for pg_stat_statements normalization
In-Reply-To: <[email protected]>
* 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