From: Md. Ezhar Ansari <ezhar.ansari@alumnux.com>
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Inquiry Regarding Automatic Tuning Features in PostgreSQL
Date: Mon, 11 Dec 2023 04:32:50 +0000
Message-ID: <BM1PR01MB3714EFEA2BA8BCDCAC2BE6A48F8FA@BM1PR01MB3714.INDPRD01.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <CANzqJaAxYfn5HqhtHMr8MithbDCKUHCbZXL-Zzf4m07pE9n7VQ@mail.gmail.com>
References: <BM1PR01MB3714E41BCDD0DEB8B0127CF68F89A@BM1PR01MB3714.INDPRD01.PROD.OUTLOOK.COM>
<CANzqJaAxYfn5HqhtHMr8MithbDCKUHCbZXL-Zzf4m07pE9n7VQ@mail.gmail.com>
Dear Ron Johnson,
Thanks for your quick response and recommendations! I'll adjust auto-analyze thresholds and buffer values based on your insights. Your specific settings are particularly helpful.
It seems there aren't specific automatic tuning features or extensions in PostgreSQL.
If you have any additional tips, I'd appreciate your guidance.
Best,
Ezhar
Get Outlook for Android<https://aka.ms/AAb9ysg;
________________________________
From: Ron Johnson <ronljohnsonjr@gmail.com>
Sent: Saturday, December 9, 2023 9:27:33 PM
To: Md. Ezhar Ansari <ezhar.ansari@alumnux.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Inquiry Regarding Automatic Tuning Features in PostgreSQL
You don't often get email from ronljohnsonjr@gmail.com. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification;
On Sat, Dec 9, 2023 at 3:17 AM Md. Ezhar Ansari <ezhar.ansari@alumnux.com<mailto:ezhar.ansari@alumnux.com>> wrote:
Dear Pgsql-admin,
I hope this email finds you well. My name is Md Ezhar Ansari, and I am currently exploring options to optimize the performance of our PostgreSQL database. In my research, I came across the automatic tuning features available in SQL Server, and I was wondering if PostgreSQL has a similar capability or if there are any recommended extensions for automatic tuning.
I am particularly interested in functionalities that can automatically analyze and adjust configuration parameters, query plans, or other aspects to enhance the overall performance of our PostgreSQL database. If there are built-in features or third-party extensions that offer such capabilities, I would appreciate any information or guidance you could provide.
Additionally, if there are any best practices or recommended approaches for performance tuning in PostgreSQL, I would be grateful for your insights.
PG has auto-analyze, but its default thresholds are pretty archaic, seeing as how PG might still run on small hardware.
PG does not cache query plans, so there's no need to drop out-of-date query plans.
Adjusting the auto-analyze thresholds, and buffer values, will get you where you need.
Here's what I set them at:
shared_buffers = $SHB # I set this at 25% of RAM
work_mem = 300MB
maintenance_work_mem = $MWM # I set this at 10% of RAM
effective_cache_size = $ECS # Should be most of RAM on a dedicated DB server (leave room for the OS!)
autovacuum = on
autovacuum_vacuum_threshold = 250
autovacuum_vacuum_cost_delay = 4ms
autovacuum_vacuum_scale_factor = 0.03 # default 10% is too low for big tables
autovacuum_max_workers = 6
autovacuum_analyze_threshold = 250
autovacuum_analyze_scale_factor = 0.03
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ezhar.ansari@alumnux.com, ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Inquiry Regarding Automatic Tuning Features in PostgreSQL
In-Reply-To: <BM1PR01MB3714EFEA2BA8BCDCAC2BE6A48F8FA@BM1PR01MB3714.INDPRD01.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox