Peter Zaitsev je soustanovitelj in izvršni direktor podjetjaPercona.
Eden od načinov, kako merimo aplikacije, je uspešnost. Ena od meritev uspešnosti aplikacije je uporabniška izkušnja, ki se na splošno prevede v "ali je moral uporabnik počakati dlje kot razumen čas, da je dobil tisto, kar je želel."
Ta metrika lahko v različnih scenarijih pomeni različne stvari. Za mobilno aplikacijo za nakupovanje odzivni čas ne sme biti daljši od nekaj sekund. Na kadrovski strani zaposlenega lahko odzivi trajajo nekaj sekund dlje.
Imamo veliko raziskav o tem, kako uspešnost vpliva na vedenje uporabnikov:
- 79 odstotkov kupcev je manj verjetno, da se bodo vrnili na počasno spletno mesto
- 47 odstotkov potrošnikov pričakuje, da se bo spletna stran naložila v dveh sekundah ali manj
- 40 odstotkov uporabnikov opusti spletno mesto, če traja dlje kot tri sekunde
- Enosekundna zakasnitev časa nalaganja strani lahko povzroči 7-odstotno izgubo pri pretvorbi in 11 odstotkov manj ogledov strani
Ne glede na standard, je nujno ohraniti dobro delovanje aplikacij. V nasprotnem primeru se bodo uporabniki pritožili (ali še huje, se obrnili na drugo aplikacijo). Eden od dejavnikov, ki vpliva na delovanje aplikacije, je zmogljivost baze podatkov. Interakcija med aplikacijami, spletnimi mesti in bazami podatkov je ključnega pomena pri določanju stopnje učinkovitosti aplikacij.
Osrednja komponenta te interakcije je, kako aplikacije poizvedujejo po zbirki podatkov in kako se baza odziva na zahteve. Vsekakor je MySQL eden najbolj priljubljenih sistemov za upravljanje baz podatkov. Več podjetij prehaja na MySQL (in druge odprtokodne zbirke podatkov) kot rešitev baz podatkov v svojih proizvodnih okoljih.
Obstaja veliko načinov konfiguriranja MySQL, s katerimi lahko zagotovite, da se vaša zbirka podatkov hitro odziva na poizvedbe in z minimalno količino poslabšanja zmogljivosti aplikacije.
Sledi nekaj bistvenih nasvetov, ki vam bodo pomagali optimizirati delovanje baze podatkov MySQL.
Ključ za optimizacijo MySQL št. 1: Naučite se uporabljati POJASNITE
Dve najpomembnejši odločitvi, ki ju sprejmete s katero koli bazo podatkov, sta oblikovanje, kako se odnosi med entitetami aplikacij preslikajo v tabele (shema baze podatkov), in način, kako aplikacije dobijo podatke, ki jih potrebujejo, v obliki, ki jo potrebujejo (poizvedbe).
Zapletene aplikacije imajo lahko zapletene sheme in poizvedbe. Če želite doseči zmogljivost in obseg, ki ga zahtevajo vaše aplikacije, ne morete računati le na intuicijo, da boste razumeli, kako se bodo izvajale poizvedbe.
Namesto ugibanja in upanja se naučite uporabljati POJASNITE ukaz. Ta ukaz vam pokaže, kako se bo izvedla poizvedba, in vam daje vpogled v to, kakšno zmogljivost lahko pričakujete, in kako se bo poizvedba spreminjala s spreminjanjem velikosti podatkov.
Obstajajo številna orodja - na primer MySQL Workbench -, ki lahko vizualizirajo POJASNITE izhod za vas, vendar morate kljub temu razumeti osnove, da ga boste lahko razumeli.
Obstajata dve različni obliki zapisa, v katerih POJASNITE ukaz zagotavlja izhod: staromodna oblika tabele in sodobnejši, strukturiran dokument JSON, ki vsebuje bistveno več podrobnosti (prikazano spodaj):
mysql> razloži format = json izberite avg (k) iz sbtest1, kjer je id med 1000 in 2000 \ G*************************** 1. vrstica ******************** *******
POJASNITE: {
“Query_block”: {
“Select_id”: 1,
“Info o stroških”: {
“Query_cost”: “762.40”
“Tabela”: {
“Table_name”: “sbtest1”,
“Access_type”: “obseg”,
“Možne_ključke”: [
“PRIMARNO”
],
“Key”: “PRIMARY”,
“Used_key_parts”: [
"Id"
],
“Key_length”: “4”,
"Row_examined_per_scan": 1874,
"Row_produced_per_join": 1874,
"Filtrirano": "100,00",
“Info o stroških”: {
“Read_cost”: “387.60”,
“Eval_cost”: “374.80”,
“Prefix_cost”: “762.40”,
“Data_read_per_join”: “351K”
},
“Used_columns”: [
"Id",
"K"
],
“Attach_condition”: “(„ sbtest`.`sbtest1`.`id` med 1000 in 2000) “
}
}
}
Ena izmed komponent, na katero morate biti pozorni, je »stroški poizvedbe«. Stroški poizvedbe se nanašajo na to, kako draga MySQL upošteva to poizvedbo glede na skupne stroške izvedbe poizvedbe in temelji na številnih različnih dejavnikih.
Preproste poizvedbe imajo običajno poizvedbe manj kot 1000. Poizvedbe s stroški med 1.000 in 100.000 veljajo za srednje stroške poizvedbe in so na splošno hitre, če izvajate samo na stotine takih poizvedb na sekundo (ne deset tisoč).
Poizvedbe s stroški nad 100.000 so drage poizvedbe. Pogosto se bodo te poizvedbe še vedno izvajale hitro, če ste en sam uporabnik sistema, vendar morate dobro premisliti, kako pogosto uporabljate takšne poizvedbe v svojih interaktivnih aplikacijah (zlasti ko število uporabnikov raste).
Seveda so to številke uspešnosti, vendar dokazujejo splošno načelo. Vaš sistem lahko bolje ali slabše obravnava delovne obremenitve poizvedb, odvisno od njegove arhitekture in konfiguracije.
Glavni dejavnik, ki določa ceno poizvedbe, je, ali poizvedba pravilno uporablja indekse. The POJASNITE ukaz vam lahko pove, ali poizvedba ne uporablja indeksov (običajno zaradi tega, kako se indeksi ustvarjajo v zbirki podatkov ali kako je zasnovana sama poizvedba). Zato je tako pomembno, da se naučimo uporabljati POJASNITE.
Ključ za optimizacijo MySQL # 2: Ustvarite prave indekse
Indeks izboljša zmogljivost poizvedbe z zmanjšanjem količine podatkov v zbirki podatkov, ki jih morajo poizvedbe pregledati. Indeksi v MySQL se uporabljajo za pospešitev dostopa do baze podatkov in pomoč pri uveljavljanju omejitev baze podatkov (kot je ENOTNA in TUJI KLJUČ).
Kazala zbirk podatkov so podobna indeksom knjig. Hranijo se na svojem mestu in vsebujejo informacije, ki so že v glavni bazi podatkov. So referenčna metoda ali zemljevid, kjer se nahajajo podatki. Kazala ne spremenijo podatkov v zbirki podatkov. Preprosto kažejo na lokacijo podatkov.
Ni indeksov, ki bi bili vedno primerni za kakršno koli delovno obremenitev. Kazala morate vedno gledati v kontekstu poizvedb, ki jih izvaja sistem.
Dobro indeksirane zbirke podatkov ne samo, da delujejo hitreje, ampak celo en manjkajoči indeks lahko upočasni bazo podatkov v iskanju. Uporaba POJASNITE (kot je bilo prej priporočeno), da poiščete manjkajoče indekse in jih dodate. A previdno: ne dodajajte indeksov, ki jih ne potrebujete! Nepotrebni indeksi upočasnjujejo baze podatkov (oglejte si mojo predstavitev o najboljših praksah indeksiranja MySQL).
Ključ za optimizacijo MySQL # 3: Brez privzetih vrednosti!
Kot katera koli programska oprema ima tudi MySQL številne nastavljive nastavitve, ki jih lahko uporabimo za spreminjanje vedenja (in navsezadnje tudi zmogljivosti). Tako kot katero koli programsko opremo skrbniki večino teh nastavljivih nastavitev prezrejo in na koncu uporabljajo v privzetem načinu.
Da bi MySQL dosegel najboljšo učinkovitost, je pomembno razumeti nastavljive nastavitve MySQL in, kar je še pomembneje, jih nastaviti tako, da najbolje delujejo v okolju vaše baze podatkov.
MySQL je privzeto prilagojen za majhno razvojno namestitev in ne za proizvodno. Običajno želite konfigurirati MySQL tako, da uporablja vse razpoložljive pomnilniške vire in dovoli število povezav, ki jih zahteva vaša aplikacija.
Tu so tri nastavitve za nastavitev zmogljivosti MySQL, ki jih morate vedno natančno preučiti:
innodb_buffer_pool_size: Vmesni pomnilnik je mesto, kjer so podatki in indeksi predpomnjeni. To je glavni razlog za uporabo sistema z veliko količino RAM-a kot strežnika baz podatkov. Če uporabljate samo pomnilniški mehanizem InnoDB, za pomnilniško področje običajno namenite približno 80 odstotkov svojega pomnilnika. Če izvajate zelo zapletene poizvedbe ali imate sočasno veliko povezav do zbirke podatkov ali imate zelo veliko tabel, boste morda morali to vrednost znižati, da dodelite več pomnilnika za druge namene.
Ko nastavljate velikost področja vmesnega pomnilnika InnoDB, se prepričajte, da je ne nastavite prevelike, ker bo povzročila zamenjavo. To popolnoma ubije vašo bazo podatkov. Enostaven način preverjanja je, da si v grafikonu Pregled sistema v Percona Monitoring and Management ogledate Dejavnost zamenjave:
PerconaKot kaže ta graf, je nekaj zamenjav v redu vsake toliko. Če pa opazite trajno aktivnost zamenjave 1 MB na sekundo ali več, boste morali zmanjšati velikost medpomnilnika (ali druge namene pomnilnika).
Če ne dobite vrednosti za innodb_buffer_pool_size pravilno na prvi poti, ne skrbite. Z MySQL 5.7 lahko dinamično spreminjate velikost vmesnega področja InnoDB, ne da bi znova zagnali strežnik baze podatkov.
innodb_log_file_size: To je velikost posamezne dnevniške datoteke InnoDB. InnoDB privzeto uporablja dve vrednosti, tako da lahko to številko podvojite, da dobite velikost krožnega prostora dnevnika ponovitve, ki ga InnoDB uporablja za zagotovitev, da so vaše transakcije trajne. To tudi optimizira uporabo sprememb v zbirki podatkov. Nastavitev innodb_log_file_size je vprašanje kompromisov. Večji kot je prostor za obnovitev, ki ga dodelite, boljšo zmogljivost boste dosegli za delovno obremenitev, ki intenzivno zapisuje, toda daljši je čas za obnovo po zrušitvi, če vaš sistem trpi zaradi izgube energije ali drugih težav.
Kako veste, ali je zmogljivost MySQL omejena z vašo trenutno velikostjo dnevniške datoteke InnoDB? Če ugotovite, koliko uporabnega prostora dnevnika ponovitve je dejansko uporabljeno, lahko ugotovite. Najlažji način je pogledati nadzorno ploščo Pernona Monitoring and Management InnoDB Metrics. V spodnjem grafu velikost datoteke dnevnika InnoDB ni dovolj velika, saj je uporabljeni prostor zelo blizu, koliko uporabnega prostora dnevnika ponovitve je na voljo (označeno z rdečo črto). Velikost vaše dnevniške datoteke bi morala biti vsaj 20 odstotkov večja od količine prostora, uporabljenega za optimalno delovanje sistema.
Perconamax_connections: Velike aplikacije pogosto zahtevajo veliko več kot privzeto število povezav. Za razliko od drugih spremenljivk, če tega ne nastavite pravilno, ne boste imeli težav z zmogljivostjo (same po sebi). Namesto tega, če število povezav ne zadostuje za potrebe vaše aplikacije, se vaša aplikacija preprosto ne bo mogla povezati z bazo podatkov (kar je za vaše uporabnike videti kot izpad). Pomembno je pravilno spremeniti to spremenljivko.
Težko je vedeti, koliko povezav potrebujete za kompleksne programe z veliko komponentami, ki se izvajajo na več strežnikih. Na srečo MySQL zelo enostavno vidi, koliko povezav se uporablja pri največjem delovanju. Običajno želite zagotoviti, da obstaja vsaj 30-odstotna razlika med največjim številom povezav, ki jih uporablja vaša aplikacija, in največjim številom razpoložljivih povezav. Enostaven način za ogled teh številk je uporaba grafa MySQL Connections Graph na nadzorni plošči MySQL Overview v Percona Monitoring and Management. Spodnji graf prikazuje zdrav sistem, kjer je na voljo lepo število dodatnih povezav.
PerconaNe pozabite, da če vaša baza podatkov deluje počasi, aplikacije pogosto ustvarijo preveliko število povezav. V takih primerih raje rešite težavo z zmogljivostjo baze podatkov, ne pa samo dovolite več povezav. Več povezav lahko poslabša osnovno težavo z zmogljivostjo.
(Opomba: Ko nastavite max_connections spremenljivka znatno višja od privzete vrednosti, morate pogosto razmisliti o povečanju drugih parametrov, kot sta velikost predpomnilnika tabele in število odprtih datotek, ki jih MySQL omogoča. Vendar to presega obseg tega članka.)
Ključ za optimizacijo MySQL št. 4: Zbirko shranite v pomnilnik
V zadnjih letih opažamo prehod na pogone SSD. Čeprav so SSD diski veliko hitrejši od vrtenja trdih diskov, še vedno ne ustrezajo razpoložljivosti podatkov v RAM-u. Ta razlika ne izhaja samo iz same zmogljivosti pomnilnika, temveč tudi iz dodatnega dela, ki ga mora baza podatkov opraviti, ko pridobi podatke iz diska ali SSD-pomnilnika.
Z nedavnimi izboljšavami strojne opreme je vse bolj mogoče shraniti bazo podatkov v pomnilnik - ne glede na to, ali uporabljate oblak ali upravljate lastno strojno opremo.
Še boljša novica je, da vam ni treba vstaviti vse svoje baze podatkov v pomnilnik, da bi dobili večino prednosti delovanja v pomnilniku. Delovni nabor podatkov morate le vstaviti v pomnilnik - podatke, do katerih najpogosteje dostopate.
Morda ste že videli nekaj člankov, ki vsebujejo določene številke o tem, kateri del zbirke podatkov bi morali hraniti v spominu, in sicer od 10 do 33 odstotkov. Pravzaprav ne obstaja številka "ena velikost za vse". Količina podatkov, ki jo je mogoče spraviti v pomnilnik za najboljšo prednost v zmogljivosti, je povezana z delovno obremenitvijo. Namesto da bi iskali določeno "čarobno" številko, bi morali preveriti, koliko V / I baze podatkov deluje v svojem stabilnem stanju (običajno nekaj ur po zagonu). Oglejte si branja, saj je branje mogoče popolnoma odstraniti, če je vaša baza podatkov v pomnilniku. Pisanje se bo vedno moralo zgoditi, ne glede na količino pomnilnika, ki ga imate na voljo.
Spodaj si lahko ogledate V / I, ki se dogaja na grafikonu V / I InnoDB na nadzorni plošči InnoDB Metrics sistema Percona Monitoring and Management.
PerconaV zgornjem grafu vidite konice do 2.000 V / I operacij na sekundo, kar kaže, da (vsaj za nekatere dele delovne obremenitve) delovni niz baze podatkov ne ustreza dobro pomnilniku.
Ključ za optimizacijo MySQL # 5: Uporabite SSD pomnilnik
Če vaša zbirka podatkov ne ustreza pomnilniku (in tudi če je), še vedno potrebujete hitro shrambo za obdelavo zapisov in preprečevanje težav z zmogljivostjo, ko se baza podatkov ogreje (takoj po ponovnem zagonu). Dandanes hitro shranjevanje pomeni SSD-je.
