MIME-Version: 1.0
From: Melanie Plageman <melanieplageman@gmail.com>
Date: Thu, 23 Feb 2023 17:08:16 -0500
Message-ID: <CAAKRu_ZngzqnEODc7LmS1NH04Kt6Y9huSjz5pp7+DXhrjDA0gw@mail.gmail.com>
Subject: Should vacuum process config file reload more often
To: Pg Hackers <pgsql-hackers@postgresql.org>
Cc: Andres Freund <andres@anarazel.de>
Content-Type: multipart/mixed; boundary="00000000000091ba6c05f56540cb"
Archived-At: <https://www.postgresql.org/message-id/CAAKRu_ZngzqnEODc7LmS1NH04Kt6Y9huSjz5pp7%2BDXhrjDA0gw%40mail.gmail.com>
Precedence: bulk

--00000000000091ba6c05f56540cb
Content-Type: text/plain; charset="UTF-8"

Hi,

Users may wish to speed up long-running vacuum of a large table by
decreasing autovacuum_vacuum_cost_delay/vacuum_cost_delay, however the
config file is only reloaded between tables (for autovacuum) or after
the statement (for explicit vacuum). This has been brought up for
autovacuum in [1].

Andres suggested that it might be possible to check ConfigReloadPending
in vacuum_delay_point(), so I thought I would draft a rough patch and
start a discussion.

Since vacuum_delay_point() is also called by analyze and we do not want
to reload the configuration file if we are in a user transaction, I
widened the scope of the in_outer_xact variable in vacuum() and allowed
analyze in a user transaction to default to the current configuration
file reload cadence in PostgresMain().

I don't think I can set and leave vac_in_outer_xact the way I am doing
it in this patch, since I use vac_in_outer_xact in vacuum_delay_point(),
which I believe is reachable from codepaths that would not have called
vacuum(). It seems that if a backend sets it, the outer transaction
commits, and then the backend ends up calling vacuum_delay_point() in a
different way later, it wouldn't be quite right.

Apart from this, one higher level question I have is if there are other
gucs whose modification would make reloading the configuration file
during vacuum/analyze unsafe.

- Melanie

[1] https://www.postgresql.org/message-id/flat/22CA91B4-D341-4075-BD3C-4BAB52AF1E80%40amazon.com#37f05e33d2ce43680f96332fa1c0f3d4

--00000000000091ba6c05f56540cb
Content-Type: text/x-patch; charset="US-ASCII"; name="v1-0001-reload-config-file-vac.patch"
Content-Disposition: attachment; 
	filename="v1-0001-reload-config-file-vac.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_lehnllhn0>
X-Attachment-Id: f_lehnllhn0

RnJvbSBhZWE2ZmJmZDkzYWIxMmU0ZTI3ODY5Yjc1NTM2N2FiODQ1NGUzZWVmIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBNZWxhbmllIFBsYWdlbWFuIDxtZWxhbmllcGxhZ2VtYW5AZ21h
aWwuY29tPgpEYXRlOiBUaHUsIDIzIEZlYiAyMDIzIDE1OjU0OjU1IC0wNTAwClN1YmplY3Q6IFtQ
QVRDSCB2MV0gcmVsb2FkIGNvbmZpZyBmaWxlIHZhYwoKLS0tCiBzcmMvYmFja2VuZC9jb21tYW5k
cy92YWN1dW0uYyB8IDIxICsrKysrKysrKysrKysrLS0tLS0tLQogMSBmaWxlIGNoYW5nZWQsIDE0
IGluc2VydGlvbnMoKyksIDcgZGVsZXRpb25zKC0pCgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQv
Y29tbWFuZHMvdmFjdXVtLmMgYi9zcmMvYmFja2VuZC9jb21tYW5kcy92YWN1dW0uYwppbmRleCBh
YTc5ZDlkZTRkLi45NzlkMTkyMjJkIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9jb21tYW5kcy92
YWN1dW0uYworKysgYi9zcmMvYmFja2VuZC9jb21tYW5kcy92YWN1dW0uYwpAQCAtNDgsNiArNDgs
NyBAQAogI2luY2x1ZGUgInBnc3RhdC5oIgogI2luY2x1ZGUgInBvc3RtYXN0ZXIvYXV0b3ZhY3V1
bS5oIgogI2luY2x1ZGUgInBvc3RtYXN0ZXIvYmd3b3JrZXJfaW50ZXJuYWxzLmgiCisjaW5jbHVk
ZSAicG9zdG1hc3Rlci9pbnRlcnJ1cHQuaCIKICNpbmNsdWRlICJzdG9yYWdlL2J1Zm1nci5oIgog
I2luY2x1ZGUgInN0b3JhZ2UvbG1nci5oIgogI2luY2x1ZGUgInN0b3JhZ2UvcHJvYy5oIgpAQCAt
NzUsNiArNzYsNyBAQCBpbnQJCQl2YWN1dW1fbXVsdGl4YWN0X2ZhaWxzYWZlX2FnZTsKIC8qIEEg
ZmV3IHZhcmlhYmxlcyB0aGF0IGRvbid0IHNlZW0gd29ydGggcGFzc2luZyBhcm91bmQgYXMgcGFy
YW1ldGVycyAqLwogc3RhdGljIE1lbW9yeUNvbnRleHQgdmFjX2NvbnRleHQgPSBOVUxMOwogc3Rh
dGljIEJ1ZmZlckFjY2Vzc1N0cmF0ZWd5IHZhY19zdHJhdGVneTsKK3N0YXRpYyBib29sIHZhY19p
bl9vdXRlcl94YWN0ID0gZmFsc2U7CiAKIAogLyoKQEAgLTMwOSw4ICszMTEsNyBAQCB2YWN1dW0o
TGlzdCAqcmVsYXRpb25zLCBWYWN1dW1QYXJhbXMgKnBhcmFtcywKIAlzdGF0aWMgYm9vbCBpbl92
YWN1dW0gPSBmYWxzZTsKIAogCWNvbnN0IGNoYXIgKnN0bXR0eXBlOwotCXZvbGF0aWxlIGJvb2wg
aW5fb3V0ZXJfeGFjdCwKLQkJCQl1c2Vfb3duX3hhY3RzOworCXZvbGF0aWxlIGJvb2wgdXNlX293
bl94YWN0czsKIAogCUFzc2VydChwYXJhbXMgIT0gTlVMTCk7CiAKQEAgLTMyNywxMCArMzI4LDEw
IEBAIHZhY3V1bShMaXN0ICpyZWxhdGlvbnMsIFZhY3V1bVBhcmFtcyAqcGFyYW1zLAogCWlmIChw
YXJhbXMtPm9wdGlvbnMgJiBWQUNPUFRfVkFDVVVNKQogCXsKIAkJUHJldmVudEluVHJhbnNhY3Rp
b25CbG9jayhpc1RvcExldmVsLCBzdG10dHlwZSk7Ci0JCWluX291dGVyX3hhY3QgPSBmYWxzZTsK
KwkJdmFjX2luX291dGVyX3hhY3QgPSBmYWxzZTsKIAl9CiAJZWxzZQotCQlpbl9vdXRlcl94YWN0
ID0gSXNJblRyYW5zYWN0aW9uQmxvY2soaXNUb3BMZXZlbCk7CisJCXZhY19pbl9vdXRlcl94YWN0
ID0gSXNJblRyYW5zYWN0aW9uQmxvY2soaXNUb3BMZXZlbCk7CiAKIAkvKgogCSAqIER1ZSB0byBz
dGF0aWMgdmFyaWFibGVzIHZhY19jb250ZXh0LCBhbmxfY29udGV4dCBhbmQgdmFjX3N0cmF0ZWd5
LApAQCAtNDUxLDcgKzQ1Miw3IEBAIHZhY3V1bShMaXN0ICpyZWxhdGlvbnMsIFZhY3V1bVBhcmFt
cyAqcGFyYW1zLAogCQlBc3NlcnQocGFyYW1zLT5vcHRpb25zICYgVkFDT1BUX0FOQUxZWkUpOwog
CQlpZiAoSXNBdXRvVmFjdXVtV29ya2VyUHJvY2VzcygpKQogCQkJdXNlX293bl94YWN0cyA9IHRy
dWU7Ci0JCWVsc2UgaWYgKGluX291dGVyX3hhY3QpCisJCWVsc2UgaWYgKHZhY19pbl9vdXRlcl94
YWN0KQogCQkJdXNlX293bl94YWN0cyA9IGZhbHNlOwogCQllbHNlIGlmIChsaXN0X2xlbmd0aChy
ZWxhdGlvbnMpID4gMSkKIAkJCXVzZV9vd25feGFjdHMgPSB0cnVlOwpAQCAtNDY5LDcgKzQ3MCw3
IEBAIHZhY3V1bShMaXN0ICpyZWxhdGlvbnMsIFZhY3V1bVBhcmFtcyAqcGFyYW1zLAogCSAqLwog
CWlmICh1c2Vfb3duX3hhY3RzKQogCXsKLQkJQXNzZXJ0KCFpbl9vdXRlcl94YWN0KTsKKwkJQXNz
ZXJ0KCF2YWNfaW5fb3V0ZXJfeGFjdCk7CiAKIAkJLyogQWN0aXZlU25hcHNob3QgaXMgbm90IHNl
dCBieSBhdXRvdmFjdXVtICovCiAJCWlmIChBY3RpdmVTbmFwc2hvdFNldCgpKQpAQCAtNTIxLDcg
KzUyMiw3IEBAIHZhY3V1bShMaXN0ICpyZWxhdGlvbnMsIFZhY3V1bVBhcmFtcyAqcGFyYW1zLAog
CQkJCX0KIAogCQkJCWFuYWx5emVfcmVsKHZyZWwtPm9pZCwgdnJlbC0+cmVsYXRpb24sIHBhcmFt
cywKLQkJCQkJCQl2cmVsLT52YV9jb2xzLCBpbl9vdXRlcl94YWN0LCB2YWNfc3RyYXRlZ3kpOwor
CQkJCQkJCXZyZWwtPnZhX2NvbHMsIHZhY19pbl9vdXRlcl94YWN0LCB2YWNfc3RyYXRlZ3kpOwog
CiAJCQkJaWYgKHVzZV9vd25feGFjdHMpCiAJCQkJewpAQCAtMjIxNCw2ICsyMjE1LDEyIEBAIHZh
Y3V1bV9kZWxheV9wb2ludCh2b2lkKQogCQkJCQkJIFdBSVRfRVZFTlRfVkFDVVVNX0RFTEFZKTsK
IAkJUmVzZXRMYXRjaChNeUxhdGNoKTsKIAorCQlpZiAoQ29uZmlnUmVsb2FkUGVuZGluZyAmJiAh
dmFjX2luX291dGVyX3hhY3QpCisJCXsKKwkJCUNvbmZpZ1JlbG9hZFBlbmRpbmcgPSBmYWxzZTsK
KwkJCVByb2Nlc3NDb25maWdGaWxlKFBHQ19TSUdIVVApOworCQl9CisKIAkJVmFjdXVtQ29zdEJh
bGFuY2UgPSAwOwogCiAJCS8qIHVwZGF0ZSBiYWxhbmNlIHZhbHVlcyBmb3Igd29ya2VycyAqLwot
LSAKMi4zNy4yCgo=
--00000000000091ba6c05f56540cb--