Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jq93g-0004ob-Dh for pgsql-hackers@arkaria.postgresql.org; Tue, 30 Jun 2020 05:43:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jq93f-0006PG-7s for pgsql-hackers@arkaria.postgresql.org; Tue, 30 Jun 2020 05:43:51 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jq93e-0006P8-Sv for pgsql-hackers@lists.postgresql.org; Tue, 30 Jun 2020 05:43:51 +0000 Received: from oss.nttdata.com ([49.212.34.109]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jq93X-0001n5-0S for pgsql-hackers@postgresql.org; Tue, 30 Jun 2020 05:43:49 +0000 Received: from hnk.local (p2604028-ipngn12401funabasi.chiba.ocn.ne.jp [180.1.110.28]) by oss.nttdata.com (Postfix) with ESMTPSA id ADD876029A; Tue, 30 Jun 2020 14:43:39 +0900 (JST) X-Virus-Status: Clean X-Virus-Scanned: clamav-milter 0.102.3 at oss.nttdata.com Subject: Re: track_planning causing performance regression To: Ants Aasma , Julien Rouhaud Cc: "Tharakan, Robins" , "pgsql-hackers@postgresql.org" References: <2895b53b033c47ccb22972b589050dd9@EX13D05UWC001.ant.amazon.com> From: Fujii Masao Message-ID: <5725cc95-6aea-d3a8-6ea5-36acf780a55d@oss.nttdata.com> Date: Tue, 30 Jun 2020 14:43:39 +0900 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:68.0) Gecko/20100101 Thunderbird/68.9.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------8E9EB861D4D477C4183ECDED" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------8E9EB861D4D477C4183ECDED Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 2020/06/29 22:23, Ants Aasma wrote: > On Mon, 29 Jun 2020 at 12:17, Julien Rouhaud > wrote: > > On Mon, Jun 29, 2020 at 10:55 AM Fujii Masao > > wrote: > > > > On 2020/06/29 16:05, Julien Rouhaud wrote: > > > On Mon, Jun 29, 2020 at 7:49 AM Tharakan, Robins > wrote: > > >> > > >> During fully-cached SELECT-only test using pgbench, Postgres v13Beta1 shows > > > > Thanks for the benchmark! > > > > > > >> ~45% performance drop [2] at high DB connection counts (when compared with v12.3) > > > > That's bad :( > > > > > > >> > > >> Disabling pg_stat_statements.track_planning (which is 'On' by default) > > >> brings the TPS numbers up to v12.3 levels. > > >> > > >> The inflection point (in this test-case) is 128 Connections, beyond which the > > >> TPS numbers are consistently low. Looking at the mailing list [1], this issue > > >> didn't surface earlier possibly since the regression is trivial at low connection counts. > > >> > > >> It would be great if this could be optimized further, or track_planning > > >> disabled (by default) so as to not trip users upgrading from v12 with pg_stat_statement > > >> enabled (but otherwise not particularly interested in track_planning). > > > > Your benchmark result seems to suggest that the cause of the problem is > > the contention of per-query spinlock in pgss_store(). Right? > > This lock contention is likely to happen when multiple sessions run > > the same queries. > > > > One idea to reduce that lock contention is to separate per-query spinlock > > into two; one is for planning, and the other is for execution. pgss_store() > > determines which lock to use based on the given "kind" argument. > > To make this idea work, also every pgss counters like shared_blks_hit > > need to be separated into two, i.e., for planning and execution. > > This can probably remove some overhead, but won't it eventually hit > the same issue when multiple connections try to plan the same query, > given the number of different queries and very low execution runtime? > It'll also quite increase the shared memory consumption. > > I'm wondering if we could instead use atomics to store the counters. > The only downside is that we won't guarantee per-row consistency > anymore, which may be problematic. > > > > The problem looks to be that spinlocks are terrible with overloaded CPU and a contended spinlock. A process holding the spinlock might easily get scheduled out leading to excessive spinning by everybody. I think a simple thing to try would be to replace the spinlock with LWLock. Yes. Attached is the POC patch that replaces per-counter spinlock with LWLock. > > I did a prototype patch that replaces spinlocks with futexes, but was not able to find a workload where it mattered. I'm not familiar with futex, but could you tell me why you used futex instead of LWLock that we already have? Is futex portable? > We have done a great job at eliminating spinlocks from contended code paths. Robins, perhaps you could try it to see if it reduces the regression you are observing. Yes. Also we need to check that this change doesn't increase performance overhead in other workloads. Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION --------------8E9EB861D4D477C4183ECDED Content-Type: text/plain; charset=UTF-8; x-mac-type="0"; x-mac-creator="0"; name="pgss_lwlock_v1.patch" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="pgss_lwlock_v1.patch" ZGlmZiAtLWdpdCBhL2NvbnRyaWIvcGdfc3RhdF9zdGF0ZW1lbnRzL3BnX3N0YXRfc3RhdGVt ZW50cy5jIGIvY29udHJpYi9wZ19zdGF0X3N0YXRlbWVudHMvcGdfc3RhdF9zdGF0ZW1lbnRz LmMKaW5kZXggY2VmOGJiNWE0OS4uYWE1MDZmNmMxMSAxMDA2NDQKLS0tIGEvY29udHJpYi9w Z19zdGF0X3N0YXRlbWVudHMvcGdfc3RhdF9zdGF0ZW1lbnRzLmMKKysrIGIvY29udHJpYi9w Z19zdGF0X3N0YXRlbWVudHMvcGdfc3RhdF9zdGF0ZW1lbnRzLmMKQEAgLTM5LDcgKzM5LDcg QEAKICAqIGluIGFuIGVudHJ5IGV4Y2VwdCB0aGUgY291bnRlcnMgcmVxdWlyZXMgdGhlIHNh bWUuICBUbyBsb29rIHVwIGFuIGVudHJ5LAogICogb25lIG11c3QgaG9sZCB0aGUgbG9jayBz aGFyZWQuICBUbyByZWFkIG9yIHVwZGF0ZSB0aGUgY291bnRlcnMgd2l0aGluCiAgKiBhbiBl bnRyeSwgb25lIG11c3QgaG9sZCB0aGUgbG9jayBzaGFyZWQgb3IgZXhjbHVzaXZlIChzbyB0 aGUgZW50cnkgZG9lc24ndAotICogZGlzYXBwZWFyISkgYW5kIGFsc28gdGFrZSB0aGUgZW50 cnkncyBtdXRleCBzcGlubG9jay4KKyAqIGRpc2FwcGVhciEpIGFuZCBhbHNvIHRha2UgdGhl IGVudHJ5J3MgcGFydGl0aW9uIGxvY2suCiAgKiBUaGUgc2hhcmVkIHN0YXRlIHZhcmlhYmxl IHBnc3MtPmV4dGVudCAodGhlIG5leHQgZnJlZSBzcG90IGluIHRoZSBleHRlcm5hbAogICog cXVlcnktdGV4dCBmaWxlKSBzaG91bGQgYmUgYWNjZXNzZWQgb25seSB3aGlsZSBob2xkaW5n IGVpdGhlciB0aGUKICAqIHBnc3MtPm11dGV4IHNwaW5sb2NrLCBvciBleGNsdXNpdmUgbG9j ayBvbiBwZ3NzLT5sb2NrLiAgV2UgdXNlIHRoZSBtdXRleCB0bwpAQCAtMTE1LDYgKzExNSwx MSBAQCBzdGF0aWMgY29uc3QgdWludDMyIFBHU1NfUEdfTUFKT1JfVkVSU0lPTiA9IFBHX1ZF UlNJT05fTlVNIC8gMTAwOwogCiAjZGVmaW5lIEpVTUJMRV9TSVpFCQkJCTEwMjQJLyogcXVl cnkgc2VyaWFsaXphdGlvbiBidWZmZXIgc2l6ZSAqLwogCisjZGVmaW5lCVBHU1NfTlVNX0xP Q0tfUEFSVElUSU9OUygpCQkocGdzc19tYXgpCisjZGVmaW5lCVBHU1NfSEFTSF9QQVJUSVRJ T05fTE9DSyhrZXkpCVwKKwkoJihwZ3NzLT5iYXNlICsJXAorCSAgIChnZXRfaGFzaF92YWx1 ZShwZ3NzX2hhc2gsIGtleSkgJSBQR1NTX05VTV9MT0NLX1BBUlRJVElPTlMoKSkpLT5sb2Nr KQorCiAvKgogICogRXh0ZW5zaW9uIHZlcnNpb24gbnVtYmVyLCBmb3Igc3VwcG9ydGluZyBv bGRlciBleHRlbnNpb24gdmVyc2lvbnMnIG9iamVjdHMKICAqLwpAQCAtMjA3LDcgKzIxMiw3 IEBAIHR5cGVkZWYgc3RydWN0IHBnc3NFbnRyeQogCVNpemUJCXF1ZXJ5X29mZnNldDsJLyog cXVlcnkgdGV4dCBvZmZzZXQgaW4gZXh0ZXJuYWwgZmlsZSAqLwogCWludAkJCXF1ZXJ5X2xl bjsJCS8qICMgb2YgdmFsaWQgYnl0ZXMgaW4gcXVlcnkgc3RyaW5nLCBvciAtMSAqLwogCWlu dAkJCWVuY29kaW5nOwkJLyogcXVlcnkgdGV4dCBlbmNvZGluZyAqLwotCXNsb2NrX3QJCW11 dGV4OwkJCS8qIHByb3RlY3RzIHRoZSBjb3VudGVycyBvbmx5ICovCisJTFdMb2NrCSAgIAkq bG9jazsJCQkvKiBwcm90ZWN0cyB0aGUgY291bnRlcnMgb25seSAqLwogfSBwZ3NzRW50cnk7 CiAKIC8qCkBAIC0yMTYsNiArMjIxLDcgQEAgdHlwZWRlZiBzdHJ1Y3QgcGdzc0VudHJ5CiB0 eXBlZGVmIHN0cnVjdCBwZ3NzU2hhcmVkU3RhdGUKIHsKIAlMV0xvY2sJICAgKmxvY2s7CQkJ LyogcHJvdGVjdHMgaGFzaHRhYmxlIHNlYXJjaC9tb2RpZmljYXRpb24gKi8KKwlMV0xvY2tQ YWRkZWQJKmJhc2U7CQkvKiBiYXNlIGFkZHJlc3Mgb2YgdGhpcyBMV0xvY2sgKi8KIAlkb3Vi bGUJCWN1cl9tZWRpYW5fdXNhZ2U7CS8qIGN1cnJlbnQgbWVkaWFuIHVzYWdlIGluIGhhc2h0 YWJsZSAqLwogCVNpemUJCW1lYW5fcXVlcnlfbGVuOyAvKiBjdXJyZW50IG1lYW4gZW50cnkg dGV4dCBsZW5ndGggKi8KIAlzbG9ja190CQltdXRleDsJCQkvKiBwcm90ZWN0cyBmb2xsb3dp bmcgZmllbGRzIG9ubHk6ICovCkBAIC00NjgsNyArNDc0LDggQEAgX1BHX2luaXQodm9pZCkK IAkgKiByZXNvdXJjZXMgaW4gcGdzc19zaG1lbV9zdGFydHVwKCkuCiAJICovCiAJUmVxdWVz dEFkZGluU2htZW1TcGFjZShwZ3NzX21lbXNpemUoKSk7Ci0JUmVxdWVzdE5hbWVkTFdMb2Nr VHJhbmNoZSgicGdfc3RhdF9zdGF0ZW1lbnRzIiwgMSk7CisJUmVxdWVzdE5hbWVkTFdMb2Nr VHJhbmNoZSgicGdfc3RhdF9zdGF0ZW1lbnRzIiwKKwkJCQkJCQkgIFBHU1NfTlVNX0xPQ0tf UEFSVElUSU9OUygpICsgMSk7CiAKIAkvKgogCSAqIEluc3RhbGwgaG9va3MuCkBAIC01NDcs NyArNTU0LDggQEAgcGdzc19zaG1lbV9zdGFydHVwKHZvaWQpCiAJaWYgKCFmb3VuZCkKIAl7 CiAJCS8qIEZpcnN0IHRpbWUgdGhyb3VnaCAuLi4gKi8KLQkJcGdzcy0+bG9jayA9ICYoR2V0 TmFtZWRMV0xvY2tUcmFuY2hlKCJwZ19zdGF0X3N0YXRlbWVudHMiKSktPmxvY2s7CisJCXBn c3MtPmJhc2UgPSBHZXROYW1lZExXTG9ja1RyYW5jaGUoInBnX3N0YXRfc3RhdGVtZW50cyIp OworCQlwZ3NzLT5sb2NrID0gJihwZ3NzLT5iYXNlICsgcGdzc19tYXgpLT5sb2NrOwogCQlw Z3NzLT5jdXJfbWVkaWFuX3VzYWdlID0gQVNTVU1FRF9NRURJQU5fSU5JVDsKIAkJcGdzcy0+ bWVhbl9xdWVyeV9sZW4gPSBBU1NVTUVEX0xFTkdUSF9JTklUOwogCQlTcGluTG9ja0luaXQo JnBnc3MtPm11dGV4KTsKQEAgLTEzNzYsMTQgKzEzODQsMTQgQEAgcGdzc19zdG9yZShjb25z dCBjaGFyICpxdWVyeSwgdWludDY0IHF1ZXJ5SWQsCiAJaWYgKCFqc3RhdGUpCiAJewogCQkv KgotCQkgKiBHcmFiIHRoZSBzcGlubG9jayB3aGlsZSB1cGRhdGluZyB0aGUgY291bnRlcnMg KHNlZSBjb21tZW50IGFib3V0Ci0JCSAqIGxvY2tpbmcgcnVsZXMgYXQgdGhlIGhlYWQgb2Yg dGhlIGZpbGUpCisJCSAqIEdyYWIgdGhlIHBhcnRpdGlvbiBsb2NrIHdoaWxlIHVwZGF0aW5n IHRoZSBjb3VudGVycyAoc2VlIGNvbW1lbnQKKwkJICogYWJvdXQgbG9ja2luZyBydWxlcyBh dCB0aGUgaGVhZCBvZiB0aGUgZmlsZSkKIAkJICovCiAJCXZvbGF0aWxlIHBnc3NFbnRyeSAq ZSA9ICh2b2xhdGlsZSBwZ3NzRW50cnkgKikgZW50cnk7CiAKIAkJQXNzZXJ0KGtpbmQgPT0g UEdTU19QTEFOIHx8IGtpbmQgPT0gUEdTU19FWEVDKTsKIAotCQlTcGluTG9ja0FjcXVpcmUo JmUtPm11dGV4KTsKKwkJTFdMb2NrQWNxdWlyZShlLT5sb2NrLCBMV19FWENMVVNJVkUpOwog CiAJCS8qICJVbnN0aWNrIiBlbnRyeSBpZiBpdCB3YXMgcHJldmlvdXNseSBzdGlja3kgKi8K IAkJaWYgKElTX1NUSUNLWShlLT5jb3VudGVycykpCkBAIC0xNDM1LDcgKzE0NDMsNyBAQCBw Z3NzX3N0b3JlKGNvbnN0IGNoYXIgKnF1ZXJ5LCB1aW50NjQgcXVlcnlJZCwKIAkJZS0+Y291 bnRlcnMud2FsX2ZwaSArPSB3YWx1c2FnZS0+d2FsX2ZwaTsKIAkJZS0+Y291bnRlcnMud2Fs X2J5dGVzICs9IHdhbHVzYWdlLT53YWxfYnl0ZXM7CiAKLQkJU3BpbkxvY2tSZWxlYXNlKCZl LT5tdXRleCk7CisJCUxXTG9ja1JlbGVhc2UoZS0+bG9jayk7CiAJfQogCiBkb25lOgpAQCAt MTc2Miw5ICsxNzcwLDkgQEAgcGdfc3RhdF9zdGF0ZW1lbnRzX2ludGVybmFsKEZ1bmN0aW9u Q2FsbEluZm8gZmNpbmZvLAogCQl7CiAJCQl2b2xhdGlsZSBwZ3NzRW50cnkgKmUgPSAodm9s YXRpbGUgcGdzc0VudHJ5ICopIGVudHJ5OwogCi0JCQlTcGluTG9ja0FjcXVpcmUoJmUtPm11 dGV4KTsKKwkJCUxXTG9ja0FjcXVpcmUoZS0+bG9jaywgTFdfU0hBUkVEKTsKIAkJCXRtcCA9 IGUtPmNvdW50ZXJzOwotCQkJU3BpbkxvY2tSZWxlYXNlKCZlLT5tdXRleCk7CisJCQlMV0xv Y2tSZWxlYXNlKGUtPmxvY2spOwogCQl9CiAKIAkJLyogU2tpcCBlbnRyeSBpZiB1bmV4ZWN1 dGVkIChpZSwgaXQncyBhIHBlbmRpbmcgInN0aWNreSIgZW50cnkpICovCkBAIC0xOTA4LDgg KzE5MTYsOCBAQCBlbnRyeV9hbGxvYyhwZ3NzSGFzaEtleSAqa2V5LCBTaXplIHF1ZXJ5X29m ZnNldCwgaW50IHF1ZXJ5X2xlbiwgaW50IGVuY29kaW5nLAogCQltZW1zZXQoJmVudHJ5LT5j b3VudGVycywgMCwgc2l6ZW9mKENvdW50ZXJzKSk7CiAJCS8qIHNldCB0aGUgYXBwcm9wcmlh dGUgaW5pdGlhbCB1c2FnZSBjb3VudCAqLwogCQllbnRyeS0+Y291bnRlcnMudXNhZ2UgPSBz dGlja3kgPyBwZ3NzLT5jdXJfbWVkaWFuX3VzYWdlIDogVVNBR0VfSU5JVDsKLQkJLyogcmUt aW5pdGlhbGl6ZSB0aGUgbXV0ZXggZWFjaCB0aW1lIC4uLiB3ZSBhc3N1bWUgbm8gb25lIHVz aW5nIGl0ICovCi0JCVNwaW5Mb2NrSW5pdCgmZW50cnktPm11dGV4KTsKKwkJLyogZGV0ZXJt aW5lIHdoaWNoIHBhcnRpdGlvbiBsb2NrIHRvIHVzZSBmb3IgdGhpcyBxdWVyeSAqLworCQll bnRyeS0+bG9jayA9IFBHU1NfSEFTSF9QQVJUSVRJT05fTE9DSyhrZXkpOwogCQkvKiAuLi4g YW5kIGRvbid0IGZvcmdldCB0aGUgcXVlcnkgdGV4dCBtZXRhZGF0YSAqLwogCQlBc3NlcnQo cXVlcnlfbGVuID49IDApOwogCQllbnRyeS0+cXVlcnlfb2Zmc2V0ID0gcXVlcnlfb2Zmc2V0 Owo= --------------8E9EB861D4D477C4183ECDED--