Re: Disk wait problem... may not be hardware... - Mailing list pgsql-general

From Jim Mlodgenski
Subject Re: Disk wait problem... may not be hardware...
Date
Msg-id CAB_5SRd_z8eL-uypDTRVBNweJio_p9+M3RgkbWyO5zt4MB0Hvg@mail.gmail.com
Whole thread Raw
In response to Re: Disk wait problem... may not be hardware...  (pf@pfortin.com)
Responses [SOLVED?] Re: Disk wait problem... not hardware...
List pgsql-general


On Fri, Oct 27, 2023 at 7:46 PM <pf@pfortin.com> wrote:
Memory: 125.5 GiB of RAM
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

>explain (analyze, buffers) select count(*) from ncvhis_2016_12_03;
Finalize Aggregate  (cost=404669.65..404669.66 rows=1 width=8) (actual
time=844.158..847.309 rows=1 loops=1) Buffers: shared hit=248 read=25022
  ->  Gather  (cost=404669.43..404669.65 rows=2 width=8) (actual
time=844.133..847.301 rows=3 loops=1) Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=248 read=25022
        ->  Partial Aggregate  (cost=403669.43..403669.45 rows=1 width=8)
(actual time=838.772..838.772 rows=1 loops=3) Buffers: shared hit=248
read=25022 ->  Parallel Index Only Scan using
ncvhis_2016_12_03_voted_party_cd_idx on ncvhis_2016_12_03
(cost=0.44..372735.05 rows=12373755 width=0) (actual time=18.277..592.473
rows=9900389 loops=3) Heap Fetches: 0 Buffers: shared hit=248 read=25022
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




data/postgresql.conf:
max_connections = 100
shared_buffers = 128MB

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.


pgsql-general by date:

Previous
From: pf@pfortin.com
Date:
Subject: Re: Disk wait problem... may not be hardware...
Next
From: "Peter J. Holzer"
Date:
Subject: Re: pg_checksums?