From: | SQL Padawan <sql_padawan(at)protonmail(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Settings for a new machine - some guidance sought. |
Date: | 2021-10-30 16:31:02 |
Message-ID: | OF3xZR69PF8951JpFXQBoMdEJN9DGR7b0ih0_rtoKwbFBcVZJRzMuHVMdx56fbqJD2FGo8Ley-08LdlRRDwT4j9iEtUtKF9FjpkP0cerATk=@protonmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 not pushed 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 due to 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 lower SSD 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 avoid spill 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 want to 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 - does this 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Peter J. Holzer | 2021-10-31 20:29:33 | Re: Question: Is it possible to get the new xlog position after query execution? |
Previous Message | Ron | 2021-10-30 15:04:46 | Re: Why does the OID jump by 3 when creating tables? |