Peter Zaitsev je spoluzakladateľom a generálnym riaditeľom spoločnostiPercona.
Jedným z spôsobov, ako merame aplikácie, je výkon. Jednou z metrík výkonu aplikácie je používateľská skúsenosť, ktorá sa vo všeobecnosti prekladá ako „musel používateľ čakať dlhšie, ako je primerané množstvo času, aby dosiahol to, čo chcel.“
Táto metrika môže v rôznych scenároch znamenať rôzne veci. V prípade mobilnej aplikácie na nakupovanie nemôžu byť časy odozvy dlhšie ako pár sekúnd. Môže sa stať, že na stránke HR zamestnanca bude trvať odpoveď o pár sekúnd dlhšie.
Máme veľa výskumov o tom, ako výkon ovplyvňuje správanie používateľa:
- 79 percent zákazníkov sa s menšou pravdepodobnosťou vráti na pomalý web
- 47 percent spotrebiteľov očakáva, že sa webová stránka načíta za 2 sekundy alebo menej
- 40 percent používateľov opustí webovú stránku, ak jej načítanie trvá dlhšie ako tri sekundy
- Jednosekundové oneskorenie v načítaní stránky môže spôsobiť 7% stratu konverzie a 11% menej zobrazení stránky
Nech je štandard akýkoľvek, je nevyhnutné udržiavať dobrý výkon aplikácií. V opačnom prípade sa používatelia sťažujú (alebo v horšom prípade prejdú do inej aplikácie). Jedným z faktorov, ktorý ovplyvňuje výkon aplikácií, je výkon databázy. Interakcia medzi aplikáciami, webovými stránkami a databázami je rozhodujúca pri určovaní úrovne výkonu aplikácie.
Ústredným komponentom tejto interakcie je to, ako aplikácie vyhľadávajú databázu a ako databáza reaguje na požiadavky. V každom prípade je MySQL jedným z najpopulárnejších systémov na správu databáz. Viac podnikov prechádza na MySQL (a ďalšie otvorené databázy) ako databázové riešenie vo svojich produkčných prostrediach.
Existuje mnoho metód konfigurácie MySQL, ktoré vám môžu pomôcť zaistiť, aby vaša databáza odpovedala na dotazy rýchlo a s minimálnym znížením výkonu aplikácie.
Nasleduje niekoľko základných tipov, ktoré vám pomôžu optimalizovať výkon vašej databázy MySQL.
Kľúč 1 pre optimalizáciu MySQL: Naučte sa, ako používať VYSVETLENIE
Dve najdôležitejšie rozhodnutia, ktoré urobíte s ľubovoľnou databázou, sú návrh spôsobu mapovania vzťahov medzi entitami aplikácie na tabuľky (schéma databázy) a návrh, ako aplikácie získajú potrebné údaje vo formáte, ktorý potrebujú (dotazy).
Komplikované aplikácie môžu mať komplikované schémy a dotazy. Ak sa chystáte dosiahnuť výkon a rozsah, ktorý vaše aplikácie vyžadujú, môžete nielen rátať s intuíciou, aby ste pochopili, ako sa budú dotazy vykonávať.
Namiesto hádania a dúfania by ste sa mali naučiť používať VYSVETLENIE príkaz. Tento príkaz vám ukáže, ako sa bude dotaz vykonávať, a poskytne vám prehľad o tom, aký výkon môžete očakávať, a o tom, ako sa bude škálovať dopyt so zmenou veľkosti údajov.
Existuje množstvo nástrojov - napríklad MySQL Workbench -, ktoré umožňujú vizualizáciu VYSVETLENIE výstup pre vás, ale stále musíte pochopiť základné informácie, aby ste to pochopili.
Existujú dva rôzne formáty, v ktorých VYSVETLENIE príkaz poskytuje výstup: staromódny formát tabuľky a modernejší štruktúrovaný dokument JSON, ktorý poskytuje podstatne viac podrobností (zobrazené nižšie):
mysql> vysvetlite format = json vyberte priem (k) z sbtest1 kde id medzi 1000 a 2000 \ G**************************** 1. riadok ******************** *******
VYSVETLENIE: {
„Query_block“: {
“Select_id”: 1,
„Cost_info“: {
„Query_cost“: „762,40“
„Table“: {
„Table_name“: „sbtest1“,
“Access_type”: “range”,
„Possible_keys“: [
"PRIMÁRNY"
],
„Key“: „PRIMARY“,
“Used_key_parts”: [
„Id“
],
“Key_length”: “4”,
“Lines_examined_per_scan”: 1874,
“Lines_produced_per_join”: 1874,
“Filtrované”: “100,00”,
„Cost_info“: {
“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` medzi 1000 a 2000) “
}
}
}
Jedným z komponentov, na ktoré by ste sa mali zamerať, sú „náklady na dopyt“. Náklady na dopyt sa vzťahujú na to, ako drahý MySQL považuje tento konkrétny dotaz z hľadiska celkových nákladov na jeho vykonanie, a je založený na mnohých rôznych faktoroch.
Jednoduché dotazy majú spravidla náklady na dopyt menej ako 1 000. Dopyty s cenou od 1 000 do 100 000 sa považujú za dotazy so strednými nákladmi. Spravidla sú rýchle, ak spustíte iba stovky takýchto dotazov za sekundu (nie desaťtisíce).
Dotazy s cenou viac ako 100 000 sú drahé dotazy. Tieto dotazy budú často bežať rýchlo, keď ste v systéme jediným používateľom, ale mali by ste si dobre premyslieť, ako často tieto dotazy vo svojich interaktívnych aplikáciách používate (najmä keď počet používateľov rastie).
Jedná sa samozrejme o čísla výkonnosti lopty, ale ukazujú všeobecný princíp. Váš systém môže spracovávať pracovné zaťaženia dotazov lepšie alebo horšie, v závislosti od jeho architektúry a konfigurácie.
Medzi faktory, ktoré určujú cenu dotazu, patrí hlavne to, či dotaz správne používa indexy. The VYSVETLENIE príkaz vám povie, či dopyt nepoužíva indexy (zvyčajne kvôli tomu, ako sú indexy vytvorené v databáze alebo ako je samotný dotaz zostavený). Preto je také dôležité naučiť sa používať VYSVETLENIE.
Kľúč 2 pre optimalizáciu MySQL: Vytvorte správne indexy
Index zlepšuje výkonnosť dotazu znížením množstva údajov v databáze, ktoré musia dotazy skenovať. Indexy v MySQL sa používajú na urýchlenie prístupu do databázy a na pomoc pri vynucovaní databázových obmedzení (napr JEDINEČNÉ a CUDZÍ KĽÚČ).
Indexy databázy sú podobné indexom kníh. Sú uchovávané na svojom vlastnom mieste a obsahujú informácie, ktoré sa už nachádzajú v hlavnej databáze. Sú referenčnou metódou alebo mapou na miesto, kde sa nachádzajú údaje. Indexy nemenia žiadne údaje v databáze. Jednoducho ukazujú na umiestnenie údajov.
Neexistujú žiadne indexy, ktoré by boli vždy správne pre akékoľvek pracovné zaťaženie. Na indexy by ste sa mali vždy pozerať v kontexte dotazov, ktoré systém spúšťa.
Dobre indexované databázy nielenže bežia rýchlejšie, ale aj jediný chýbajúci index môže spomaliť prehľadávanie databázy. Použite VYSVETLENIE (ako sa predtým odporúčalo) vyhľadať chýbajúce indexy a pridať ich. Ale buďte opatrní: Nepridávajte indexy, ktoré nepotrebujete! Zbytočné indexovanie spomaľuje databázy (pozrite si moju prezentáciu o osvedčených postupoch indexovania MySQL).
Kľúč 3 pre optimalizáciu MySQL: Žiadne predvolené nastavenia!
Ako každý softvér, aj MySQL má mnoho konfigurovateľných nastavení, ktoré možno použiť na úpravu správania (a nakoniec aj výkonu). Rovnako ako každý softvér, mnoho z týchto konfigurovateľných nastavení správcovia ignorujú a nakoniec sa použijú v predvolenom režime.
Aby ste dosiahli najlepší výkon MySQL, je dôležité porozumieť konfigurovateľným nastaveniam MySQL a - čo je dôležitejšie - nastaviť ich tak, aby fungovali čo najlepšie pre vaše databázové prostredie.
V predvolenom nastavení je MySQL vyladený pre inštaláciu malého rozsahu, nie pre produkčný rozsah. Zvyčajne chcete nakonfigurovať MySQL tak, aby využívala všetky dostupné pamäťové prostriedky a aby umožňovala počet pripojení, ktoré vaša aplikácia vyžaduje.
Tu sú tri nastavenia vyladenia výkonu MySQL, ktoré by ste mali vždy dôkladne preskúmať:
innodb_buffer_pool_size: V oblasti vyrovnávacej pamäte sa ukladajú údaje a indexy do medzipamäte. To je hlavný dôvod používania systému s veľkým množstvom pamäte RAM ako databázového servera. Ak spúšťate iba úložný modul InnoDB, zvyčajne pre oblasť vyrovnávacej pamäte vyhradíte asi 80 percent svojej pamäte. Ak prevádzkujete veľmi komplikované dotazy alebo máte veľmi veľký počet súbežných pripojení k databáze alebo máte veľmi veľký počet tabuliek, bude pravdepodobne potrebné znížiť túto hodnotu, aby ste pridelili viac pamäte na iné účely.
Keď nastavujete veľkosť oblasti vyrovnávacej pamäte InnoDB, musíte sa uistiť, že ju nenastavíte príliš veľkú, pretože to spôsobí zámenu. Toto absolútne zabíja výkon vašej databázy. Ľahký spôsob, ako to skontrolovať, je pozrieť sa na Výmenu aktivity v grafe Prehľad systému v aplikácii Percona Monitoring and Management:
PerconaAko ukazuje tento graf, niektoré výmeny sú vždy v poriadku. Ak však zaznamenáte trvalú aktivitu výmeny 1 MB za sekundu alebo viac, budete musieť zmenšiť veľkosť oblasti vyrovnávacej pamäte (alebo iné využitie pamäte).
Ak nedostanete hodnotu pre innodb_buffer_pool_size správne na prvý pokus, nebojte sa. Počnúc MySQL 5.7 môžete dynamicky meniť veľkosť oblasti vyrovnávacej pamäte InnoDB bez reštartovania databázového servera.
innodb_log_file_size: Toto je veľkosť jedného súboru protokolu InnoDB. InnoDB štandardne používa dve hodnoty, aby ste mohli toto číslo zdvojnásobiť, aby ste získali veľkosť kruhového priestoru pre opakované protokoly, ktoré InnoDB používa, aby sa ubezpečil, že vaše transakcie sú trvácne. To tiež optimalizuje použitie zmien v databáze. Nastavenie innodb_log_file_size je otázkou kompromisov. Čím väčšie prerozdelené miesto pridelíte, tým lepší bude výkon, ktorý dosiahnete pri pracovnom zaťažení náročnom na zápis, ale čím dlhšie bude trvať zotavenie po zlyhaní, ak dôjde k strate napájania alebo iným problémom.
Ako viete, či je váš výkon MySQL obmedzený vašou aktuálnou veľkosťou súboru protokolu InnoDB? Poznáte to tak, že sa pozriete na to, koľko využiteľného priestoru na opakované protokoly sa v skutočnosti využíva. Najjednoduchším spôsobom je pozrieť sa na dashboard Percona Monitoring and Management InnoDB Metrics. V nižšie uvedenom grafe nie je veľkosť súboru protokolu InnoDB dostatočne veľká, pretože použitý priestor sa posúva veľmi blízko k tomu, koľko využiteľného priestoru na opätovné zaznamenanie je k dispozícii (označené červenou čiarou). Veľkosť vášho denníka by mala byť minimálne o 20 percent väčšia ako veľkosť miesta použitého na optimálne fungovanie vášho systému.
Perconamax_connections: Rozsiahle aplikácie často vyžadujú oveľa viac, ako je predvolený počet pripojení. Na rozdiel od iných premenných, pokiaľ ich nenastavíte správne, nebudete mať problémy s výkonom (ako také). Namiesto toho, ak počet pripojení nie je dostatočný pre potreby vašej aplikácie, vaša aplikácia sa jednoducho nebude môcť pripojiť k databáze (čo vašim používateľom vyzerá ako výpadok). Správne uvedenie tejto premennej je dôležité.
Môže byť ťažké vedieť, koľko pripojení potrebujete pre zložité aplikácie s mnohými komponentmi bežiacimi na viacerých serveroch. MySQL našťastie umožňuje veľmi ľahko zistiť, koľko pripojení sa používa v špičkovej prevádzke. Spravidla sa chcete ubezpečiť, že medzi maximálnym počtom pripojení, ktoré vaša aplikácia používa, a maximálnym počtom pripojení, ktoré sú k dispozícii, je minimálne 30-percentná medzera. Ľahký spôsob, ako zobraziť tieto čísla, je použitie grafu pripojenia MySQL na hlavnom paneli prehľadu MySQL v časti Monitorovanie a správa Percona. Nasledujúci graf ukazuje zdravý systém, v ktorom je k dispozícii veľké množstvo ďalších pripojení.
PerconaPamätajte na to, že ak vaša databáza beží pomaly, aplikácie často vytvárajú nadmerný počet pripojení. V takýchto prípadoch by ste mali pracovať skôr na probléme s výkonom databázy, ako jednoducho povoliť viac pripojení. Viac spojení môže zhoršiť základný problém s výkonom.
(Poznámka: Keď nastavíte max_connections premenná výrazne vyššia ako predvolená hodnota, musíte často zvážiť zvýšenie ďalších parametrov, ako je veľkosť medzipamäte tabuľky a počet otvorených súborov, ktoré MySQL umožňuje. To však presahuje rámec tohto článku.)
Kľúč 4 pre optimalizáciu MySQL: Udržujte databázu v pamäti
V posledných rokoch sme zaznamenali prechod na jednotky SSD (Solid State Drive). Aj keď sú disky SSD oveľa rýchlejšie ako disky s pevným diskom, stále nemajú porovnanie s dostupnosťou údajov v pamäti RAM. Tento rozdiel pochádza nielen od samotného výkonu úložiska, ale aj od ďalšej práce, ktorú musí databáza urobiť, keď načíta údaje z disku alebo úložiska SSD.
Vďaka nedávnym vylepšeniam hardvéru je čoraz viac možné dostať vašu databázu do pamäte - či už bežíte v cloude alebo spravujete svoj vlastný hardvér.
Ešte lepšou správou je, že na získanie väčšiny výhod výkonu v pamäti nemusíte vložiť celú svoju databázu do pamäte. Musíte len vložiť pracovnú sadu údajov do pamäte - dáta, ku ktorým sa pristupuje najčastejšie.
Možno ste videli niektoré články, ktoré poskytujú konkrétne čísla o tom, akú časť databázy by ste si mali uchovať v pamäti, a to v rozmedzí od 10 percent do 33 percent. V skutočnosti neexistuje žiadne číslo „jedna veľkosť pre všetkých“. Množstvo dát, ktoré sa zmestí do pamäte, aby sa dosiahla najlepšia výhoda výkonu, súvisí s pracovným zaťažením. Namiesto hľadania konkrétneho „magického“ čísla by ste mali skontrolovať, koľko vstupno-výstupných operácií beží databáza v ustálenom stave (zvyčajne niekoľko hodín po spustení). Pozrite sa na čítania, pretože čítania môžu byť úplne vylúčené, ak je vaša databáza v pamäti. K zápisom bude vždy potrebné dôjsť bez ohľadu na to, koľko pamäte máte k dispozícii.
Ďalej vidíte I / O deje v I / O grafe InnoDB na dashboarde InnoDB Metrics v Percona Monitoring and Management.
PerconaV grafe vyššie vidíte hroty vysoké až 2 000 I / O operácií za sekundu, čo ukazuje, že (aspoň pre niektoré časti pracovnej záťaže) pracovná sada databázy dobre nezapadá do pamäte.
Kľúč 5 na optimalizáciu MySQL: Používajte úložisko SSD
Ak sa vaša databáza nezmestí do pamäte (a aj keď to tak je), stále potrebujete rýchle úložisko na zvládnutie zápisov a na zabránenie problémom s výkonom pri zahrievaní databázy (hneď po reštarte). V dnešnej dobe rýchle úložisko znamená SSD.
