From: | Jim Mlodgenski <jimmy76(at)gmail(dot)com> |
---|---|
To: | pf(at)pfortin(dot)com |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Disk wait problem... may not be hardware... |
Date: | 2023-10-28 22:34:50 |
Message-ID: | CAB_5SRd_z8eL-uypDTRVBNweJio_p9+M3RgkbWyO5zt4MB0Hvg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Oct 27, 2023 at 7:46 PM <pf(at)pfortin(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Peter J. Holzer | 2023-10-29 01:43:10 | Re: pg_checksums? |
Previous Message | pf | 2023-10-28 17:00:33 | Re: Disk wait problem... may not be hardware... |