Jak dostroić i zoptymalizować wydajność MySQL 5.7 na Ubuntu 18.04 VPS lub serwerze dedykowanym

Wprowadzenie

Zanim zaczniemy, musisz zrozumieć czynniki wpływające na wydajność MySQL, abyś mógł nauczyć się dostroić i zoptymalizować serwer MySQL we właściwy sposób i uzyskać właściwe wyniki. Główne czynniki to:

  • Ilość pobieranych danych
  • Dostępne zasoby, tj. Procesor, pamięć (RAM)
  • Ładowanie uruchamiane przez serwer MySQL>

Przyczyny niskiej wydajności serwera MySQL obejmują:

  • Zły projekt bazy danych
  • Wąskie gardła sprzętowe
  • Złe kodowanie
  • Złe indeksowanie
  • Niewystarczająca konfiguracja

Uwaga specjalna: jak widać, kilka z tych czynników można rozwiązać poprzez uaktualnienie do lepszej usługi hostingowej. HostAdvice oferuje najlepszych dostawców hostingu MySQL na podstawie opinii ekspertów i użytkowników.

Instalowanie mysqltuner i tuning-primer

Zainstaluj mysqltuner

Mysqltuner to wysokowydajny skrypt Perl do strojenia MySQL, który przedstawia migawkę stanu zdrowia serwera MySQL i podaje konkretne zalecenia dotyczące poprawy, zwiększenia wydajności, stabilności i wydajności.

Uruchom poniższe polecenia, aby zainstalować mysqltuner

$ cd / tmp
$ sudo wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl – cichy – certyfikat-no-check –output-document = / usr / local / bin / mysqltuner
$ sudo chmod u + x / usr / local / bin / mysqltuner

Zainstaluj tuning-primer

Tuning Primer to skrypt powłoki, który uzyskuje informacje z wewnętrznych elementów serwera MySQL i zawiera zalecenia dotyczące modyfikacji zmiennych serwera.

Uruchom poniższe polecenia, aby zainstalować element tuningujący:

$ sudo apt install bc -y
$ sudo wget http://www.day32.com/MySQL/tuning-primer.sh –output-document = / usr / local / bin / tuning-primer
$ sudo chmod u + x / usr / local / bin / tuning-primer

Dostrajanie i optymalizacja serwera mysql

Strojenie serwera MySQL odbywa się w pliku konfiguracyjnym MySQL.

/etc/mysql/mysql.conf.d/mysqld.cnf

Uruchom mysqltuner.

$ sudo mysqltuner
$ sudo mysqltuner
>> MySQLTuner 1.7.9 – Major Hayden
>> Raporty o błędach, prośby o funkcje i pliki do pobrania na stronie http://mysqltuner.com/
>> Uruchom z „–help”, aby uzyskać dodatkowe opcje i filtrowanie wyników

[-] Pominięto sprawdzanie wersji skryptu MySQLTuner
[OK] Zalogowany przy użyciu poświadczeń z konta obsługi Debiana.
[OK] Aktualnie działająca obsługiwana wersja MySQL 5.7.22-0ubuntu18.04.1
[OK] Działa w architekturze 64-bitowej

——– Zalecenia dotyczące pliku dziennika ——————————————————————
[-] Plik dziennika: /var/log/mysql/error.log(18K)
[OK] Plik dziennika /var/log/mysql/error.log istnieje
[OK] Plik dziennika /var/log/mysql/error.log jest czytelny.
[OK] Plik dziennika /var/log/mysql/error.log nie jest pusty
[OK] Plik dziennika /var/log/mysql/error.log jest mniejszy niż 32 Mb
[!!] /var/log/mysql/error.log zawiera 14 ostrzeżeń.
[!!] /var/log/mysql/error.log zawiera 3 błędy.
[-] Wykryto 3 start (y) w /var/log/mysql/error.log
[-] 1) 2018-06-06T17: 55: 47.928847Z 0 [Uwaga] / usr / sbin / mysqld: gotowy do połączeń.
[-] 2) 2018-06-06T17: 55: 44.798684Z 0 [Uwaga] mysqld: gotowy do połączeń.
[-] 3) 2018-06-06T17: 55: 41.931105Z 0 [Uwaga] mysqld: gotowy do połączeń.
[-] Wykryto 2 zamknięcia w /var/log/mysql/error.log
[-] 1) 2018-06-06T17: 55: 46.410548Z 0 [Uwaga] mysqld: Zakończono zamykanie
[-] 2) 2018-06-06T17: 55: 43.758366Z 0 [Uwaga] mysqld: Zakończono zamykanie

——– Statystyka silnika pamięci masowej —————————————————————–
[-] Status: + ARCHIWUM + BLACKHOLE + CSV -FEDERATED + InnoDB + MEMORY + MRG_MYISAM + MyISAM + PERFORMANCE_SCHEMA
[-] Dane w tabelach InnoDB: 16 KB (tabele: 1)
[OK] Suma podzielonych tabel: 0

——– Zalecenia dotyczące bezpieczeństwa ——————————————————————
[OK] Nie ma anonimowych kont dla użytkowników bazy danych
[OK] Wszyscy użytkownicy bazy danych mają przypisane hasła
[-] Błąd # 80860 MySQL 5.7: Unikaj testowania hasła, gdy aktywowane jest hasło_weryfikacji

——– Zalecenia bezpieczeństwa CVE ————————————————————–
[-] Pominięto z powodu niezdefiniowanej opcji –cvefile

——– Wskaźniki wydajności ———————————————————————–
[-] Up: 9m 8s (110 q [0,201 qps], 38 conn, TX: 187 K, RX: 10 K)
[-] Odczyt / zapis: 98% / 2%
[-] Rejestrowanie binarne jest wyłączone
[-] Pamięć fizyczna: 3,6G
[-] Maksymalna pamięć MySQL: 352,4 mln
[-] Inna pamięć procesowa: 175,4 mln
[-] Całkowita liczba buforów: 192,0 mln globalnych + 1,1 mln na wątek (151 wątków maks.)
[-] P_S Maksymalne użycie pamięci: 72B
[-] Galera GCache Maksymalne użycie pamięci: 0B
[OK] Maksymalne osiągnięte użycie pamięci: 194,1 mln (5,26% zainstalowanej pamięci RAM)
[OK] Maksymalne możliwe użycie pamięci: 352,4 mln (9,54% zainstalowanej pamięci RAM)
[OK] Ogólne możliwe użycie pamięci w innym procesie jest zgodne z dostępną pamięcią
[OK] Wolne zapytania: 0% (0/110)
[OK] Najwyższe wykorzystanie dostępnych połączeń: 1% (2/151)
[OK] Przerwane połączenia: 0,00% (0/38)
[!!] rozpoznawanie nazw jest aktywne: odwrotne rozpoznawanie nazw jest tworzone dla każdego nowego połączenia i może zmniejszyć wydajność
[!!] Pamięć podręczna zapytań może być domyślnie wyłączona z powodu rywalizacji o muteks.
[!!] Wydajność bufora zapytań: 0,0% (0 z pamięci podręcznej / 49 wyborów)
[OK] Zapytanie ściąga pamięć podręczną dziennie: 0
[OK] Sortowanie wymagające tabel tymczasowych: 0% (0 sortowania temp / 2 sortowania)
[OK] Brak złączeń bez indeksów
[OK] Tabele tymczasowe utworzone na dysku: 4% (36 na dysku / ogółem 846)
[OK] Współczynnik trafień w pamięci podręcznej wątków: 94% (2 utworzone / 38 połączeń)
[OK] Współczynnik trafień w pamięci podręcznej tabeli: 95% (591 otwarty / 620 otwarty)
[OK] Wykorzystany limit otwartych plików: 1% (50 / 5K)
[OK] Blokady tabeli nabyte natychmiast: 100% (158 natychmiast / 158 blokad)

——– Schemat wydajności ————————————————————————
[-] Pamięć używana przez P_S: 72B
[-] Schemat Sys jest zainstalowany.

——– ThreadPool Metrics ————————————————————————
[-] Statystyki ThreadPool są wyłączone.

——– Metryki MyISAM —————————————————————————-
[!!] Użyty bufor kluczy: 18,3% (używane 3 MB / pamięć podręczna 16 MB)
[OK] Rozmiar bufora klucza / całkowite indeksy MyISAM: 16,0 M / 43,0 K.
[!!] Odczyt Współczynnik trafień bufora klucza: 94,1% (118 z pamięci podręcznej / 7 odczytów)

——– Metryki InnoDB —————————————————————————-
[-] InnoDB jest włączony.
[-] Współbieżność wątków InnoDB: 0
[OK] Plik InnoDB na tabelę jest aktywowany
[OK] Pula buforów InnoDB / rozmiar danych: 128,0 M / 16,0 K.
[!!] Stosunek Rozmiar pliku dziennika InnoDB / Rozmiar puli buforów InnoDB (75%): 48,0 M * 2 / 128,0 M powinien być równy 25%
[OK] Instancje puli buforów InnoDB: 1
[-] Liczba fragmentów puli buforów InnoDB: 1 na 1 instancję (y) puli buforów
[OK] Innodb_buffer_pool_size wyrównany z Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!] Wydajność bufora odczytu InnoDB: 88,39% (1933 odsłon / 2187 ogółem)
[!!] Efektywność zapisu dziennika InnoDB: 0% (5 trafień / 0 ogółem)
[OK] Dziennik InnoDB czeka: 0,00% (0 oczekiwań / 5 zapisów)

——– AriaDB Metrics —————————————————————————-
[-] AriaDB jest wyłączone.

——– TokuDB Metrics —————————————————————————-
[-] TokuDB jest wyłączone.

——– Metryki XtraDB —————————————————————————-
[-] XtraDB jest wyłączony.

——– RocksDB Metrics —————————————————————————
[-] RocksDB jest wyłączony.

——– Spider Metrics —————————————————————————-
[-] Pająk jest wyłączony.

——– Połącz metryki —————————————————————————
[-] Połącz jest wyłączony.

——– Galera Metrics —————————————————————————-
[-] Galera jest wyłączona.

——– Metryki replikacji ———————————————————————–
[-] Synchroniczna replikacja Galera: NIE
[-] Brak urządzeń podrzędnych replikacji dla tego serwera.
[-] Format binlog: ROW
[-] Obsługa XA włączona: WŁ
[-] Półsynchroniczna replikacja Master: Nie aktywowano
[-] Półsynchroniczna replikacja Slave: Nie aktywowano
[-] To jest samodzielny serwer

——– Rekomendacje —————————————————————————
Ogólne zalecenia:
Kontroluj linie ostrzegawcze w pliku /var/log/mysql/error.log
Kontroluj linie błędów w pliku /var/log/mysql/error.log
MySQL został uruchomiony w ciągu ostatnich 24 godzin – zalecenia mogą być niedokładne
Skonfiguruj konta tylko za pomocą adresu IP lub podsieci, a następnie zaktualizuj konfigurację za pomocą pominięcia nazwy-rozwiązania = 1
Przed zmianą rozmiaru pliku dziennika_wpisu i / lub pliku dziennika_wpisu przeczytaj: http://bit.ly/2wgkDvS
Zmienne do dostosowania:
query_cache_size (= 0)
query_cache_type (= 0)
query_cache_limit (> 1 mln lub użyj mniejszych zestawów wyników)
innodb_log_file_sile powinien być (= 16M), jeśli to możliwe, więc całkowity rozmiar plików dziennika InnoDB jest równy 25% wielkości puli buforów.

Skoncentruj się na ostatniej części wyniku w sekcji „Zalecenia” i sprawdź zmienne, które należy zwiększyć.

Utwórz kopię zapasową pliku konfiguracyjnego mysql i edytuj rzeczywisty plik konfiguracyjny.

$ sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Dostosuj zmienną pliku zgodnie z zaleceniami. Możesz skomentować domyślne wartości zmiennych i zwiększyć wartości zmiennych do dwukrotności wartości domyślnej. Właściwie znalezienie najlepszych wartości dla twojego serwera może zająć kilka dni.

Po wprowadzeniu zmian zrestartuj serwer mysql.

$ sudo systemctl zrestartuj mysql

Uwaga specjalna: jeśli nie uruchomi się ponownie, wróć i skomentuj nowe wartości i zacznij dostosowywać jeden po drugim i zobacz, które dostosowanie zmiennych powoduje awarię serwera

Jeśli serwer pomyślnie się przeładuje, uruchom ponownie mysqltuner i sprawdź, czy są jeszcze jakieś zalecenia dotyczące dostosowania wartości zmiennych.

Wykonaj podobny proces dostrajania podkładu.

Uruchom tuning primera

$ sudo tuning-primer
$ sudo tuning-primer

— MYSQL PERFORMANCE TUNING PRIMER —
– Przez: Matthew Montgomery –

MySQL wersja 5.7.22-0ubuntu18.04.1 x86_64

Czas pracy = 0 dni 0 godz. 1 min 49 sek
Śr. qps = 0
Suma pytań = 15
Wątki połączone = 1

Ostrzeżenie: serwer nie działał przez co najmniej 48 godzin.
Korzystanie z tych zaleceń może nie być bezpieczne

Aby dowiedzieć się więcej informacji na temat każdego z nich
wizyta wydajność zmiennych zmiennych środowiska wykonawczego:
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
Odwiedź stronę http://www.mysql.com/products/enterprise/advisors.html
aby uzyskać informacje na temat usługi monitorowania i doradztwa korporacyjnego MySQL

WOLNE ZADANIA
Dziennik powolnych zapytań NIE jest włączony.
Bieżący długi_kwerenda = 10.000000 sek.
Masz 0 z 36, które trwają dłużej niż 10.000000 sek. ukończyć
Wygląda na to, że twój długi_kwot_w porządku

DZIENNIK AKTUALIZACJI BINARNEJ
Dziennik aktualizacji binarnych NIE jest włączony.
Odzyskanie punktu w czasie nie będzie możliwe
Zobacz http://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html

NICI ROBOCZE
Bieżący rozmiar wątku_cache = 8
Bieżące wątki_buforowane = 0
Bieżące wątki_per_sek = 0
Historyczne wątki_per_sek = 0
Twój rozmiar wątku_cache jest w porządku

MAKSYMALNE POŁĄCZENIA
Obecne maks. Połączenia = 151
Bieżące wątki = 1
Historyczne max_used_connections = 1
Liczba używanych połączeń wynosi 0% skonfigurowanego maksimum.
Używasz mniej niż 10% skonfigurowanych max_connections.
Obniżenie max_connections może pomóc uniknąć nadmiernej alokacji pamięci
Widzieć "ZUŻYCIE PAMIĘCI" sekcja, aby upewnić się, że nie przesadzasz

Nie włączono obsługi InnoDB!

ZUŻYCIE PAMIĘCI
Maks. Kiedykolwiek przydzielona pamięć: 177 mln
Skonfigurowane bufory maksymalnej liczby wątków: 160 mln
Skonfigurowane maks. Globalne bufory: 176 mln
Skonfigurowany maksymalny limit pamięci: 336 M.
Pamięć fizyczna: 3,60 G.
Wydaje się, że maksymalny limit pamięci mieści się w dopuszczalnych normach

KLUCZOWY BUFOR
Bieżąca przestrzeń indeksu MyISAM = 43 K.
Aktualny key_buffer_size = 16 M
Współczynnik opuszczania pamięci podręcznej kluczy wynosi 1: 2
Współczynnik bez bufora klucza = 81%
Twój key_buffer_size wydaje się być w porządku

Zapytanie o pamięć
Pamięć podręczna zapytań jest włączona
Obecny rozmiar_pamięci_podstawowej = 16 mln
Bieżące zapytanie_pamięci_użytej = 16 K.
Obecny query_cache_limit = 1 M
Bieżący bufor pamięci podręcznej Współczynnik wypełnienia pamięci = .10%
Bieżący query_cache_min_res_unit = 4 K.
Twój query_cache_size wydaje się być zbyt wysoki.
Być może możesz użyć tych zasobów w innym miejscu
MySQL nie buforuje wyników zapytań, które są większe niż rozmiar query_cache_limit

OPERACJE SORTOWANIA
Obecny sort_buffer_size = 256 K.
Obecny read_rnd_buffer_size = 256 K.
Nie wykonano żadnych operacji sortowania
Bufor sortowania wydaje się być w porządku

ŁĄCZY
Bieżący rozmiar Join_buffer = 260,00 K.
Masz 0 zapytań, w których łączenie nie mogło poprawnie używać indeksu
Wygląda na to, że twoje połączenia prawidłowo używają indeksów

LIMIT OTWARTYCH PLIKÓW
Bieżące pliki open_files_limit = 5000 plików
Open_files_limit powinien zazwyczaj być ustawiony na co najmniej 2x-3x
z table_cache, jeśli masz intensywne użycie MyISAM.
Twoja wartość open_files_limit wydaje się być w porządku

DANE TABELI
Bieżący table_open_cache = 2000 tabel
Aktualny table_definition_cache = 1400 tabel
Masz w sumie 119 tabel
Masz 249 otwartych stołów.
Wartość table_cache wydaje się być w porządku

TABELE TEMP
Obecny max_heap_table_size = 16 M
Prąd tmp_table_size = 16 M
Z 361 tabel temp. 6% utworzono na dysku
Utworzony stosunek tabel tabel tmp wydaje się być w porządku

SKANOWANIE TABELI
Aktualny read_buffer_size = 128 K.
Współczynnik skanowania tabeli = 673: 1
read_buffer_size wydaje się być w porządku

BLOKOWANIE STOŁU
Aktualny współczynnik oczekiwania na blokadę = 0: 219
Twoje blokowanie stołu wydaje się być w porządku

Uwaga specjalna: Skoncentruj się bardziej na podświetlonym na czerwono i żółto tekście, ponieważ zawiera zalecenia dotyczące optymalizacji i poprawy wydajności mysql. W pobliżu

Dostosuj zmienną pliku zgodnie z zaleceniami. Możesz skomentować domyślne wartości zmiennych i dostosować wartości zmiennych zgodnie z sugestiami. W pobliżu

Po wprowadzeniu zmian zrestartuj serwer MySQL.

$ sudo systemctl zrestartuj mysql

Uwaga specjalna: jeśli nie uruchomi się ponownie, wróć i skomentuj nowe wartości i zacznij dostosowywać jeden po drugim i zobacz, które dostosowanie zmiennych powoduje awarię serwera.

Jeśli serwer pomyślnie uruchomi się ponownie, uruchom ponownie tuneprimer i sprawdź, czy są jeszcze jakieś zalecenia dotyczące dostosowania wartości zmiennych.

Wniosek

Bardzo ważne jest, aby pamiętać, że narzędzia do strojenia mysqltuner lub tuning-primer nie są rozwiązaniami dla źle działającego serwera MySQL, ale raczej skryptami, które poprowadzą cię do dostrojenia serwera MySQL.

Dla najlepszej wydajności,

  • Dokładnie przejrzyj zapytania wysłane do serwera i zoptymalizuj strukturę / projekt bazy danych oraz zapytanie SQL
  • Oceń czynniki wpływające na wydajność MySQL, jak wyjaśniono we wstępie.
  • Rozważ ulepszenie sprzętu fizycznego serwera, aby uzyskać lepszą wydajność i zoptymalizuj konfiguracje MySQL w oparciu o specyfikacje sprzętowe.
  • Zrób więcej badań, jak dostroić konfiguracje MySQL w oparciu o aplikacje wykorzystujące serwer MySQL.

Zdecydowanie zaleca się regularne uruchamianie narzędzia mysqltuner lub tuningprimer w okresie 48 godzin po zrestartowaniu serwera MySQL, aby uzyskać znaczącą statystykę użytkowania.

Sprawdź 3 najlepsze usługi hostingowe MySQL:

Hosting A2

Cena wywoławcza:
3,92 USD


Niezawodność
9.3


cennik
9.0


Przyjazny użytkownikowi
9.3


Wsparcie
9.3


funkcje
9.3

Przeczytaj recenzje

Odwiedź A2 Hosting

FastComet

Cena wywoławcza:
2,95 USD


Niezawodność
9.7


cennik
9.5


Przyjazny użytkownikowi
9.7


Wsparcie
9.7


funkcje
9,6

Przeczytaj recenzje

Odwiedź FastComet

Hostinger

Cena wywoławcza:
0,99 USD


Niezawodność
9.3


cennik
9.3


Przyjazny użytkownikowi
9.4


Wsparcie
9.4


funkcje
9.2

Przeczytaj recenzje

Odwiedź Hostinger

Powiązane artykuły instruktażowe

  • Jak dostroić i zoptymalizować wydajność MySQL 8.0 na serwerze CentOS 7
    ekspert
  • Jak wykonać kopię zapasową bazy danych MySQL na Ubuntu 18.04 VPS lub serwerze dedykowanym
    pośredni
  • Jak zainstalować MYSQL 8.0 i utworzyć bazę danych na Ubuntu 18.04 Linux VPS
    pośredni
  • Jak wyłączyć MySQL 5 "tryb ścisły" na Ubuntu 18.04 VPS lub serwerze dedykowanym
    pośredni
  • Jak zainstalować PhpMyAdmin na Ubuntu 18.04 VPS lub serwerze dedykowanym
    pośredni
Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me