Logi – MySQL – częste problemy

przez | 4 maja 2015

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;

markos
Specjalizuję się w systemach komputerowych Windows oraz Linux. Oprócz wykonywanej pracy w zawodzie, informatyka jest moją pasją.
0 0 vote
Article Rating
Subscribe
Powiadom o
guest
0 komentarzy
Inline Feedbacks
View all comments