Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qn19s-009i9e-LF for pgsql-bugs@arkaria.postgresql.org; Sun, 01 Oct 2023 18:27:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qn19q-00E8tB-VP for pgsql-bugs@arkaria.postgresql.org; Sun, 01 Oct 2023 18:27:10 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qn19q-00E8sx-Lw for pgsql-bugs@lists.postgresql.org; Sun, 01 Oct 2023 18:27:10 +0000 Received: from mail-yw1-x112e.google.com ([2607:f8b0:4864:20::112e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qn19n-007eF0-Rt for pgsql-bugs@lists.postgresql.org; Sun, 01 Oct 2023 18:27:09 +0000 Received: by mail-yw1-x112e.google.com with SMTP id 00721157ae682-5a22f9e2f40so32535317b3.1 for ; Sun, 01 Oct 2023 11:27:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=joeconway.com; s=google; t=1696184827; x=1696789627; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:subject:from:references:cc:to :content-language:user-agent:mime-version:date:message-id:from:to:cc :subject:date:message-id:reply-to; bh=46dgNg3E6rtzRq6ix/9/CuoPTeaC6LjLhxRUQR+yJ58=; b=Wo0RTNb/m79AzpkAr+7lN39C5p99lR4jzuL6rIcPQBT7Wvq+e2oUteZE0kxp43F8Nb e+0zVxb54u4Pu5XNM79JKXp05lUATt1OpHabQAQrfbEvSTlsGblUtuMt4kueFr0QuCuY HSezCw3kza6pyG2XeIqE2MjdEqd0SeK9dCL9wvb9eeAnGJiM+eS8gMqnRAHpjYmbP9U/ 8ZHYZjL3iL/n6He4oZHEPEDLQX0bDVAprM/dEoUVaKkgUaU0L1OWaSm6xjdOfjmnkLN1 9dRGtiNBOtwtdKy5amKvX0O5YCZZFZUq/EZ7pWZ/iQynBRUXHicjamq6ayMHUyT+Qx1k JYsg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696184827; x=1696789627; h=content-transfer-encoding:in-reply-to:subject:from:references:cc:to :content-language:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=46dgNg3E6rtzRq6ix/9/CuoPTeaC6LjLhxRUQR+yJ58=; b=anyjw6Ae2kMujPkEbjpPwn7bbwTEwXaB/SWCYBiIKvVpRLMNQy6NWKSPtnq75vEZWK hKMB+/4X60ag4k3q9g6+Rn15oA1GzVl9JW4vmrZeyFTmDx0AltAnjXMMsRv42Xq7XuC3 HmCW6SgUNLt1VxH0Q8BrfbYgvKjTfklix6sTxPMxH+ikyBTsErgaIP38KvziMkay2bY2 m+/TGnxEOszz+kmv+cHHCFNl1Tk82XjTpe+bDnwOmsXhIV+PfT/zY4VftgHyLPyu4fQ8 O+jyOrXJsuGs+8tGfnR/6vDFLDXefljBACRNuHVSBzSdOLS9F2ptHrMi13P2YL5Qpvzj 0Jbw== X-Gm-Message-State: AOJu0YwN/r48dZ46h8bVKeasGGQI+R290bfcg3G2b128xTmtfjHnfE2E Sjv2mPD0AFJsX+C0EyP5UsEsBg== X-Google-Smtp-Source: AGHT+IFb6Vu62w9smzk62s1IZUh/VJxzuuv8q1+Ep3TwayQXHQcHtBlSqbg5tjYzpTM2zLg3qtRENg== X-Received: by 2002:a0d:d88d:0:b0:59f:4dba:66d2 with SMTP id a135-20020a0dd88d000000b0059f4dba66d2mr9278551ywe.0.1696184827208; Sun, 01 Oct 2023 11:27:07 -0700 (PDT) Received: from [192.168.4.41] (162-239-31-113.lightspeed.dybhfl.sbcglobal.net. [162.239.31.113]) by smtp.gmail.com with ESMTPSA id v136-20020a81488e000000b005a42740fcd4sm1120556ywa.94.2023.10.01.11.27.06 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 01 Oct 2023 11:27:06 -0700 (PDT) Message-ID: Date: Sun, 1 Oct 2023 14:27:05 -0400 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.15.1 Content-Language: en-US To: Tom Lane Cc: Jeff Janes , ravi.s.agrawal23@gmail.com, pgsql-bugs@lists.postgresql.org References: <18141-198c521115e07b7c@postgresql.org> <1848411.1696183788@sss.pgh.pa.us> From: Joe Conway Subject: Re: BUG #18141: sorry, too many clients error occurring very frequently In-Reply-To: <1848411.1696183788@sss.pgh.pa.us> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 10/1/23 14:09, Tom Lane wrote: > Joe Conway writes: >> Maybe, maybe not. I have seen two other cases that are similar. One was >> an upgrade from 12.8 to 12.12 and the other an upgrade from 13.4 to 13.8. > >> I checked and 12.8 was stamped is the same date as 13.4, and 12.12 the >> same day as 13.8. > >> In both cases queries against pg_stat_statements suddenly started taking >> more memory leading to spillage to pg_temp and a step degradation in >> overall performance. In at least one of those cases the >> solution/workaround was to increase work_mem. In the other I think >> pg_stat_statements was disabled. > >> Myself and at least one other hacker looked at the pg_stat_statements >> specific changes in that time interval and saw no smoking gun. > >> But it is possible that something else backpatched to both branches >> between Aug 09, 2021 and Aug 8, 2022 has caused a more general >> performance regression which we have yet to track down. > > Hmm. My first instinct is to wonder about changes in plan selection. > How complex were the troublesome queries? I think both of these cases involved a number of common attributes: * The queries against pg_stat_statements were relatively complex * The other queries on the system were relatively long and complex (and thus the query string length in pg_stat_statements) * Prior to the upgrade the systems were overall keeping up, but extremely busy In one case it seems that the upgrade caused a significant increase of temp file usage. This impacted the system enough that other active queries took longer, and thus number of active connections increased. Raising work_mem eliminated the temp file usage and cpu loads dropped back to similar levels as they were prior to the minor upgrade. The other case had different specifics, but generally involved increased memory usage. In that one eliminating the use of pg_stat_statements restored performance. They did not try raising work_mem (as I understand it), and I did not get any info regarding temp file spillage there. -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com