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 1qx5js-00G5BK-9u for pgsql-general@arkaria.postgresql.org; Sun, 29 Oct 2023 13:22:00 +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 1qx5jp-007RSc-Va for pgsql-general@arkaria.postgresql.org; Sun, 29 Oct 2023 13:21:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qx5jp-007RSU-6i for pgsql-general@lists.postgresql.org; Sun, 29 Oct 2023 13:21:57 +0000 Received: from rs6162.luxsci.com ([162.142.76.255]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qx5jj-003ejl-Ad for pgsql-general@lists.postgresql.org; Sun, 29 Oct 2023 13:21:55 +0000 Received: from pfortin.com (h199.241.136.40.dynamic.ip.windstream.net [40.136.241.199]) (authenticated bits=0) by rs6162.luxsci.com (8.15.2/8.14.9) with ESMTPSA id 39TDLkuD2110961 (version=TLSv1.3 cipher=TLS_AES_256_GCM_SHA384 bits=256 verify=NOT) for ; Sun, 29 Oct 2023 13:21:47 GMT Date: Sun, 29 Oct 2023 09:21:46 -0400 From: pf@pfortin.com Cc: pgsql-general@lists.postgresql.org Subject: [SOLVED?] Re: Disk wait problem... not hardware... Message-ID: <20231029092146.53351f7e@pfortin.com> In-Reply-To: References: <20231023145459.143faba7.pfortin@pfortin.com> <20231023175529.59f309bb@pfortin.com> <9938ff1a-8837-454b-a50c-a748e84c172a@aklaver.com> <20231026095000.4swq6vbgdsw3palp@hjp.at> <20231026155016.6b739a7e.pfortin@pfortin.com> <20231026220325.2204a3f6.pfortin@pfortin.com> <20231027170711.xgz3xrf7cujx7udh@hjp.at> <20231027194609.1bf96324.pfortin@pfortin.com> X-Mailer: Claws Mail 4.1.1git67 (GTK 3.24.38; x86_64-pc-linux-gnu) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Lux-Comment: Message 39TDLkuD2110961 sent by user #22429 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pfortin.com; h=date:from:cc:subject:message-id:in-reply-to:references:mime-version:content-type:content-transfer-encoding; s=dkim; bh=2pTM8XEqRzKUWYb7XZzEfUPPrjEUZyLIxNrGUHY+Jxk=; b=txTXxuqPm0gv57YTT82JW4RlooDun4hxxdW1hndCUTqvi3xqbncUaLiTAfZNuBGaou8oy+3oS0i0axL2a1CFvgsZXwY/mvWHn7jSq11AWU2BvOZ22RQ5on98/eq/W1k8KWTsYkWDjBkJukyb51FuBmXS3jNPMSG1NZoUcL8DXMA= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Sat, 28 Oct 2023 18:34:50 -0400 Jim Mlodgenski wrote: Looking like a GOLD star for Jim... >On Fri, Oct 27, 2023 at 7:46=E2=80=AFPM wrote: > >> Memory: 125.5 GiB of RAM >> =20 >It looks like you have a large amount of memory allocated to the server > >But your plans are doing reads instead of pulling things from shared >buffers Assuming you are referring to "Buffers: shared hit=3D248 read=3D25022" ? >>explain (analyze, buffers) select count(*) from ncvhis_2016_12_03; >> Finalize Aggregate (cost=3D404669.65..404669.66 rows=3D1 width=3D8) (ac= tual >> time=3D844.158..847.309 rows=3D1 loops=3D1) Buffers: shared hit=3D248 re= ad=3D25022 =20 >> -> Gather (cost=3D404669.43..404669.65 rows=3D2 width=3D8) (actual = =20 >> time=3D844.133..847.301 rows=3D3 loops=3D1) Workers Planned: 2 >> Workers Launched: 2 >> Buffers: shared hit=3D248 read=3D25022 =20 >> -> Partial Aggregate (cost=3D403669.43..403669.45 rows=3D1 >> width=3D8) (actual time=3D838.772..838.772 rows=3D1 loops=3D3) Buffers: = shared >> hit=3D248 read=3D25022 -> Parallel Index Only Scan using >> ncvhis_2016_12_03_voted_party_cd_idx on ncvhis_2016_12_03 >> (cost=3D0.44..372735.05 rows=3D12373755 width=3D0) (actual >> time=3D18.277..592.473 rows=3D9900389 loops=3D3) Heap Fetches: 0 Buffers: >> shared hit=3D248 read=3D25022 Planning Time: 0.069 ms JIT: >> Functions: 8 >> Options: Inlining false, Optimization false, Expressions true, >> Deforming true Timing: Generation 0.284 ms, Inlining 0.000 ms, >> Optimization 0.268 ms, Emission 3.590 ms, Total 4.143 ms Execution >> Time: 847.498 ms >> >> =20 > > >data/postgresql.conf: >> max_connections =3D 100 >> shared_buffers =3D 128MB I semi-misquoted; it was running on 128MB when first reported; then 512MB when I sent this message. >It looks like you are running with the stock config for shared_buffers. >With only 128MB dedicated for shared_buffers and such a big database, >you'll be thrashing the cache pretty hard. With 125GB on the server, try >upping shared_buffers to something more like 32GB. WOW! Some tables now have a barely noticeable delay; on the whole, looking MUCH better. Testing with a just created copy[1] on the NVMe, it's better; but it's slowly going back to longer delays. Looks like count(*) may have easily-missed side-effects (more below). My team members were suggesting a possible motherboard issue since the problem happened on both the NVMe M.2 SSD, and on a backup SATA-3 platter drive. Your suggestion arrived just in time as I was about to wipe out the SSD and re-import everything fresh. =20 Another thing that bothers me is that when I copied the DB, the: cp -a ./var ./var2 # same drive, same partition replicating the DB onto free space on the same SSD, it was experiencing slow reads 18-20KB/sec; the writes were quick, large blocks; so I was suspecting something with the SSD... System buffers..? How autoload row count (or count(*)) can hurt: The queries which triggered this issue are all the same (WB built-in): SELECT count(*) FROM table; # WB =3D SQL-workbench/J These are all static tables. Does PG maintain a table row count so as to avoid having to count each time? WB is setup to: * autoload table row count * autoload table data (restricted with LIMIT) Walking through these 8M & 33M row tables (3.8GB & 5.2GB respectively) must be filling the shared_buffers and thrashing even 32GB... Ah-ha! Turning off "autoload table row count" has "resolved" this issue. I can now scroll through all the tables at high speed (hold down the arrow key to walk through all the tables. Not very useful; but walking through the tables with only 128MB shared-buffers would have been the ultimate buffer thrashing... ;p Even walking through the tables to collect their row counts (whether manually or programmatically. ). That said, the 32GB shared-buffers may now be masking NVMe drive issues... Thanks!!!, Pierre Time and complexity to find the cause of an issue is inversely proportional to the simplicity of the issue...