Strona korzysta z plików cookies w celu realizacji usług i zgodnie z Polityką Plików Cookies.



21.02.2019

Wdrażanie projektów AI

Infrastruktura OVH
21.02.2019

Certyfikacja kluczy

HEUTHES-CAK
21.02.2019

Kopie zapasowe

Veeam Availability for AWS
21.02.2019

Dysk SSD Samsung 970 EVO Plus

Dysk SSD Samsung 970 EVO Plus
21.02.2019

Szyfrowane USB

Kingston IronKey D300 Serialized
21.02.2019

Bezpieczeństwo sieci

Check Point Maestro i seria 6000
21.02.2019

Ochrona danych

Commvault IntelliSnap i ScaleProtect
21.02.2019

Ułatwienie telekonferencji

Plantronics Calisto 3200 i 5200
21.02.2019

Transformacja centrów danych

Fujitsu PRIMEFLEX for VMware vSAN

Optymalizacja konfiguracji MySQL

Data publikacji: 21-02-2019 Autor: Mariusz Ferdyn
Rys. 1. Wynik zapytania SHOW...

W trakcie naprawiania lub poprawiania wydajności działania MySQL można zauważyć, że administratorzy często używają podczas wdrożenia podstawowego polecenia yum install mysql i zapominają o sprawie. Przy takim podejściu nie ma co się później dziwić, że wydajność działania bazy danych nie jest na najwyższym poziomie.

 

Najprostszym sposobem na zwiększenie wspomnianej wydajności wydaje się zamówienie nowych maszyn z większą ilością pamięci RAM i procesorów. Niestety ze względu na domyślne parametry pracy bazy danych MySQL wynikające z prostej instalacji po zakupie i wdrożeniu zakupionego sprzętu znacząca poprawa nie następuje.

Przyjrzeliśmy się, z jakich konfiguracji korzystają tacy dostawcy jak Amazon i Microsoft, oraz podpowiadamy, na jakie parametry warto zwrócić uwagę, optymalizując działanie nadzorowanych baz danych. Często bowiem nawet nie analizując zapytań czy zastosowania bazy, wystarczy zmienić opcje pokazane w tabeli 1, żeby zaobserwować znaczącą poprawę efektywności działania. Poniżej przedstawiamy, co oznaczają poszczególne parametry:

 

  • innodb_buffer_pool_size – pamięć dla samych danych oraz indeksu. Wartość oczekiwana to zazwyczaj od 70 do 80 procent pamięci fizycznej.
  • innodb_log_file_size – rozmiar dziennika powtórzeń. Dzienniki powtórzeń zapewniają, że operacje zapisu są szybkie, niezawodne oraz najważniejsze, że w przypadku nieoczekiwanej awarii dane nie ulegną straceniu. Jeżeli ustawiona wartość jest za mała, wówczas dane z dziennika powtórzeń muszą trafić do plików danych, co z kolei jest bardziej pracochłonne i zajmuje więcej czasu. 512 MB zazwyczaj daje odpowiednio dużo miejsca na rejestrowanie operacji zapisu.
  • max_connections – czasami aplikacje nie zamykają poprawnie połączeń. Większa wartość da serwerowi więcej czasu na zamknięcie niewykorzystanych połączeń i możliwość przyjęcia nowych połączeń. Maksymalna liczba połączeń to 10 000, ale zalecane maksimum to 5000.
  • innodb_file_per_table – to ustawienie włącza lub wyłącza funkcję silnika InnoDB odpowiedzialną za przechowywanie tabel w oddzielnych plikach. Z wydajnościowego punktu widzenia rozbicie tabel na oddzielne pliki zwiększa wydajność. Należy również włączyć tę opcję, aby zapewnić efektywne zastosowanie szeregu zaawansowanych operacji administracyjnych. Włączenie wspomnianej funkcji może przyspieszyć transmisję obszaru danych z tabel. Począwszy od wersji MySQL 5.6 domyślne ustawienie to 1 (włączone). W starszych wersjach domyślnie ustawiane było 0 (wyłączone). Wartość ta powinna zostać zmieniona przed tworzeniem tabel, ponieważ dotyczy to tylko nowo utworzonych tabel.
  • innodb_flush_log_at_trx_commit – wartością domyślną jest 1, przy zakresie wartości od 0 do 2. Domyślna wartość jest najbardziej odpowiednią opcją dla samodzielnego serwera MySQL. Ustawienie 2 zapewnia największą spójność danych i jest odpowiednie dla konfiguracji wieloserwerowych (MySQL Cluster). Ustawienie 0 pozwala na utratę danych, co może wpływać na niezawodność (w niektórych przypadkach możemy osiągnąć znacznie lepszą wydajność). Jest to odpowiednie ustawienie dla Slave w rozwiązaniach wieloserwerowych MySQL.
  • innodb_log_buffer_size – ilość pamięci RAM przeznaczona na bufor dziennika. Pozwala na uruchamianie transakcji bez konieczności zapisania pliku dziennika na dysk przed zatwierdzeniem transakcji. W przypadku gdy istnieje duży obiekt binarny lub pole tekstowe, pamięć podręczna zostanie jednak szybko zużyta i uruchomione zostaną częste operacje we/wy dysku. Dlatego warto zwiększyć rozmiar bufora, jeśli zmienna stanu Innodb_log_waits nie jest równa 0.
  • query_cache_size – najlepszą opcją jest ustawienie tej wartości na zero (domyślne ustawienie w MySQL 5.6) i wyłączenie tego mechanizmu już na początku konfiguracji. Do przyspieszania zapytań należy używać innych metod. Jest to jedno z bardziej kontrowersyjnych ustawień, ponieważ wartość różna od zera może przyspieszać realizację pewnych zapytań. Jeżeli zdecydujemy się na modyfikację, należy pamiętać o następujących zasadach. Zaczynamy od wartości 10 MB i zwiększamy ją o 10 MB. Zasadniczo nie powinniśmy przekroczyć 200 MB, choć niektóre źródła podają wartość nawet o połowę mniejszą – 100 MB. Duży rozmiar bufora zapytań prowadzi do znacznego obniżenia wydajności, co wynika z nadmiaru i blokowania pamięci podręcznej. Zapytania buforowania zawierają blokady wyłączne. Ponadto wszelkie wstawianie, aktualizowanie, usuwanie lub inne modyfikacje tabeli powodują czyszczenie wszystkich istotnych wpisów w pamięci podręcznej zapytań. Dzieje się tak nawet wtedy, gdy dostępna jest wolna pamięć w query_cache_size. W rezultacie im większa jest pamięć podręczna zapytań, tym więcej czasu systemowego wykorzystuje się do blokowania, opróżniania i narzutu. Zalecenia te mogą być inne w przypadku specyficznej bazy danych – np. tylko do odczytu.


Znajomość powyższych parametrów to podstawa zarządzania i wdrażania MySQL. Z kolei dostawcy rozwiązań chmurowych mogą nam trochę pomóc w ustawieniu pozostałych parametrów MySQL. Sprawdziliśmy, jakie parametry bazy danych stosowane są domyślnie przez największe firmy, czy i na ile odbiegają one od wartości domyślnych oraz jaki wpływ mogą mieć te zmiany na wydajność. Dzięki takiej analizie otrzymamy listę parametrów, na które trzeba zwracać uwagę podczas optymalizowania baz danych MySQL. Spis parametrów uruchomieniowych MySQL można znaleźć na stronach:
 

  • mariadb.com/kb/en/library/innodb-system-variables,
  • dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html.


Najprostszym sposobem zmiany parametrów jest edycja pliku tekstowego my.cnf, który w Linuksie zazwyczaj znajduje się w katalogu /etc lub w przypadku systemów Windows w pliku my.ini znajdującym się zwykle w ProgramDataMySQLMySQL Server 5.7. Należy pamiętać, aby po zmianie parametrów zrestartować serwer bazy danych za pomocą komendy service mysqld restart (Linux) lub net stop MySQL57 i następnie net start MySQL57 (Windows).

Warto też wspomnieć, że zawarte w artykule odniesienia do bazy MySQL są analogiczne dla MariaDB, a więc bazy danych stworzonej przez pracowników MySQL AB po przejęciu firmy przez Oracle. Celem głównym projektu MariaDB jest ciągłe udostępnianie bazy danych na licencji GPL, w przeciwieństwie do niepewnego statusu licencji MySQL, która zależy teraz wyłącznie od Oracle. Autorzy MariaDB stawiają sobie również za cel utrzymanie kompatybilności z wcześniejszymi wersjami MySQL.

> AZURE MYSQL A WIELKOŚĆ ZDEFINIOWANEJ INSTANCJI

Przygotowane zostały trzy bazy danych Azure Database for MySQL:
 

  • Basic 2 vCores – 100 GB (cena 59,88 euro),
  • General Purpose – 4 vCores, 100 GB (cena 272,83 euro),
  • Memory Optimized – 4 vCores, 100 GB (cena 370,15 euro).


Ciekawostka: wykonanie komendy SHOW VARIABLES LIKE "%version%" zwraca informacje pokazane na rys. 1. Narzędzie graficzne MySQL Workbench jak na rys. 2. Jak widać, baza danych uruchomiona jest na systemie Windows. Pełne dane konfiguracyjne każdej instancji zostały udostępnione na stronie miesięcznika „IT Professional” (adres WWW na końcu artykułu).

Porównując parametry MySQL pomiędzy instancjami, można zauważyć różnice pokazane w tabeli 2. Parametry te wpływają na wydajność i nie można zmienić ich z poziomu Panelu zarządzania Azure. Warto natomiast zwrócić uwagę na poniżej wymienione parametry podczas optymalizacji MySQL. Oznaczają one:
 

  • back_log – rozmiar kolejki dla przychodzących połączeń TCP/IP. Kiedy główny wątek otrzymuje nowe połączenia, wówczas tworzone są nowe wątki. Tworzenie nowego wątku jest pracochłonne, a wartość back_log wskazuje liczbę żądań, które będą czekać w kolejce, zanim MySQL przestanie odpowiadać na nowe żądania, odmawiając połączenia. Według dokumentacji należy zwiększać wartość tylko wtedy, gdy spodziewamy się dużej liczby połączeń w krótkim czasie.
  • host_cache_size – liczba zapamiętanych zapytań DNS. Należy zwiększać wartość, jeżeli do MySQL łączymy się z różnych hostów.
  • open_files_limit – maksymalna liczba deskryptorów plików, które są wymagane do otwierania nowych połączeń, przechowywania tabel w pamięci podręcznej i tworzenia tabel tymczasowych, używanych do rozwiązywania skomplikowanych zapytań i uzyskiwania dostępu do zapamiętanych zapytań. Jeśli MySQL nie będzie w stanie otworzyć nowych plików, może przestać działać poprawnie.
  • thread_cache_size – liczba wątków pamięci podręcznej serwera do ponownego użycia. Jeżeli limit nie zostanie osiągnięty, w momencie gdy klient rozłączy się, jego wątki zostaną umieszczone w pamięci podręcznej i ponownie wykorzystane tam, gdzie będzie to możliwe. Są one zwalniane po 5 minutach bezczynności. Parametr ten należy ustawiać, zwłaszcza na serwerach z dużą liczbą połączeń na sekundę, tak aby większość mogła używać danych zapamiętanych z wątku. Przy zwiększaniu pamięci w serwerze należy zmodyfikować przede wszystkim wartość innodb_buffer_pool_size, natomiast jeżeli drastycznie zmienia się liczba klientów korzystających z danej bazy MySQL, można przyjrzeć się innym parametrom.

[...]

 

W świecie IT od ponad 20 lat. Początkowo współpracownik periodyków „Bajtek” i „Commodore & Amiga”. Od 2000 roku specjalizuje się w technologiach Microsoftu. Pełnił funkcję Technical Learning Guide podczas największych konferencji Microsoft TechEd i Ignite w USA. 

Pełna treść artykułu jest dostępna w papierowym wydaniu pisma.

.

Transmisje online zapewnia: StreamOnline

All rights reserved © 2019 Presscom / Miesięcznik "IT Professional"