Cum să dezactivați jurnalul de interogări lente. Profilare interogări în MySQL

Profilarea interogărilor MySQL este o tehnică utilă pentru analiza performanței generale a aplicațiilor bazate pe baze de date. Când se dezvoltă aplicații medii spre mari, există de obicei sute de solicitări răspândite într-o bază mare de cod, iar baza de date procesează multe solicitări pe secundă. Fără profilarea interogărilor, devine foarte dificil să se determine locația și cauzele blocajelor aplicațiilor. Acest tutorial descrie câteva tehnici utile de profilare a interogărilor folosind instrumentele încorporate MySQL.

Jurnal de interogări lente MySQL

Jurnalul de interogări lente MySQL (sau jurnalul de interogări lente) este un jurnal în care MySQL trimite interogări lente și potențial problematice.

Această caracteristică vine cu MySQL, dar este dezactivată implicit. MySQL determină ce interogări trebuie incluse în acest jurnal folosind variabile speciale care vă permit să profilați interogarea în funcție de cerințele de performanță ale aplicației. De obicei, interogările care durează mai mult pentru procesare și interogările care au indecși incorecți sunt introduse în acest jurnal.

Variabile de profilare

Variabilele de bază ale serverului pentru configurarea jurnalului de interogări lente MySQL sunt:

slow_query_log global
slow_query_log_file global
long_query_time global/session
log_queries_not_using_indexes global
min_examined_row_limit global/session

slow_query_log – o variabilă logică pentru a activa sau dezactiva jurnalul de interogare lentă.

slow_query_log_file – calea absolută a fișierului jurnal de interogare. Directorul de fișiere trebuie să fie deținut de utilizatorul mysqld și să aibă permisiuni adecvate de citire și scriere. Demonul mysql va fi pornit cel mai probabil ca mysql, dar pentru a fi sigur, rulați comanda într-un terminal Linux:

ps -ef | grep bin/mysqld | tăiați -d" " -f1

Ieșirea va afișa utilizatorul curent și utilizatorul mysqld.

cd /var/log
mkdir mysql
chmod 755 mysql
chown mysql:mysql mysql

• long_query_time – timpul în secunde pentru a verifica durata interogării. Dacă valoarea este 5, toate cererile care durează mai mult de 5 secunde pentru a fi procesate vor fi înregistrate.
• log_queries_not_using_indexes – valoare booleană, care determină dacă interogările care nu folosesc indecși trebuie înregistrate. Când se analizează, astfel de interogări sunt importante.
• min_examined_row_limit – definește numărul minim de rânduri care trebuie analizate. Cu o valoare de 1000, toate interogările care analizează mai puțin de 1000 de rânduri vor fi ignorate.

Variabilele serverului MySQL pot fi setate în fișierul de configurare MySQL sau folosind dinamic interfata utilizator sau linia de comandă MySQL. Dacă sunt setate variabile în fișierul de configurare, acestea vor persista când serverul este repornit, dar serverul trebuie repornit pentru a le activa. Fișierul de configurare MySQL se află de obicei în /etc/my.cnf sau /etc/mysql/my.cnf. Pentru a găsi fișierul de configurare, introduceți (poate fi necesar să extindeți căutarea la alte directoare rădăcină):

găsiți /etc -name my.cnf
găsiți /usr -name my.cnf

După ce ați găsit fișierul de configurare, adăugați variabilele necesare la secțiune:

….
jurnal de interogare lentă = 1
slow-query-log-file = /var/log/mysql/localhost-slow.log
timp_interogare_lung = 1
jurnal-interogări-nu-utilizare-indexuri

Pentru ca modificările să aibă efect, trebuie să reporniți serverul. Dacă modificările trebuie activate imediat, setați variabilele în mod dinamic:

mysql> SET GLOBAL slow_query_log = "ON";
mysql> SET GLOBAL slow_query_log_file = "/var/log/mysql/localhost-slow.log";
mysql> SET GLOBAL log_queries_not_using_indexes = "ON";
mysql> SET SESSION long_query_time = 1;
mysql> SET SESSION min_examined_row_limit = 100;

Pentru a verifica valorile variabilelor:

mysql> AFIȚI VARIABILELE GLOBALE CA „Slow_query_log”;
mysql> AFIȘAȚI VARIABILELE DE SESIUNE CA „long_query_time”;

Unul dintre dezavantajele schimbării dinamice a variabilelor MySQL este că variabilele se vor pierde atunci când serverul este repornit. Prin urmare, toate variabilele importante care trebuie salvate ar trebui adăugate la fișier.

Generarea unei interogări de profilare

Acum sunteți familiarizat cu setările jurnalului de interogări lente. Încercați să generați date de interogare pentru profilare.

Nota: Exemplul dat aici a fost rulat pe o instanță MySQL care rulează fără niciun jurnal de interogare lentă configurat. Aceste interogări de testare pot fi executate prin intermediul GUI sau linie de comandă MySQL.

La monitorizarea jurnalului de interogări lente, este util să deschideți două ferestre de terminal: o conexiune pentru trimiterea instrucțiunilor MySQL și a doua pentru vizualizarea jurnalului de interogări.

Conectați-vă la serverul MySQL folosind consola ca utilizator cu privilegii SUPER ADMIN. Pentru a începe, creați o bază de date și un tabel de testare, adăugați-i date inactiv și activați înregistrarea lentă a interogărilor.

Nota: În mod ideal, acest exemplu este cel mai bine rulat într-un mediu fără alte aplicații care utilizează MySQL pentru a evita aglomerarea jurnalului de interogări.

$> mysql -u -p
mysql> CREATE DATABASE profile_sampling;

mysql> USE profile_sampling;

mysql> CREATE TABLE utilizatori (id TINYINT PRIMARY KEY AUTO_INCREMENT, nume VARCHAR(255));

mysql> INSERT INTO users (nume) VALUES ("Walter"),("Skyler"),("Jesse"),("Hank"),("Walter Jr.", ("Marie"),("Saul") "), ("Gustavo"), ("Hector"), ("Mike");

mysql> SET GLOBAL slow_query_log = 1;

mysql> SET GLOBAL slow_query_log_file = "/var/log/mysql/localhost-slow.log";

mysql> SET GLOBAL log_queries_not_using_indexes = 1;

mysql> SET long_query_time = 10;

mysql> SET min_examined_row_limit = 0;

Acum aveți o bază de date de testare și un tabel cu câteva date. Jurnalul de interogări lent este activat. Am setat în mod deliberat timpul de procesare a cererii la mare și am dezactivat verificarea numărului de rânduri. Pentru a vizualiza jurnalul, introduceți:

cd /var/log/mysql
ls -l

Deocamdată, nu ar trebui să existe un jurnal de solicitări lente în dosar, deoarece în acest moment nu au existat cereri. Dacă un astfel de jurnal există deja, aceasta înseamnă că baza de date a întâlnit deja interogări lente de când ați activat suportul pentru jurnalul de interogări lente. Acest lucru poate denatura rezultatele acestui exemplu. Reveniți la fila MySQL și rulați:

mysql> USE profile_sampling;
mysql> SELECT * FROM utilizatori WHERE id = 1;

Interogarea executată pur și simplu preia datele și folosește indexul primei chei din tabel. Această interogare a fost rapidă și a folosit un index, deci nu este înregistrată în jurnalul de interogări lente. Reveniți la director și asigurați-vă că nu a fost creat niciun jurnal de interogări. Acum reveniți la fereastra MySQL și rulați:

mysql>

Această interogare nu utilizează un index. Acum ceva de genul acesta ar trebui să apară în jurnalul /var/log/mysql/localhost-slow.log:

# Ora: 140322 13:54:58

utilizați profile_sampling;
SET timestamp=1395521698;

Un alt exemplu. Creșteți numărul minim de rânduri de analizat și trimiteți o solicitare ca aceasta:

mysql> SET min_examined_row_limit = 100;
mysql> SELECT * FROM utilizatori WHERE nume = "Walter";

Datele nu vor fi adăugate în jurnal deoarece au fost analizate mai puțin de 100 de rânduri în timpul solicitării.

Nota: Dacă datele nu au fost adăugate în jurnal, trebuie să verificați mai mulți factori. Mai întâi verificați permisiunile directorului în care este creat jurnalul. Trebuie să fie deținut de utilizatorul/grupul mysqld și să aibă privilegii chmod 755. Apoi, ar trebui să verificați dacă există alte setări de interogare lente pe server care suprascriu setările dvs. Resetați valorile implicite pentru a elimina toate variabilele de solicitare lentă din fișierul de configurare și reporniți serverul. De asemenea, puteți seta dinamic variabilele globale la valorile lor implicite. Dacă faceți modificări dinamic, deconectați-vă și conectați-vă din nou la MySQL pentru a actualiza setările.

Analizarea datelor de profilare a interogărilor

Luați în considerare următoarele date:

# Ora: 140322 13:54:58
#Utilizator@Gazdă: root@localhost
# Query_time: 0.000303 Lock_time: 0.000090 Rows_sent: 1 Rows_examined: 10
utilizați profile_sampling;
SET timestamp=1395521698;
SELECT * FROM utilizatori WHERE nume = "Jesse";

Această intrare afișează:

• Timpul de execuție a interogării
• Cine l-a trimis
• Cât a durat procesarea cererii?
• Lungime
• Câte rânduri au fost returnate
• Câte rânduri au fost analizate

Acest lucru este util deoarece orice solicitare care încalcă cerințele de performanță specificate în variabile ajunge în jurnal. Acest lucru permite unui dezvoltator sau administrator să urmărească rapid solicitările care nu funcționează. În plus, datele de profilare a interogărilor vă pot ajuta să determinați ce circumstanțe fac ca aplicația dvs. să funcționeze slab.

Folosind mysqldumpslow

Profilarea poate fi inclusă în aplicațiile bazate pe baze de date pentru a asigura un flux moderat de date.

Pe măsură ce dimensiunea jurnalului crește, devine dificilă analizarea tuturor datelor, iar interogările problematice se pot pierde cu ușurință în ele. MySQL oferă un instrument numit mysqldumpslow, care ajută la evitarea acestei probleme prin împărțirea jurnalului de interogări lente. Binarul este legat la MySQL (pe Linux), așa că pur și simplu puteți rula comanda:

mysqldumpslow -t 5 -s la /var/log/mysql/localhost-slow.log

Comanda poate accepta diverși parametri pentru a-și personaliza ieșirea. Exemplul de mai sus va afișa primele 5 interogări sortate după durata medie de interogare. Astfel de șiruri sunt mai lizibile și sunt, de asemenea, grupate după solicitare.

Număr: 2 Timp=68,34s (136s) Blocare=0,00s (0s) Rânduri=39892974,5 (79785949), root@localhost
SELECT PL.pl_title, P.page_title
DE LA pagina P
INNER JOIN link-uri de pagină PL
ON PL.pl_namespace = P.page_namespace
UNDE P.page_namespace = N
…

Ieșirea arată următoarele date:

• Număr: de câte ori a fost înregistrată cererea.
• Timp: timpul mediu și total al solicitării (în paranteze).
• Blocare: timp de blocare a mesei.
• Rânduri: numărul de rânduri returnate.

Comanda exclude valorile numerice și de șir, astfel încât interogările identice cu diferite condiții WHERE sunt tratate ca aceleași. Instrumentul mysqldumpslow elimină necesitatea de a revizui în mod constant jurnalul de interogări lente, permițându-vă să efectuați verificări automate regulate. Opțiunile de comandă mysqldumpslow vă permit să rulați expresii complexe.

Solicitați defalcare

Un alt instrument de profilare de reținut este Instrumentul de defalcare a interogărilor complexe. Vă permite să identificați interogările problematice în jurnalul de interogări lente și să le rulați în MySQL. Mai întâi trebuie să activați profilarea și apoi să rulați interogarea:

mysql> SET SESSION profiling = 1;
mysql> USE profile_sampling;
mysql> SELECT * FROM utilizatorii WHERE nume = "Jesse";
mysql> AFIȚI PROFILE;

Odată ce profilarea este activată, SHOW PROFILES va afișa un tabel care asociază Query_ID cu expresia SQL. Găsiți Query_ID corespunzător interogării care rulează și executați următoarea interogare (înlocuiți # cu Query_ID):

mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=#;

Comanda va returna un tabel:

STATE este un pas în procesul de execuție a interogării, iar DURATION este timpul necesar pentru a finaliza acel pas în secunde. Acesta nu este un instrument foarte util, dar poate ajuta la determinarea care parte a execuției interogării cauzează cea mai mare latență.

Nota Notă: Acest instrument nu trebuie utilizat într-un mediu de producție.

Performanță lentă a jurnalului de interogări

Tot ce rămâne este să ne dăm seama cum afectează jurnalul de interogări lent performanța. În general, este sigur să rulați jurnalele de interogare lente într-un mediu de producție; Nici CPU, nici I/O nu ar trebui să fie afectate. Cu toate acestea, ar trebui să aveți o strategie de monitorizare a dimensiunii jurnalului pentru a vă asigura că jurnalul nu devine prea mare pentru sistem de fișiere. În plus, atunci când rulați înregistrarea lentă a interogărilor într-un mediu de producție, ar trebui să setați long_query_time la 1 sau mai mare.

Concluzie

Un jurnal de interogări lent vă poate ajuta să identificați interogările problematice și să evaluați performanța generală a interogărilor. În același timp, dezvoltatorul poate obține o înțelegere detaliată a modului în care funcționează aplicația interogări MySQL. Instrumentul mysqldumpslow vă permite să gestionați jurnalele de interogări lente și să le încorporați cu ușurință în procesul de dezvoltare. Prin identificarea interogărilor problematice, puteți optimiza procesarea interogărilor pentru a îmbunătăți performanța.

Concept

Jurnalele de server (fișiere jurnal, jurnal de server)- fișiere stocate pe server care conțin informatii de sistem servere, precum și înregistrarea tuturor datelor posibile despre vizitatorul resursei web.

Jurnalele sunt folosite de administratorii de sistem pentru a analiza vizitatorii, studiind tiparele de comportament ale anumitor grupuri de utilizatori, precum și obținerea diverselor informații despre aceștia, precum: browserul utilizat, adresa IP, date despre locația geografică a clientului și multe altele. Pe lângă analiză, în acest fel puteți afla despre accesul neautorizat pe site, puteți afla mai exact cine a fost făcut exact și puteți transfera date despre în acest caz, către autoritățile competente.

Datele din fișierul jurnal, în forma sa pură, nu vor fi de înțeles pentru utilizatorii obișnuiți, care vor vedea în toate acestea doar un set de caractere într-o ordine de neînțeles. Dar pentru administratorii de sistemși dezvoltatorii web, acesta este un text complet lizibil și destul de ușor informatii utile.

De fiecare dată când un client accesează o resursă web, sunt declanșate simultan mai multe evenimente, despre a căror secvență vom vorbi.

1. Efectuarea unei cereri de pagină. Când introduceți o adresă în linia browserului sau când urmați un link web activ, de exemplu, dintr-o pagină cu rezultate ale unui motor de căutare, browserul caută și se conectează la serverul pe care se află pagina și face o solicitare pentru aceasta. În același timp, transmite următoarele informații către server:
- adresa IP a computerului client care solicită pagina (dacă utilizați un server proxy, adresa IP a proxy-ului dvs.);
- adresa paginii de Internet solicitata de utilizator (adresa IP);
- ora și data exactă la care a fost făcută cererea;
- date despre locația reală a clientului (dacă se folosește un server proxy, atunci adresa proxy reală);
- informatii despre browserul utilizat de client (nume, versiune etc.);
- date despre pagina web de pe care clientul a transferat.

2. Transferul datelor solicitate. Datele solicitate (pagina web, fișiere, cookie-uri etc.) sunt transferate de pe server pe computerul utilizatorului.

3. Scrieți în jurnalul serverului. După toate, apare o intrare de jurnal, care indică toate datele care au apărut în ultimele două evenimente. Acestea sunt toate informațiile transmise în primul paragraf, precum și informații despre datele transmise.

Cum să vizualizați jurnalele serverului

Fișierele jurnal sunt stocate într-un fișier acces.log indiferent de tipul de server web pe care îl utilizați (Apache, Nginx, proxy squid etc.) Acest fișier este document text, pe fiecare rând din care este scris o contestație. Formate de înregistrare în acces.log destul de mult, dar cel mai popular este combinat, în care intrarea are următoarea formă și secvență:

Cod: %h %l %u %t \"%r\" %>s %b \"%(Referer)i\" \"%(User-Agent)i\"
Unde:

%h- gazda/adresa IP de la care a fost facuta cererea;
%t- ora solicitării către server și fusul orar al serverului;
%r- versiunea, continutul si tipul cererii;
%s- cod de stare HTTP;
%b- numărul de octeți trimiși de server;
%(Referer)- URL sursa cererii;
%(User-Agent)- Antet HTTP, cu informații despre cerere (aplicație client, limbă etc.);
%(Gazdă)- numele gazdei virtuale care este accesată.

Când ați terminat, această linie arată cam așa:

127.0.0.1 - - "GET /index.php HTTP/1..0 (compatibil; MSIE 7.0; Windows NT 5.1)"

Citirea manuală a jurnalelor va necesita destul de mult timp și efort. Prin urmare, webmasterii experimentați folosesc un software special numit „Analizoare de fișiere jurnal”. Ei analizează toate datele, care este destul de dificil de citit de oameni, și produc date structurate. Acestea sunt programe precum: Analog, WebAnalizer, Webalizer, Awstats, Webtrends etc. Tipuri de special software destul de multe, printre ele sunt atât programe plătite, cât și gratuite. Prin urmare, sunt sigur că fiecare va găsi ceva pe placul său.

Unde găsiți jurnalele site-ului

Dacă aveți găzduire obișnuită, atunci cel mai probabil va trebui să-i scrieți hosterului și să-i cereți jurnalele. De asemenea, destul de des, le puteți solicita prin intermediul panoului de găzduire. Diferiți hosteri o fac diferit. De exemplu, pentru a solicita de la hosterul meu, faceți clic pe pagina de start panouri:

Jurnal de erori error.log

Error.log- un fișier în care se păstrează și jurnalele. Dar nu vizitatori, ci erori care au apărut pe server. Așa cum este cazul cu acces.log, fiecare linie a fișierului este responsabilă pentru o eroare care a apărut. Înregistrarea se efectuează ținând cont de informații precum: data și ora exactă la care s-a produs eroarea, adresa IP la care a fost emisă eroarea, tipul de eroare, precum și motivul apariției acesteia.

Concluzie

Jurnalele sunt un instrument destul de puternic și informativ cu care să lucrați. Dar în zilele noastre, acestea sunt înlocuite cu instrumente precum Yandex.Metrica, Google Analytics etc., simplificându-ne astfel viața. Cu toate acestea, dacă intenționați să vă dezvoltați, să creșteți și să învățați ceva nou, vă recomand cu siguranță să cunoașteți mai bine acest subiect.

Jurnalele de evenimente sunt primul și cel mai simplu instrument pentru determinarea stării sistemului și identificarea erorilor. Există patru jurnaluri principale în MySQL:

• Jurnal de erori— jurnalul de erori standard care este colectat în timp ce serverul rulează (inclusiv pornire și oprire);
• Jurnal binar— un jurnal al tuturor comenzilor de modificare a bazei de date, necesare pentru replicare și copii de siguranță;
• Jurnal general de interogări— jurnalul de interogări principal;
• Jurnal de interogări lent— jurnalul cererilor lente.

Jurnal de erori

Acest jurnal conține toate erorile care au apărut în timpul rulării serverului, inclusiv erori critice, precum și oprirea, pornirea serverului și avertismente. De aici ar trebui să începeți în cazul unei defecțiuni a sistemului. În mod implicit, toate erorile sunt trimise în consolă (stderr), de asemenea, puteți înregistra erorile în syslog (implicit pe Debian) sau într-un fișier jurnal separat:

Log_error=/var/log/mysql/mysql_error.log

# Erorile vor fi scrise în mysql_error.log

Vă recomandăm să păstrați acest jurnal activat pentru a identifica rapid erorile. Și pentru a înțelege ce înseamnă aceasta sau acea eroare, MySQL are utilitarul de eroare:

Shell> perror 13 64 Cod eroare OS 13: Permisiune refuzată Cod eroare OS 64: Mașina nu este în rețea

# Explică semnificația codurilor de eroare

Jurnal binar (aka binar).

Toate comenzile de modificare a bazei de date sunt înregistrate în jurnalul binar, util pentru replicare si recuperare.

Se aprinde astfel:

Log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 5 max_binlog_size = 500M

# Specifică locația, durata de viață și dimensiunea maximă a fișierului

Vă rugăm să rețineți că, dacă nu aveți de gând să scalați sistemul și să implementați toleranța la erori, atunci este mai bine să nu activați jurnalul binar. Este consumatoare de resurse și reduce performanța sistemului.

Jurnal de solicitare

Acest jurnal conține toate interogările SQL primite și informații despre conexiunile client. Poate fi util pentru analiza și optimizarea indexului, precum și pentru identificarea interogărilor eronate:

General_log_file = /var/log/mysql/mysql.log general_log = 1

# Include jurnalul și indică locația fișierului

De asemenea, îl puteți activa/dezactiva în timp ce serverul MySQL rulează:

SET GLOBAL general_log = "ON"; SET GLOBAL general_log = "OFF";

# Nu trebuie să reporniți serverul pentru a-l folosi

Jurnal de cereri lent

Jurnalul este util pentru identificarea interogărilor lente, adică ineficiente. Citiți mai multe în acest articol.

Vizualizarea jurnalelor

Pentru a vizualiza jurnalele pe Debian (Ubuntu), trebuie să rulați:

# Coada jurnalului de erori -f /var/log/syslog # Coada jurnalului de interogări -f /var/log/mysql/mysql.log # Înregistrați cererile lente coada -f /var/log/mysql/mysql-slow.log

# Dacă jurnalele nu sunt specificate separat, acestea se află în /var/lib/mysql

Rotirea buștenilor

Nu uitați să comprimați (arhivați, rotiți) fișierele de jurnal, astfel încât acestea să ocupe mai puțin spațiu pe server. Pentru a face acest lucru, utilizați utilitarul logrotate prin editarea fișierului de configurare /etc/logrotate.d/mysql-server:

# - Am pus totul într-un singur bloc și am adăugat sharedscripts, astfel încât mysql să primească # flush-logs"d o singură dată. # Altfel, jurnalele binare ar crește automat de n ori în fiecare zi. # - Jurnalul de erori este învechit, mesajele merg în syslog acum./var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log( rotiți zilnic 7 lipsă creați 640 mysql adm comprimați scripturi partajate postrotate test -x /usr/bin/mysqladmin || ieșire 0 # Dacă acest lucru eșuează, verificați debian.conf! MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf" dacă [ -z "`$MYADMIN ping 2>/dev/null`" ]; apoi # Chiar nu există mysqld sau mai degrabă lipsește un utilizator debian-sys-maint? # Dacă acest lucru se întâmplă și nu este o eroare, vă rugăm să raportați o eroare. #if ps cax | grep -q mysqld; apoi if killall -q -s0 -umysql mysqld; apoi ieșiți din 1 fi altfel $MYADMIN flush-logs fi endscript )

# Comprimă și arhivează jurnalele necesare, curăță fișierele

Jurnal DDL

MySQL menține, de asemenea, un jurnal de limbaj de date. Acesta colectează date din operațiuni precum DROP_TABLE și ALTER_TABLE. Jurnalul este folosit pentru a recupera de la defecțiunile apărute în timpul unor astfel de operațiuni. Jurnalul DDL este un fișier binar și nu este destinat să fie citit de utilizator, așa că nu îl modificați sau ștergeți.

Cel mai important

Porniți întotdeauna jurnalul de erori, utilizați jurnalul de interogări pentru a verifica conexiunea aplicației la baza de date, a verifica interogările și funcționarea. Jurnalul de interogări lente este util pentru optimizarea performanței MySQL.

Interogări de profilare în Mysql utilizat pentru a evalua performanța aplicației dvs. Când dezvoltați aplicații medii spre mari, trebuie să faceți față cu sute de solicitări distribuite în codul dvs. care sunt executate în fiecare secundă. Fără tehnici de profilare a interogărilor, poate fi foarte dificil să aflați ce cauzează performanța aplicației dvs. să sufere.

Ce este jurnalul de interogări lent în MySQL?

MySQL Slow Query Log - un jurnal care semnalează interogări lente și potențial problematice. MySQL acceptă această funcționalitate în mod implicit, dar este dezactivată. Prin setarea anumitor variabile de server, putem specifica ce cereri ne interesează. Cel mai adesea, avem nevoie de interogări care necesită o anumită perioadă de timp pentru a fi finalizate sau de interogări care nu procesează corect indecșii.

Setarea variabilelor de profilare

Principalele variabile pentru configurarea jurnalului de interogări:

Slow_query_log G slow_query_log_file G long_query_time G/S log_queries_not_using_indexes G min_examined_row_limit G/S

Comentariu: G - variabile globale, S - variabile de sistem

• slow_query_log - valoare booleană inclusiv jurnalul
• slow_query_log_file - calea absolută către fișierul jurnal. Proprietarul directorului trebuie să fie un utilizator mysqld, iar directorul trebuie să aibă permisiunile corecte de citire și scriere. Cel mai adesea demonul mysql rulează ca utilizator mysql.

Pentru a verifica, executați următoarele comenzi:

Ps-ef | grep bin/mysqld | tăiați -d" " -f1

Ieșirea comenzii vă va oferi numele utilizatorului curent și al utilizatorului mysqld. Exemplu de configurare a directorului /var/log/mysql:

Cd /var/log sudo mkdir mysql sudo chmod 755 mysql sudo chown mysql:mysql mysql

• long_query_time - timpul în secunde pentru a verifica durata interogării. De exemplu, cu o valoare de 5, fiecare cerere care durează mai mult de 5 secunde va fi înregistrată.
• log_queries_not_using_indexes - valoare booleană, permite salvarea interogărilor care nu folosesc indecși. Astfel de întrebări sunt foarte importante în analiză.
• min_examined_row_limit - specifică valoarea minimă pentru numărul de rânduri de date de analizat. O valoare de 1000 va ignora interogările care returnează mai puțin de 1000 de rânduri de valori.

Aceste variabile pot fi setate în fișierul de configurare MySQL, dinamic prin GUI MySQL sau linia de comandă MySQL. Dacă variabilele sunt specificate în fișierul de configurare, serverul le va instala data viitoare când pornește. De obicei, acest fișier se află la /etc, /usr, /etc/my.cnf sau /etc/mysql/my.cnf. Iată comenzile pentru a căuta fișierul de configurare (uneori ar trebui să extindeți căutarea în alte directoare rădăcină):

Găsiți /etc -name my.cnf găsiți /usr -name my.cnf

Când găsiți fișierul, adăugați variabilele necesare în secțiunea:

; ... slow-query-log = 1 slow-query-log-file = /var/log/mysql/localhost-slow.log long_query_time = 1 log-queries-not-using-indexes ; nu este nevoie de sens aici

Modificările vor intra în vigoare numai data viitoare când porniți MySQL dacă trebuie să modificați dinamic parametrii, utilizați alte metode pentru setarea variabilelor:

Mysql> SET GLOBAL slow_query_log = "ON"; mysql> SET GLOBAL slow_query_log_file = "/var/log/mysql/localhost-slow.log"; mysql> SET GLOBAL log_queries_not_using_indexes = "ON"; mysql> SET SESSION long_query_time = 1; mysql> SET SESSION min_examined_row_limit = 100;

Puteți verifica valorile variabilelor după cum urmează:

Mysql> AFIȘAȚI VARIABILELE GLOBALE CA „Slow_query_log”; mysql> AFIȘAȚI VARIABILELE DE SESIUNE CA „long_query_time”;

Principalul dezavantaj al unei instalări dinamice este că valorile se vor pierde la pornirea sistemului. Este recomandat să specificați parametri importanți în configurația MySQL.

Nota: Sintaxa pentru setarea dinamică a parametrilor prin comanda SET și utilizarea unui fișier de configurare este ușor diferită, de exemplu slow_query_log / slow-query-log . În documentația oficială DBMS veți găsi descriere completă sintaxă. Formatul Option-File este folosit pentru fișierul de configurare, System Variable Name - numele variabilelor când instalare dinamică valorile.

Generarea datelor pentru profilarea interogărilor

Am trecut în revistă principalele puncte pentru configurarea profilării, acum vom crea interogările care ne interesează. Acest exemplu a fost folosit pe o alergare Server MySQL fara niciuna presetări jurnal. Exemple de interogări pot fi lansate atât prin intermediul GUI MySQL, cât și prin instrumentele de comandă DBMS. La monitorizarea jurnalului de interogări lente, este obișnuit să deschideți două ferestre cu o conexiune: una pentru a rula interogări, cealaltă pentru a vizualiza jurnalul.

$> mysql -u -p mysql> CREATE DATABASE profile_sampling; mysql> USE profile_sampling; mysql> CREATE TABLE utilizatori (id TINYINT PRIMARY KEY AUTO_INCREMENT, nume VARCHAR(255)); mysql> INSERT INTO users (nume) VALUES ("Walter"),("Skyler"),("Jesse"),("Hank"),("Walter Jr.", ("Marie"),("Saul") "), ("Gustavo"), ("Hector"), ("Mike"); mysql> SET GLOBAL slow_query_log = 1; mysql> SET GLOBAL slow_query_log_file = "/var/log/mysql/localhost-slow.log"; mysql> SET GLOBAL log_queries_not_using_indexes = 1; mysql> SET long_query_time = 10; mysql> SET min_examined_row_limit = 0;

Acum avem o bază de date cu date de testare. Am lansat profilarea, dar am stabilit în mod deliberat timpul de răspuns și numărul de linii să fie mici. Pentru a vizualiza jurnalul utilizați comanda:

Cd /var/log/mysql ls -l

În teorie, fișierul jurnal nu ar trebui să existe încă, deoarece nu am făcut interogări la baza de date. Dacă există, înseamnă că profilarea a fost configurată mai devreme și acest lucru poate distorsiona rezultatele exemplului. Rulați în consolă:

Mysql> USE profile_sampling; mysql> SELECT * FROM utilizatori WHERE id = 1;

Interogarea noastră utilizează indexul cheii primare din tabel. Solicitarea a fost procesată foarte rapid folosind indexul, deci nu ar trebui să fie reflectată în jurnal. Vă rugăm să rețineți că fișierul jurnal nu ar fi trebuit să fie creat.

Acum faceți următoarele:

Mysql> SELECT * FROM utilizatorii WHERE nume = "Jesse";

Aici nu am folosit indici. Ar trebui să vedem acum această solicitare în jurnal:

Sudo cat /var/log/mysql/localhost-slow.log # Time: 140322 13:54:58 # User@Host: root @ localhost # Query_time: 0.000303 Lock_time: 0.000090 Rows_sent: 1 Rows_examined: 10; use profiles: 10 SET timestamp=1395521698; SELECT * FROM utilizatori WHERE nume = "Jesse";

Să ne uităm la un alt exemplu. Ridicați ștacheta pentru numărul de linii din răspuns și rulați următoarea interogare:

Mysql> SET min_examined_row_limit = 100; mysql> SELECT * FROM utilizatori WHERE nume = "Walter";

Solicitarea nu va fi reflectată în jurnal, deoarece nu am depășit 100 de rânduri în răspunsul la cerere.

Nota: Dacă datele nu sunt afișate în jurnal, atunci în primul rând trebuie să luați în considerare următorii factori. Primul este drepturile asupra directorului în care este stocat fișierul jurnal. Grupul și utilizatorul trebuie să corespundă utilizatorului mysqld, drepturile trebuie să fie chmod 755. În al doilea rând, este posibil ca profilarea să fi fost configurată mai devreme. Eliminați toate valorile variabilelor de profilare existente din fișierul de configurare și reporniți serverul sau setați variabilele în mod dinamic. Dacă ați folosit metoda dinamică, veți ieși și vă veți conecta din nou în consola MySQL.

Analiza datelor de profilare a interogărilor

Luați în considerare exemplul de mai sus:

# Time: 140322 13:54:58 # User@Host: root @ localhost # Query_time: 0.000303 Lock_time: 0.000090 Rows_sent: 1 Rows_examined: 10 use profile_sampling; SET timestamp=1395521698; SELECT * FROM utilizatori WHERE nume = "Jesse";

Aici vedem:

• Ora la care a început cererea
• Utilizatorul care a făcut cererea
• Cereri orar de deschidere
• Durata blocării
• Numărul de rânduri selectate
• Numărul de linii analizate

Aceste date sunt foarte utile, deoarece cu ajutorul lor putem găsi și elimina cauza încetinirii sistemului. De asemenea, un dezvoltator sau un administrator MySQL va putea întotdeauna să vadă interogări problematice și aș dori să remarc că găsirea lor aici este mult mai rapidă decât studiind codul programului aplicatii. Cu profilarea pe termen lung, puteți monitoriza condițiile de funcționare la viteză mică.

Folosind mysqldumpslow

Jurnalul înregistrează în mod constant date, de regulă, scrie mult mai mult decât se citește din el. La dimensiune mare log, citirea acestuia devine problematică. MySQL include un instrument numit mysqldumpslow care ajută la menținerea integrității jurnalului. Programul în sine este combinat cu MySQL (on sisteme Linux). Pentru ao utiliza, executați comanda necesară și transmiteți-i calea către fișierul jurnal:

Sudo mysqldumpslow -t 5 -s la /var/log/mysql/localhost-slow.log

Există o serie de parametri care vă ajută să personalizați rezultatul comenzii. În exemplul de mai jos vom vedea ultimele cinci solicitări sortate după durata medie. Ca rezultat, citirea jurnalului devine mult mai convenabilă. (ieșire modificată pentru a afișa valorile reale ale jurnalului):

Count: 2 Time=68.34s (136s) Lock=0.00s (0s) Rows=39892974.5 (79785949), root@localhost SELECT PL.pl_title, P.page_title FROM pagina P INNER JOIN pagelinks PL ON PL.pl_namespace = P. WHERE P.page_namespace = N ...

Ce vedem:

• Număr - numărul de apariții ale cererii în jurnal
• Timp - timpul mediu și total al cererii
• Blocare - timp de blocare a mesei
• Rânduri - Numărul de rânduri selectate

Comanda exclude datele de interogare numerice și șir, adică interogările cu aceeași clauză WHERE vor fi considerate la fel. Datorită acestui instrument, nu trebuie să vă uitați constant la jurnal. Datorită cantitate mare parametrii de comandă, puteți sorta ieșirea după cum doriți. Există, de asemenea, dezvoltări terță parte cu funcționalități similare, de exemplu pt-query-digest.

Solicitați defalcare

Ar trebui să acordați atenție unui alt instrument care vă permite să defalcați interogările complexe. Cel mai adesea trebuie să luați o interogare din jurnal și apoi să o rulați direct în consola MySQL. Mai întâi trebuie să activați profilarea și apoi să rulați interogarea:

Mysql> SET SESSION profiling = 1; mysql> USE profile_sampling; mysql> SELECT * FROM utilizatorii WHERE nume = "Jesse"; mysql> AFIȚI PROFILE;

După activarea profilării, SHOW PROFILES va afișa un tabel care leagă Query_ID și expresia SQL. Găsiți Query_ID-ul corespunzător și rulați următoarea interogare (înlocuiți # cu Query_ID-ul dvs.):

Mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=#;

Exemplu de ieșire:

SEQ STATE DURATION 1 începând cu 0,000046 2 verificare permisiuni 0,000005 3 deschidere tabele 0,000036

STAT- un pas în procesul de executare a unei cereri, DURATĂ- durata pasului în secunde. Acest instrument nu este folosit foarte des, dar uneori poate fi extrem de util pentru a determina cauza performanței lente a interogării.

Descrierea detaliată a coloanelor:

Descrierea detaliată a pașilor:

Nota: Acest instrument nu trebuie utilizat în modul de producție server, cu excepția analizei unor interogări specifice.

Performanță lentă a jurnalului de interogări

Ultima întrebare este modul în care profilarea afectează performanța serverului în ansamblu. În modul de producție al serverului, puteți utiliza în siguranță o astfel de înregistrare, nu ar trebui să afecteze nici CPU, nici I/O. Cu toate acestea, merită să acordați atenție dimensiunii fișierului jurnal, acesta nu ar trebui să devină prohibitiv. De asemenea, aș dori să remarc din experiență că setarea valorii variabilei long_query_time la 1 secundă sau mai mare.

Important: Nu trebuie să utilizați instrumentul de profilare - SET profiling = 1 - pentru a înregistra toate cererile, de exemplu. Nu este recomandat să utilizați variabila general_log în modul produs și sub sarcini mari.

Concluzie

Profilarea interogărilor vă poate ajuta foarte mult la izolarea interogării problematice și la evaluarea performanței generale. Dezvoltatorul poate studia și modul în care funcționează interogările MySQL ale aplicației sale. Instrumentul mysqldumpslow vă ajută să vizualizați și să procesați jurnalele de interogări. După identificarea interogărilor problematice, tot ce rămâne este să le reglați pentru performanță maximă.