From: | Greg Sabino Mullane <htamfids(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Improve pg_stat_statements by making jumble handle savepoint names better |
Date: | 2023-07-24 20:09:23 |
Message-ID: | CAKAnmm+2s9PA4OaumwMJReWHk8qvJ_-g1WqxDRDAN1BSUfxyTw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Please find attached a patch to jumble savepoint name, to prevent certain
transactional commands from filling up pg_stat_statements.
This has been a problem with some busy systems that use django, which likes
to wrap everything in uniquely named savepoints. Soon, over 50% of
your pg_stat_statements buffer is filled with savepoint stuff, pushing out
the more useful queries. As each query is unique, it looks like this:
postgres=# select calls, query, queryid from pg_stat_statements where query
~ 'save|release|rollback' order by 2;
calls | query | queryid
-------+----------------------------------------------+----------------------
1 | release b900150983cd24fb0d6963f7d28e17f7 | 8797482500264589878
1 | release ed9407630eb1000c0f6b63842defa7de | -9206510099095862114
1 | rollback | -2049453941623996126
1 | rollback to c900150983cd24fb0d6963f7d28e17f7 | -5335832667999552746
1 | savepoint b900150983cd24fb0d6963f7d28e17f7 | -1888817254996647181
1 | savepoint c47bce5c74f589f4867dbd57e9ca9f80 | 355123032993044571
1 | savepoint c900150983cd24fb0d6963f7d28e17f7 | -5921314469994822125
1 | savepoint d8f8e0260c64418510cefb2b06eee5cd | -981090856656063578
1 | savepoint ed9407630eb1000c0f6b63842defa7de | -25952890433218603
As the actual name of the savepoint is not particularly useful, the patch
will basically ignore the savepoint name and allow things to be collapsed:
calls | query | queryid
-------+----------------+----------------------
2 | release $1 | -7998168840889089775
1 | rollback | 3749380189022910195
1 | rollback to $1 | -1816677871228308673
5 | savepoint $1 | 6160699978368237767
Without the patch, the only solution is to keep raising
pg_stat_statements.max to larger and larger values to compensate for the
pollution of the
statement pool.
Cheers,
Greg
Attachment | Content-Type | Size |
---|---|---|
savepoint_jumble.v1.patch | application/octet-stream | 3.9 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2023-07-24 20:17:56 | Re: odd buildfarm failure - "pg_ctl: control file appears to be corrupt" |
Previous Message | Tristan Partin | 2023-07-24 19:57:58 | Re: psql not responding to SIGINT upon db reconnection |