Settings for a new machine - some guidance sought. - Mailing list pgsql-general

Good afternoon everyone,

I have a new laptop - running version 13.4 - simple binary from EnterpriseDB - no install!

I plan to have a dual-boot machine - Windows 125 GB - have to be able to run Tableau which doesn't have a Linux
desktop...

Linux - the rest...


======== Spec =====================

 - 8 GB RAM
- 256 GB NVMe disk
 - Intel Core i5-1135G7 (11th Gen) Processor

The page here:
https://laptoping.com/cpus/product/intel-core-i5-1135g7/

tells me that this CPU has 

Number of Cores Quad-core / 2 computing threads per core

===================================

So, I input these parameters into pgtune.

and received output as follows - I'm not simply dumping the output here and expecting y'all to do all of the work! :-)

I looked up each parameter and what it does - I'm not into aimless button-twiddling!

I looked at:
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and the EnterpriseDB white papers:
Configuring and Tuning PostgreSQL and EDB Postgres Advanced Server for Window & Linux
and various other sites.

Windows

max_connections = 100 - not important - not a server - it's a single-user machine.

shared_buffers = 2GB
# Default - 128MB - recommendation is in line with Tuning Your PostgreSQL Server - 25% of RAM

effective_cache_size = 6GB
# Default - 4GB   - "aggressive" recommendation - this is fine - I want my server to be as performant as possible - I'm
notpushed if my brower is sluggish! 

maintenance_work_mem = 512MB
# Default - 64MB  - says it's OK to set it higher as only one maintenance operation can be happening at any one time
dueto locking 


checkpoint_completion_target = 0.9
# Default - 0.5   - 0.9 the "useful maximum" - note to self - read up on links on the PostgreSQL tuning page!


wal_buffers = 16MB
# Default - 4MB (512 8kB units)  = 16MB "upper limit" (repeat note to self)

default_statistics_target = 100
# Default - 100 - seems OK - "reasonable starting point"

random_page_cost = 1.1
# Default - 4 - 4 is for HDDs - 1.1 is for SSDs? I've seen this written in blogs - reduce from 4 for SSDs because of
lowerSSD penalty for random I/O 

============= Puzzle ====================

work_mem = 2621kB
# Default - 4096kb 4MB - This one puzzles me - according to the PostgreSQL tuning page, it should be biggish - so as to
avoidspill to disk. 

From the EnterpriseDB tuning document, I get a "good starting point" is:
((Total RAM - shared_buffers)/ (16 x CPU cores)) which is (according to my calculations): 50MB - so why 2MB?

==========================================


min_wal_size = 1GB
# Default - 80MB - I've changed this setting before, and I think that larger settings are better? I'd go for 4GB
min/8GBmax?

Maybe on a server where you want your recovery time to be v. small and your recovery point to be seconds ago, you might
wantto keep these smaller? 

max_wal_size = 4GB                     # Default - 1GB

I'd go with 8? I'm still shaky on what exactly the balance is for these two?



max_worker_processes = 8
# Default - 8 - I have 8 cores - but there's considerable overhead with parallel queries? But, single-user machine -
doesthis matter? 

max_parallel_workers_per_gather = 4
# Default - 2 - half of max_worker_processes appears to be the consensus?
So, per query, a maximum of 4 workers will be assigned?


max_parallel_workers = 8
# Default - 8 - So, I can't under any circumstances have any more than 8 parallel workers

max_parallel_maintenance_workers = 4
# Default - 2 - seems reasonable - only applies to VACUUM and CREATE INDEX (CONCURRENTLY)

PGTune for Linux gave one extra setting - (it's 0 - unsettable - on Windows)

From here:
https://postgresqlco.nf/doc/en/param/effective_io_concurrency/

"SSDs and other memory-based storage can often process many concurrent requests, so the best value might be in the
hundreds."

So, 200 seems reasonable.


I would be grateful if anyone could comment on the suitability or otherwise of any/some/all of these settings.

Good references/URLs/.pdfs/texts much appreciated.

Please let me know if you require any more information.

SQLP!


Sent with ProtonMail Secure Email.