Jak dostroić i zoptymalizować wydajność MySQL 8.0 na serwerze CentOS 7

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

W tym artykule pokazujemy, jak dostroić i zoptymalizować wydajność MySQL 8.0 na CentOS 7 Linux VPS.

Warunki wstępne:

  • Konfiguracja VPS CentOS 7
  • Zainstalowany serwer MySQL 8.0
  • Utworzono użytkownika z uprawnieniami sudo

Instalowanie mysqltuner i tuning-primer

Zainstaluj mysqltuner

Mysqltuner to wysokowydajny skrypt Perl dostrajający 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

Najpierw zainstaluj epel-release, ponieważ pakiet mysqltuner znajduje się w repozytorium epel-release

$ sudo yum install -y epel-release

Następnie zainstaluj mysqltuner

$ sudo mniam zainstaluj -y 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 mniam zainstaluj bc -y
$ git clone https://github.com/RootService/tuning-primer.git

Uwaga specjalna: Incase git nie jest zainstalowany, możesz go zainstalować za pomocą

$ sudo mniam zainstaluj git -y

Przejdź do katalogu tuning-primer i ustaw, aby skrypt tuning-primer był wykonywalny w następujący sposób:

$ cd tuning-primer
$ sudo chmod + x tuning-primer.sh

Dostrajanie i optymalizacja serwera mysql

Strojenie serwera MySQL odbywa się w pliku konfiguracyjnym MySQL.

/etc/my.cnf

Uruchom mysqltuner.

$ sudo mysqltuner

Wprowadź użytkownika mysql ze wszystkimi uprawnieniami (w naszym przypadku „root”) i hasło po wyświetleniu monitu

[linuxuser @ centos7-hostadvice4 ~] $ sudo mysqltuner
Podaj swój login administratora MySQL: root
Wprowadź hasło administracyjne MySQL: >> MySQLTuner 1.6.0 – 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
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
[!!] Obecnie działa nieobsługiwana wersja MySQL 8.0.11
[OK] Działa w architekturze 64-bitowej

——– Statystyka silnika pamięci masowej ——————————————-
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
[-] Status: + ARCHIWUM + BLACKHOLE + CSV -FEDERATED + InnoDB + MRG_MYISAM
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
[-] Dane w tabelach InnoDB: 16 KB (tabele: 1)
[OK] Suma podzielonych tabel: 0
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.

——– Zalecenia dotyczące bezpieczeństwa ——————————————-
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
[OK] Nie ma anonimowego konta u wszystkich użytkowników bazy danych
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
BŁĄD 1054 (42S22) w wierszu 1: Nieznana kolumna „hasło” w „klauzula gdzie”
[OK] Wszyscy użytkownicy bazy danych mają przypisane hasła
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
BŁĄD 1064 (42000) w wierszu 1: Wystąpił błąd w składni SQL; sprawdź instrukcję, która odpowiada wersji Twojego serwera MySQL, czy jest odpowiednia składnia do użycia w pobliżu „(użytkownik) LUB CAST (hasło jako plik binarny) = HASŁO (GÓRNY (użytkownik)) LUB CAST (hasło jako B” w wierszu 1
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
[!!] Nie ma podstawowej listy plików haseł !
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
mysql: [Ostrzeżenie] Używanie hasła w interfejsie wiersza poleceń może być niepewne.
Zastosowanie niezainicjowanej wartości dodatkowo (+) w linii / bin / mysqltuner 1934, <>
wiersz 2 (# 1)
(W niezainicjowany) Nieokreślona wartość została użyta tak, jakby już była
zdefiniowane. Zostało to zinterpretowane jako "" lub 0, ale może to był błąd.
Aby wyłączyć to ostrzeżenie, przypisz zdefiniowaną wartość do zmiennych.

Aby pomóc ci dowiedzieć się, co było niezdefiniowane, Perl spróbuje ci powiedzieć
nazwa zmiennej (jeśli istnieje), która nie została zdefiniowana. W niektórych przypadkach
nie może tego zrobić, więc mówi również, jakiej operacji użyłeś
nieokreślona wartość w. Zauważ jednak, że perl optymalizuje twój program
Pomoc operacja wyświetlana w ostrzeżeniu niekoniecznie musi się pojawić
dosłownie w twoim programie. Na przykład, "to $ foo" jest zwykle
zoptymalizowany do "że " . $ foo, a ostrzeżenie będzie odnosić się do
operator konkatenacji (.), nawet jeśli nie ma. w
twój program.

Zastosowanie niezainicjowanej wartości w dziale (/) w linii / bin / mysqltuner 1934, <>
wiersz 2 (# 1)
Zastosowanie niezainicjowanej wartości $ mystat {"Qcache_lowmem_prunes"} w równaniu numerycznym (==)
at / bin / mysqltuner line 1949, <> wiersz 2 (# 1)

——– Wskaźniki wydajności ————————————————-
[-] Czas oczekiwania: 1h 37m 3s (12 q [0,002 qps], 20 conn, TX: 20K, RX: 2K)
[-] Odczyt / zapis: 100% / 0%
[-] Rejestrowanie binarne jest włączone (TRYB GTID: WYŁ.)
[-] Całkowita liczba buforów: 168,0 mln globalnych + 1,1 mln na wątek (151 maks. Wątków)
[OK] Maksymalne osiągnięte użycie pamięci: 169,1 mln (4,78% zainstalowanej pamięci RAM)
[OK] Maksymalne możliwe użycie pamięci: 341,4 mln (9,65% zainstalowanej pamięci RAM)
[OK] Wolne zapytania: 0% (0/12)
[OK] Najwyższe wykorzystanie dostępnych połączeń: 0% (1/151)
[!!] Przerwane połączenia: 40,00% (8/20)
Zastosowanie niezainicjowanej wartości $ myvar {"query_cache_size"} liczbowo lt (<) w
/ bin / mysqltuner linia 2281, <> wiersz 2 (# 1)
[!!] Pamięć podręczna zapytań jest wyłączona
[OK] Tabele tymczasowe utworzone na dysku: 0% (0 na dysku / ogółem 2)
[OK] Współczynnik trafień w pamięci podręcznej wątków: 95% (1 utworzone / 20 połączeń)
[OK] Współczynnik trafień w pamięci podręcznej tabeli: 83% (118 otwarty / 142 otwarty)
[OK] Zastosowano limit otwartych plików: 0% (2 / 10K)
[OK] Blokady tabeli nabyte natychmiast: 100% (2 natychmiastowe / 2 blokady)
[OK] Dostęp do pamięci podręcznej binlog: 0% (0 pamięci / 0 ogółem)

——– Metryki MyISAM —————————————————–
[!!] Użyty bufor kluczy: 18,2% (używany 1 MB / 8 MB pamięci podręcznej)
[!!] Nie można obliczyć rozmiaru indeksu MyISAM – ponownie uruchom skrypt jako użytkownik root

——– Metryki InnoDB —————————————————–
[-] InnoDB jest włączony.
[OK] Pula buforów InnoDB / rozmiar danych: 128,0 M / 16,0 K.
[OK] Instancje puli buforów InnoDB: 1
[!!] InnoDB Używany bufor: 11,08% (908 wykorzystanych / 8192 łącznie)
[OK] Wydajność bufora odczytu InnoDB: 94,07% (12243 trafień / 13015 ogółem)
[!!] InnoDB Wydajność bufora zapisu: 0,00% (0 trafień / 1 ogółem)
[OK] Dziennik InnoDB czeka: 0,00% (0 oczekiwań / 12 zapisów)

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

——– Metryki replikacji ————————————————-
[-] Brak urządzeń podrzędnych replikacji dla tego serwera.
[-] To jest samodzielny serwer..

——– Rekomendacje —————————————————–
Ogólne zalecenia:
MySQL zaczął działać w ciągu ostatnich 24 godzin – zalecenia mogą być niedokładne
Zmniejsz lub wyeliminuj niezamknięte połączenia i problemy z siecią
Zmienne do dostosowania:
query_cache_size (>= 8 mln)
[linuxuser @ centos7-hostadvice4 ~] $

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/my.cnf /etc/my.cnf.bak
$ sudo vim /etc/my.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
[linuxuser @ centos7-hostadvice4 tuning-primer] $ sudo ./tuning-primer.sh
— MYSQL PERFORMANCE TUNING PRIMER 2.0.1-r1 —
– Przez: Matthew Montgomery –
– Przez: Markus Kohlmeyer –

MySQL wersja 8.0.11 x86_64

Czas pracy = 0 dni 0 godz. 2 min 52 sek
Śr. qps = 0
Suma pytań = 4
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/8.0/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 25, które trwają dłużej niż 10.000000 s. ukończyć
Twój długi_okres_czasu może być za długi, zwykle ustawiam go poniżej 5 sekund.

DZIENNIK AKTUALIZACJI BINARNEJ
Dziennik aktualizacji binarnych jest włączony
Expire_logs_days nie jest ustawiony.
Mysqld zachowa cały dziennik binarny, dopóki polecenia RESET MASTER lub PURGE MASTER LOGS nie zostaną uruchomione ręcznie
Ustawienie expire_logs_days pozwoli Ci automatycznie usunąć stare dzienniki binarne
Zobacz http://dev.mysql.com/doc/refman/8.0/en/purge-master-logs.html

NICI ROBOCZE
Bieżący rozmiar wątku_cache = 9
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

STAN INNODB
Bieżąca przestrzeń indeksu InnoDB = 240 K.
Bieżąca przestrzeń danych InnoDB = 2 M
Obecna pula buforów InnoDB za darmo = 88%
Obecny innodb_buffer_pool_size = 128 M
W zależności od tego, ile miejsca zajmują indeksy innodb, może to być bezpieczne
aby zwiększyć tę wartość do 2/3 całkowitej pamięci systemowej

ZUŻYCIE PAMIĘCI
Maks. Kiedykolwiek przydzielona pamięć: 153 mln
Skonfigurowane bufory maksymalnej liczby wątków: 178 mln
Skonfigurowane maks. Globalne bufory: 152 mln
Skonfigurowany maksymalny limit pamięci: 330 mln
Plus 16 M na utworzony tymczasowy stół
Pamięć fizyczna: 3,45 G.
Wydaje się, że maksymalny limit pamięci mieści się w dopuszczalnych normach

KLUCZOWY BUFOR
Brak odczytu klucza?!
Poważnie przyjrzyj się używaniu niektórych indeksów
Bieżąca przestrzeń indeksu MyISAM = 0 bajtów
Aktualny key_buffer_size = 8 M
Współczynnik braku pamięci podręcznej klucza wynosi 1: 0
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 obsługiwana, ale nie włączona
Być może powinieneś ustawić rozmiar_pamięci_podstawowej

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 3 zapytania, w których łączenie nie mogło poprawnie użyć indeksu
Powinieneś włączyć "log-queries-not-using-indexes"
Następnie poszukaj niezindeksowanych połączeń w dzienniku powolnych zapytań.

LIMIT OTWARTYCH PLIKÓW
Obecny open_files_limit = 10000 plików
Open_files_limit powinien zazwyczaj być ustawiony na co najmniej 2x-3x
z table_open_cache, jeśli masz duże użycie MyISAM.
Twoja wartość open_files_limit wydaje się być w porządku

DANE TABELI
Bieżący table_open_cache = 4000 tabel
Aktualny table_definition_cache = 2000 tabel
Masz w sumie 136 tabel
Masz 165 otwartych stołów.
Wydaje się, że wartość table_open_cache jest w porządku

TABELE TEMP
Obecny max_heap_table_size = 16 M
Prąd tmp_table_size = 16 M
Z 95 tabel temp. 0% zostało utworzonych 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 = 793: 1
read_buffer_size wydaje się być w porządku

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

[linuxuser @ centos7-hostadvice4 tuning-primer] $

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. Skonsultuj się z HostAdvice, aby uzyskać najlepsze opcje hostingu MySQL.
  • 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 tuning-primer przez okres 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 5.7 na Ubuntu 18.04 VPS lub serwerze dedykowanym
    ekspert
  • Jak zainstalować phpMyAdmin na serwerze CentOS 7 VPS lub serwerze dedykowanym
    pośredni
  • 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 CentOS 7 Linux VPS lub serwerze dedykowanym
    pośredni
  • Jak tworzyć nowe konta użytkowników MySQL i zarządzać uprawnieniami MySQL
    Nowicjusz
Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me