Обсуждение: Using PGTune - is it a good source for system configuration?
Hi all, I used PGTune to obtain system configuration recommendations. I'm wondering if these can be taken as "gospel" or are there any caveats or "gotchas" to be aware of? Below is my config and the recommendations - any comments, advice, URLs or anything else anybody else might find useful appreciated! # DB Version: 13 # OS Type: linux # DB Type: mixed # Total Memory (RAM): 32 GB # CPUs num: 2 # Data Storage: ssd max_connections = 100 shared_buffers = 8GB effective_cache_size = 24GB maintenance_work_mem = 2GB checkpoint_completion_target = 0.9 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 1.1 effective_io_concurrency = 200 work_mem = 41943kB min_wal_size = 1GB max_wal_size = 4GB max_worker_processes = 2 max_parallel_workers_per_gather = 1 max_parallel_workers = 2 max_parallel_maintenance_workers = 1 Should you require any further information, please don't hesitate to get back to me here on-list. TIA and rgs, Pól...
On Wed, Sep 1, 2021 at 9:20 AM Pól Ua Laoínecháin <linehanp@tcd.ie> wrote:
I used PGTune to obtain system configuration recommendations. I'm
wondering if these can be taken as "gospel"
Not according to its "about" page...
or are there any caveats
or "gotchas" to be aware of?
# DB Type: mixed
But they are probably better than the generic defaults at least. The more pertinent question is whether you are getting good enough performance for the amount of effort spent or whether more effort, and customization to your exact situation, is needed to get even better performance. Because the standard caveat is that if you are not experimenting on production-like workloads the suggestions and/or data you are seeing will not be tailored to your production environment. It's nearly impossible to reduce the gap to zero, but pgTune still leaves a pretty sizable gap (though whether that matters depends, again, on the characteristics of the production environment).
David J.
On Wed, 2021-09-01 at 17:19 +0100, Pól Ua Laoínecháin wrote: > I used PGTune to obtain system configuration recommendations. I'm > wondering if these can be taken as "gospel" or are there any caveats > or "gotchas" to be aware of? It is a serving suggestion and gives you food for thought. It does not substitute thinking for yourself and reading the documentation for those parameters. > # DB Version: 13 > # OS Type: linux > # DB Type: mixed > # Total Memory (RAM): 32 GB > # CPUs num: 2 > # Data Storage: ssd > > max_parallel_workers_per_gather = 1 > max_parallel_workers = 2 > max_parallel_maintenance_workers = 1 For example, I think these are not sane values for a mixed workload with 2 CPU cores. With that configuration, a single query could keep both cores busy. I'd set all these to 0. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Hi, and thanks for replying. > Not according to its "about" page... Yeah, sure, it's not a silver bullet, but the thrust of my question was: do the community experts consider it reliable enough as a first approximation? Your remarks about "better than the generic defaults" reassures me that this is the case. This is just my laptop - but I've taken PGTune's recommendations before for a data loading and it was a real help... One other question that I forgot to include in my original! I have a two CPU core (that's why I put 2 for the number of CPUs) but my processor supports hyperthreading (Intel Core i5, 7th Gen), so should I really put 4 as the no. Changing this from 2 to 4 changes the work_mem from 41943kB to 20971kB and the worker settings from max_worker_processes = 2 max_parallel_workers_per_gather = 1 max_parallel_workers = 2 max_parallel_maintenance_workers = 1 to max_worker_processes = 4 max_parallel_workers_per_gather = 2 max_parallel_workers = 4 max_parallel_maintenance_workers = 2 So, which would be the correct Number of CPUs setting given my processor? TIA and rgs, Pól... > David J.
Yes, I have found that most PostgreSQL workloads do benefit some (like 25% to 30%) from Intel hyperthreading. With 4 threads, I would probably start out at: max_worker_processes = 4 max_parallel_workers_per_gather = 2 max_parallel_workers = 3 max_parallel_maintenance_workers = 1 And then keep an eye on htop ... and go from there. Remember that for this generation of processor, the speculative executionpatches make context switching (changing which software thread a hyperthread is working on) quite expensive, soits bad to max out the processor, and your gains above the 2 cores will be small. But honestly I have never run PostgreSQL on a machine with fewer than 12 cores / 24 threads. --Stephen -----Original Message----- From: Pól Ua Laoínecháin <linehanp@tcd.ie> Sent: Wednesday, September 1, 2021 12:16 Cc: pgsql-novice <pgsql-novice@lists.postgresql.org> Subject: Re: Using PGTune - is it a good source for system configuration? Hi, and thanks for replying. > Not according to its "about" page... Yeah, sure, it's not a silver bullet, but the thrust of my question was: do the community experts consider it reliable enough as a first approximation? Your remarks about "better than the generic defaults" reassures me that this is the case. This is just my laptop - but I've taken PGTune's recommendations before for a data loading and it was a real help... One other question that I forgot to include in my original! I have a two CPU core (that's why I put 2 for the number of CPUs) but my processor supports hyperthreading (Intel Core i5,7th Gen), so should I really put 4 as the no. Changing this from 2 to 4 changes the work_mem from 41943kB to 20971kB and the worker settings from max_worker_processes = 2 max_parallel_workers_per_gather = 1 max_parallel_workers = 2 max_parallel_maintenance_workers = 1 to max_worker_processes = 4 max_parallel_workers_per_gather = 2 max_parallel_workers = 4 max_parallel_maintenance_workers = 2 So, which would be the correct Number of CPUs setting given my processor? TIA and rgs, Pól... > David J.