Reglarea variabilelor sistemului MySQL pentru performanțe ridicate

Pentru majoritatea dezvoltatorilor de aplicații, baza de date este un altar al zeilor demonilor lăsat cel mai bine neaprobat. Dar nu trebuie să fie așa!


Alte lucruri fiind egale, nivelul de confort pe care un dezvoltator îl are cu baza de date de bază îi definește nivelul de vechime. Putina baza de date si putina experienta de codare = dezvoltator junior; puțină bază de date și experiență bună de codificare = dezvoltator de nivel mediu; bază de date bună și experiență bună de codificare = dezvoltator senior.

Este o realitate aspră, care chiar devs cu 6-8 ani în lupta lor centura pentru a explica complicațiile optimizatorului de interogare și preferă să privească spre cer atunci când a fost întrebat despre ajustarea bazelor de date.

De ce?

În mod surprinzător, motivul nu este lenea (deși în unele părți este).

Ideea este că bazele de date sunt o forță proprie de a lupta. Chiar și în mod tradițional, când nu existau decât tipurile relaționale de baze de date care să se ocupe, stăpânirea lor a fost o cale miracolă și de carieră în sine; în aceste zile, avem atât de multe tipuri de baze de date, încât este imposibil să ne așteptăm ca un singur suflet muritor să stăpânească totul.

Acestea fiind spuse, există șanse mari să fiți încă mulțumit de bazele de date relaționale sau să faceți parte dintr-o echipă care are un produs care rulează într-o bază de date relațională satisfăcător timp îndelungat și lung. Și în nouă cazuri din zece, sunteți pe MySQL (sau MariaDB). Pentru aceste cazuri, scufundările doar puțin mai adânci sub capotă oferă beneficii masive în creșterea performanței aplicației și merită învățat puțin.

Curios? Să ne scufundăm!

Nu e curios? Ei bine, scufundați-vă în orice caz, pentru că cariera dvs. depinde de asta! ��

Optimizați memoria cache MySQL

Aproape toată optimizarea în domeniul calculatoarelor se reduce la memorie în cache. Într-un capăt, CPU menține mai multe niveluri de memorie cache pentru a accelera calculele sale, iar pe celălalt, aplicațiile web folosesc în mod agresiv soluții caching ca Redis pentru a obține rezultate precomputate pentru utilizatori, mai degrabă decât să lovească de fiecare dată în baza de date..

Dar hei, chiar și slaba bază de date MySQL are propriul cache de interogare! Adică de fiecare dată când întrebați ceva și datele sunt încă neprevăzute, MySQL va servi aceste rezultate în memorie în loc să rulați interogarea din nou, făcând aplicația ridicol mai rapidă.

Puteți verifica dacă aveți cache de interogare disponibil (notă, disponibilă, neactivată) în baza de date rulând această interogare în consola bazei de date:

MariaDB [(nici unul)]> ARĂTAȚI VARIABILILE CA „have_query_cache”;
+——————+——-+
| Numele variabilului | Valoare |
+——————+——-+
| au_query_cache | DA |
+——————+——-+

Așadar, puteți vedea că eu execut MariaDB și că am cache de interogare disponibilă pentru a fi activată. Este extrem de puțin probabil să îl fiți oprit dacă utilizați o instalare standard MySQL.

Acum să vedem dacă am activat cache-ul de interogare:

MariaDB [(nici unul)]> ARĂTAȚI VARIABILILE LIKE „query_cache_type”;
+——————+——-+
| Numele variabilului | Valoare |
+——————+——-+
| query_cache_type | ON |
+——————+——-+

Da, o iau. Dar în caz că nu, îl puteți activa spunând:

MariaDB [(nici unul)]> SET GLOBAL query_cache_type = ON;

Interesant este că această variabilă acceptă și o a treia valoare care denotă „la cerere”, ceea ce înseamnă că MySQL va memora în cache doar acele întrebări pe care le spunem, dar nu vom intra aici.

Cu aceasta, ați activat interogarea în cache și ați făcut primul pas către o configurație MySQL mai robustă! Spun primul pas pentru că, deși îl activează este o îmbunătățire majoră, trebuie să ajustăm memoria cache pentru a se potrivi cu configurarea noastră. Deci, să învățăm să facem asta.

Cealaltă variabilă de interes aici este query_cache_size, a cărei funcție este explicativă:

MariaDB [(nici unul)]> ARĂTAȚI VARIABILI CA „query_cache_size”;
+——————+———-+
| Numele variabilului | Valoare |
+——————+———-+
| query_cache_size | 16777216 |
+——————+———-+

Deci, am o memorie cache de dimensiuni de aproximativ 16 MB. Rețineți că, chiar și dacă este activată memoria cache, dar această dimensiune este zero, memoria cache este dezactivată efectiv. De aceea, verificarea unei singure variabile nu este suficientă. Acum, ar trebui să setați o dimensiune de cache de interogare, dar cât de mult ar trebui să fie? În primul rând, rețineți că funcția de memorie în cache a interogărilor va avea nevoie de ea însăși de 4 KB pentru a stoca metadatele sale, deci orice selectați trebuie să fie peste aceasta.

Să presupunem că setați dimensiunea cache-ului de interogare la 500 KB:

MariaDB [(nici unul)]> SET GLOBAL query_cache_size = 500000;

Face acest lucru destul de mult? Ei bine, nu, pentru că modul în care motorul de interogare va ajunge să funcționeze de fapt depinde de alte câteva lucruri:

  • În primul rând, variabila query_cache_size trebuie să fie suficient de mare pentru a vă menține rezultatul interogărilor. Dacă este prea mic, nimic nu va fi pus în cache.
  • În al doilea rând, dacă query_cache_size este setat la un număr prea mare, vor exista două tipuri de probleme: 1) Motorul va trebui să efectueze lucrări suplimentare pentru stocarea și localizarea rezultatelor interogării în această zonă de memorie masivă. 2) Dacă majoritatea interogărilor au dimensiuni mult mai mici, memoria cache se va fragmenta, iar beneficiile utilizării unei memorii cache se vor pierde..

De unde știi că cache-ul se fragmentează? Verificați numărul total de blocuri din cache astfel:

MariaDB [(nici unul)]> arată starea de genul „Qcache_total_blocks”;
+———————+——-+
| Numele variabilului | Valoare |
+———————+——-+
| Qcache_total_blocks | 33 |
+———————+——-+

Dacă numărul este foarte mare, memoria cache este fragmentată și trebuie curățată.

Deci, pentru a evita aceste probleme, asigurați-vă că dimensiunea query_cache_size este aleasă cu înțelepciune. Dacă te simți frustrat că nu te-am lăsat cu un număr concret aici, mi-e teamă că așa stau lucrurile odată ce treci în trecut și îți dai pasul în inginerie. Trebuie să consultați aplicația pe care o executați și să vedeți care sunt dimensiunile de interogare pentru rezultatele interogării importante și apoi să setați acest număr. Și chiar atunci s-ar putea să sfârșești făcând o greșeală. ��

Filetarea, grupurile de așteptare, așteptarea și expirarea timpului

Probabil că aceasta este partea cea mai interesantă a modului în care funcționează MySQL și a-l înțelege corect înseamnă a-ți face aplicația de câteva ori mai rapidă!

Filetat

MySQL este un server cu mai multe fire. Asta înseamnă că, de fiecare dată când există o nouă conexiune la serverul MySQL, deschide un nou thread cu datele de conectare și îi transmite clientului un mâner (doar în cazul în care vă întrebați ce este un thread, consultați acest). Clientul trimite apoi toate întrebările peste acest thread și primește rezultate. Acest lucru ne duce să punem o întrebare firească: câte fire pot învârti MySQL? Răspunsul se află în următoarea secțiune.

Piscina de fire

Niciun program dintr-un sistem informatic nu poate deschide atâtea fire cât dorește. Motivul este dublu: 1) Memoria costurilor firelor (RAM), iar sistemul de operare pur și simplu nu vă va permite să mergeți în jos și să mâncați toate. 2) Gestionarea, să zicem, a unui milion de fire este o sarcină masivă pe cont propriu, iar dacă serverul MySQL ar putea crea atât de multe fire, acesta ar muri încercând să se ocupe cu capul general.

Pentru a evita aceste probleme, MySQL vine cu un pool de thread – un număr fix de fire care fac parte dintr-un pool la început. Noile solicitări de conexiune determină MySQL să ridice unul dintre aceste thread-uri și să returneze datele de conexiune, iar dacă toate firele sunt folosite, conexiunile noi sunt refuzate în mod natural. Să vedem cât de mare este poolul de fire:

ariaDB [(nici unul)]> arată variabile precum „thread_pool_size”;
+——————+——-+
| Numele variabilului | Valoare |
+——————+——-+
| thread_pool_size | 4 |
+——————+——-+

Așadar, mașina mea permite maxim patru conexiuni în același timp. Este interesant de remarcat faptul că numărul 4 provine de la faptul că am un procesor cu patru nuclee, ceea ce înseamnă că computerul meu poate rula doar 4 sarcini paralele simultan (vorbesc aici de activități cu adevărat paralele, nu de cele concurente). În mod ideal, aceasta este limita care ar trebui să conducă valoarea thread_pool_size, dar pe mașinile cu beefier în creștere, beneficiază până la un punct. Dacă nu doriți să faceți ca toate conexiunile noi să aștepte și sunteți bine să vă atingeți de performanță (din nou, aceasta este o zonă pe care o puteți judeca cel mai bine în funcție de performanța aplicației dvs. sub sarcină), o creștere până la 8 poate fi o idee bună.

Totuși, setarea acesteia peste 16 este o idee groaznică, dacă nu aveți o mașină cu 32 de nuclee, întrucât performanța se degradează semnificativ. Gaura iepurelui din bazinele de fir din MySQL merge adânc, dar dacă sunteți interesat, aici e o discuție mai detaliată.

Așteptare și expirare

După ce un fir a fost creat și atașat unui client, ar fi pierdut resurse dacă clientul nu a trimis întrebări în următoarele câteva secunde (sau minute). Drept urmare, MySQL încheie o conexiune după o perioadă de inactivitate. Aceasta este controlată de variabila wait_timeout:

MariaDB [(nici unul)]> arată variabile precum „aștepta%”;
+—————+——-+
| Numele variabilului | Valoare |
+—————+——-+
| wait_timeout | 28800 |
+—————+——-+

Valoarea rezultată este în câteva secunde. Așadar, da, în mod implicit MySQL este setat să aștepte 8+ ore înainte de a salva cablul! Acest lucru poate fi bun dacă aveți întrebări de lungă durată și doriți de fapt să le așteptați (dar chiar și atunci, opt ore este absurd!), Dar groaznic în majoritatea cazurilor. Când este executată o interogare, această valoare este setată la 0 (adică pentru totdeauna), dar, în general, aceasta ar trebui să fie setată la o valoare foarte mică (5 secunde, de exemplu, sau poate chiar mai puțin) pentru a elibera conexiunea pentru alte procese..

Reglarea tabelelor temporare

Să începem cu ce sunt tabelele temporare în MySQL.

Să presupunem că avem un MySQL care arată structural astfel: TABLA UNIUNE (TABELUL B INNER JOIN C). Adică, suntem interesați să unim tabelele B și C, apoi să executăm o unire a rezultatului cu tabelul A. Acum, MySQL ar proceda mai întâi la alăturarea tabelelor B și C, dar înainte de a putea efectua o uniune, trebuie pentru a stoca aceste date undeva. Aici intră tabele temporare – MySQL le folosește pentru a stoca date în etape intermediare în interogări complexe temporar și, odată terminată interogarea, această tabelă temporară este eliminată..

Acum întrebarea este: de ce să ne deranjăm cu toate acestea?

Pur și simplu, deoarece tabelul temporar, doar un rezultat al interogării, este date care sunt utilizate de MySQL în calcul, iar viteza de acces a acesteia (printre alte limitări) va determina cât de rapid este executată interogarea. De exemplu, stocarea tabelului temporar în memoria RAM va fi de câteva ori mai rapidă decât stocarea ei pe disc.

Există două variabile care controlează acest comportament:

MariaDB [(nici unul)]> arată variabile precum „MariaDB [(niciuna)]> afișează variabile precum „tmp_table_size”;
+—————-+———-+

| Numele variabilului | Valoare |

+—————-+———-+

| tmp_table_size | 16777216 |

+—————-+———-+
„;
+———————+———-+
| Numele variabilului | Valoare |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(nici unul)]> afișează variabile precum „tmp_table_size”;
+—————-+———-+
| Numele variabilului | Valoare |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

Primul, max_heap_table_size, ne spune câtă RAM poate fi folosită de o tabel MySQL („heap” aici se referă aici la structura de date utilizată în alocarea și gestionarea RAM – citiți mai mult aici), în timp ce al doilea, tmp_table_size, arată care este dimensiunea maximă a tabelului temporar. În cazul meu, ambele sunt setate la 16 MB, deși punctul pe care încerc să-l fac ca creșterea doar tmp_table_size nu va funcționa la fel de general, MySQL ar fi în continuare limitat de max_table_heap_size.

Acum vine ideea: dacă tabelele temporare care sunt create sunt mai mari decât limita permisă de aceste variabile, MySQL ar fi obligat să le scrie pe hard disk, rezultând performanțe extrem de slabe. Treaba noastră acum este simplă: faceți tot posibilul pentru a ghici cea mai exactă dimensiune a datelor pentru tabelele temporare și a modifica aceste variabile la această limită. Cu toate acestea, aș dori să aveți precauție împotriva absurdului: să setați această limită la 16 GB (presupunând că aveți această memorie RAM) atunci când majoritatea tabelelor dvs. temporare au o dimensiune mai mică de 24 MB este o prostie – pur și simplu pierdeți RAM care ar putea ” am fost utilizate de alte interogări sau părți ale sistemului (cache, de exemplu).

Concluzie

Nu este posibil să acoperi toate variabilele de sistem dintr-un singur articol sau chiar toate cele importante dintr-un articol atunci când documentația MySQL în sine se întinde pe câteva mii de cuvinte. În timp ce am acoperit câteva variabile universale aici, te-aș încuraja să analizezi variabilele de sistem pentru motorul pe care îl folosești (InnoDB sau MyISAM).

Rezultatul meu cel mai de dorit pentru a scrie acest articol este să vă luați trei lucruri:

  1. MySQL este un software tipic care funcționează în limitele stabilite de sistemul de operare. Nu este un program misterios care să știe Dumnezeu și ce este imposibil de îmblânzit. De asemenea, din fericire, nu este atât de dificil să înțelegem cum este configurată și controlată de variabilele sale de sistem.
  2.  Nu există o setare unică care să facă instalarea MySQL să mărească. Nu ai de ales decât să te uiți în sistemele tale de rulare (nu uita, optimizarea vine după ce aplicația este în producție, nu înainte), fă cele mai bune ghiciri și măsurători și trăiește cu realitatea că nu va fi niciodată perfectă.
  3. Reglarea variabilelor nu este singura modalitate de a optimiza MySQL – interogări de scriere eficiente este un alt lucru important, dar este ceva ce voi aborda într-un alt articol. Dar ideea este că, chiar dacă ați făcut o analiză dumnezeiască și ați ajustat cât mai bine acești parametri, este totuși posibil să duceți totul la capăt.

Care este variabila dvs. de sistem preferată pentru reglare? ��

ETICHETE:

  • Bază de date

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me
    Like this post? Please share to your friends:
    Adblock
    detector
    map