MIME-Version: 1.0
References: <CADUqk8Uqw5QaUqLdd-0SBCvZVncrE3JMJB9+yDwO_uMv_hTYCg@mail.gmail.com>
 <20230217003204.cx5zu2aocjbwz5ns@awork3.anarazel.de> <CADUqk8UoG-zg-HEjF5xhPi5=j9h5=_BdCvxWwp1K8EKavbZe0g@mail.gmail.com>
 <20230217034000.ddcklsr6hfdifyqk@awork3.anarazel.de> <CAApHDvrbaGtG5CXRmh0aK5O8EAhxJGwGGXu9=m9GRLOQRkPTkw@mail.gmail.com>
 <20230217175201.lspbj47wnjxhastu@awork3.anarazel.de>
In-Reply-To: <20230217175201.lspbj47wnjxhastu@awork3.anarazel.de>
From: David Rowley <dgrowleyml@gmail.com>
Date: Wed, 22 Feb 2023 14:45:39 +1300
Message-ID: <CAApHDvrMhp8FSsSZH6o2ixNT1Ueze6D6GmNd-zV1nHpz0A-0sQ@mail.gmail.com>
Subject: Re: Reducing System Allocator Thrashing of ExecutorState to Alleviate
 FDW-related Performance Degradations
To: Andres Freund <andres@anarazel.de>
Cc: "Jonah H. Harris" <jonah.harris@gmail.com>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000a6883a05f5400e26"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvrMhp8FSsSZH6o2ixNT1Ueze6D6GmNd-zV1nHpz0A-0sQ%40mail.gmail.com>
Precedence: bulk

--000000000000a6883a05f5400e26
Content-Type: text/plain; charset="UTF-8"

On Sat, 18 Feb 2023 at 06:52, Andres Freund <andres@anarazel.de> wrote:
> And did you increase ALLOCSET_DEFAULT_INITSIZE everywhere, or just passed a
> larger block size in CreateExecutorState()?  If the latter,the context
> freelist wouldn't even come into play.

I think this piece of information is critical to confirm what the issue is.

> A 8MB max block size is pretty darn small if you have a workload that ends up
> with a gigabytes worth of blocks.

We should probably review that separately.  These kinds of definitions
don't age well. The current ones appear about 23 years old now, so we
might be overdue to reconsider what they're set to.

2002-12-15 21:01:34 +0000 150) #define ALLOCSET_DEFAULT_MINSIZE   0
2000-06-28 03:33:33 +0000 151) #define ALLOCSET_DEFAULT_INITSIZE  (8 * 1024)
2000-06-28 03:33:33 +0000 152) #define ALLOCSET_DEFAULT_MAXSIZE   (8 *
1024 * 1024)

... I recall having a desktop with 256MBs of RAM back then...

Let's get to the bottom of where the problem is here before we
consider adjusting those. If the problem is unrelated to that then we
shouldn't be discussing that here.

> And the problem also could just be that the default initial blocks size takes
> too long to ramp up to a reasonable block size. I think it's 20 blocks to get
> from ALLOCSET_DEFAULT_INITSIZE to ALLOCSET_DEFAULT_MAXSIZE.  Even if you
> allocate a good bit more than 8MB, having to additionally go through 20
> smaller chunks is going to be noticable until you reach a good bit higher
> number of blocks.

Well, let's try to help Johan get the information to us. I've attached
a quickly put together patch which adds some debug stuff to aset.c.
Johan, if you have a suitable test instance to try this on, can you
send us the filtered DEBUG output from the log messages starting with
"AllocSet" with and without your change?  Just the output for just the
2nd execution of the query in question is fine.  The first execution
is not useful as the cache of MemoryContexts may not be populated by
that time.  It sounds like it's the foreign server that would need to
be patched with this to test it.

If you can send that in two files we should be able to easily see what
has changed in terms of malloc() calls between the two runs.

David

--000000000000a6883a05f5400e26
Content-Type: text/plain; charset="US-ASCII"; name="aset_debug_hacks.diff"
Content-Disposition: attachment; filename="aset_debug_hacks.diff"
Content-Transfer-Encoding: base64
Content-ID: <f_lef0b5jw0>
X-Attachment-Id: f_lef0b5jw0

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL21tZ3IvYXNldC5jIGIvc3JjL2JhY2tlbmQv
dXRpbHMvbW1nci9hc2V0LmMKaW5kZXggMDI2ZjU0NTY3Ni4uNTkwNTk1YmYyNSAxMDA2NDQKLS0t
IGEvc3JjL2JhY2tlbmQvdXRpbHMvbW1nci9hc2V0LmMKKysrIGIvc3JjL2JhY2tlbmQvdXRpbHMv
bW1nci9hc2V0LmMKQEAgLTQyNyw2ICs0MjcsOCBAQCBBbGxvY1NldENvbnRleHRDcmVhdGVJbnRl
cm5hbChNZW1vcnlDb250ZXh0IHBhcmVudCwKIAkJCSgoTWVtb3J5Q29udGV4dCkgc2V0KS0+bWVt
X2FsbG9jYXRlZCA9CiAJCQkJc2V0LT5rZWVwZXItPmVuZHB0ciAtICgoY2hhciAqKSBzZXQpOwog
CisJCQllbG9nKERFQlVHMSwgIkFsbG9jU2V0Q29udGV4dENyZWF0ZUludGVybmFsOiByZWN5Y2xp
bmcgY29udGV4dCBmb3IgJXMiLCAoKE1lbW9yeUNvbnRleHQpIHNldCktPm5hbWUpOworCiAJCQly
ZXR1cm4gKE1lbW9yeUNvbnRleHQpIHNldDsKIAkJfQogCX0KQEAgLTUyMiw2ICs1MjQsOCBAQCBB
bGxvY1NldENvbnRleHRDcmVhdGVJbnRlcm5hbChNZW1vcnlDb250ZXh0IHBhcmVudCwKIAogCSgo
TWVtb3J5Q29udGV4dCkgc2V0KS0+bWVtX2FsbG9jYXRlZCA9IGZpcnN0QmxvY2tTaXplOwogCisJ
ZWxvZyhERUJVRzEsICJBbGxvY1NldENvbnRleHRDcmVhdGVJbnRlcm5hbDogYWxsb2NhdGluZyBu
ZXcgY29udGV4dCBmb3IgJXMgKCV6dSBieXRlcykiLCAoKE1lbW9yeUNvbnRleHQpc2V0KS0+bmFt
ZSwgZmlyc3RCbG9ja1NpemUpOworCiAJcmV0dXJuIChNZW1vcnlDb250ZXh0KSBzZXQ7CiB9CiAK
QEAgLTczMyw2ICs3MzcsOCBAQCBBbGxvY1NldEFsbG9jKE1lbW9yeUNvbnRleHQgY29udGV4dCwg
U2l6ZSBzaXplKQogCQlpZiAoYmxvY2sgPT0gTlVMTCkKIAkJCXJldHVybiBOVUxMOwogCisJCWVs
b2coREVCVUcxLCAiQWxsb2NTZXRBbGxvYzogbWFsbG9jKCV6dSkgZGVkaWNhdGVkIGJsb2NrOiAl
cyIsIGJsa3NpemUsIGNvbnRleHQtPm5hbWUpOworCiAJCWNvbnRleHQtPm1lbV9hbGxvY2F0ZWQg
Kz0gYmxrc2l6ZTsKIAogCQlibG9jay0+YXNldCA9IHNldDsKQEAgLTk0Myw2ICs5NDksOCBAQCBB
bGxvY1NldEFsbG9jKE1lbW9yeUNvbnRleHQgY29udGV4dCwgU2l6ZSBzaXplKQogCQlpZiAoYmxv
Y2sgPT0gTlVMTCkKIAkJCXJldHVybiBOVUxMOwogCisJCWVsb2coREVCVUcxLCAiQWxsb2NTZXRB
bGxvYzogbWFsbG9jKCV6dSkgbmV3IGJsb2NrOiAlcyIsIGJsa3NpemUsIGNvbnRleHQtPm5hbWUp
OworCiAJCWNvbnRleHQtPm1lbV9hbGxvY2F0ZWQgKz0gYmxrc2l6ZTsKIAogCQlibG9jay0+YXNl
dCA9IHNldDsK
--000000000000a6883a05f5400e26--