Częste problemy – MySQL :
Problemy z wykonywaniem zapytań (opóźnienia)
Przeciążony serwer MySQL
Ogólne problemy z serwerem MySQL, Błędy
Nieoptymalne zapytania MySQL to takie zapytania, których wykonanie trwa dłużej niż 1 sekunda.
Powodują np. powolne otwieranie strony internetowej, przeciążanie serwera.
Rozwiązania/Procedury :
1. Odczyt logów (/var/log/mysql) :
slowlog (mysql-slow.log) – logi nieoptymalnych zapytań:
– log zawiera zapytania, których czas wykonania przekroczył czas ustawiony w konfiguracji /etc/mysql/my.cnf. Parametr (long_query_time)
– logi trzeba aktywować. Domyślnie wyłączone.
Plik : /var/log/mysql/mysql-slow.log
general log (mysql.log) :
– logowanie wszystkich zapytań, które zostały wysłane do serwera MySQL
– logowanie informacji o połączeniach z bazą danych
– logi tego typu zapisują pełne zapytania SQL w postaci tekstowej.
– logi trzeba aktywować. Domyślnie wyłączone. Włączyć tylko na jakiś czas. Uważać na duże rozmiary pliku.
Plik : /var/log/mysql/mysql.log
binlog (mysql-bin.log) :
– logowanie zmian, jakie zostały wprowadzone w bazach danych (postać binarna)
– przydatne logi przy odtwarzaniu bazy danych
– logi trzeba aktywować. Domyślnie wyłączone.
#log_bin = /var/log/mysql/mysql-bin.log
Plik : /var/log/mysql/mysql-bin.log
1.1 Logowanie obciążenia – MySQL – logi nieoptymalnych zapytań (plik mysql-slow.log) :
Narzędzie Slow Query Log – umożliwia znalezienie tych zapytań, które powodują spadek wydajności bazy danych.
Narzędzie wymaga konfiguracji.
/etc/mysql/my.cnf :
slow_query_log_file = /var/log/mysql/mysql-slow.log # ścieżka do pliku logów nieoptymalnych zapytań SQL slow_query_log = 1 # aktywowanie narzędzia Slow Query Log long_query_time = 2 # ustawienie od jakiego czasu wykonania zapytania MySQL dane zapytanie # zostanie uznane jako wolne. # Domyślną wartością są dwie sekundy. Wartości można wyrażać również w mikrosekundach np.0.100000 to 1/10 sekundy log_queries_not_using_indexes # logowanie również zapytań bez indeksów
Restart serwera :
/etc/init.d/mysqld restart
tail -f /var/log/mysql/mysql-slow.log
Jeśli serwer MySQL wykona zapytanie dłużej niż określoną wartość w long_query_time, zostaną dodane wpisy do pliku mysql-slow.log.
Przykład wpisu (przy ustawieniu long_query_time = 0.100000) :
# Time: 150504 18:32:05 # User@Host: debian-sys-maint[debian-sys-maint] @ localhost [] # Query_time: 0.133923 Lock_time: 0.000683 Rows_sent: 0 Rows_examined: 979 SET timestamp=1430757125; select count(*) into @discard from `information_schema`.`COLUMNS`; # User@Host: debian-sys-maint[debian-sys-maint] @ localhost [] # Query_time: 0.500549 Lock_time: 0.000642 Rows_sent: 0 Rows_examined: 110 SET timestamp=1430757125; select count(*) into @discard from `information_schema`.`PARTITIONS`;
Objaśnienie :
– kiedy wpis został dodany (Time) – 150504 18:32:05
– jak długo trwało zapytanie (Query_time)
pierwsze zapytanie : 0.133923 – ponad 1/10 sekundy
drugie zapytanie : 0.500549 – pół sekundy
– jakie zapytania ?
select count(*) into @discard from `information_schema`.`COLUMNS` select count(*) into @discard from `information_schema`.`PARTITIONS`
Warto skorzystać z narzędzia mysqldumpslow. Można wyświetlić średni czas wykonania zapytań, posortować zapytania.
Reading mysql slow query log from /var/log/mysql/mysql-slow.log Count: 1 Time=0.50s (0s) Lock=0.00s (0s) Rows=0.0 (0), debian-sys-maint[debian-sys-maint]@localhost select count(*) into @discard from `information_schema`.`PARTITIONS` Count: 1 Time=0.13s (0s) Lock=0.00s (0s) Rows=0.0 (0), debian-sys-maint[debian-sys-maint]@localhost select count(*) into @discard from `information_schema`.`COLUMNS`
2. Wykonanie optymalizacji tabel w bazie :
Pierwsza metoda :
Zalogowanie się do serwera:
mysql -u root -p
show databases; use nazwa_bazy_danych;
wykonanie :
OPTIMIZE TABLE nazwa_tabeli;
Druga metoda (optymalizacja wszystkich baz) :
mysqlcheck -p -o --all-databases
3. Logowanie obciążenia przez zapytania : MySQL – powtarzające się zapytania.
Zapytania wywoływane w grupie mogą wywoływać spadek wydajności serwera MySQL.
Narzędzie General Log Query – logowanie zapytań , które są przesyłane do serwera MySQL.
Edycja pliku /etc/mysql/my.cnf:
general_log_file = /var/log/mysql/mysql.log general_log = 1 # włączenie logowania
4. Utworzenie indeksów
Częstymi przyczynami nieoptymalnych zapytań są :
– zapytania SELECT z klauzulą WHERE dotyczącą tabel bez indeksów
– zapytania z klauzulą JOIN dotyczącą tabel bez indeksów
Aby wyszukiwać szybciej dane należy stosować tzw. indeksy.
Można do zarządzania indeksami stosować narzędzie phpmyadmin.
5. Optymalizacja bazy danych
Wykorzystanie skryptów :
mysql-tuning-primer:
https://launchpad.net/mysql-tuning-primer
https://github.com/major/MySQLTuner-perl/zipball/master
Dodatek :
Przydatne polecenia w MySQL dla administratora bazy :
SHOW ERRORS; # wyświetlenie błędów systemowych
SHOW WARNINGS; # wyświetlenie ostrzeżeń
SHOW DATABASES; # wyświetla listę baz danych na serwerze
SHOW FULL PROCESSLIST; # lista użytkowników podłączonych do bieżącej instancji MySQL
kill ID_procesu; # przerwanie połączenia
SHOW TABLE STATUS; # wyświetlenie informacji o tabelach
SHOW CREATE DATABASE nazwa_bazy; # wyświetlenie informacji o bazie
SHOW CREATE TABLE nazwa_tabeli; # wyświetlenie informacji o tabeli
SHOW INDEX FROM nazwa_tabeli; # wyświetlenie listy indeksów w podanej tabeli
SHOW GRANTS FOR nazwa_uzytkownika; # wyświetlenie praw dostępu podanego użytkownika
np.
SHOW GRANTS FOR postfixadmin@localhost;
SHOW PRIVILEGES; # wyświetlenie listy przywilejów
SHOW CHARACTER SET LIKE filtr;
