Kot pri vseh relacijskih zbirkah podatkov se tudi MySQL lahko izkaže za zapleteno zver, ki se lahko takoj ustavi, tako da vaše aplikacije ostanejo na cedilu, vaše podjetje pa na cedilu.
Resnica je, da so najpogostejše napake osnova večine težav z zmogljivostjo MySQL. Da bi zagotovili, da strežnik MySQL brni z največjo hitrostjo in zagotavlja stabilno in dosledno delovanje, je pomembno, da odpravite te napake, ki so pogosto zakrite z nekaj prefinjenosti v vaši delovni obremenitvi ali konfiguracijski past.
Na srečo se izkaže, da imajo številne težave z zmogljivostjo MySQL podobne rešitve, zaradi česar je odpravljanje težav in nastavitev MySQL obvladljiva naloga.
Tu je 10 nasvetov za izboljšanje učinkovitosti MySQL.
Nasvet za zmogljivost MySQL št. 1: Profilite svojo delovno obremenitev
Najboljši način, kako razumeti, kako vaš strežnik preživi svoj čas, je profiliranje delovne obremenitve strežnika. S profiliranjem delovne obremenitve lahko izpostavite najdražje poizvedbe za nadaljnje uglaševanje. Tu je čas najpomembnejša meritev, kajti ko pošljete poizvedbo proti strežniku, vas zelo nič ne zanima, razen kako hitro se dokonča.
Najboljši način za profiliranje vaše delovne obremenitve je orodje, kot je analizator poizvedb MySQL Enterprise Monitor ali pt-query-digest iz Percona Toolkit. Ta orodja zajemajo poizvedbe, ki jih strežnik izvede, in vrnejo tabelo opravil, razvrščenih po padajočem vrstnem redu odzivnega časa, tako da najdražja in najzahtevnejša opravila takoj preusmerijo na vrh, tako da lahko vidite, kam usmeriti svoja prizadevanja.
Orodja za profiliranje delovnih obremenitev združujejo podobne poizvedbe, tako da si lahko ogledate poizvedbe, ki so počasne, pa tudi hitre, a izvedene večkrat.
Nasvet za zmogljivost MySQL št. 2: Razumevanje štirih temeljnih virov
Strežnik baz podatkov za svoje delovanje potrebuje štiri temeljne vire: CPU, pomnilnik, disk in omrežje. Če je katera od teh šibka, neredna ali preobremenjena, potem strežnik baz podatkov zelo verjetno deluje slabo.
Razumevanje temeljnih virov je pomembno na dveh posebnih področjih: izbira strojne opreme in odpravljanje težav.
Pri izbiri strojne opreme za MySQL poskrbite za dobro delujoče komponente vsepovsod. Enako pomembno je, da jih primerno dobro uravnotežite med seboj. Organizacije pogosto izberejo strežnike s hitrimi CPU-ji in diski, ki pa so pomrzni. V nekaterih primerih je dodajanje pomnilnika poceni način povečanja zmogljivosti po velikostih, zlasti pri obremenitvah, ki so vezane na disk. To se morda zdi nerazumljivo, toda v mnogih primerih so diski preveč izkoriščeni, ker ni dovolj pomnilnika za shranjevanje delovnih podatkov strežnika.
Še en dober primer tega ravnotežja je CPU. V večini primerov bo MySQL dobro deloval s hitrimi CPU-ji, ker se vsaka poizvedba izvaja v eni niti in je ni mogoče paralelizirati med CPU-ji.
Ko gre za odpravljanje težav, preverite delovanje in izkoriščenost vseh štirih virov, pri čemer pazljivo ugotovite, ali delujejo slabo ali jih preprosto zahteva preveč dela. To znanje lahko pomaga hitro rešiti težave.
Nasvet za zmogljivost MySQL št. 3: MySQL ne uporabljajte kot čakalne vrste
Čakalne vrste in vzorci dostopa, podobni čakalni vrsti, se lahko prikradejo v vašo aplikacijo, ne da bi to vedeli. Če na primer nastavite stanje elementa tako, da ga lahko določen delovni postopek zahteva, preden začne z njim, potem nehote ustvarite čakalno vrsto. Pogost primer je označevanje e-poštnih sporočil kot poslanih, njihovo pošiljanje in nato označevanje kot poslano.
Čakalne vrste povzročajo težave iz dveh glavnih razlogov: serializirajo vašo delovno obremenitev, preprečujejo vzporedno opravljanje nalog in pogosto privedejo do tabele, ki vsebuje nedokončano delo in pretekle podatke o opravilih, ki so bila že davno obdelana. Oba dodajata zakasnitev v aplikaciji in nalaganje v MySQL.
Nasvet za zmogljivost MySQL št. 4: najprej filtrirajte rezultate po najcenejših
Odličen način za optimizacijo MySQL je najprej poceni, neprecizno delo, nato pa trdo in natančno delo na manjšem naboru podatkov.
Recimo, da iščete nekaj v določenem polmeru geografske točke. Prvo orodje v orodju mnogih programerjev je formula velikega kroga (Haversine) za izračun razdalje vzdolž površine krogle. Težava te tehnike je v tem, da formula zahteva veliko trigonometričnih operacij, ki so zelo zahtevne za CPU. Izračuni velikih krogov ponavadi tečejo počasi in izkoristijo CPU naprave v nebo.
Preden uporabite formulo velikega kroga, zmanjšajte zapise na majhno podmnožico skupnega zneska in nastali niz obrežite na natančen krog. Kvadrat, ki vsebuje krog (natančno ali nenatančno), je enostaven način za to. Tako svet zunaj trga nikoli ne dobi vseh teh dragih trig funkcij.
Nasvet za zmogljivost MySQL št. 5: Spoznajte dve pasti smrti
Razširljivost ni tako nejasna, kot morda mislite. Pravzaprav obstajajo natančne matematične opredelitve razširljivosti, ki so izražene kot enačbe. Te enačbe poudarjajo, zakaj se sistemi ne merijo tako dobro, kot bi morali.
Vzemimo univerzalni zakon o razširljivosti, definicijo, ki je priročna pri izražanju in kvantificiranju značilnosti razširljivosti sistema. Težave s skaliranjem pojasnjuje z vidika dveh temeljnih stroškov: serializacije in preslušavanja.
Vzporedni procesi, ki se morajo ustaviti, da se zgodi nekaj serializiranega, so po naravi omejeni v svoji razširljivosti. Če morajo vzporedni procesi ves čas klepetati med seboj, da uskladijo svoje delo, se medsebojno omejijo.
Izogibajte se serializaciji in preslušanju, vaša aplikacija se bo veliko bolje razširila. Kaj to pomeni v notranjosti MySQL? Razlikuje se, vendar bi bili nekateri primeri izogibanje izključnim ključavnicam v vrsticah. Čakalne vrste, točka št. 3 zgoraj, se zaradi tega pogosto slabo spreminjajo.
Nasvet za zmogljivost MySQL št. 6: Ne osredotočajte se preveč na konfiguracijo
DBA običajno porabijo ogromno časa za prilagajanje konfiguracij. Rezultat običajno ni velik napredek in je včasih lahko celo zelo škodljiv. Videl sem veliko "optimiziranih" strežnikov, ki so se nenehno sesuvali, jim zmanjkovalo pomnilnika in so slabo delovali, ko je bila delovna obremenitev nekoliko intenzivnejša.
Privzete nastavitve, ki jih dobite z MySQL, so enkratne in slabo zastarele, vendar vam ni treba vsega konfigurirati. Bolje je, da pravilno popravite osnove in spremenite druge nastavitve le, če je to potrebno. V večini primerov lahko s pravilno nastavitvijo približno 10 možnosti dosežete 95 odstotkov največje zmogljivosti strežnika. Nekaj primerov, ko to ne velja, bo izjemnih primerov, ki so edinstveni glede na vaše okoliščine.
V večini primerov orodja za »uglaševanje« strežnikov niso priporočljiva, ker ponavadi dajejo smernice, ki za posebne primere niso smiselne. Nekateri imajo celo kodirane nevarne, netočne nasvete - na primer razmerja zadetkov v predpomnilniku in formule porabe pomnilnika. Te nikoli niso imele prav in s časom so postale še manj pravilne.
Nasvet za zmogljivost MySQL št. 7: Pazite na poizvedbe o paginaciji
Aplikacije, ki paginirajo, strežnik običajno postavijo na kolena. Ko vam prikažejo stran z rezultati s povezavo na naslednjo stran, te aplikacije običajno združujejo in razvrščajo na načine, ki ne morejo uporabljati indeksov, in uporabljajo OMEJITEV in odmik ki povzročijo, da strežnik opravi veliko dela pri ustvarjanju in nato zavrže vrstice.
Optimizacije lahko pogosto najdemo v samem uporabniškem vmesniku. Namesto da bi v rezultatih prikazali natančno število strani in povezave do vsake strani posebej, lahko samo prikažete povezavo do naslednje strani. Ljudem lahko tudi preprečite, da bi šli na strani predaleč od prve strani.
Na strani poizvedbe, namesto da bi uporabili OMEJITEV s odmik, lahko izberete še eno vrstico, kot jo potrebujete, in ko uporabnik klikne povezavo »naslednja stran«, lahko to zadnjo vrstico določite kot izhodišče za naslednji niz rezultatov. Če bi si na primer uporabnik ogledoval stran z vrsticami od 101 do 120, bi izbral tudi vrstico 121; če želite upodobiti naslednjo stran, povprašajte strežnik za vrstice, večje ali enake 121, omejitev 21.
Nasvet za zmogljivost MySQL št. 8: Nestrpno shranjujte statistiko in nenamerno opozarjajte
Spremljanje in opozarjanje sta bistvenega pomena, kaj pa se zgodi s tipičnim sistemom spremljanja? Začne pošiljati lažne pozitivne učinke, sistemski skrbniki pa ustavijo pravila filtriranja e-pošte, da ustavijo šum. Kmalu je vaš nadzorni sistem popolnoma neuporaben.
O spremljanju rad razmišljam na dva načina: zajem meritev in opozarjanje. Zelo pomembno je, da zajamete in shranite vse meritve, ki jih morda lahko, ker jih boste veseli, ko boste poskušali ugotoviti, kaj se je v sistemu spremenilo. Nekega dne se bo pojavila nenavadna težava in všeč vam bo možnost, da pokažete na graf in prikažete spremembo delovne obremenitve strežnika.
Nasprotno pa obstaja težnja, da se opozori preveč. Ljudje pogosto opozorijo na stvari, kot je razmerje med zadetki ali število začasnih tabel, ustvarjenih na sekundo. Težava je v tem, da za takšno razmerje ni dobrega praga. Pravi prag se ne razlikuje samo od strežnika do strežnika, ampak tudi od ure do ure, ko se vaša delovna obremenitev spreminja.
Posledično opozarjajte zmerno in samo ob pogojih, ki kažejo na določeno težavo, ki jo je mogoče odpraviti. Nizko razmerje med zadetki medpomnilnika ni izvedljivo in tudi ne kaže na resnično težavo, vendar je strežnik, ki se ne odzove na poskus povezave, dejanska težava, ki jo je treba rešiti.
Nasvet za zmogljivost MySQL št. 9: Spoznajte tri pravila indeksiranja
Indeksiranje je verjetno najbolj napačno razumljena tema v zbirkah podatkov, ker obstaja toliko načinov, kako se zmedeti glede tega, kako indeksi delujejo in kako jih strežnik uporablja. Veliko truda je treba, da resnično razumeš, kaj se dogaja.
Ko so indeksi pravilno zasnovani, imajo v strežniku baz podatkov tri pomembne namene:
- Indeksi omogočajo strežniku, da poišče skupine sosednjih vrstic namesto posameznih vrstic. Mnogi mislijo, da je namen indeksa najti posamezne vrstice, vendar iskanje posameznih vrstic vodi v naključne diskovne operacije, kar je počasno. Veliko bolje je najti skupine vrstic, ki so vse ali večina zanimivih, kot pa poiskati vrstice po vrsti.
- Indeksi omogočajo, da se strežnik izogne razvrščanju z branjem vrstic v želenem vrstnem redu. Razvrščanje je drago. Branje vrstic v želenem vrstnem redu je veliko hitrejše.
- Indeksi omogočajo, da strežnik zadovolji celotne poizvedbe samo iz indeksa, s čimer se izogne potrebi po dostopu do tabele. To je različno znano kot indeks zajema ali poizvedba samo za indeks.
Če lahko svoje indekse in poizvedbe oblikujete tako, da izkoristite te tri priložnosti, lahko poizvedbe hitreje naredite za nekaj velikosti.
Nasvet za uspešnost MySQL št. 10: Izkoristite strokovno znanje svojih vrstnikov
Ne poskušajte iti sami. Če se zmedete nad težavo in počnete tisto, kar se vam zdi logično in smiselno, je to super. To bo delovalo približno 19-krat od 20. Drugič se boste spustili po zajčji luknji, ki bo zelo draga in dolgotrajna, ravno zato, ker se zdi, da ima rešitev, ki jo poskušate, zelo smiselno.
Zgradite mrežo virov, povezanih z MySQL - in to presega nabore orodij in vodnike za odpravljanje težav. Na poštnih seznamih, forumih, spletnih straneh z vprašanji itd. Konference, sejmi in prireditve lokalnih skupin uporabnikov ponujajo dragocene priložnosti za pridobivanje vpogledov in vzpostavljanje odnosov z vrstniki, ki vam lahko v kratkem pomagajo.
Za tiste, ki iščejo orodja za dopolnitev teh nasvetov, si lahko ogledate čarovnika za konfiguracijo Percona za MySQL, svetovalca za poizvedbe Percona za MySQL in vtičnike Percona Monitoring. (Opomba: Za dostop do prvih dveh povezav boste morali ustvariti račun Percona. Brezplačno.) Čarovnik za konfiguracijo vam lahko pomaga ustvariti osnovno datoteko my.cnf za nov strežnik, ki je boljši od vzorčnih datotek, ki so priložene strežnik. Svetovalec za poizvedbe bo analiziral vaš SQL, da bo lažje odkril potencialno slabe vzorce, kot so poizvedbe o paginaciji (št. 7). Vtičniki za spremljanje Percona so nabor vtičnikov za spremljanje in grafično prikazovanje, ki vam pomagajo pri nenehnem shranjevanju statistik in nenamernem opozarjanju (št. 8). Vsa ta orodja so prosto dostopna.
