Programavimas

10 esminių „MySQL“ našumo patarimų

Kaip ir visose reliacinėse duomenų bazėse, „MySQL“ gali pasirodyti esąs sudėtingas žvėris, kuris gali akimirksniu nuskaityti iki galo, palikdamas jūsų programas rimtai, o jūsų verslas - linijoje.

Tiesa, dažniausiai „MySQL“ našumo problemos yra dažniausiai pasitaikančios klaidos. Norint užtikrinti, kad „MySQL“ serveris dundėtų maksimaliu greičiu ir užtikrintų stabilų bei nuoseklų veikimą, svarbu pašalinti šias klaidas, kurias dažnai užgožia kai kurie jūsų darbo krūvio subtilumai ar konfigūracijos spąstai.

Laimei, daugelis „MySQL“ našumo problemų yra panašių sprendimų, todėl trikčių šalinimas ir „MySQL“ derinimas tampa valdoma užduotimi.

Čia yra 10 patarimų, kaip pasiekti puikių „MySQL“ rezultatų.

„MySQL“ patarimas dėl našumo Nr. 1: apibūdinkite savo darbo krūvį

Geriausias būdas suprasti, kaip jūsų serveris praleidžia laiką, yra serverio darbo krūvio profiliavimas. Apibendrindami savo darbo krūvį, galite atskleisti brangiausias užklausas tolesniam derinimui. Čia svarbiausia metrika yra laikas, nes kai pateikiate užklausą prieš serverį, jums labai mažai kas rūpi, išskyrus tai, kaip greitai ji baigiama.

Geriausias būdas apibūdinti savo darbo krūvį yra įrankis, pvz., „MySQL Enterprise Monitor“ užklausų analizatorius arba „Percona Toolkit“ pateiktas pt-query-digest. Šie įrankiai užfiksuoja užklausas, kurias vykdo serveris, ir pateikia užduočių lentelę, surūšiuotą mažėjant atsakymo laiko eilės tvarkai, iškart į viršų sukurdamas brangiausias ir daug laiko reikalaujančias užduotis, kad galėtumėte pamatyti, kur sutelkti savo pastangas.

Darbo krūvio profiliavimo įrankiai sugrupuoja panašias užklausas, kad galėtumėte pamatyti lėtas ir greitas, bet daug kartų vykdomas užklausas.

„MySQL“ patarimas Nr. 2: supraskite keturis pagrindinius išteklius

Kad veiktų, duomenų bazės serveriui reikia keturių pagrindinių išteklių: procesoriaus, atminties, disko ir tinklo. Jei kuri nors iš jų yra silpna, nepastovi ar perkrauta, duomenų bazės serveris greičiausiai veikia blogai.

Suprasti pagrindinius išteklius svarbu dviejose konkrečiose srityse: aparatinės įrangos pasirinkimo ir trikčių šalinimo problemose.

Pasirinkdami „MySQL“ aparatinę įrangą, užtikrinkite, kad visi komponentai veiktų gerai. Lygiai taip pat svarbu, subalansuokite juos pakankamai gerai. Dažnai organizacijos pasirenka serverius su greitais procesoriais ir diskais, tačiau trūksta atminties. Kai kuriais atvejais atminties pridėjimas yra pigus būdas padidinti našumą didumo eilėmis, ypač darbo krūviams, susietiems su disku. Tai gali atrodyti nenaudinga, tačiau daugeliu atvejų diskai yra per daug naudojami, nes nepakanka atminties, kad būtų galima laikyti serverio darbo rinkinį.

Kitas geras šio balanso pavyzdys yra susijęs su procesoriais. Daugeliu atvejų „MySQL“ gerai veiks su greitais procesoriais, nes kiekviena užklausa vykdoma viena gija ir negali būti lygiagreti procesoriams.

Kalbant apie trikčių šalinimą, patikrinkite visų keturių išteklių našumą ir naudojimą, atidžiai stebėdami, ar jų darbas yra prastas, ar tiesiog jų prašoma atlikti per daug darbo. Šios žinios gali padėti greitai išspręsti problemas.

„MySQL“ našumo patarimas Nr. 3: nenaudokite „MySQL“ kaip eilės

Eilės ir į eilę panašūs prieigos modeliai gali patekti į jūsų programą to nežinant. Pvz., Jei nustatote elemento būseną taip, kad konkretus darbuotojo procesas galėtų pareikšti pretenziją prieš jį veikdamas, tada netyčia sukuriate eilę. El. Laiškų žymėjimas kaip neišsiųstų, siuntimas, tada pažymėjimas kaip išsiųstais yra įprastas pavyzdys.

Eilės kelia problemų dėl dviejų pagrindinių priežasčių: jos sudaro jūsų darbo krūvį, neleisdamos užduočių atlikti lygiagrečiai, todėl dažnai pateikiama lentelė, kurioje yra nebaigtas darbas, taip pat seniai apdorotų darbų istoriniai duomenys. Abi programos prideda delsą ir įkelia į „MySQL“.

„MySQL“ našumo patarimas Nr. 4: pirmiausia filtruokite rezultatus pagal pigiausius

Puikus būdas optimizuoti „MySQL“ yra pirmiausia atlikti pigius, netikslius darbus, tada sunkų ir tikslų darbą su mažesniu gautu duomenų rinkiniu.

Pavyzdžiui, tarkime, kad kažko ieškote tam tikru geografinio taško spinduliu. Pirmasis įrankis daugelio programuotojų įrankių rinkinyje yra didžiojo apskritimo (Haversine) formulė, skirta apskaičiuoti atstumą išilgai sferos paviršiaus. Šios technikos problema yra ta, kad formulė reikalauja daug trigonometrinių operacijų, kurios yra labai intensyvios procesoriaus. Skaičiavimai dideliu ratu paprastai vyksta lėtai, todėl mašinos procesoriaus naudojimas pakyla į viršų.

Prieš taikydami „didelio apskritimo“ formulę, sumažinkite įrašus mažam pogrupiui sumos ir supjaustykite gautą rinkinį tiksliai. Kvadratas, kuriame yra apskritimas (tiksliai ar netiksliai), yra paprastas būdas tai padaryti. Tokiu būdu už aikštės ribų esantis pasaulis niekada nepasiekia visų tų brangių trigimo funkcijų.

„MySQL“ patarimas dėl našumo Nr. 5: Žinokite du mirties spąstus apie mastelio keitimą

Mastelio mastas nėra toks neapibrėžtas, kaip galite tikėti. Tiesą sakant, yra tikslūs matematiniai mastelio apibrėžimai, kurie išreiškiami lygtimis. Šios lygtys pabrėžia, kodėl sistemos nesikeičia taip gerai, kaip turėtų.

Paimkime universalų mastelio įstatymą, apibrėžimą, kuris yra patogus išreikšti ir kiekybiškai įvertinti sistemos mastelio charakteristikas. Jame mastelio problemos paaiškinamos dviem pagrindinėmis sąnaudomis: serializavimu ir peržengimu.

Lygiagrečiai vykstantys procesai, kurie turi sustoti, kad kažkas vyktų serializuotu, iš esmės yra ribojami. Panašiai, jei lygiagrečiai vykstantiems procesams reikia nuolat bendrauti tarpusavyje, kad būtų koordinuojamas jų darbas, jie vienas kitą riboja.

Venkite serializavimo ir nesusipratimų, ir jūsų programa bus žymiai geresnė. Ką tai reiškia „MySQL“ viduje? Jis skiriasi, tačiau kai kuriais pavyzdžiais būtų išvengta išskirtinių spynų ant eilučių. Dėl šios priežasties 3 punkte nurodytos eilės paprastai menkai masto.

„MySQL“ patarimas dėl našumo Nr. 6: per daug nesikoncentruokite į konfigūraciją

DBA paprastai praleidžia daug laiko, keisdami konfigūracijas. Rezultatas paprastai nėra didelis pagerėjimas ir kartais gali būti labai žalingas. Mačiau daug „optimizuotų“ serverių, kurie nuolat gedo, pritrūko atminties ir veikė prastai, kai šiek tiek padidėjo darbo krūvis.

Numatytieji numatytieji „MySQL“ variantai yra vieno dydžio ir labai pasenę, tačiau jums nereikia visko sukonfigūruoti. Geriau sutvarkyti pagrindus ir pakeisti kitus nustatymus tik prireikus. Daugeliu atvejų, jei tinkamai nustatote maždaug 10 parinkčių, galite pasiekti 95 procentus didžiausio serverio našumo. Kelios situacijos, kai tai netaikoma, bus išskirtinės jūsų aplinkybėms.

Daugeliu atvejų serverio „derinimo“ įrankiai nerekomenduojami, nes jie paprastai pateikia gaires, kurios nėra prasmingos konkrečiais atvejais. Kai kurie netgi turi užkoduotų pavojingų, netikslių patarimų, tokių kaip talpyklos paspaudimų rodikliai ir atminties suvartojimo formulės. Tai niekada nebuvo teisinga, o laikui bėgant jie tapo dar mažiau teisingi.

„MySQL“ patarimas dėl našumo Nr. 7: saugokitės puslapių užklausų

Programos, kurios pagina, paprastai priverčia serverį atsiklaupti. Rodydamos rezultatų puslapį su nuoroda, kad pereitumėte į kitą puslapį, šios programos paprastai grupuojamos ir rūšiuojamos taip, kad negalėtų naudoti rodyklių, ir jos naudoja LIMIT ir kompensuoti dėl to serveris daug dirba generuodamas, tada išmesdamas eiles.

Optimizavimą dažnai galima rasti pačioje vartotojo sąsajoje. Užuot rodę tikslų rezultatų skaičių puslapiuose ir nuorodas į kiekvieną puslapį atskirai, galite tiesiog parodyti nuorodą į kitą puslapį. Taip pat galite užkirsti kelią žmonėms eiti į puslapius per toli nuo pirmo puslapio.

Užklausos pusėje, užuot naudojus LIMIT su kompensuoti, galite pasirinkti dar vieną eilutę nei jums reikia, o kai vartotojas spusteli nuorodą „kitas puslapis“, galite paskirti tą paskutinę eilutę kaip pradinį tašką kitam rezultatų rinkiniui. Pvz., Jei vartotojas peržiūrėjo puslapį su 101–120 eilutėmis, taip pat pasirinktumėte 121 eilutę; norėdami pateikti kitą puslapį, turėtumėte pateikti užklausą serveriui, jei eilutės yra didesnės arba lygios 121, 21 limitas.

„MySQL“ patarimas dėl našumo Nr. 8: noriai išsaugokite statistiką, nenoriai įspėkite

Stebėjimas ir perspėjimas yra būtini, tačiau kas nutinka tipinei stebėjimo sistemai? Pradedama siųsti klaidingą informaciją, o sistemos administratoriai nustato el. Pašto filtravimo taisykles, kad sustabdytų triukšmą. Netrukus jūsų stebėjimo sistema yra visiškai nenaudinga.

Man patinka galvoti apie stebėjimą dviem būdais: fiksuoti metriką ir įspėti. Labai svarbu užfiksuoti ir išsaugoti visas metrikas, kurias tik įmanoma, nes džiaugsitės jas turėdami, kai bandysite išsiaiškinti, kas pasikeitė sistemoje. Kada nors iškils keista problema, ir jums patiks galimybė nurodyti grafiką ir parodyti serverio darbo krūvio pasikeitimą.

Priešingai, yra tendencija įspėti per daug. Žmonės dažnai įspėja apie buferio įvykių santykį ar laikinų lentelių, sukurtų per sekundę, skaičių. Problema ta, kad tokiam santykiui nėra gero slenksčio. Teisingas slenkstis skiriasi ne tik kiekviename serveryje, bet ir valandomis, nes keičiasi jūsų darbo krūvis.

Dėl to įspėkite saikingai ir tik tokiomis sąlygomis, kurios nurodo aiškią, veiksmo problemą. Mažas buferio įvykio koeficientas nėra veiksmas, taip pat tai nerodo tikros problemos, tačiau serveris, kuris neatsako į bandymą prisijungti, yra aktuali problema, kurią reikia išspręsti.

„MySQL“ patarimas dėl našumo Nr. 9: sužinokite tris indeksavimo taisykles

Indeksavimas yra bene labiausiai nesuprasta duomenų bazių tema, nes yra labai daug būdų supainioti, kaip veikia indeksai ir kaip serveris juos naudoja. Norint iš tikrųjų suprasti, kas vyksta, reikia daug pastangų.

Tinkamai sukonstruoti indeksai duomenų bazės serveryje tarnauja trims svarbiems tikslams:

  1. Indeksai leidžia serveriui rasti gretimų eilučių grupes vietoj atskirų eilučių. Daugelis žmonių mano, kad indekso tikslas yra rasti atskiras eilutes, tačiau radus atskiras eilutes, atsitiktinės disko operacijos vyksta lėtai. Daug geriau rasti eilučių grupes, kurios visos arba dauguma jų yra įdomios, nei rasti eiles po vieną.
  2. Indeksai leidžia serveriui išvengti rūšiavimo skaitant eilutes norima tvarka. Rūšiavimas yra brangus. Eilutes skaitykite norima tvarka daug greičiau.
  3. Indeksai leidžia serveriui patenkinti visas užklausas vien iš rodyklės, kad būtų išvengta poreikio patekti į lentelę. Tai įvairiai vadinama uždengiamuoju indeksu arba tik indekso užklausa.

Jei galite sukurti savo indeksus ir užklausas, kad išnaudotumėte šias tris galimybes, galite padaryti savo užklausas keliais dydžiais greičiau.

„MySQL“ patarimas dėl našumo Nr. 10: Pasitelkite savo bendraamžių patirtį

Nesistenkite to atlikti vienas. Jei nesuprantate problemos ir darote tai, kas jums atrodo logiška ir protinga, tai puiku. Tai veiks maždaug 19 kartų iš 20. Kitą kartą nužengsite į triušio skylę, kuri bus labai brangi ir daug laiko reikalaujanti būtent dėl ​​to, kad bandomas sprendimas atrodo labai prasmingas.

Sukurkite su „MySQL“ susijusių išteklių tinklą - ir tai išeina už įrankių rinkinių ir trikčių šalinimo vadovų. Yra keletas nepaprastai nusimanančių žmonių, tykančių pašto adresų sąrašuose, forumuose, klausimų ir atsakymų svetainėse ir pan. Konferencijos, parodos ir vietiniai vartotojų grupės renginiai suteikia vertingų galimybių įgyti įžvalgų ir užmegzti santykius su bendraamžiais, kurie gali jums padėti.

Ieškantiems įrankių, kurie papildytų šiuos patarimus, galite peržiūrėti „MySQL“ „Percona“ konfigūravimo vedlį, „MySQL“ „Percona Query Advisor“ ir „Percona Monitoring“ papildinius. (Pastaba: norėdami pasiekti šias dvi pirmąsias nuorodas, turėsite susikurti „Percona“ paskyrą. Tai nemokama.) Konfigūravimo vedlys gali padėti sukurti pagrindinį failą my.cnf naujam serveriui, kuris yra pranašesnis už failų pavyzdžius, kurie siunčiami kartu su serverio. Užklausų patarėjas išanalizuos jūsų SQL, kad padėtų aptikti potencialiai blogus modelius, pvz., Puslapių užklausas (Nr. 7). „Percona Monitoring Plugins“ yra stebėjimo ir grafikos įskiepių rinkinys, padedantis noriai išsaugoti statistiką ir nenoriai įspėti (Nr. 8). Visi šie įrankiai yra laisvai prieinami.