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.
