Wcześniej czy później zawsze pojawia się potrzeba zoptymalizowania naszej bazy MySQL. Przedstawię kilka zmian w konfiguracji, które powinny zwiększyć wydajność w większości przypadków.
MyISAM - key_buffer_size
Najprostszą optymalizacją baz/tabel z mechanizmem MyISAM jest odpowiednie dobranie bufora na cache dla kluczy i indeksów (dane nigdy nie są cachowane). Poniższe zapytanie pozwala oszacować zalecany rozmiar cache’u:
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;
Wynik określa zalecany rozmiar bufora (parametr key_buffer_size w pliku /etc/mysql/my.cnf) dla bieżącego stanu bazy - warto ciut dodać na zapas. Na systemach 32 bitowych parametr key_buffer_size może przyjmować maksymalnie 4GB, na 64 bitowych maksymalnie 8GB.
[mysqld]
key_buffer_size=xxxM
InnoDB - innodb_buffer_pool_size
W przypadku InnoDB cachowane mogą być zarówno dane jak i klucze/indeksy a rozmiar bufora określa parametr innodb_buffer_pool_size. Zalecaną minimalną wartość tego parametru możemy oszacować zapytaniem:
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;
W przypadku InnoDB po zmianie wartości innodb_buffer_pool_size konieczne jest również ustawienie innodb_log_file_size na 25% wartości innodb_buffer_pool_size lub 2047M (należy wybrać mniejszą z wartości). By wygenerować pliki log o nowych rozmiarach musimy postępować według poniższej instrukcji:
- Dopisujemy w pliku my.cnf parametry:
[mysqld] innodb_buffer_pool_size=xxxM innodb_log_file_size=25% xxxM lub 2047M - Wyłączamy bazę:
invoke-rc.d mysql stop - Kasujemy obecnie pliki log:
rm /var/lib/mysql/ib_logfile[01] - Uruchamiamy bazę:
invoke-rc.d mysql start - Po starcie bazy zostaną wygenerowane nowe pliki log o nowych rozmiarach.
InnoDB - kompaktowanie plików
W domyślnej konfiguracji MySQL dla baz InnoDB (na Debianie na bank, przypuszczam że na innych distro jest podobnie) wszystkie tabele, indeksy, metadane tabel i inne dane dotyczące table InnoDB przechowywane są w jednym pliku: /var/lib/mysql/ibdata1
Nie jest to optymalne ustawienie szczególnie gdy mamy dużo baz i o znacznych rozmiarach - wykonywanie wielu równoczesnych operacji na jednym gigantycznym pliku potrafi mocno przymulić.
Próba kompaktowania/optymalizowania tabel InnoDB nie powoduje zmniejszenia tego pliku - bo gdy próbujemy optymalizować tabele InnoDB powoduje to:
- ułożenie danych i indeksów wewnątrz pliku ibdata1 w sposób ciągły,
- wzrost rozmiaru pliku ibdata1 ponieważ powyższe dane dopisywane są na jego końcu.
Niewiele osób spodziewa się takiego rezultatu. Można zmniejszyć rozmiar tego pliku wyłączając dane tabel i ich indeksy do osobnych plików ale proces ten wymaga pełnego backupu bazy i jej odtworzenia, wiąże się więc z chwilowym (a w przypadku dużych baz - dłuższym) przestojem.
- Robimy pełny backup bazy, np. poleceniem:
mysqldump --all-databases --single-transaction -uroot -p > my-dump.sql
(dump’a można dodatkowo skompresować gzipem dodając pipe’a - przyspieszy to odzyskiwanie przez zmniejszenie ilości danych potrzebnych do odczytania z dysku)
Kasujemy wszystkie bazy z wyjątkiem schematu mysql (de facto powinno wystarczyć skasowanie i odzyskanie tylko baz korzystających z tabel InnoDB)
Wyłączamy usługę:
invoke-rc.d mysql stop
- Dodajemy w pliku
/etc/mysql/my.cnfparametry:
[mysqld]
innodb_file_per_table
innodb_log_file_size=25% xG
innodb_buffer_pool_size=xG
- pierwsza opcja powoduje właściwe rozdzielenie tabel InnoDB do różnych plików (dodanie tej opcji na serwerze na którym znajdują się bazy InnoDB spowoduje ich uszkodzenie - odradzam),
- drugą i trzecią linijkę konfigurujemy według wcześniejszej instrukcji o
innodb_buffer_pool_size, - możemy też na czas przywracania danych dodać opcję
bulk_insert_buffer_size=256M, skróci to czas potrzebny na przywrócenie bazy.
- Kasujemy pliki:
ibdata1,ib_logfile0andib_logfile1:
rm /var/lib/mysql/ibdata1
rm /var/lib/mysql/ib_logfile[01]
- Uruchamiamy serwer MySQL:
invoke-rc.d mysql start
- Przywracamy wszystkie bazy z dump’a:
cat my-dump.sql | mysql -uroot -p
Plik ibdata1 urośnie ale od tej pory będzie zawierać wyłącznie metadane tabel a poszczególne tabele i indeksy będą przechowywane w osobnych plikach, np. tabela.ibd. Teraz optymalizowanie tabel InnoDB będzie powodować zmniejszenie rozmiarów plików *.ibd a plik ibdata1 nie będzie tak obciążony.
Zmiana metody flush’a
W niektórych przypadkach ustawienie opcji innodb_flush_method na wartość O_DIRECT może poprawić wydajność, choć w innych wydajność może się pogorszyć (na forach sugerowano występowanie problemu na dedykowanych macierzach). Można bezpiecznie włączyć tę opcję i wykonać kilka bechmarków:
[mysqld]
innodb_flush_method=O_DIRECT
Cache wyników zapytań
Sprawdźmy najpierw czy cachowanie jest włączone (u mnie było to domyślne ustawienie), wydajemy zapytanie:
SHOW VARIABLES LIKE 'query_cache_type';
Możliwe są 3 ustawienia:
- ON (
query_cache_type = 1) - cachowanie wszystkich zapytań, - OFF (
query_cache_type = 0) - cachowanie na żądanie, - DEMAND (
query_cache_type = 2) - cachowanie wyłączone.
W przypadku opcji DEMAND cachowanie jest włączane jeżeli po SELECT’cie dodamy SQL_CACHE. Mi osobiście najbardziej odpowiada opcja z cachowaniem wszystkiego.
Następnie należy ustawić w pliku my.cnf poniższe zmienne według potrzeb:
query_cache_limit = 2M
query_cache_size = 32M
Pierwsza opcja ustala maksymalny rozmiar pojedynczego zapytanie, które będzie cachowane - zapytania większe nie będą cachowane. Druga opcja ustala rozmiar całego bufora na cache (przeważnie więcej działa lepiej).
MyISAM - unikanie repair with keycache
Gdy nasza baza urośnie i będziemy mieć w niej tabele o rozmiarze przekraczającym 2GB to da się zauważyć że pewne operacja jak np. zakładanie indeksu, optymalizacja, naprawa - trwają cholernie długo. Można to szczególnie odczuć właśnie w momencie przekraczania rozmiaru 2GB i tak utworzenie indeksu na tabeli o rozmiarze 1,9GB trwa powiedzmy kilkanaście/kilkadziesiąt minut, a ta sama operacja na bazie o rozmiarze 2,1GB może zająć nawet kilka godzin.
Przyczynę łatwo namierzyć obserwując wynik polecenia:
SHOW PROCESSLIST;
w trakcie operacji na “małej” i “dużej” tabeli. “Mała” zatrzymuje się na dłużej na operacji Repair By Sorting, a “duża” kona godzinami na Repair With Keycache. Właśnie różnica w działaniu obu mechanizmów sortowania daje w kość:
- repair by sorting - wykorzystuje do sortowania wiele plików tymczasowych i wymaga sporo wolnego miejsca w katalogu ustawionym w opcji tmpdir (domyślnie ustawionej na /tmp) - jeżeli miejsca będzie za mało to wybierany będzie mechanizm “repair with keycache”,
- repair with keycache - wykorzystuje do sortowania bardzo mały bufor (u mnie 8MB), jest ok 10~20 krotnie wolniejszy niż “repair by sorting” a do tego tworzy mniej optymalne indeksy.
O tym który z mechanizmów zostanie wybrany decyduje opcja myisam_max_sort_file_size - zmienna ta ma domyślnie wartość 2GB i właśnie dlatego problemy pojawiają się po przekroczeniu tego rozmiaru. Proponuję ustawić ją sporo powyżej rozmiaru największych tablic - oczywiście jeśli miejsce w temp’ie pozwoli na to, np:
myisam_max_sort_file_size=8GB
Przy takim ustawieniu warto mieć w /tmp minimum drugie tyle wolnego miejsca.