Ugađanje varijabli MySQL sustava za visoke performanse

Za većinu programera aplikacija baza podataka je oltar demonskih bogova koje je najbolje ostaviti neprimjerenima. Ali ne mora biti tako!


Ako su podjednake, razina udobnosti koju programer ima s osnovnom bazom podataka definira razinu njihovog staža. Malo baze podataka i malo iskustva s kodiranjem = mlađi programer; malo baze podataka i dobro iskustvo kodiranja = programer na srednjoj razini; dobra baza podataka i dobro iskustvo kodiranja = stariji programer.

Teška je stvarnost da se čak i vragovi sa 6-8 godina pod pojasevima pokušavaju objasniti sitnice alata za optimizaciju upita i radije gledaju u nebo kada ih pitaju o podešavanje baze podataka.

Zašto?

Iznenađujuće, razlog nije lijenost (iako je u nekim dijelovima i to).

Poanta je u tome da su baze podataka same po sebi protivne snage. Čak i tradicionalno, kada su se radile samo relacijske vrste baza podataka, savladavanje istih bilo je čudo i put karijere na svojstven način; ovih dana imamo toliko vrsta baza podataka da je jednostavno nemoguće očekivati ​​da će jedna, smrtna duša svladati sve.

U skladu s tim, postoji velika vjerojatnost da ste i dalje zadovoljni s relacijskim bazama podataka ili ste dio tima koji ima proizvod koji na relacijskoj bazi podataka zadovoljava dugo, dugo vremena. A u devet od deset slučajeva na MySQL ste (ili MariaDB). U tim slučajevima, ronjenje samo malo dublje ispod haube donosi ogromne prednosti u poboljšanju performansi aplikacije i svako malo vrijedi učiti.

Znatiželjan? Zaronimo!

Nije znatiželjno? Pa, zaronite ionako, jer o tome ovisi vaša karijera! ��

Optimizirajte MySQL keš upita

Gotovo sva optimizacija u području računala svodi se na predmemoriranje. Na jednom kraju, CPU održava nekoliko razina predmemorije kako bi ubrzao svoje izračune, a na drugom, web aplikacije agresivno koriste rješenja za predmemoriranje poput Redisa za posluživanje unaprijed izračunatih rezultata korisnicima, a ne udaranje u bazu podataka svaki put.

Ali hej, čak i siromašna MySQL baza podataka ima vlastiti predmemoriju upita! To jest, svaki put kada nešto zatražite, a podaci su i dalje neiskorišteni, MySQL će poslužiti ove кеhirane rezultate umjesto da ponovo izvrši upit, čineći aplikaciju smiješnom bržom.

Možete provjeriti imate li u vašoj bazi podataka predmemoriju upita (napomena, dostupno, nije omogućeno) pokretanjem ovog upita u konzoli baze podataka:

MariaDB [(nema)]> POKAŽI VARIABLES LIKE ‘have_query_cache’;
+——————+——-+
| Ime varijable | Vrijednost |
+——————+——-+
| have_query_cache | DA |
+——————+——-+

Dakle, možete vidjeti da vodim MariaDB i da imam na raspolaganju keširanje upita koje treba uključiti. Izuzetno je malo vjerovatno da biste ga isključili ako upotrebljavate standardnu ​​instalaciju MySQL.

A sada da vidimo je li zapravo uključen predmemorija upita:

MariaDB [(nema)]> POKAŽI VARIABLES LIKE ‘query_cache_type’;
+——————+——-+
| Ime varijable | Vrijednost |
+——————+——-+
| query_cache_type | ON |
+——————+——-+

Da jesam. Ali u slučaju da nemate, možete ga uključiti govoreći:

MariaDB [(nema)]> SET GLOBAL query_cache_type = UKLJUČEN;

Zanimljivo je da i ova varijabla prihvaća treću vrijednost koja označava “na zahtjev”, što znači da će MySQL predmemorirati samo one upite na koje mu kažemo, ali nećemo u njih ulaziti ovdje.

S ovim putem imate spremanje uputa i napravili ste prvi korak ka snažnijoj MySQL postavci! Kažem prvi korak jer iako smo ga uključili je veliko poboljšanje, trebamo prilagoditi keširanje upita prema našem postavljanju. Dakle, naučimo to raditi.

Druga varijabla koja nas ovdje zanima je query_cache_size, čija je funkcija samorazumljiva:

MariaDB [(nema)]> POKAŽI PROMJENE VERIABLES LIKE ‘query_cache_size’;
+——————+———-+
| Ime varijable | Vrijednost |
+——————+———-+
| query_cache_size | 16777216 |
+——————+———-+

Dakle, imam predmemoriju upita veličine oko 16 MB. Imajte na umu da je čak i ako je predmemoriranje upita uključeno, ali ova veličina je nula, predmemoriranje učinkovito isključeno. Zato provjera samo jedne varijable nije dovoljna. Sada biste trebali postaviti veličinu predmemorije upita, ali koliko ona treba biti? Prvo, imajte na umu da će za spremanje metapodataka značajki predmemoriranja upita trebati 4 KB, tako da sve što odaberete treba biti iznad toga.

Recimo da ste postavili veličinu predmemorije upita na 500 KB:

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

Radi li ovo dovoljno? Pa, ne, jer kako će mehanizam upita zapravo raditi, ovisi o još nekoliko stvari:

  • Prije svega, varijabla query_cache_size mora biti dovoljno velika da drži rezultate vašeg upita. Ako je premalen, ništa se neće predmemorirati.
  • Drugo, ako je query_cache_size postavljen na previsok broj, pojavit će se dvije vrste problema: 1) Motor će morati obaviti dodatni posao pohranjivanja i lociranja rezultata upita u ovom ogromnom području memorije. 2) Ako većina upita rezultira u mnogo manjim veličinama, predmemorija će se fragmentirati, a koristi korištenja predmemorije gube se.

Kako znate da se predmemorija fragmentira? Provjerite ukupni broj blokova u predmemoriji ovako:

MariaDB [(nema)]> prikaži status poput “Qcache_total_blocks”;
+———————+——-+
| Ime varijable | Vrijednost |
+———————+——-+
| Qcache_total_blocks | 33 |
+———————+——-+

Ako je broj vrlo velik, predmemorija je fragmentirana i treba je pročistiti.

Stoga, kako biste izbjegli ove probleme, provjerite je li veličina query_cache_size odabrana mudro. Ako se osjećate frustrirano što vas ovdje nisam ostavio sa konkretnim brojem, bojim se da će tako postupiti nakon što napredujete i krenete u inženjering. Morate pogledati aplikaciju koju pokrećete i vidjeti koje su veličine upita za važne rezultate upita, a zatim postaviti ovaj broj. Čak i tada možda završite pogrešku. ��

Navoj niti, baze niti, čekanje i čekanja

Ovo je vjerojatno najzanimljiviji dio načina na koji MySQL funkcionira i ispravnost toga čini vašu aplikaciju nekoliko puta bržom!

Threading

MySQL je višeslojni poslužitelj. To znači da svaki put kada se dogodi nova veza s MySQL poslužiteljem, ona otvara novu nit s podacima o vezi i prosljeđuje ručku klijentu (samo u slučaju da se pitate što je nit, pogledajte ovaj). Klijent tada šalje sve upite preko ove niti i dobiva rezultate. To nas dovodi do prirodnog pitanja: koliko niti može da se okreće MySQL? Odgovor se nalazi u sljedećem odjeljku.

Navojni bazen

Nijedan program u računalnom sustavu ne može otvoriti onoliko niti koliko želi. Razlog je dvostruk: 1) Konci koštaju memoriju (RAM), a operativni sustav vam neće dopustiti da pojedete i pojedete sve. 2) Upravljanje, recimo, milijun niti je ogroman zadatak sam po sebi, i ako bi MySQL poslužitelj mogao stvoriti toliko niti, umro bi pokušavajući da se riješi nadzemnih.

Da biste izbjegli ove probleme, MySQL dolazi s nizom niti – fiksnim brojem niti koje su na početku dio bazena. Novi zahtjevi za povezivanjem uzrokuju da MySQL pokupi jednu od tih niti i vrati podatke o vezi, a ukoliko se svi niti iskoriste, nove veze se prirodno odbijaju. Pogledajmo koliko je velik niz niti:

ariaDB [(nema)]> prikaži varijable poput ‘thread_pool_size’;
+——————+——-+
| Ime varijable | Vrijednost |
+——————+——-+
| nit_pool_size | 4 |
+——————+——-+

Dakle, moj stroj omogućuje maksimalno četiri veze istovremeno. Zanimljivo je primijetiti da broj 4 proizlazi iz činjenice da imam četverojezgreni procesor, što znači da moje računalo može istovremeno pokrenuti samo 4 paralelna zadatka (ovdje govorim o istinski paralelnim zadacima, a ne o istodobnim). U idealnom slučaju, ovo je granica koja bi trebala pokretati vrijednost thread_pool_size, ali na povećanim strojevima za košnice koristi se bodu. Ako ne želite da sve nove veze pričekate i u redu je da poduzmete neki učinak (opet, ovo je područje o kojem možete najbolje prosuditi na temelju performansi vaše aplikacije pod opterećenjem), nalet na 8 može biti dobra ideja.

Međutim, postavljanje iznad 16 godina je strašna ideja ako nemate 32-jezgreni stroj, jer se performanse značajno smanjuju. Zečja rupa bazena s nitima u MySQL-u ide duboko, ali ako ste zainteresirani, evo detaljnija rasprava.

Čekanje i istek vremena

Nakon što nit stvorite i pridružite klijentu, gubit ćete resurse ako klijent narednih nekoliko sekundi (ili minuta) nije poslao nikakve upite. Kao rezultat toga, MySQL prekida vezu nakon razdoblja neaktivnosti. Ovo se kontrolira varijablom wait_timeout:

MariaDB [(nema)]> prikažite varijable poput “pričekaj%”;
+—————+——-+
| Ime varijable | Vrijednost |
+—————+——-+
| wait_timeout | 28800 |
+—————+——-+

Dobivena vrijednost je u sekundi. Pa da, podrazumijevano je MySQL postavljen da čeka 8+ sati prije nego što skine kabel! To može biti dobro ako imate dugotrajne upite i zapravo ih želite pričekati (ali čak i tada, osam sati je apsurdno!), Ali u većini slučajeva užasno. Kada se pokrene upit, ova vrijednost postavljena je na 0 (znači zauvijek), ali općenito, ona bi trebala biti postavljena na vrlo nisku vrijednost (na primjer, 5 sekundi ili možda čak i manje) kako bi se oslobodila veza za druge procese.

Ugađanje privremenih tablica

Započnimo s privremenim tablicama u MySQL-u.

Pretpostavimo da imamo MySQL koji strukturno izgleda ovako: TABELA UNIJA (TABELA B UNUTRAJ JOIN C). Odnosno, zanima nas spajanje tablica B i C, a zatim izvođenje objedinjavanja rezultata s tablicom A. Sada bi MySQL prvo pristupio pridruživanju tablicama B i C, ali prije nego što može izvesti uniju, potrebna mu je pohraniti ove podatke negdje. Tu dolaze privremene tablice – MySQL ih koristi za privremeno spremanje podataka u međufaznim fazama složenih upita, a nakon što je upit završen, ta se privremena tablica odbacuje.

Sada je pitanje: zašto bismo se mučili sa svim tim?

Jednostavno zato što je privremena tablica, samo rezultat upita, podaci koje MySQL koristi u računanju, a brzina njegovog pristupa (među ostalim ograničenjima) će odrediti koliko brzo se upit izvršava. Primjerice, pohranjivanje privremene tablice u RAM-u bit će nekoliko puta brže od pohrane na disk.

Postoje dvije varijable koje kontroliraju ovo ponašanje:

MariaDB [(nema)]> prikaži varijable poput “MariaDB [(nijedna)]> prikažite varijable poput ‘tmp_table_size’;
+—————-+———-+

| Ime varijable | Vrijednost |

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

| tmp_table_size | 16777216 |

+—————-+———-+
„;
+———————+———-+
| Ime varijable | Vrijednost |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(nema)]> prikažite varijable poput ‘tmp_table_size’;
+—————-+———-+
| Ime varijable | Vrijednost |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

Prva, max_heap_table_size, govori nam koliko RAM-a može koristiti MySQL tablica („heap“ se ovdje odnosi na strukturu podataka koja se koristi za raspodjelu i upravljanje RAM-om – pročitajte više ovdje), dok druga, tmp_table_size, pokazuje koja je maksimalna veličina privremene tablice. U mom slučaju oba su postavljena na 16 MB, iako točka koju pokušavam učiniti da povećanje samo tmp_table_size neće raditi kao cjelokupni, MySQL će i dalje biti ograničen max_table_heap_size.

Sada dolazi točka: ako bi privremene tablice koje se kreiraju veće od ograničenja dopuštenih ovim varijablama, MySQL bi bio prisiljen upisati ih na tvrdi disk, što rezultira izuzetno lošom izvedbom. Naš je posao sada jednostavan: potrudimo se pogoditi najtočniju veličinu podataka za privremene tablice i prilagoditi ove varijable do te granice. Međutim, želio bih upozoriti na apsurd: postavljanje ovog ograničenja na 16 GB (pod pretpostavkom da imate toliko RAM-a) kada je većina vaših privremenih tablica manja od 24 MB glupost – jednostavno trošite RAM koji bi mogao ‘ koristili su me drugi upiti ili dijelovi sustava (na primjer, predmemorija).

Zaključak

Nije moguće pokriti sve sistemske varijable u jednom članku ili čak sve one važne u jednom članku kada sama MySQL dokumentacija obuhvaća nekoliko tisuća riječi. Iako smo ovdje pokrenuli neke univerzalne varijable, preporučam vam da pogledate sistemske varijable za motor koji koristite (InnoDB ili MyISAM).

Moj najpoželjniji ishod za pisanje ovog članka je da vam oduzmete tri stvari:

  1. MySQL je tipičan dio softvera koji radi u granicama koje određuje operativni sustav. To nije neki tajanstveni program koji Bog zna-što i nemoguće je ukrotiti. Također, na sreću, nije tako teško razumjeti kako je postavljen i kontrolira ga varijabla sustava.
  2.  Ne postoji niti jedna postavka koja će učiniti da se MySQL instalacija pokrene za zumiranje. Nemate drugog izbora nego gledati unutar svojih pokretačkih sustava (zapamtite, optimizacija dolazi nakon što aplikacija bude u produkciji, a ne prije), napravite najbolja nagađanja i mjerenja i živite sa stvarnošću da nikad neće biti savršeno.
  3. Ugađanje varijabli nije jedini način za optimizaciju MySQL – efikasnih upita za pisanje još je jedna velika stvar, ali to je nešto na što ću se obratiti u drugom članku. Ali poanta je, čak i ako ste napravili božansku analizu i prilagodili te parametre na najbolji način, još uvijek je moguće da sve zaustavite i vrište.

Koja je vaša omiljena sistemska varijabla za podešavanje? ��

OZNAKE:

  • Baza podataka

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