public inbox for [email protected]
help / color / mirror / Atom feedFrom: Roman Khapov <[email protected]>
To: [email protected] <[email protected]>
Subject: Cancel problems of query to pg_stat_statements
Date: Thu, 24 Apr 2025 22:49:16 +0500
Message-ID: <[email protected]> (raw)
------==--bound.20.dbernar-corp-main-90.vla.yp-c.yandex.net
Content-Transfer-Encoding: base64
Content-Type: text/html; charset=utf-8
PGRpdj5IaSE8L2Rpdj48ZGl2PsKgPC9kaXY+PGRpdj5SZWNlbnRseSB3ZSBmYWNlZCBhIHByb2Js
ZW0gaW4gb3V0IHByb2R1Y3Rpb24gcHNxbCBpbnN0YWxsYXRpb24sIHdoaWNoIHdhcyB0aGF0IHdl
IGhhZCB0byBjYW5jZWwgYWxsIHJlcXVlc3RzIHRvIHRoZSBkYiwgaW5jbHVkaW5nIHBlcmZvcm1h
bmNlIG1vbml0b3JpbmcgcmVxdWVzdHMsIHRoYXQgdXNlcyBwc19zdGF0X3N0YXRlbWVudHMuIEJ1
dCB3ZSBjb3VsZCBub3QgY2FuY2VsIHRoZSByZXF1ZXN0IGluIHVzdWFsIHdheSwgYW5kIGhhZCB0
byBraWxsIC05IHRoZSBwZyBwcm9jZXNzIG9mIGl0LjwvZGl2PjxkaXY+wqA8L2Rpdj48ZGl2Pldl
J3ZlIG5vdGljZWQgdGhhdCB0aGUgdGhlIHF1ZXJ5IGV4ZWN1dGlvbiBzdHVjayBvbiA8c3BhbiBz
dHlsZT0iYmFja2dyb3VuZC1jb2xvcjp0cmFuc3BhcmVudCI+UEdTU19URVhUX0ZJTEUgZmlsZSBy
ZWFkaW5nIDwvc3Bhbj5pbiBmdW5jdGlvbiBxdGV4dF9sb2FkX2ZpbGUsIHdoaWNoIGRvZXNuJ3Qg
aGF2ZSA8c3BhbiBzdHlsZT0iYmFja2dyb3VuZC1jb2xvcjp0cmFuc3BhcmVudCI+Q0hFQ0tfRk9S
X0lOVEVSUlVQVFMgaW4gdGhlIHJlYWQgY3ljbGUuIEluIGFkZGl0aW9uIHRvIG91ciBjYXNlIHdp
dGggbGFyZ2UgUEdTU19URVhUX0ZJTEUgKGFuZCBtYXliZSB0aGUgcHJvYmxlbXMgd2l0aCB2aXJ0
dWFsIGRpc2sgaS9vKSB0aGF0IGNhbiBleHBsYWluIHVuY2FuY2VsbGFibGUgcGdfc3RhdF9zdGF0
ZW1lbnRzIHF1ZXJpZXMuPC9zcGFuPjwvZGl2PjxkaXY+wqA8L2Rpdj48ZGl2PjxzcGFuIHN0eWxl
PSJiYWNrZ3JvdW5kLWNvbG9yOnRyYW5zcGFyZW50Ij5BbHNvLCB0aGUgcmVhZGluZyBibG9jayBz
aXplIGNhbiBiZSByZWR1Y2VkIGZyb20gMUdCIHRvIDMyTUIgaW4gb3JkZXIgdG8gaW5jcmVhc2Ug
dGhlIGZyZXF1ZW5jeSBvZiBDSEVDS19GT1JfSU5URVJSVVBUUyBjYWxscyB3aXRob3V0IDwvc3Bh
bj5xdGV4dF9sb2FkX2ZpbGXCoHBlcmZvcm1hbmNlIGRlZ3JhZGF0aW9uPHNwYW4gc3R5bGU9ImJh
Y2tncm91bmQtY29sb3I6dHJhbnNwYXJlbnQiPi4gVG8gY2hlY2sgdGhhdCBJIGRpZCBzb21lIGxp
dHRsZSB0ZXN0aW5nIHdpdGggZmlvIGxpa2U6PC9zcGFuPjwvZGl2PjxkaXY+wqA8L2Rpdj48ZGl2
PmZpbyAtLW5hbWU9cmVhZHRlc3QgLS1maWxlbmFtZT0uL3JhbmRvbS1ieXRlcy1maWxlIC0tcnc9
cmVhZCAtLWJzPTMybSAtLXNpemU9MTBHIC0taW9lbmdpbmU9bGliYWlvIC0tZGlyZWN0PTEgLS1u
dW1qb2JzPTE8L2Rpdj48ZGl2PsKgPC9kaXY+PGRpdj5TbywgSSBtYWRlIGEgc2ltcGxlIHBhdGNo
IHRoYXQgYWRkcyBDSEVDS19GT1JfSU5URVJSVVBUUyBjYWxsIGluIHJlYWQgY3ljbGUgb2YgcXRl
eHRfbG9hZF9maWxlwqBhbmQgY2hhbmdlIG1heCB2YWx1ZSBvZiB0b3JlYWQgZnJvbSAxR0IgdG8g
MzJNQi48L2Rpdj48ZGl2PsKgPC9kaXY+PGRpdj48c3BhbiBzdHlsZT0id2hpdGUtc3BhY2U6cHJl
LXdyYXAiPkkgd291bGQgYXBwcmVjaWF0ZSB5b3VyIGZlZWRiYWNrIG9uIHRoZXNlIGNoYW5nZXMu
PC9zcGFuPjwvZGl2PjxkaXY+wqA8L2Rpdj48ZGl2PsKgPC9kaXY+
------==--bound.20.dbernar-corp-main-90.vla.yp-c.yandex.net
Content-Disposition: attachment;
filename="cancelable-qtext_load_file.patch"
Content-Transfer-Encoding: base64
Content-Type: text/x-diff;
name="cancelable-qtext_load_file.patch"
RnJvbSBlYWIwNDFmY2QxZjQ5NzNiMDNmNmJlYmQzN2JlZjMzOTVmZTY0YjRiIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBya2hhcG92IDxyLmtoYXBvdkB5YS5ydT4KRGF0ZTogVGh1LCAy
NCBBcHIgMjAyNSAxNzowMTo1NCArMDAwMApTdWJqZWN0OiBbUEFUQ0hdIHBnX3N0YXRfc3RhdGVt
ZW50cy5jOiBjYW5jZWxhYmxlIHF0ZXh0X2xvYWRfZmlsZQoKSW4gdGhlIGNhc2Ugb2YgYSBsYXJn
ZSBQR1NTX1RFWFRfRklMRSwgdGhlIHdvcmsgdGltZSBvZiB0aGUgcXRleHRfbG9hZF9maWxlCmZ1
bmN0aW9uIHdpbGwgYmUgcXVpdGUgbG9uZywgYW5kIHRoZSBxdWVyeSB0byB0aGUgcGdfc3RhdF9z
dGF0ZW1lbnRzIHRhYmxlCndpbGwgbm90IGJlIGNhbmNlbGxhYmxlLCBhcyB0aGVyZSBpcyBubyBD
SEVDS19GT1JfSU5URVJSVVBUIGluIHRoZSBmdW5jdGlvbi4KCkFsc28sIHRoZSBhbW91bnQgb2Yg
Ynl0ZXMgcmVhZCBjYW4gcmVhY2ggMSBHQiwgd2hpY2ggbGVhZHMgdG8gYSBzbG93IHJlYWQKc3lz
dGVtIGNhbGwgdGhhdCBkb2VzIG5vdCBhbGxvdyBjYW5jZWxsYXRpb24gb2YgdGhlIHF1ZXJ5LiBU
ZXN0aW5nIHRoZSBzcGVlZApvZiBzZXF1ZW50aWFsIHJlYWQgdXNpbmcgZmlvIHdpdGggZGlmZmVy
ZW50IGJsb2NrIHNpemVzIHNob3dzIHRoYXQgdGhlcmUgaXMKbm8gc2lnbmlmaWNhbnQgZGlmZmVy
ZW5jZSBiZXR3ZWVuIDE2IE1CIGJsb2NrcyBhbmQgMSBHQiBibG9ja3MuCgpUaGVyZWZvcmUsIHRo
aXMgcGF0Y2ggY2hhbmdlcyB0aGUgbWF4aW11bSByZWFkIHZhbHVlIGZyb20gMSBHQiB0byAxNiBN
QiBhbmQKYWRkcyBDSEVDS19GT1JfSU5URVJSVVBUSU9OIGluIHRoZSByZWFkIGxvb3Agb2YgcXRl
eHRfbG9hZF9maWxlIHRvIG1ha2UgaXQgY2FuY2VsbGFibGUuCgpTaWduZWQtb2ZmLWJ5OiBya2hh
cG92IDxyLmtoYXBvdkB5YS5ydT4KLS0tCiBjb250cmliL3BnX3N0YXRfc3RhdGVtZW50cy9wZ19z
dGF0X3N0YXRlbWVudHMuYyB8IDQgKysrLQogMSBmaWxlIGNoYW5nZWQsIDMgaW5zZXJ0aW9ucygr
KSwgMSBkZWxldGlvbigtKQoKZGlmZiAtLWdpdCBhL2NvbnRyaWIvcGdfc3RhdF9zdGF0ZW1lbnRz
L3BnX3N0YXRfc3RhdGVtZW50cy5jIGIvY29udHJpYi9wZ19zdGF0X3N0YXRlbWVudHMvcGdfc3Rh
dF9zdGF0ZW1lbnRzLmMKaW5kZXggOTc3ODQwN2NiYTMuLmNkMzRmMWNlMjQ4IDEwMDY0NAotLS0g
YS9jb250cmliL3BnX3N0YXRfc3RhdGVtZW50cy9wZ19zdGF0X3N0YXRlbWVudHMuYworKysgYi9j
b250cmliL3BnX3N0YXRfc3RhdGVtZW50cy9wZ19zdGF0X3N0YXRlbWVudHMuYwpAQCAtMjM2NSw3
ICsyMzY1LDkgQEAgcXRleHRfbG9hZF9maWxlKFNpemUgKmJ1ZmZlcl9zaXplKQogCW5yZWFkID0g
MDsKIAl3aGlsZSAobnJlYWQgPCBzdGF0LnN0X3NpemUpCiAJewotCQlpbnQJCQl0b3JlYWQgPSBN
aW4oMTAyNCAqIDEwMjQgKiAxMDI0LCBzdGF0LnN0X3NpemUgLSBucmVhZCk7CisJCWludAkJCXRv
cmVhZCA9IE1pbigzMiAqIDEwMjQgKiAxMDI0LCBzdGF0LnN0X3NpemUgLSBucmVhZCk7CisKKwkJ
Q0hFQ0tfRk9SX0lOVEVSUlVQVFMoKTsKIAogCQkvKgogCQkgKiBJZiB3ZSBnZXQgYSBzaG9ydCBy
ZWFkIGFuZCBlcnJubyBkb2Vzbid0IGdldCBzZXQsIHRoZSByZWFzb24gaXMKLS0gCjIuNDMuMAoK
Cg==
------==--bound.20.dbernar-corp-main-90.vla.yp-c.yandex.net--
Attachments:
[text/x-diff] cancelable-qtext_load_file.patch (1.6K, 2-cancelable-qtext_load_file.patch)
download | inline diff:
From eab041fcd1f4973b03f6bebd37bef3395fe64b4b Mon Sep 17 00:00:00 2001
From: rkhapov <[email protected]>
Date: Thu, 24 Apr 2025 17:01:54 +0000
Subject: [PATCH] pg_stat_statements.c: cancelable qtext_load_file
In the case of a large PGSS_TEXT_FILE, the work time of the qtext_load_file
function will be quite long, and the query to the pg_stat_statements table
will not be cancellable, as there is no CHECK_FOR_INTERRUPT in the function.
Also, the amount of bytes read can reach 1 GB, which leads to a slow read
system call that does not allow cancellation of the query. Testing the speed
of sequential read using fio with different block sizes shows that there is
no significant difference between 16 MB blocks and 1 GB blocks.
Therefore, this patch changes the maximum read value from 1 GB to 16 MB and
adds CHECK_FOR_INTERRUPTION in the read loop of qtext_load_file to make it cancellable.
Signed-off-by: rkhapov <[email protected]>
---
contrib/pg_stat_statements/pg_stat_statements.c | 4 +++-
1 file changed, 3 insertions(+), 1 deletion(-)
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 9778407cba3..cd34f1ce248 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -2365,7 +2365,9 @@ qtext_load_file(Size *buffer_size)
nread = 0;
while (nread < stat.st_size)
{
- int toread = Min(1024 * 1024 * 1024, stat.st_size - nread);
+ int toread = Min(32 * 1024 * 1024, stat.st_size - nread);
+
+ CHECK_FOR_INTERRUPTS();
/*
* If we get a short read and errno doesn't get set, the reason is
--
2.43.0
view thread (4+ 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]
Subject: Re: Cancel problems of query to pg_stat_statements
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