MySQL – Proste metody optymalizacji

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:

  1. Dopisujemy w pliku my.cnf parametry:
    [mysqld]
    innodb_buffer_pool_size=xxxM
    innodb_log_file_size=25% xxxM lub 2047M
  2. Wyłączamy bazę:
    invoke-rc.d mysql stop
  3. Kasujemy obecnie pliki log:
    rm /var/lib/mysql/ib_logfile[01]
  4. Uruchamiamy bazę:
    invoke-rc.d mysql start
  5. 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.

  1. 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)

  2. Kasujemy wszystkie bazy z wyjątkiem schematu mysql (de facto powinno wystarczyć skasowanie i odzyskanie tylko baz korzystających z tabel InnoDB)
  3. Wyłączamy usługę:
    invoke-rc.d mysql stop
  4. Dodajemy w pliku /etc/mysql/my.cnf parametry:
    [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.

  5. Kasujemy pliki: ibdata1, ib_logfile0 and ib_logfile1:
    rm /var/lib/mysql/ibdata1
    rm /var/lib/mysql/ib_logfile[01]
  6. Uruchamiamy serwer MySQL:
    invoke-rc.d mysql start
  7. 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.

Źródło

http://dba.stackexchange.com/questions/3163/mysql-5-1-innodb-configuration-24gb-ram-bi-xeon-high-load

3 thoughts on “MySQL – Proste metody optymalizacji”

    1. Można zmienić tą opcję bez dump’a/restore’a ale to nie zmniejszy rozmiaru pliku ibdata1, na którym operacje nadal będą się “zrzynać”. Tak jak to opisałeś przy bazach, które ciężko wyłączyć może to być pomocne rozwiązanie.

Leave a Reply

Your email address will not be published. Required fields are marked *