MySQL — optymalizacja i konfiguracja my.cnf
Opublikowano: 9 kwietnia 2026 · Kategoria: Baza danych, VPS
Domyślna konfiguracja MySQL jest bardzo zachowawcza — zaprojektowana żeby działała na słabym sprzęcie, nie żeby była wydajna. Na VPS ze świeżo zainstalowanym MySQL często innodb_buffer_pool_size wynosi zaledwie 128 MB. Przy dobrze dobranej konfiguracji możesz uzyskać 5–10× szybsze zapytania bez zmiany ani linii kodu aplikacji.
Lokalizacja i struktura my.cnf
# Typowe lokalizacje my.cnf / mysqld.cnf # /etc/mysql/my.cnf (główny plik) # /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu/Debian — tu edytuj) # /etc/my.cnf (CentOS/RHEL) # Sprawdź gdzie MySQL szuka plików konfiguracyjnych mysqld --help --verbose 2>/dev/null | grep "Default options" # Sprawdź aktywną konfigurację mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
InnoDB Buffer Pool — najważniejszy parametr
InnoDB Buffer Pool to pamięć podręczna danych i indeksów. Jeśli dane mieszczą się w buffer pool, MySQL serwuje je z RAM — bez odczytu dysku. Cel: buffer pool hit ratio powyżej 99%.
# Sprawdź aktualny hit ratio
mysql -u root -p -e "
SELECT
FORMAT(
(1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100,
2
) AS 'Buffer Pool Hit Ratio %'
FROM information_schema.INNODB_METRICS
WHERE name IN ('buffer_pool_reads', 'buffer_pool_read_requests')
LIMIT 1;
"
# Lub krócej
mysql -u root -p -e "SHOW STATUS LIKE 'Innodb_buffer_pool_read%';" Gotowa konfiguracja — VPS 2 GB RAM
# /etc/mysql/mysql.conf.d/mysqld.cnf — VPS 2 GB RAM [mysqld] # === PODSTAWOWE === user = mysql bind-address = 127.0.0.1 # Tylko lokalne połączenia (bezpieczeństwo) port = 3306 # === INNODB BUFFER POOL (kluczowy parametr) === # 60% z 2 GB = ~1200 MB innodb_buffer_pool_size = 1G innodb_buffer_pool_instances = 1 # 1 na każde GB buffer pool # === INNODB OGÓLNE === innodb_log_file_size = 256M # Większy = rzadszy flush = szybszy zapis innodb_flush_log_at_trx_commit = 2 # 1=bezpieczny, 2=szybszy (ryzyko utraty 1s danych) innodb_flush_method = O_DIRECT # Pomija cache OS (lepsze dla dużego buffer pool) # === POŁĄCZENIA === max_connections = 100 # PHP-FPM z 20 workerami: max_connections = workers * 4 thread_cache_size = 16 # Cache wątków — zmniejsza overhead tworzenia połączeń wait_timeout = 600 # Rozłącz nieaktywne połączenia po 10 min interactive_timeout = 600 # === TABELE TYMCZASOWE === # Jeśli tymczasowa tabela przekroczy ten limit, MySQL zapisze ją na dysk tmp_table_size = 64M max_heap_table_size = 64M # === QUERY CACHE (MYSQL 5.7 TYLKO) === # W MySQL 8.0 query_cache_type i query_cache_size zostały usunięte # query_cache_type = 0 # query_cache_size = 0 # === SORT I JOIN BUFFERS === sort_buffer_size = 2M # Per połączenie (nie zmniejszaj za bardzo) join_buffer_size = 2M read_rnd_buffer_size = 4M # === BINLOG (replikacja / point-in-time recovery) === # Odkomentuj jeśli używasz replikacji # log_bin = /var/log/mysql/mysql-bin.log # binlog_expire_logs_seconds = 604800 # 7 dni # === SLOW QUERY LOG === slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 # Loguj zapytania trwające > 1 sekundy log_queries_not_using_indexes = 0 # Ustaw na 1 żeby znaleźć brakujące indeksy
Gotowa konfiguracja — VPS 4 GB RAM
# /etc/mysql/mysql.conf.d/mysqld.cnf — VPS 4 GB RAM [mysqld] bind-address = 127.0.0.1 # 65% z 4 GB = ~2.6 GB innodb_buffer_pool_size = 2G innodb_buffer_pool_instances = 2 # 1 na każde GB buffer pool innodb_log_file_size = 512M innodb_flush_log_at_trx_commit = 1 # Pełne bezpieczeństwo (produkcja) innodb_flush_method = O_DIRECT innodb_io_capacity = 400 # IOPS dysku (SSD: 400-2000) innodb_io_capacity_max = 2000 max_connections = 200 thread_cache_size = 32 wait_timeout = 600 interactive_timeout = 600 tmp_table_size = 128M max_heap_table_size = 128M sort_buffer_size = 4M join_buffer_size = 4M read_rnd_buffer_size = 8M slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 0.5
Porównanie parametrów dla różnych rozmiarów RAM
| Parametr | VPS 1 GB | VPS 2 GB | VPS 4 GB | VPS 8 GB |
|---|---|---|---|---|
innodb_buffer_pool_size | 512 MB | 1 GB | 2 GB | 5 GB |
innodb_buffer_pool_instances | 1 | 1 | 2 | 4 |
max_connections | 50 | 100 | 200 | 400 |
tmp_table_size | 32 MB | 64 MB | 128 MB | 256 MB |
innodb_log_file_size | 128 MB | 256 MB | 512 MB | 1 GB |
Analiza slow query log
# Podsumowanie slow query log (wbudowane narzędzie) mysqldumpslow -t 10 /var/log/mysql/slow.log # -t 10 = top 10 najwolniejszych zapytań # Lepsze narzędzie: pt-query-digest (Percona Toolkit) sudo apt install percona-toolkit pt-query-digest /var/log/mysql/slow.log | head -100 # Sprawdź plan wykonania konkretnego zapytania EXPLAIN SELECT * FROM wp_posts WHERE post_status = 'publish' ORDER BY post_date DESC LIMIT 10; # Sprawdź kolumnę "type" — "ALL" = pełny skan tabeli (problem!), "index" lub "ref" = OK
MySQLTuner — automatyczna analiza
# Pobierz i uruchom MySQLTuner (po min. 24h działania serwera) wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl perl mysqltuner.pl --user root --pass TwojeHaslo --forcemem 2048 # MySQLTuner wskaże m.in.: # [!!] InnoDB buffer pool / data size: 128.0M / 2.5G (za mały!) # [OK] Buffer pool hit rate: 99.98% # [!!] Temporary tables created on disk: 35% (za dużo — zwiększ tmp_table_size) # [!!] Thread cache hit rate: 85% (zwiększ thread_cache_size)