Re: Inquiry Regarding Automatic Tuning Features in PostgreSQL

Поиск
Список
Период
Сортировка
От Ron Johnson
Тема Re: Inquiry Regarding Automatic Tuning Features in PostgreSQL
Дата
Msg-id CANzqJaAxYfn5HqhtHMr8MithbDCKUHCbZXL-Zzf4m07pE9n7VQ@mail.gmail.com
обсуждение исходный текст
Ответ на Inquiry Regarding Automatic Tuning Features in PostgreSQL  ("Md. Ezhar Ansari" <ezhar.ansari@alumnux.com>)
Ответы Re: Inquiry Regarding Automatic Tuning Features in PostgreSQL  ("Md. Ezhar Ansari" <ezhar.ansari@alumnux.com>)
Список pgsql-admin
Дерево обсуждения
On Sat, Dec 9, 2023 at 3:17 AM Md. Ezhar Ansari <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

В списке pgsql-admin по дате отправления:

Предыдущее
От: "Md. Ezhar Ansari"
Дата:
Сообщение: Inquiry Regarding Automatic Tuning Features in PostgreSQL
Следующее
От: Scott Ribe
Дата:
Сообщение: Re: Postgres storage migration