Optymalizacja baz danych

1. PostgresSQL

Optymalizacja konfiguracji PostgreSQL dla środowisk produkcyjnych

Opis zawiera zalecane ustawienia konfiguracyjne PostgreSQL, które poprawiają wydajność i stabilność w środowiskach produkcyjnych oraz testowych. Konfiguracja zakłada instalację na systemach Linux, w środowiskach lokalnych (on-premise), z nowoczesnym sprzętem: dyski SSD/NVMe, 32 CPU, 128 GB RAM lub więcej.

Zarządzanie połączeniami

Parametr max_connections określa maksymalną liczbę jednoczesnych połączeń do bazy danych.
Liczba maksymalnych połączeń do bazy wyrażona jest wzorem: max_connections = max(4 * number of CPU cores, 100).

W środowiskach on-prem wartość 100 zwykle wystarcza. W przypadku wielu replik ezdrp-api i braku connection pool (np. pgbouncer), wartość można zwiększyć do 200.

max_connections = 100

Bufory i pamięć podręczna

Parametr shared_buffers określa ilość pamięci RAM przeznaczonej na buforowanie danych przez PostgreSQL.
Następująca zależność -> 25% dostępnej pamięci RAM (dla 32GB RAM systemu operacyjnego -> shared_buffers = 8GB).

shared_buffers = 8GB

Parametr effective_cache_size określa przewidywaną ilość pamięci RAM, którą system operacyjny może wykorzystać do buforowania danych.

effective_cache_size = 32GB

Zależność -> RAM – shared_buffers = effective_cache_size (dla 32GB RAM – 8GB shared_buffers = 24GB).
Parametr maintenance_work_mem określa ilość pamięci dostępnej dla operacji konserwacyjnych, takich jak VACUUM, CREATE INDEX czy ALTER TABLE.

maintenance_work_mem = 2GB

Wartość 2GB jest odpowiednia.

WAL (Write-Ahead Logging)

Parametr wal_buffers określa ilość pamięci przydzielonej na bufory WAL (Write-Ahead Logging).
Wielkości plików WAL dla systemów testowych wystarczy wartość natywna, natomiast dla systemów produkcyjnych poniższe wartości są poprawne.

wal_buffers = -1

Wartość „-1” jest wystarczająca dla większości nieobciążonych systemów. Stanowi ona zależność 1/32 wartości shared_buffers i jest automatycznie obliczana.
Dla systemów z dużą ilością jednoczesnych zapytań, optymalnym jest 16MB.

Parametry te definiują minimalny i maksymalny rozmiar plików WAL.

min_wal_size = 1GB

max_wal_size = 4GB

W środowiskach produkcyjnych powyższe wartości są optymalne.

Koszty i równoległość I/O

Parametr random_page_cost określa koszt odczytu losowej strony z dysku, wykorzystywany przez optymalizator zapytań.

random_page_cost = 1.1

Dla dysków HDD wymagana wartość 4, dla dysków SSD oraz NVMe 1.1.

Parametr effective_io_concurrency określa liczbę jednoczesnych operacji I/O, jakie PostgreSQL może wykonywać.

effective_io_concurrency = 200

Dla dysków HDD = 2 dla SSD = 200, zaś NVMe +200.
Zmienna przyjmuje wartość od 0-1000.

Przetwarzanie równoległe

Parametr max_worker_processes określa maksymalną liczbę procesów roboczych.

max_worker_processes = 16

Nie więcej niż liczba corów CPU.

Parametr max_parallel_workers_per_gather definiuje maksymalną liczbę procesów równoległych używanych podczas wykonywania pojedynczego zapytania.

max_parallel_workers_per_gather = 4

Limit liczby równoległych procesów, które mogą zostać użyte podczas jednego zapytania.

Parametr max_parallel_workers określa maksymalną liczbę procesów równoległych używanych przez system PostgreSQL.

max_parallel_workers = 16

Nie więcej niż max_worker_processes.

Parametr max_parallel_maintenance_workers określa maksymalną liczbę procesów równoległych wykorzystywanych do operacji konserwacyjnych (np. VACUUM).

max_parallel_maintenance_workers = 4

Limit ilości równoległych procesów, które mogą zostać użyte do prac konserwacyjnych.

Poniżej przykładowe wartości postgresql.conf dla serwera 32CPU 128GB RAM, dysk SSD/NVMe, Postgresql w wersji 16 zainstalowany w środowisku Linux:

max_connections = 200
shared_buffers = 32GB
effective_cache_size = 96GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 20971kB
huge_pages = try
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 32
max_parallel_workers_per_gather = 4
max_parallel_workers = 32
max_parallel_maintenance_workers = 4

2. Rabbit

Problem przejścia Rabbita w tryb read-only.

RabbitMQ monitoruje ilość wolnego miejsca na dysku, aby zapobiec awarii spowodowanej jego zapełnieniem. Gdy miejsca jest zbyt mało, blokuje przychodzące wiadomości, aby nie dopuścić do dalszego zapisu danych.

Możliwe rozwiązania:

• zwiększenie ilości miejsca na dysku,
• zmiana parametru opisana poniżej.

Dokumentacja Rabbit

Należy w pliku /etc/rabbitmq/rabbitmq.conf zmienić parametr z disk_free_limit.relative na disk_free_limit.absolute, można użyć jednostek pamięci (KB, MB GB itp.) w następujący sposób:

disk_free_limit.absolute = 5 GB

systemctl restart rabbitmq-server