monitorizarea metricilor de performanță MySQL

author
17 minutes, 57 seconds Read

această postare face parte 1 dintr-o serie de 3 părți despre monitorizarea MySQL. Partea 2 se referă la colectarea valorilor de la MySQL, iar partea 3 explică modul de monitorizare a MySQL folosind Datadog.

ce este MySQL?

MySQL este cel mai popular server de baze de date relaționale open source din lume. Deținut de Oracle, MySQL este disponibil în ediția comunitară descărcabilă gratuit, precum și în edițiile comerciale cu funcții și suport adăugate. Lansat inițial în 1995, MySQL a generat de atunci furci de profil înalt pentru tehnologii concurente precum MariaDB și Percona.

statistici cheie MySQL

dacă baza dvs. de date rulează lent sau nu reușește să servească interogări din orice motiv, fiecare parte a stivei dvs. care depinde de acea bază de date va suferi și probleme de performanță. Pentru ca baza de date să funcționeze fără probleme, puteți monitoriza activ valorile care acoperă patru domenii de performanță și utilizarea resurselor:

  • query throughput
  • Query execution performance
  • Connections
  • Buffer pool usage

utilizatorii MySQL pot accesa sute de Valori din Baza de date, astfel încât în acest articol ne vom concentra pe o mână de valori cheie care vă vor permite să obțineți o perspectivă în timp real asupra sănătății și performanței bazei de date. În a doua parte a acestei serii vă vom arăta cum să accesați și să colectați toate aceste valori.

acest articol face referire la terminologia metrică introdusă în seria noastră de monitorizare 101, care oferă un cadru pentru colectarea și alertarea metrică.

compatibilitate între versiuni și tehnologii

unele dintre strategiile de monitorizare discutate în această serie sunt specifice versiunilor MySQL 5.6 și 5.7. Diferențele dintre aceste versiuni vor fi evidențiate pe parcurs.

majoritatea metricilor și strategiilor de monitorizare prezentate aici se aplică și tehnologiilor compatibile MySQL, cum ar fi MariaDB și Percona Server, cu unele diferențe notabile. De exemplu, unele dintre caracteristicile din MySQL Workbench, care este detaliat în partea 2 a acestei serii, nu sunt compatibile cu versiunile disponibile în prezent ale MariaDB.

utilizatorii Amazon RDS ar trebui să consulte ghidurile noastre specializate de monitorizare pentru MySQL pe RDS și pentru Amazon Aurora compatibil cu MySQL.

transfer de interogare

interogări MySQL
nume descriere Tip Metric disponibilitate
întrebări număr de declarații executate (trimise de client) lucru:transfer variabilă de stare Server
Com_selectați selectați declarații lucru: transfer variabilă de stare Server
scrie inserează, actualizează sau șterge lucru: Transfer calculat din variabilele de stare ale serverului

preocuparea dvs. principală în monitorizarea oricărui sistem este să vă asigurați că activitatea sa se desfășoară în mod eficient. Activitatea unei baze de date rulează interogări, astfel încât prima dvs. prioritate de monitorizare ar trebui să vă asigurați că MySQL execută interogări așa cum era de așteptat.

MySQL are un contor intern (o „variabilă de stare a serverului”, în limbajul MySQL) numit Questions, care este incrementat pentru toate declarațiile trimise de aplicațiile client. Vizualizarea centrată pe client furnizată de metrica Questions face adesea mai ușor de interpretat decât contorul Queries aferent, care numără și declarațiile executate ca parte a programelor stocate, precum și comenzi precum PREPARE și DEALLOCATE PREPARE rulate ca parte a declarațiilor pregătite pe partea serverului.

pentru a interoga o variabilă de stare a serverului, cum ar fi Questions sau Com_select:

SHOW GLOBAL STATUS LIKE "Questions";+---------------+--------+| Variable_name | Value |+---------------+--------+| Questions | 254408 |+---------------+--------+

de asemenea, puteți monitoriza defalcarea comenzilor de citire și scriere pentru a înțelege mai bine volumul de muncă al bazei de date și pentru a identifica potențialele blocaje. Interogările de citire sunt în general capturate de valoarea Com_select. Scrie increment una dintre cele trei variabile de stare, în funcție de comanda:

scrie = Com_insert + Com_update + Com_delete

Metric pentru a alerta: întrebări

rata actuală de interogări va crește și va scădea în mod natural și, ca atare, nu este întotdeauna o metrică acționabilă bazată pe praguri fixe. Dar merită să alertați cu privire la modificările bruște ale volumului de interogare—scăderile drastice ale debitului, în special, pot indica o problemă serioasă.

performanța interogării

Grafic de latență MySQL
nume descriere Tip Metric disponibilitate
timp de rulare interogare timp mediu de rulare, pe schemă lucru: performanță interogare schemă performanță
erori de interogare Numărul de instrucțiuni SQL care au generat erori lucru: Eroare interogare schemă de performanță
Slow_queries numărul de interogări care depășesc configurabil long_query_time limita lucru:performanță variabilă de stare Server

utilizatorii MySQL au o serie de opțiuni pentru monitorizarea latenței interogării, atât prin utilizarea metricilor încorporate MySQL, cât și prin interogarea schemei de performanță. Activat în mod implicit de la MySQL 5.6.6, tabelele bazei de date performance_schema din MySQL stochează statistici de nivel scăzut despre evenimentele serverului și execuția interogării.

performance schema statement digest

multe valori cheie sunt conținute în tabelul events_statements_summary_by_digest al schemei de performanță, care captează informații despre latență, erori și volumul interogării pentru fiecare instrucțiune normalizată. Un rând eșantion din tabel arată o declarație care a fost rulat de două ori și care a luat 325 milisecunde, în medie, pentru a executa (toate măsurătorile timer sunt în picosecunde):

*************************** 1. row *************************** SCHEMA_NAME: employees DIGEST: 0c6318da9de53353a3a1bacea70b4fce DIGEST_TEXT: SELECT * FROM `employees` WHERE `emp_no` > ? COUNT_STAR: 2 SUM_TIMER_WAIT: 650358383000 MIN_TIMER_WAIT: 292045159000 AVG_TIMER_WAIT: 325179191000 MAX_TIMER_WAIT: 358313224000 SUM_LOCK_TIME: 520000000 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 520048 SUM_ROWS_EXAMINED: 520048... SUM_NO_INDEX_USED: 0 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2016-03-24 14:25:32 LAST_SEEN: 2016-03-24 14:25:55

tabelul digest normalizează toate declarațiile (așa cum se vede în câmpul DIGEST_TEXT de mai sus), ignorând valorile datelor și standardizând spațiul alb și capitalizarea, astfel încât următoarele două interogări să fie considerate aceleași:

select * from employees where emp_no >200;SELECT * FROM employees WHERE emp_no > 80000;

pentru a extrage o durată medie de execuție per schemă în microsecunde, puteți interoga schema de performanță:

SELECT schema_name , SUM(count_star) count , ROUND( (SUM(sum_timer_wait) / SUM(count_star)) / 1000000) AS avg_microsec FROM performance_schema.events_statements_summary_by_digest WHERE schema_name IS NOT NULL GROUP BY schema_name;+--------------------+-------+--------------+| schema_name | count | avg_microsec |+--------------------+-------+--------------+| employees | 223 | 171940 || performance_schema | 37 | 20761 || sys | 4 | 748 |+--------------------+-------+--------------+

în mod similar, pentru a număra numărul total de declarații pe schemă care au generat erori:

SELECT schema_name , SUM(sum_errors) err_count FROM performance_schema.events_statements_summary_by_digest WHERE schema_name IS NOT NULL GROUP BY schema_name;+--------------------+-----------+| schema_name | err_count |+--------------------+-----------+| employees | 8 || performance_schema | 1 || sys | 3 |+--------------------+-----------+

schema sys

interogarea schemei de performanță așa cum se arată mai sus funcționează excelent pentru preluarea programatică a valorilor din Baza de date. Cu toate acestea, pentru interogări și investigații ad hoc, este de obicei mai ușor să utilizați schema SYS a MySQL. Schema sys oferă un set organizat de valori într-un format mai ușor de citit de om, făcând interogările corespunzătoare mult mai simple. De exemplu, pentru a găsi cele mai lente declarații (cele din percentila 95 în timpul rulării):

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

sau pentru a vedea ce declarații normalizate au generat erori:

SELECT * FROM sys.statements_with_errors_or_warnings;

multe alte exemple utile sunt detaliate în documentația schemei sys. Schema sys este inclusă în MySQL începând cu versiunea 5.7.7, dar utilizatorii MySQL 5.6 o pot instala cu doar câteva comenzi. A se vedea partea 2 din această serie pentru instrucțiuni.

interogări lente

pe lângă bogăția de date de performanță disponibile în schema de performanță și schema sys, MySQL are un contor Slow_queries, care crește de fiecare dată când timpul de execuție al unei interogări depășește numărul de secunde specificat de parametrul long_query_time. Pragul este setat la 10 secunde în mod implicit:

SHOW VARIABLES LIKE 'long_query_time';+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+

parametrul long_query_time poate fi ajustat cu o singură comandă. De exemplu, pentru a seta pragul de interogare lent la cinci secunde:

SET GLOBAL long_query_time = 5;

(rețineți că poate fi necesar să închideți sesiunea și să vă reconectați la baza de date pentru ca modificarea să fie aplicată la nivel de sesiune.)

investigarea problemelor de performanță a interogării

dacă interogările dvs. se execută mai lent decât se aștepta, este adesea cazul în care o interogare modificată recent este vinovatul. Dacă nicio interogare nu este determinată a fi prea lentă, următoarele lucruri de evaluat sunt valorile la nivel de sistem pentru a căuta constrângeri în resursele de bază (CPU, i/o pe disc, memorie și rețea). Saturația procesorului și blocajele I/O sunt vinovați comuni. De asemenea, puteți verifica valoarea Innodb_row_lock_waits, care contează cât de des a trebuit să aștepte motorul de stocare InnoDB pentru a obține o blocare pe un anumit rând. InnoDB a fost motorul de stocare implicit de la MySQL versiunea 5.5, iar MySQL folosește blocarea la nivel de rând pentru tabelele InnoDB.

pentru a crește viteza operațiilor de citire și scriere, mulți utilizatori vor dori să regleze dimensiunea bazinului tampon utilizat de InnoDB pentru a memora în cache datele din tabel și index. Mai multe despre monitorizarea și redimensionarea bazinului tampon de mai jos.

metrici pentru a alerta pe

  • timp de rulare interogare: gestionarea latență pentru bazele de date cheie este critică. Dacă timpul mediu de execuție pentru interogări într-o bază de date de producție începe să crească, căutați constrângeri de resurse în instanțele bazei de date, posibile dispute pentru blocări de rând sau de tabel și modificări ale modelelor de interogare din partea clientului.
  • erori de interogare: O creștere bruscă a erorilor de interogare poate indica o problemă cu aplicația dvs. client sau cu baza de date în sine. Puteți utiliza schema sys pentru a explora rapid ce interogări pot cauza probleme. De exemplu, pentru a lista cele 10 declarații normalizate care au returnat cele mai multe erori:
      SELECT * FROM sys.statements_with_errors_or_warnings ORDER BY errors DESC LIMIT 10;  
  • Slow_queries: modul în care definiți o interogare lentă (și, prin urmare, modul în care configurați parametrul long_query_time) depinde de cazul de utilizare. Oricare ar fi definiția dvs. de „lent”, probabil că veți dori să investigați dacă numărul de interogări lente crește peste nivelurile de bază. Pentru a identifica interogările reale care se execută încet, puteți interoga schema sys sau vă puteți scufunda în Jurnalul opțional de interogare lentă MySQL, care este dezactivat în mod implicit. Mai multe informații despre activarea și accesarea jurnalului de interogare lentă sunt disponibile în documentația MySQL.

conexiuni

conexiuni MySQL
nume descriere Tip Metric disponibilitate
Threads_connected conexiuni deschise în prezent resursă: Utilizare variabilă de stare Server
Threads_running execută în prezent conexiuni resursă: utilizare variabilă de stare Server
Connection_errors_ intern Numărul de conexiuni refuzate din cauza erorii serverului resursă: eroare variabilă de stare Server
Aborted_connects Numărul de încercări de conectare eșuate la server resursă: Eroare variabilă de stare Server
Connection_errors_ max_connections Numărul de conexiuni refuzate din cauza max_connections limită resursă: eroare variabilă de stare Server

verificarea și setarea limitei de conexiune

monitorizarea conexiunilor client este esențială, deoarece odată ce ați epuizat conexiunile disponibile, noile conexiuni client vor fi refuzate. Limita conexiunii MySQL este implicită la 151, dar poate fi verificată cu o interogare:

SHOW VARIABLES LIKE 'max_connections';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections | 151 |+-----------------+-------+

documentația MySQL sugerează că serverele robuste ar trebui să poată gestiona conexiuni în sute sau mii mari:

„Linux sau Solaris ar trebui să poată suporta 500 până la 1000 de conexiuni simultane în mod obișnuit și până la 10.000 de conexiuni dacă aveți multe gigabyte de RAM disponibile și volumul de muncă de la fiecare este scăzut sau timpul de răspuns țintă nesolicitat. Windows este limitat la (tabele deschise 2 + conexiuni deschise) < 2048 datorită stratului de compatibilitate Posix utilizat pe acea platformă.”

limita de conectare poate fi reglată din mers:

SET GLOBAL max_connections = 200;

această setare va reveni la valoarea implicită atunci când serverul repornește. Pentru a seta permanent limita de conexiune, adăugați o linie ca aceasta la fișierul de configurare my.cnf (consultați această postare pentru ajutor în localizarea fișierului de configurare):

max_connections = 200

monitorizarea utilizării conexiunii

MySQL expune o Threads_connected fire de conectare metrice de numărare—un fir pe conexiune. Monitorizând această valoare alături de limita de conexiune configurată, vă puteți asigura că aveți suficientă capacitate pentru a gestiona conexiuni noi. MySQL expune, de asemenea, metrica Threads_running pentru a izola care dintre aceste fire procesează în mod activ interogări la un moment dat, spre deosebire de conexiunile care sunt deschise, dar sunt în prezent inactive.

dacă serverul dvs. atinge limita max_connections, va începe să refuze conexiunile. În acest caz, metrica Connection_errors_max_connections va fi incrementată, la fel ca și metrica Aborted_connects urmărirea tuturor încercărilor de conectare eșuate.

MySQL expune o varietate de alte valori privind erorile de conectare, care vă pot ajuta să investigați problemele de conexiune. Metrica Connection_errors_internal este una bună de urmărit, deoarece este incrementată numai atunci când eroarea vine de la serverul însuși. Erorile interne pot reflecta o condiție în afara memoriei sau incapacitatea serverului de a porni un fir nou.

metrici pentru a alerta pe

  • Threads_connected: dacă un client încearcă să se conecteze la MySQL atunci când toate conexiunile disponibile sunt în uz, MySQL va returna o eroare „prea multe conexiuni” și va crește Connection_errors_max_connections. Pentru a preveni acest scenariu, ar trebui să monitorizați numărul de conexiuni deschise și să vă asigurați că acesta rămâne în siguranță sub limita configurată max_connections.
  • Aborted_connects: dacă acest contor crește, clienții dvs. încearcă și nu reușesc să se conecteze la baza de date. Investigați sursa problemei cu valori de conectare cu granulație fină, cum ar fi Connection_errors_max_connections și Connection_errors_internal.

utilizarea bazinului tampon

MySQL buffer pool utilizare
nume descriere Tip Metric disponibilitate
Innodb_buffer_pool_pages_total numărul Total de pagini din rezerva tampon resursă: Utilizare variabilă de stare Server
utilizarea Buffer pool raportul dintre paginile utilizate și totalul paginilor din buffer pool resursă: utilizare calculată din variabilele de stare ale serverului
Innodb_buffer_pool_read_requests cereri făcute la piscina tampon resursă: utilizare variabilă de stare Server
Innodb_buffer_pool_reads solicită piscina tampon nu a putut îndeplini resursă: Saturație variabilă de stare a serverului

motorul de stocare implicit MySQL, InnoDB, utilizează o zonă de memorie numită buffer pool pentru a memora în cache date pentru tabele și indexuri. Valorile Buffer pool sunt valori ale resurselor, spre deosebire de valorile de lucru și, ca atare, sunt utile în primul rând pentru investigarea (mai degrabă decât detectarea) problemelor de performanță. Dacă performanța bazei de date începe să alunece în timp ce I/o-ul discului crește, extinderea bazinului tampon poate oferi adesea beneficii.

dimensionarea pool-ului tampon

pool-ul tampon este implicit la un număr relativ mic de 128 mebibytes, dar MySQL recomandă să îl puteți mări până la 80% din memoria fizică pe un server de baze de date dedicat. MySQL adaugă, de asemenea, câteva note de precauție, totuși, deoarece cheltuielile de memorie ale InnoDB pot crește amprenta de memorie cu aproximativ 10 la sută dincolo de dimensiunea rezervei tampon alocate. Și dacă rămâneți fără memorie fizică, sistemul dvs. va recurge la paginare și performanța va suferi semnificativ.

piscina tampon, de asemenea, pot fi împărțite în regiuni separate, cunoscute sub numele de instanțe. Utilizarea mai multor instanțe poate îmbunătăți concurența pentru grupurile tampon din gama multi-GiB.

operațiunile de redimensionare Buffer-pool sunt efectuate în bucăți, iar dimensiunea buffer pool trebuie setată la un multiplu al dimensiunii Buck de câte ori numărul de instanțe:

innodb_buffer_pool_size = N * innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

dimensiunea bucată implicit la 128 MiB, dar este configurabil ca de MySQL 5.7.5. Valoarea ambilor parametri poate fi verificată după cum urmează:

SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_chunk_size";SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_instances";

dacă interogarea innodb_buffer_pool_chunk_size nu returnează niciun rezultat, parametrul nu este acordabil în versiunea dvs. de MySQL și se poate presupune că este de 128 MiB.

pentru a seta dimensiunea buffer pool și numărul de instanțe la pornirea serverului:

$ mysqld --innodb_buffer_pool_size=8G --innodb_buffer_pool_instances=16

începând cu MySQL 5.7.5, puteți redimensiona, de asemenea, piscina tampon din mers printr-o comandă SET specificând dimensiunea dorită în octeți. De exemplu, cu două instanțe buffer pool, puteți seta fiecare la dimensiunea 4 GiB setând dimensiunea totală la 8 GiB:

SET GLOBAL innodb_buffer_pool_size=8589934592;

cheie InnoDB buffer pool metrics

MySQL expune o mână de valori pe buffer pool și utilizarea acestuia. Unele dintre cele mai utile sunt valorile care urmăresc dimensiunea totală a bazinului tampon, cât de mult este utilizat și cât de eficient servește piscina tampon.

valorile Innodb_buffer_pool_read_requests și Innodb_buffer_pool_reads sunt esențiale pentru înțelegerea utilizării buffer pool. Innodb_buffer_pool_read_requests urmărește numărul de cereri de citire logică, în timp ce Innodb_buffer_pool_reads urmărește numărul de cereri pe care piscina tampon nu le-a putut satisface și, prin urmare, a trebuit să fie citită de pe disc. Având în vedere că citirea din memorie este, în general, ordine de mărime mai rapidă decât citirea de pe disc, performanța va avea de suferit dacă Innodb_buffer_pool_reads începe să urce.

utilizarea Buffer pool este o valoare utilă pentru a verifica înainte de a lua în considerare redimensionarea buffer pool. Metrica de utilizare nu este disponibilă din cutie, dar poate fi ușor calculată după cum urmează:

(Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free) / Innodb_buffer_pool_pages_total

dacă baza dvs. de date servește un număr mare de citiri de pe disc, dar piscina tampon este departe de a fi plină, este posibil ca memoria cache să fi fost curățată recent și să se încălzească în continuare. Dacă piscina dvs. tampon nu se umple, dar servește în mod eficient citirile, setul dvs. de date de lucru se potrivește probabil confortabil în memorie.

utilizarea ridicată a bazinului tampon, pe de altă parte, nu este neapărat un lucru rău izolat, deoarece datele vechi sau neutilizate sunt îmbătrânite automat din cache folosind o politică LRU. Dar dacă piscina tampon nu vă servește în mod eficient volumul de lucru citit, poate fi timpul să vă măriți memoria cache.

conversia valorilor rezervei tampon în octeți

majoritatea valorilor rezervei tampon sunt raportate ca număr de pagini de memorie, dar aceste valori pot fi convertite în octeți, ceea ce facilitează conectarea acestor valori cu dimensiunea reală a rezervei tampon. De exemplu, pentru a găsi dimensiunea totală a buffer pool-ului în octeți folosind variabila de stare server urmărirea paginilor totale din buffer pool:

Innodb_buffer_pool_pages_total * innodb_page_size

dimensiunea paginii InnoDB este reglabilă, dar implicit este de 16 KiB sau 16.384 octeți. Valoarea sa curentă poate fi verificată cu o interogare SHOW VARIABLES :

SHOW VARIABLES LIKE "innodb_page_size";

concluzie

în această postare am explorat câteva dintre cele mai importante valori pe care ar trebui să le monitorizați pentru a păstra filele privind activitatea și performanța MySQL. Dacă vă construiți monitorizarea MySQL, captarea valorilor prezentate mai jos vă va pune pe calea către înțelegerea modelelor de utilizare a bazei de date și a constrângerilor potențiale. De asemenea, vă vor ajuta să identificați când este necesar să scalați sau să mutați instanțele bazei de date către gazde mai puternice pentru a menține performanțe bune ale aplicației.

  • query throughput
  • interogare latență și erori
  • conexiuni Client și erori
  • Buffer pool utilizare

Partea 2 din această serie oferă instrucțiuni pentru colectarea și monitorizarea tuturor valorilor de care aveți nevoie de la MySQL.

mulțumiri

Multe mulțumiri lui Dave Stokes de la Oracle și Ewen Fortune de la VividCortex pentru furnizarea de feedback valoros cu privire la acest articol înainte de publicare.

Similar Posts

Lasă un răspuns

Adresa ta de email nu va fi publicată.