Uglaševanje spremenljivk sistema MySQL za visoke zmogljivosti

Za večino razvijalcev aplikacij je baza podatkov oltar demonskih bogov, ki jih je najbolje pustiti neprilagojene. A ne bi smelo biti tako!


Če so enake stvari, stopnja udobja, ki jo ima razvijalci z osnovno bazo podatkov, določa njihovo stopnjo delovne dobe. Malo podatkovne baze in malo izkušenj s kodiranjem = mlajši razvijalci; malo baze podatkov in dobre izkušnje s kodiranjem = razvijalec na srednji ravni; dobra baza podatkov in dobra izkušnja s kodiranjem = starejši razvijalec.

Huda resničnost je, da se celo vragoli, ki imajo 6-8 let pod pasom, trudijo, da pojasnijo podrobnosti orodja za optimizacijo poizvedb in raje pogledajo v nebo, ko ga vprašajo o uglaševanje baze podatkov.

Zakaj?

Presenetljivo je, da razlog ni lenoba (čeprav je v nekaterih delih tako).

Bistvo je, da so baze podatkov same od sebe. Celo tradicionalno, ko so se ukvarjale samo relacijske vrste baz podatkov, je bilo obvladovanje le-teh čudežno in poklicna pot sama po sebi; V teh dneh imamo toliko vrst baz podatkov, da je nemogoče pričakovati, da bo ena sama smrtna duša obvladala vse.

Glede na to obstaja velika verjetnost, da ste še vedno zadovoljni z relacijskimi bazami podatkov ali ste del ekipe, ki ima izdelek, ki v ustrezni bazi podatkov deluje zadovoljivo dolgo, dolgo. In v devetih od desetih primerov ste na MySQL (ali MariaDB). V teh primerih potapljanje le malo globlje pod pokrovom prinaša velike koristi pri izboljšanju zmogljivosti aplikacij in se jih je vsekakor vredno naučiti.

Radoveden? Potopimo se noter!

Ni radovedno? No, vseeno se potopite, saj je od tega odvisna vaša kariera! ��

Optimizirajte predpomnilnik poizvedb MySQL

Skoraj vsa optimizacija na področju računalnikov prihaja do predpomnjenja. Na enem koncu CPU vzdržuje več stopenj predpomnilnika, da pospeši svoje izračune, na drugi strani pa spletne aplikacije agresivno uporabljajo predpomnilne rešitve, kot je Redis, za strežnik predhodno izračunanih rezultatov uporabnikom, namesto da bi vsakič posegali v bazo podatkov.

Ampak hej, tudi slaba baza podatkov MySQL ima svoj predpomnilnik poizvedb! To pomeni, da bo MySQL vsakič, ko nekaj poiščete in so podatki še vedno zastareli, postregel s temi predpomnjenimi rezultati, namesto da bi znova izvajal poizvedbo, s čimer bo aplikacija smešno hitrejša.

Lahko preverite, ali imate v svoji bazi podatkov na voljo predpomnilnik poizvedb (opomba, na voljo, ni omogočena), tako da to poizvedbo zaženete na konzoli baze podatkov:

MariaDB [(nič)]> PRIKAŽI VARIABLES LIKE ‘have_query_cache’;
+——————+——-+
| Ime spremenljivke | Vrednost |
+——————+——-+
| have_query_cache | DA |
+——————+——-+

Torej lahko vidite, da vodim MariaDB in da imam na voljo predpomnilnik poizvedb. Zelo malo verjetno je, da bi ga izključili, če uporabljate standardno namestitev MySQL.

Zdaj pa poglejmo, ali imam vklopljen predpomnilnik poizvedb:

MariaDB [(nič)]> PRIKAŽI VARIABLES LIKE ‘query_cache_type’;
+——————+——-+
| Ime spremenljivke | Vrednost |
+——————+——-+
| query_cache_type | ON |
+——————+——-+

Ja, res je. V nasprotnem primeru pa ga lahko vklopite tako, da rečete:

MariaDB [(nič)]> SET GLOBAL query_cache_type = VKLJUČEN;

Zanimivo je, da ta spremenljivka sprejme tudi tretjo vrednost, ki označuje “na zahtevo”, kar pomeni, da bo MySQL predpomnil samo tiste poizvedbe, na katere mu odgovorimo, vendar tega ne bomo spuščali tukaj.

S tem ste nadaljevali predpomnjenje in naredili ste prvi korak k bolj robustni namestitvi MySQL! Pravim, da je prvi korak, ker je vključitev le-tega velika izboljšava, moramo prilagoditi predpomnilnik poizvedb, da ustreza naši nastavitvi. Torej, naučimo se tega.

Druga spremenljivka, ki nas zanima, je query_cache_size, katere funkcija je samoumevna:

MariaDB [(nič)]> PRIKAŽI VARIABLES LIKE ‘query_cache_size’;
+——————+———-+
| Ime spremenljivke | Vrednost |
+——————+———-+
| query_cache_size | 16777216 |
+——————+———-+

Torej, imam predpomnilnik poizvedb velikosti približno 16 MB. Upoštevajte, da je predpomnjenje dejansko izključeno, tudi če je predpomnilnik poizvedb vklopljen, vendar je ta velikost enaka nič. Zato preverjanje samo ene spremenljivke ni dovolj. Zdaj bi morali določiti velikost predpomnilnika poizvedb, koliko pa mora biti? Najprej upoštevajte, da bo za shranjevanje svojih metapodatkov funkcija predpomnjenja poizvedb sama potrebovala 4 KB, tako da karkoli izberete, mora biti nad tem.

Recimo, da nastavite velikost predpomnilnika poizvedbe na 500 KB:

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

Ali to počne dovolj? No, ne, kajti od tega, kako bo iskalni mehanizem dejansko deloval, je odvisno od še nekaj stvari:

  • Najprej mora biti spremenljivka query_cache_size dovolj velika, da zadrži rezultate vaših poizvedb. Če je premajhen, se nič ne predpomni.
  • Drugič, če je število query_cache_size nastavljeno na previsoko število, bo prišlo do dveh vrst težav: 1) Motor mora opraviti dodatna dela za shranjevanje in iskanje rezultatov poizvedb v tem velikem pomnilniškem območju. 2) Če ima večina poizvedb veliko manjše velikosti, se predpomnilnik razdrobi in koristi uporabe predpomnilnika se izgubijo.

Kako veste, da se predpomnilnik razdrobi? Preverite skupno število blokov v predpomnilniku, kot je ta:

MariaDB [(nič)]> prikaži stanje, kot je ‘Qcache_total_blocks’;
+———————+——-+
| Ime spremenljivke | Vrednost |
+———————+——-+
| Qcache_total_blocks | 33 |
+———————+——-+

Če je število zelo veliko, je predpomnilnik razdrobljen in ga je treba izprazniti.

Da bi se izognili tem težavam, poskrbite, da je velikost query_cache_size izbrana pametno. Če se počutite frustrirani, ker vas nisem pustil s konkretnimi številkami, bojim se, da bodo tako stvari, ko se premaknete mimo razvoja in stopite v inženiring. V aplikaciji, ki jo zaženete, morate pogledati, kakšne so velikosti poizvedb za pomembne rezultate poizvedbe in nato nastaviti to številko. Pa tudi takrat boste morda naredili napako. ��

Navoj niti, baze niti, čakanje in časovne omejitve

To je verjetno najzanimivejši del, kako deluje MySQL, in če je pravilno, pomeni, da bo vaša aplikacija nekajkrat hitrejša!

Niti

MySQL je strežnik z več niti. To pomeni, da vsakič, ko pride do nove povezave s strežnikom MySQL, odpre novo nit s podatki o povezavi in ​​stranki posreduje ročaj (samo v primeru, da se sprašujete, kaj je nit, glejte to). Odjemalec nato pošlje vsa vprašanja po tej niti in prejme rezultate. To nas vodi k naravnemu vprašanju: koliko niti lahko MySQL zavrti? Odgovor je v naslednjem razdelku.

Navojni bazen

Noben program v računalniškem sistemu ne more odpreti toliko niti, kot želi. Razlog je dvojen: 1) Niti stanejo pomnilnika (RAM), operacijski sistem pa vam ne bo dovolil, da bi se trudili in pojedli vse. 2) Upravljanje, recimo, milijona niti, je sama po sebi obsežna naloga, in če bi strežnik MySQL lahko ustvaril toliko niti, bi umrl pri poskusu reševanja.

Da bi se izognili tem težavam, MySQL prihaja z nizom niti – fiksnim številom niti, ki so del bazena na začetku. Nove zahteve za povezavo povzročijo, da MySQL pobere eno od teh niti in vrne podatke o povezavi, in če so vsi navoji porabljeni, se nove povezave seveda zavrnejo. Poglejmo, kako velik je navojni niz:

ariaDB [(nič)]> prikažite spremenljivke, kot je ‘nit_pool_size’;
+——————+——-+
| Ime spremenljivke | Vrednost |
+——————+——-+
| nit_pool_size | 4 |
+——————+——-+

Torej moj stroj omogoča največ štiri povezave hkrati. Zanimivo je, da številka 4 izvira iz dejstva, da imam štirijedrni procesor, kar pomeni, da lahko računalnik hkrati izvaja samo 4 vzporedne naloge (tukaj govorim o resnično vzporednih nalogah, ne pa o sočasnih). V idealnem primeru je to meja, ki bi morala poganjati vrednost thread_pool_size, toda na čebeljih strojih, ki povečujejo, je to koristno do točke. Če ne želite, da vse nove povezave čakajo in je v redu, da si prikažete nekaj uspešnosti (spet je to področje, ki ga lahko najbolje presodite glede na uspešnost vaše aplikacije pod obremenitvijo), pa je nalet na 8 morda dobra ideja.

Vendar pa je postavljanje preko 16 let grozljiva ideja, če nimate 32-jedrnega stroja, saj zmogljivost znatno poslabša. Kunčja luknja niti v bazenih MySQL sega globoko, če pa vas zanima, tukaj je podrobnejša razprava.

Čakanje in časovne omejitve

Ko je nit ustvarjena in pritrjena na stranko, bi bila izguba sredstev, če odjemalec v naslednjih nekaj sekundah (ali minutah) ni poslal nobenih poizvedb. Zaradi tega MySQL prekine povezavo po obdobju neaktivnosti. To nadzira spremenljivka wait_timeout:

MariaDB [(nič)]> prikaže spremenljivke, kot je ‘čakati%’;
+—————+——-+
| Ime spremenljivke | Vrednost |
+—————+——-+
| wait_timeout | 28800 |
+—————+——-+

Nastala vrednost je v sekundah. Torej da, privzeto je MySQL nastavljen na čakanje 8+ ur, preden začne vrvico! To je lahko dobro, če imate dolgotrajne poizvedbe in jih dejansko želite počakati (a tudi takrat je osem ur nesmiselno!), A v večini primerov grozno. Ko je poizvedba zagnana, je ta vrednost nastavljena na 0 (kar pomeni za vedno), na splošno pa je treba to nastaviti na zelo nizko vrednost (na primer 5 sekund ali morda še manj), da se sprosti povezava za druge procese.

Uglaševanje začasnih tabel

Začnimo s tem, kaj so začasne tabele v MySQL.

Recimo, da imamo MySQL, ki je strukturno videti takole: TABELA UNIJA (TABELA B VNOSI PRIDRUŽI SE C). To pomeni, da nas zanima združevanje tabel B in C in nato izvedba združevanja rezultata s tabelo A. Zdaj bi MySQL najprej nadaljeval združevanje tabel B in C, vendar preden lahko izvede zvezo, potrebuje da nekje shrani te podatke. Tu pridejo začasne tabele – MySQL jih uporablja za začasno shranjevanje podatkov v vmesnih fazah v zapletenih poizvedbah in ko je poizvedba končana, se ta začasna tabela zavrže..

Zdaj je vprašanje: zakaj bi se trudili z vsem tem?

Preprosto zato, ker so začasna tabela, le rezultat poizvedbe, podatki, ki jih MySQL uporablja pri računanju, in hitrost njegovega dostopa (med drugimi omejitvami) bo določila, kako hitro se poizvedba izvrši. Na primer, shranjevanje začasne tabele v RAM-u bo nekajkrat hitrejše kot shranjevanje na disku.

Obstajata dve spremenljivki, ki nadzirata to vedenje:

MariaDB [(nič)]> prikaži spremenljivke, kot je „MariaDB [(nič)]> prikaže spremenljivke, kot je ‘tmp_table_size’;
+—————-+———-+

| Ime spremenljivke | Vrednost |

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

| tmp_table_size | 16777216 |

+—————-+———-+
‘;
+———————+———-+
| Ime spremenljivke | Vrednost |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(nič)]> prikaže spremenljivke, kot je ‘tmp_table_size’;
+—————-+———-+
| Ime spremenljivke | Vrednost |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

Prva, max_heap_table_size, nam pove, koliko RAM-a lahko porabi tabela MySQL (tu se tukaj “nanaša kopica” na podatkovno strukturo, ki se uporablja pri razporejanju in upravljanju RAM-a – preberite več tukaj), medtem ko druga, tmp_table_size, prikazuje, kakšna je največja velikost začasne tabele. V mojem primeru sta obe nastavljeni na 16 MB, čeprav želim, da povečanje samo tmp_table_size ne bo delovalo kot splošno, bo MySQL še vedno omejen z max_table_heap_size.

Zdaj je smisel: če so ustvarjene začasne tabele večje od omejitve, ki jo te spremenljivke dovoljujejo, bi bil MySQL prisiljen zapisati na trdi disk, kar ima za posledico izjemno slabe zmogljivosti. Naša naloga je zdaj preprosta: po najboljših močeh uganimo najbolj natančno velikost podatkov za začasne tabele in prilagodimo te spremenljivke do te meje. Vendar bi rad previden pred nesmiselnostjo: nastavitev te omejitve na 16 GB (ob predpostavki, da imate toliko RAM-a), ko je večina vaših začasnih tabel velikosti manj kot 24 MB, je neumnost – preprosto zapravljate RAM, ki bi lahko ” so bile uporabljene v drugih poizvedbah ali delih sistema (na primer predpomnilnik).

Zaključek

V enem članku ni mogoče zajeti vseh sistemskih spremenljivk ali celo vseh pomembnih v enem članku, če sama dokumentacija MySQL obsega več tisoč besed. Čeprav smo tukaj zajemali nekaj univerzalnih spremenljivk, vas priporočam, da pogledate sistemske spremenljivke za motor, ki ga uporabljate (InnoDB ali MyISAM).

Moj najbolj zaželen rezultat pri pisanju tega članka je, da vam odvzamem tri stvari:

  1. MySQL je tipičen del programske opreme, ki deluje v mejah, ki jih določa operacijski sistem. To ni skrivnostni program, ki ne ve, kaj ve in kaj je nemogoče ukrotiti. Tudi na srečo ni težko razumeti, kako je nastavljena in da jo nadzorujejo njene sistemske spremenljivke.
  2.  Ni nobene nastavitve, zaradi katere bi se namestitev MySQL lahko zvišala. Nimate druge izbire, kot da pogledate v svoje tekoče sisteme (ne pozabite, optimizacija pride, ko je aplikacija v izdelavi, ne prej), naredite najboljša ugibanja in meritve in živite z realnostjo, da nikoli ne bo popolna.
  3. Uglaševanje spremenljivk ni edini način za optimizacijo MySQL – učinkovite pisne poizvedbe je še en velik problem, vendar bom nekaj obravnaval v drugem članku. Bistvo pa je, da tudi če ste naredili božjo analizo in prilagodili te parametre na najboljše, je še vedno mogoče, da vse zaustavite.

Katera je vaša najljubša sistemska spremenljivka za uglaševanje? ��

Oznake:

  • Baza podatkov

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