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 1qv1Eo-002e2l-F3 for pgsql-general@arkaria.postgresql.org; Mon, 23 Oct 2023 20:09:22 +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 1qv1Em-00El02-DE for pgsql-general@arkaria.postgresql.org; Mon, 23 Oct 2023 20:09:21 +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 1qv1Em-00Ekzq-2O for pgsql-general@lists.postgresql.org; Mon, 23 Oct 2023 20:09:20 +0000 Received: from aart.rice.edu ([168.7.56.48]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qv1Ej-001xTm-By for pgsql-general@lists.postgresql.org; Mon, 23 Oct 2023 20:09:19 +0000 Received: by aart.rice.edu (Postfix, from userid 18612) id 88740C152150; Mon, 23 Oct 2023 15:09:16 -0500 (CDT) Date: Mon, 23 Oct 2023 15:09:16 -0500 From: Ken Marshall To: pgsql-general@lists.postgresql.org Subject: Re: Disk wait problem... Message-ID: References: <20231023145459.143faba7.pfortin@pfortin.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > Hi, > > I have a 1.6TB database with over 330 tables on a 4TB NVMe SSD. All > tables are static (no updates); most in 8M and 33M row sizes. Queries have > been great, until recently. > Also attached is the relevant system journal entries for one query that > took 2 seconds and two more that took 62 and 52 seconds... Unlike the > above, these had 3 processes in disk-wait -- see attached screenshot... > > I just stopped PG, updated the full backup which was done overnight (so > the update took about a second), restarted PG and the problem persists... > > Any suggestions where to look next? > > Thanks, > Pierre Hi Pierre, I have seen this type of behavior when parallel queries were enabled and PostgreSQL decided that a parallel sequential scan query was the fastest option. Try disabling them and see if that helps. Regards, Ken