Message-ID: <a81f2f7ef34afc24a89c613671ea017e3651329c.camel@j-davis.com>
Subject: Re: Should io_method=worker remain the default?
From: Jeff Davis <pgsql@j-davis.com>
To: Andres Freund <andres@anarazel.de>
Cc: pgsql-hackers@postgresql.org
Date: Fri, 05 Sep 2025 13:25:49 -0700
In-Reply-To: <adywrhdn5zcfeldzug2pkzbdxizactr46goh4doiztvexrqomy@lgvl26gui63a>
References: <d68e2a4f8c356107e5167408ad80eaa2fac0f57d.camel@j-davis.com>
	 <adywrhdn5zcfeldzug2pkzbdxizactr46goh4doiztvexrqomy@lgvl26gui63a>
Content-Type: multipart/mixed; boundary="=-bwyzFdolNOj9e5UXWC5B"
User-Agent: Evolution 3.52.3-0ubuntu1 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/a81f2f7ef34afc24a89c613671ea017e3651329c.camel%40j-davis.com>
Precedence: bulk

--=-bwyzFdolNOj9e5UXWC5B
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, 2025-09-03 at 11:55 -0400, Andres Freund wrote:
> I think the regression is not due to anything inherent to worker, but
> due to
> pressure on AioWorkerSubmissionQueueLock - at least that's what I'm
> seeing on
> a older two socket machine. It's possible the bottleneck is different
> on a
> newer machine (my newer workstation is busy on another benchmark rn).

I believe what's happening is that parallelism of the IO completion
work (e.g. checksum verification) is reduced. In worker mode, the
completion work is happening on the io workers (of which there are 3);
while in sync mode the completion work is happening in the backends (of
which there are 32).

There may be lock contention too, but I don't think that's the primary
issue.

I attached a test patch for illustration. It simplifies the code inside
the LWLock to enqueue/dequeue only, and simplifies and reduces the
wakeups by doing pseudo-random wakeups only when enqueuing. Reducing
the wakeups should reduce the number of signals generated without
hurting my case, because the workers are never idle. And reducing the
instructions while holding the LWLock should reduce lock contention.
But the patch barely makes a difference: still around 24tps.

What *does* make a difference is changing io_worker_queue_size. A lower
value of 16 effectively starves the workers of work to do, and I get a
speedup to about 28tps. A higher value of 512 gives the workers more
chance to issue the IOs -- and more responsibility to complete them --
and it drops to 17tps. Furthermore, while the test is running, the io
workers are constantly at 100% (mostly verifying checksums) and the
backends are at 50% (20% when io_worker_queue_size=3D512).

As an aside, I'm building with meson using -Dc_args=3D"-msse4.2 -Wtype-
limits -Werror=3Dmissing-braces". But I notice that the meson build
doesn't seem to use -funroll-loops or -ftree-vectorize when building
checksums.c. Is that intentional? If not, perhaps slower checksum
calculations explain my results.

Regards,
	Jeff Davis


--=-bwyzFdolNOj9e5UXWC5B
Content-Disposition: attachment; filename="test-aio.patch"
Content-Type: text/x-patch; name="test-aio.patch"; charset="UTF-8"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3N0b3JhZ2UvYWlvL21ldGhvZF93b3JrZXIuYyBiL3Ny
Yy9iYWNrZW5kL3N0b3JhZ2UvYWlvL21ldGhvZF93b3JrZXIuYwppbmRleCBiNWFjMDczYTkxMC4u
MjVhZmEzODI5MzIgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3N0b3JhZ2UvYWlvL21ldGhvZF93
b3JrZXIuYworKysgYi9zcmMvYmFja2VuZC9zdG9yYWdlL2Fpby9tZXRob2Rfd29ya2VyLmMKQEAg
LTQ4LDEwICs0OCw2IEBACiAjaW5jbHVkZSAidXRpbHMvd2FpdF9ldmVudC5oIgogCiAKLS8qIEhv
dyBtYW55IHdvcmtlcnMgc2hvdWxkIGVhY2ggd29ya2VyIHdha2UgdXAgaWYgbmVlZGVkPyAqLwot
I2RlZmluZSBJT19XT1JLRVJfV0FLRVVQX0ZBTk9VVCAyCi0KLQogdHlwZWRlZiBzdHJ1Y3QgUGdB
aW9Xb3JrZXJTdWJtaXNzaW9uUXVldWUKIHsKIAl1aW50MzIJCXNpemU7CkBAIC02OSwxMCArNjUs
MTEgQEAgdHlwZWRlZiBzdHJ1Y3QgUGdBaW9Xb3JrZXJTbG90CiAKIHR5cGVkZWYgc3RydWN0IFBn
QWlvV29ya2VyQ29udHJvbAogewotCXVpbnQ2NAkJaWRsZV93b3JrZXJfbWFzazsKKwlpbnQgZHVt
bXk7CiAJUGdBaW9Xb3JrZXJTbG90IHdvcmtlcnNbRkxFWElCTEVfQVJSQVlfTUVNQkVSXTsKIH0g
UGdBaW9Xb3JrZXJDb250cm9sOwogCitzdGF0aWMgaW50CW5leHRXYWtldXBXb3JrZXIgPSAwOwog
CiBzdGF0aWMgc2l6ZV90IHBnYWlvX3dvcmtlcl9zaG1lbV9zaXplKHZvaWQpOwogc3RhdGljIHZv
aWQgcGdhaW9fd29ya2VyX3NobWVtX2luaXQoYm9vbCBmaXJzdF90aW1lKTsKQEAgLTk0LDcgKzkx
LDcgQEAgY29uc3QgSW9NZXRob2RPcHMgcGdhaW9fd29ya2VyX29wcyA9IHsKIGludAkJCWlvX3dv
cmtlcnMgPSAzOwogCiAKLXN0YXRpYyBpbnQJaW9fd29ya2VyX3F1ZXVlX3NpemUgPSA2NDsKK3N0
YXRpYyBpbnQJaW9fd29ya2VyX3F1ZXVlX3NpemUgPSA1MTI7CiBzdGF0aWMgaW50CU15SW9Xb3Jr
ZXJJZDsKIHN0YXRpYyBQZ0Fpb1dvcmtlclN1Ym1pc3Npb25RdWV1ZSAqaW9fd29ya2VyX3N1Ym1p
c3Npb25fcXVldWU7CiBzdGF0aWMgUGdBaW9Xb3JrZXJDb250cm9sICppb193b3JrZXJfY29udHJv
bDsKQEAgLTE1Miw3ICsxNDksNiBAQCBwZ2Fpb193b3JrZXJfc2htZW1faW5pdChib29sIGZpcnN0
X3RpbWUpCiAJCQkJCQkmZm91bmQpOwogCWlmICghZm91bmQpCiAJewotCQlpb193b3JrZXJfY29u
dHJvbC0+aWRsZV93b3JrZXJfbWFzayA9IDA7CiAJCWZvciAoaW50IGkgPSAwOyBpIDwgTUFYX0lP
X1dPUktFUlM7ICsraSkKIAkJewogCQkJaW9fd29ya2VyX2NvbnRyb2wtPndvcmtlcnNbaV0ubGF0
Y2ggPSBOVUxMOwpAQCAtMTYyLDE3ICsxNTgsMjAgQEAgcGdhaW9fd29ya2VyX3NobWVtX2luaXQo
Ym9vbCBmaXJzdF90aW1lKQogfQogCiBzdGF0aWMgaW50Ci1wZ2Fpb193b3JrZXJfY2hvb3NlX2lk
bGUodm9pZCkKK3BnYWlvX3dvcmtlcl9jaG9vc2Uodm9pZCkKIHsKLQlpbnQJCQl3b3JrZXI7CisJ
aW50IHdvcmtlcjsKIAotCWlmIChpb193b3JrZXJfY29udHJvbC0+aWRsZV93b3JrZXJfbWFzayA9
PSAwKQotCQlyZXR1cm4gLTE7CisJd29ya2VyID0gbmV4dFdha2V1cFdvcmtlcjsKKwluZXh0V2Fr
ZXVwV29ya2VyID0gKG5leHRXYWtldXBXb3JrZXIgKyAxKSAlIGlvX3dvcmtlcnM7CiAKLQkvKiBG
aW5kIHRoZSBsb3dlc3QgYml0IHBvc2l0aW9uLCBhbmQgY2xlYXIgaXQuICovCi0Jd29ya2VyID0g
cGdfcmlnaHRtb3N0X29uZV9wb3M2NChpb193b3JrZXJfY29udHJvbC0+aWRsZV93b3JrZXJfbWFz
ayk7Ci0JaW9fd29ya2VyX2NvbnRyb2wtPmlkbGVfd29ya2VyX21hc2sgJj0gfihVSU5UNjRfQygx
KSA8PCB3b3JrZXIpOwotCUFzc2VydChpb193b3JrZXJfY29udHJvbC0+d29ya2Vyc1t3b3JrZXJd
LmluX3VzZSk7CisJaWYgKE15QmFja2VuZFR5cGUgPT0gQl9JT19XT1JLRVIgJiYgbmV4dFdha2V1
cFdvcmtlciA9PSBNeUlvV29ya2VySWQpCisJeworCQlpZiAoaW9fd29ya2VycyA+IDEpCisJCQlu
ZXh0V2FrZXVwV29ya2VyID0gKG5leHRXYWtldXBXb3JrZXIgKyAxKSAlIGlvX3dvcmtlcnM7CisJ
CWVsc2UKKwkJCXJldHVybiAtMTsKKwl9CiAKIAlyZXR1cm4gd29ya2VyOwogfQpAQCAtMjQ1LDgg
KzI0NCw2IEBAIHBnYWlvX3dvcmtlcl9zdWJtaXRfaW50ZXJuYWwoaW50IG51bV9zdGFnZWRfaW9z
LCBQZ0Fpb0hhbmRsZSAqKnN0YWdlZF9pb3MpCiB7CiAJUGdBaW9IYW5kbGUgKnN5bmNocm9ub3Vz
X2lvc1tQR0FJT19TVUJNSVRfQkFUQ0hfU0laRV07CiAJaW50CQkJbnN5bmMgPSAwOwotCUxhdGNo
CSAgICp3YWtldXAgPSBOVUxMOwotCWludAkJCXdvcmtlcjsKIAogCUFzc2VydChudW1fc3RhZ2Vk
X2lvcyA8PSBQR0FJT19TVUJNSVRfQkFUQ0hfU0laRSk7CiAKQEAgLTI2MSwyNSArMjU4LDIwIEBA
IHBnYWlvX3dvcmtlcl9zdWJtaXRfaW50ZXJuYWwoaW50IG51bV9zdGFnZWRfaW9zLCBQZ0Fpb0hh
bmRsZSAqKnN0YWdlZF9pb3MpCiAJCQkgKiB3ZSBjYW4gdG8gd29ya2VycywgdG8gbWF4aW1pemUg
Y29uY3VycmVuY3kuCiAJCQkgKi8KIAkJCXN5bmNocm9ub3VzX2lvc1tuc3luYysrXSA9IHN0YWdl
ZF9pb3NbaV07Ci0JCQljb250aW51ZTsKIAkJfQorCX0KKwlMV0xvY2tSZWxlYXNlKEFpb1dvcmtl
clN1Ym1pc3Npb25RdWV1ZUxvY2spOworCisJaWYgKG51bV9zdGFnZWRfaW9zID4gbnN5bmMpCisJ
eworCQlpbnQJCQl3b3JrZXIgPSBwZ2Fpb193b3JrZXJfY2hvb3NlKCk7CiAKLQkJaWYgKHdha2V1
cCA9PSBOVUxMKQorCQlpZiAod29ya2VyID49IDApCiAJCXsKLQkJCS8qIENob29zZSBhbiBpZGxl
IHdvcmtlciB0byB3YWtlIHVwIGlmIHdlIGhhdmVuJ3QgYWxyZWFkeS4gKi8KLQkJCXdvcmtlciA9
IHBnYWlvX3dvcmtlcl9jaG9vc2VfaWRsZSgpOwotCQkJaWYgKHdvcmtlciA+PSAwKQotCQkJCXdh
a2V1cCA9IGlvX3dvcmtlcl9jb250cm9sLT53b3JrZXJzW3dvcmtlcl0ubGF0Y2g7Ci0KLQkJCXBn
YWlvX2RlYnVnX2lvKERFQlVHNCwgc3RhZ2VkX2lvc1tpXSwKLQkJCQkJCSAgICJjaG9vc2luZyB3
b3JrZXIgJWQiLAotCQkJCQkJICAgd29ya2VyKTsKKwkJCUxhdGNoCSAgICp3YWtldXAgPSBpb193
b3JrZXJfY29udHJvbC0+d29ya2Vyc1t3b3JrZXJdLmxhdGNoOworCQkJU2V0TGF0Y2god2FrZXVw
KTsKIAkJfQogCX0KLQlMV0xvY2tSZWxlYXNlKEFpb1dvcmtlclN1Ym1pc3Npb25RdWV1ZUxvY2sp
OwotCi0JaWYgKHdha2V1cCkKLQkJU2V0TGF0Y2god2FrZXVwKTsKIAogCS8qIFJ1biB3aGF0ZXZl
ciBpcyBsZWZ0IHN5bmNocm9ub3VzbHkuICovCiAJaWYgKG5zeW5jID4gMCkKQEAgLTMxNyw3ICsz
MDksNiBAQCBwZ2Fpb193b3JrZXJfZGllKGludCBjb2RlLCBEYXR1bSBhcmcpCiAJQXNzZXJ0KGlv
X3dvcmtlcl9jb250cm9sLT53b3JrZXJzW015SW9Xb3JrZXJJZF0uaW5fdXNlKTsKIAlBc3NlcnQo
aW9fd29ya2VyX2NvbnRyb2wtPndvcmtlcnNbTXlJb1dvcmtlcklkXS5sYXRjaCA9PSBNeUxhdGNo
KTsKIAotCWlvX3dvcmtlcl9jb250cm9sLT5pZGxlX3dvcmtlcl9tYXNrICY9IH4oVUlOVDY0X0Mo
MSkgPDwgTXlJb1dvcmtlcklkKTsKIAlpb193b3JrZXJfY29udHJvbC0+d29ya2Vyc1tNeUlvV29y
a2VySWRdLmluX3VzZSA9IGZhbHNlOwogCWlvX3dvcmtlcl9jb250cm9sLT53b3JrZXJzW015SW9X
b3JrZXJJZF0ubGF0Y2ggPSBOVUxMOwogCUxXTG9ja1JlbGVhc2UoQWlvV29ya2VyU3VibWlzc2lv
blF1ZXVlTG9jayk7CkBAIC0zNTQsNyArMzQ1LDYgQEAgcGdhaW9fd29ya2VyX3JlZ2lzdGVyKHZv
aWQpCiAJaWYgKE15SW9Xb3JrZXJJZCA9PSAtMSkKIAkJZWxvZyhFUlJPUiwgImNvdWxkbid0IGZp
bmQgYSBmcmVlIHdvcmtlciBzbG90Iik7CiAKLQlpb193b3JrZXJfY29udHJvbC0+aWRsZV93b3Jr
ZXJfbWFzayB8PSAoVUlOVDY0X0MoMSkgPDwgTXlJb1dvcmtlcklkKTsKIAlpb193b3JrZXJfY29u
dHJvbC0+d29ya2Vyc1tNeUlvV29ya2VySWRdLmxhdGNoID0gTXlMYXRjaDsKIAlMV0xvY2tSZWxl
YXNlKEFpb1dvcmtlclN1Ym1pc3Npb25RdWV1ZUxvY2spOwogCkBAIC00NTgsMTAgKzQ0OCw2IEBA
IElvV29ya2VyTWFpbihjb25zdCB2b2lkICpzdGFydHVwX2RhdGEsIHNpemVfdCBzdGFydHVwX2Rh
dGFfbGVuKQogCXdoaWxlICghU2h1dGRvd25SZXF1ZXN0UGVuZGluZykKIAl7CiAJCXVpbnQzMgkJ
aW9faW5kZXg7Ci0JCUxhdGNoCSAgICpsYXRjaGVzW0lPX1dPUktFUl9XQUtFVVBfRkFOT1VUXTsK
LQkJaW50CQkJbmxhdGNoZXMgPSAwOwotCQlpbnQJCQlud2FrZXVwcyA9IDA7Ci0JCWludAkJCXdv
cmtlcjsKIAogCQkvKgogCQkgKiBUcnkgdG8gZ2V0IGEgam9iIHRvIGRvLgpAQCAtNDcwLDM2ICs0
NTYsOSBAQCBJb1dvcmtlck1haW4oY29uc3Qgdm9pZCAqc3RhcnR1cF9kYXRhLCBzaXplX3Qgc3Rh
cnR1cF9kYXRhX2xlbikKIAkJICogdG8gZW5zdXJlIHRoYXQgd2UgZG9uJ3Qgc2VlIGFuIG91dGRh
dGVkIGRhdGEgaW4gdGhlIGhhbmRsZS4KIAkJICovCiAJCUxXTG9ja0FjcXVpcmUoQWlvV29ya2Vy
U3VibWlzc2lvblF1ZXVlTG9jaywgTFdfRVhDTFVTSVZFKTsKLQkJaWYgKChpb19pbmRleCA9IHBn
YWlvX3dvcmtlcl9zdWJtaXNzaW9uX3F1ZXVlX2NvbnN1bWUoKSkgPT0gLTEpCi0JCXsKLQkJCS8q
Ci0JCQkgKiBOb3RoaW5nIHRvIGRvLiAgTWFyayBzZWxmIGlkbGUuCi0JCQkgKgotCQkJICogWFhY
OiBJbnZlbnQgc29tZSBraW5kIG9mIGJhY2sgcHJlc3N1cmUgdG8gcmVkdWNlIHVzZWxlc3MKLQkJ
CSAqIHdha2V1cHM/Ci0JCQkgKi8KLQkJCWlvX3dvcmtlcl9jb250cm9sLT5pZGxlX3dvcmtlcl9t
YXNrIHw9IChVSU5UNjRfQygxKSA8PCBNeUlvV29ya2VySWQpOwotCQl9Ci0JCWVsc2UKLQkJewot
CQkJLyogR290IG9uZS4gIENsZWFyIGlkbGUgZmxhZy4gKi8KLQkJCWlvX3dvcmtlcl9jb250cm9s
LT5pZGxlX3dvcmtlcl9tYXNrICY9IH4oVUlOVDY0X0MoMSkgPDwgTXlJb1dvcmtlcklkKTsKLQot
CQkJLyogU2VlIGlmIHdlIGNhbiB3YWtlIHVwIHNvbWUgcGVlcnMuICovCi0JCQlud2FrZXVwcyA9
IE1pbihwZ2Fpb193b3JrZXJfc3VibWlzc2lvbl9xdWV1ZV9kZXB0aCgpLAotCQkJCQkJICAgSU9f
V09SS0VSX1dBS0VVUF9GQU5PVVQpOwotCQkJZm9yIChpbnQgaSA9IDA7IGkgPCBud2FrZXVwczsg
KytpKQotCQkJewotCQkJCWlmICgod29ya2VyID0gcGdhaW9fd29ya2VyX2Nob29zZV9pZGxlKCkp
IDwgMCkKLQkJCQkJYnJlYWs7Ci0JCQkJbGF0Y2hlc1tubGF0Y2hlcysrXSA9IGlvX3dvcmtlcl9j
b250cm9sLT53b3JrZXJzW3dvcmtlcl0ubGF0Y2g7Ci0JCQl9Ci0JCX0KKwkJaW9faW5kZXggPSBw
Z2Fpb193b3JrZXJfc3VibWlzc2lvbl9xdWV1ZV9jb25zdW1lKCk7CiAJCUxXTG9ja1JlbGVhc2Uo
QWlvV29ya2VyU3VibWlzc2lvblF1ZXVlTG9jayk7CiAKLQkJZm9yIChpbnQgaSA9IDA7IGkgPCBu
bGF0Y2hlczsgKytpKQotCQkJU2V0TGF0Y2gobGF0Y2hlc1tpXSk7Ci0KIAkJaWYgKGlvX2luZGV4
ICE9IC0xKQogCQl7CiAJCQlQZ0Fpb0hhbmRsZSAqaW9oID0gTlVMTDsK


--=-bwyzFdolNOj9e5UXWC5B--