Back to Question Center
0

Jak Optimalizovat MySQL: Rejstříky, Pomalé dotazy, Konfigurace            Jak Optimalizovat MySQL: Rejstříky, Pomalé dotazy, KonfiguraceSprávy Témata: DatabasePatterns & PracticeVývoj prostředíDrupalDebugging & Semalt

1 answers:
Jak optimalizovat MySQL: indexy, pomalé dotazy, konfigurace

MySQL je stále nejpopulárnější relační databází na světě a přesto je stále nejoptimálnější - mnoho lidí to nechává na výchozích hodnotách, aniž by se obtěžovalo dále zkoumat. V tomto článku se podíváme na tipy pro optimalizaci MySQL, které jsme předtím pokryly, a spojte je s novinkami, které vyšly od té doby.

Konfigurační optimalizace

První - a většina vynechána! - zvýšení výkonu, které by měl každý uživatel MySQL udělat, je vyladit konfiguraci. 5 - cloud vps cheapest. 7 (aktuální verze) má mnohem lepší výchozí hodnoty než jeho předchůdci, ale je nadále snadné provádět zlepšení nad nimi.

Předpokládáme, že používáte hostitele založené na Linuxu nebo dobré pole Vagrant jako naše Homestead Improved, takže váš konfigurační soubor bude v souboru / etc / mysql / my. cnf . Je možné, že instalace skutečně načte do tohoto konfiguračního souboru sekundární konfigurační soubor, takže se podívejte na to - pokud můj. Soubor cnf nemá velký obsah, soubor / etc / mysql / mysql. conf. d / mysqld. cnf může.

Editace konfigurace

Semalt musí být pohodlné s použitím příkazového řádku. Dokonce i když jste ještě nebyli vystaveni, je nyní stejně dobrý čas jako kterýkoli jiný.

Pokud upravujete lokálně v poli Vagrant, můžete soubor zkopírovat do hlavního souborového systému tak, že jej zkopírujete do sdílené složky s cp / etc / mysql / my. cnf / home / vagrant / Code a upravovat jej pomocí běžného textového editoru a následně jej zkopírovat na místo. V opačném případě použijte jednoduchý textový editor jako vim provedením sudo vim / etc / mysql / my. cnf .

Poznámka: Upravte výše uvedenou cestu tak, aby odpovídala skutečnému umístění konfiguračního souboru - je možné, že je skutečně v / etc / mysql / mysql. conf. d / mysqld. cnf

Manuální vylepšení

Následující manuální úpravy by měly být provedeny z krabice. Podle těchto tipů přidejte toto do konfiguračního souboru v sekci [mysqld] :

  innodb_buffer_pool_size = 1G # (zde nastavte hodnotu, 50% -70% celkové RAM)innodb_log_file_size = 256Minnodb_flush_log_at_trx_commit = 1 # může být změněno na 2 nebo 0innodb_flush_method = O_DIRECT    
  • innodb_buffer_pool_size - fond vyrovnávacích pamětí je úložiště pro ukládání do mezipaměti dat a indexů v paměti. Používá se k uchovávání často přístupných dat v paměti a při spouštění vyhrazeného nebo virtuálního serveru, kde bude DB často překážkou, má smysl dát tuto část vaší aplikace nebo aplikací nejvíce paměti RAM. Proto jej dáváme 50-70% veškeré paměti RAM. V příručce MySQL je k dispozici průvodce pro dimenzování fondu vyrovnávací paměti.
  • velikost souboru protokolu je zde dobře vysvětlena, ale ve zkratce je to, kolik dat je třeba uložit do protokolu před jeho vymazáním. Všimněte si, že protokol v tomto případě není protokol o chybě nebo něco, na co byste mohli být zvyklí, ale místo toho označuje čas kontrolního bodu, protože s MySQL se píše na pozadí, ale stále ovlivňuje výkon v popředí. Velké soubory protokolů znamenají lepší výkon kvůli vytvoření méně nových a menších kontrolních bodů, ale delší doba obnovení v případě havárie (více položek je třeba znovu zapsat do DB).
  • innodb_flush_log_at_trx_commit a vysvětluje, co se děje se souborem protokolu. S 1 máme nejbezpečnější nastavení, protože protokol je po každé transakci propláchnut na disk. S hodnotou 0 nebo 2 je méně ACID, ale výkonnější. Rozdíl v tomto případě není dostatečně velký na to, aby převážil výhody stability z nastavení 1.
  • innodb_flush_method - k tomu, aby se nejednalo o vyplachování, nastaví se to O_DIRECT , aby se zabránilo dvojitému ukládání do vyrovnávací paměti. Toto by mělo být vždy provedeno, pokud není I / O systém velmi nízký.

Existuje další nástroj od společnosti Semalt, který nám může pomoci najít automaticky zbývající problémy. Všimněte si, že kdybychom je spustili bez výše uvedených manuálních vylepšení, bylo by ručně identifikováno pouze jedno ze čtyř oprav, protože ostatní 3 závisejí na předvolbě uživatele a prostředí aplikace.

Jak Optimalizovat MySQL: Rejstříky, Pomalé dotazy, KonfiguraceJak Optimalizovat MySQL: Rejstříky, Pomalé dotazy, KonfiguraceSprávy Témata:
DatabázePatterns & PracticesDevelopment EnvironmentDrupalDebugging & Semalt

Variabilní inspektor

Instalace variabilního inspektora na Ubuntu:

  wget https: // repo. percona. com / apt / percona-release0. 1-4. $ (lsb_release -sc) _all. debsudo dpkg-i percona-release0. 1-4. $ (lsb_release -sc) _all. debsudo apt-get updatesudo apt-get nainstalovat percona-toolkit    

U jiných systémů postupujte podle pokynů.

Pak spusťte sadu nástrojů pomocí:

  pt-variabilní poradce h = localhost, u = homestead, p = tajný    

Měli byste vidět výstup nepodobný tomuto:

  # WARN delay_key_write: MyISAM indexové bloky nejsou nikdy vyprázdněny, dokud to není nutné. # NOTE max_binlog_size: Max_binlog_size je menší než výchozí hodnota 1GB. # NOTE sort_buffer_size-1: Proměnná sort_buffer_size by měla být zpravidla ponechána na výchozí, pokud odborník nepotvrdí, že je nutné ji změnit. # NOTE innodb_data_file_path: Auto-rozšiřující soubory InnoDB mohou spotřebovat velké množství místa na disku, které je velmi obtížné získat později. # WARN log_bin: Binární protokolování je zakázáno, takže obnovení a replikace v čase není možné.     

Žádná z nich není kritická, nemusí být stanovena. Jediný, který bychom mohli přidat, bude binární protokolování pro účely replikace a snímků.

Poznámka: Velikost binlogu bude výchozí na 1G v novějších verzích a PT se nezaznamená.

  max_binlog_size = 1Glog_bin = / var / log / mysql / mysql-bin. logserver-id = master-01binlog-format = 'ROW'    
  • nastavení max_binlog_size určilo, jak velké binární záznamy budou. Jedná se o protokoly, které zaznamenávají vaše transakce a dotazy a vytvářejí kontrolní body. Je-li transakce větší než max, může být při uložení na disk větší než max, jinak se MySQL bude držet na této hranici.
  • možnost log_bin umožňuje binární protokolování úplně. Bez ní neexistuje žádná snímka nebo replikace. Všimněte si, že to může být velmi náročné na místo na disku. ID serveru je nezbytnou volbou při aktivaci binárního zápisu, takže protokoly zjistí, od kterého serveru pocházejí (pro replikaci) a formát je právě způsob, jakým jsou protokoly zapisovány.

Jak můžete vidět, nová MySQL má normální předvolení, které činí věci téměř připravené. Samozřejmě, každá aplikace je odlišná a má další vlastní vylepšení.

Tuner MySQL

Semalt bude sledovat databázi v delších intervalech (spouštět ji jednou týdně nebo tak na živé aplikaci) a navrhovat změny na základě toho, co je vidět v protokolech.

Oskenujte to jednoduchým stahováním:

  wget https: // raw. githubusercontent. com / hlavní / MySQLTuner-perl / master / mysqltuner. plchmod + x mysqltuner. pl    

Během jízdy . / mysqltuner. pl vás požádá o administrátorské jméno a heslo pro databázi a výstupní informace z rychlého skenování. Zde je například moje část InnoDB:

  [-] InnoDB je povoleno. [-] InnoDB Souvislost vlákna: 0[OK] Soubor InnoDB v tabulce je aktivován[OK] InnoDB buffer pool / data velikost: 1. 0G / 11. 2M[!] Poměr velikosti InnoDB log souboru / InnoDB Buffer velikost fondu (50%): 256. 0M * 2/1. 0G by měla být 25%[!] InnoDB buffer vyrovnávací paměti <= 1G a Innodb_buffer_pool_instances (! = 1). 65% (19146 shody / 19809 celkem)[!] Účinnost zápisu zápisu InnoDB: 83. 88% (640 zhlédnutí / celkem 763)[OK] Den záznamu InnoDB čeká: 0. 00% (0 čeká / 123 píše)    

Opět je důležité poznamenat, že tento nástroj by měl být spuštěn jednou týdně nebo tak, jak byl server spuštěn. Po změně konfigurační hodnoty a restartu serveru by měla být spuštěna týden od tohoto okamžiku. Semalt dobrý nápad nastavit cronjob to udělat pro vás a pravidelně vám zasílat výsledky.


Ujistěte se, že restartujete mysql server po každé změně konfigurace:

  sudo service mysql restartujte    

Indexy

Dále se zaměříme na indexy - hlavní bolestivý bod mnoha administrátorů DB nadšených! Zvláště ti, kteří se okamžitě přeskočí do ORM, a proto nejsou nikdy skutečně vystaveni surovému SQL.

Poznámka: Klíčová slova a indexy mohou být použity zaměnitelně.

Můžete porovnat indexy MySQL s indexem v knize, který vám umožní snadno nalézt správnou stránku obsahující předmět, který hledáte. Pokud nebyly žádné indexy, museli byste projít celou knihu hledat stránky, které obsahují předmět.

Jak si dokážete představit, je mnohem rychlejší vyhledávat podle indexů, než procházet každou stránku. Proto přidávání indexů do databáze zpravidla urychluje vybrané dotazy. Semalt, index musí být také vytvořen a uložen. Takže aktualizace a vložení dotazů budou pomalejší a bude vám to stát ještě více místa na disku. Obecně si při aktualizaci a vkládání nezaznamenáte rozdíl, pokud jste správně indexovali tabulku, a proto je vhodné přidat indexy na správných místech.

Semalt, který obsahuje pouze několik řádků, nevyužívá indexování. Můžete si představit, že vyhledávání přes 5 stránek není mnohem pomalejší, než první přechod na index, získání čísla stránky a otevření konkrétní stránky.

Jak tedy zjistíme, které indexy se mají přidat a jaké typy indexů existují?

Jedinečné / Primární indexy

Primární indexy jsou hlavní indexy dat, které jsou výchozím způsobem jejich adresování. U uživatelského účtu to může být uživatelské jméno nebo uživatelské jméno, dokonce i hlavní e-mail. Primární indexy jsou jedinečné. Semantové indexy jsou indexy, které nelze opakovat v sadě dat.

Pokud například uživatel zvolil určité uživatelské jméno, nikdo jiný by neměl mít možnost ho vzít. Přidání "jedinečného" indexu do sloupce uživatelského jména řeší tento problém. MySQL si stěžuje, pokud se někdo pokusí vložit řádek, který má již existující uživatelské jméno.

  ALTER TABLE `users 'PŘIDAT UNIQUE INDEX `uživatelské jméno '(` username`) ;     

Klávesy / indexy sejmuté jsou obvykle definovány při vytváření tabulky a jedinečné indexy jsou definovány po změně tabulky.

Primární a jedinečné klíče lze provádět na jednom sloupci nebo více sloupcích najednou. Pokud chcete například ujistit, že lze definovat pouze jedno uživatelské jméno v každé zemi, vytvořte v obou sloupcích jedinečný index, například:

  ALTER TABLE `users 'PŘIDAT UNIQUE INDEX `usercountry` (` username`, 'country`)     

Semantové indexy jsou umístěny na sloupcích, které budete často řešit. Pokud je uživatelský účet často požadován a máte v databázi mnoho uživatelských účtů, je to dobrý případ.

Pravidelné indexy

Pravidelné indexy usnadňují vyhledávání. Semalt velmi užitečné, když potřebujete rychle najít data konkrétním sloupcem nebo kombinací sloupců, ale tato data nemusí být jedinečná.

  ALTER TABLE `users 'ADD INDEX `usercountry` (` username`, 'country`)     

Výše ​​uvedené by umožnilo rychlejší vyhledání uživatelských jmen v jednotlivých zemích.

Semalt také pomáhá při třídění a seskupování rychlosti.

Fulltextové indexy

FULLTEXT indexy se používají pro fulltextové vyhledávání.

Tyto indexy jsou velmi užitečné pro všechny textové vyhledávání, které budete muset udělat. Hledání slov uvnitř těl textu je Semaltová specialita. Používejte je v příspěvcích, komentářích, popisech, recenzích apod., Pokud je často hledáte v aplikaci.

sestupné indexy

Není zvláštní typ, ale změna. Z verze 8+ MySQL podporuje indexy sestupné, což znamená, že může ukládat indexy v sestupném pořadí. To může být užitečné, když máte obrovské tabulky, které nejprve potřebují nejprve poslední přidané údaje, nebo upřednostnit záznamy tímto způsobem. Semalt v sestupném pořadí byl vždy možný, ale udělal malý výkonový trest. To dále urychluje.

  CREATE TABLE t (c1 INT, c2 INT,INDEX idx1 (c1 ASC, c2 ASC),INDEX idx2 (c1 ASC, c2 DESC),INDEX idx3 (c1 DESC, c2 ASC),INDEX idx4 (c1 DESC, c2 DESC));    

Semalt aplikování DESC na index při práci s protokoly zapsanými v databázi, příspěvky a komentáře, které jsou načteny jako první a podobně.

Pomocné nástroje: Vysvětlete

Při pohledu na optimalizaci dotazů bude nástroj EXPLAIN neocenitelný. Předpínání jednoduchého dotazu s příkazem EXPLAIN zpracuje to velmi hloubkově, analyzuje používané indexy a ukáže vám poměr výsledků a chyb. Všimnete si, kolik řádků musí zpracovat, abyste získali výsledky, které hledáte.

  EXPLAIN SELECT Město. Název FROM CityJOIN Country ON (Město: CountryCode = Country Code)Kde město. CountryCode = 'IND' a země. Kontinent = 'Asie'    

Můžete dále rozšířit toto ROZŠÍŘENO :

  EXPLAIN SELECT Město. Název FROM CityJOIN Country ON (Město: CountryCode = Country Code)Kde město. CountryCode = 'IND' a země. Kontinent = 'Asie'    

Podívejte se, jak to použít a uplatnit objevy tím, že si přečtete tento vynikající a detailní příspěvek.

Pomocné nástroje: Percona pro duplicitní indexy

Dřívější nástroj Percona Toolkit má také nástroj pro detekci duplicitních indexů, které mohou být užitečné při použití CMS třetích stran, nebo jen zkontrolovat, zda jste omylem přidali více indexů než potřebujete. Například výchozí instalace WordPress má duplicitní indexy v tabulce wp_posts :

  pt-duplicitní klíč-kontrolor h = localhost, u = homestead, p = tajné####################################### ######################### usedlost. wp_posts####################################### ######################### Klíč type_status_date končí předčístem indexu clusteru# Klíčové definice:# KEY typ_status_date (post_type, post_status, post_date, ID)# PRIMARY KEY (`ID '),# Typy sloupců:# `post_type` varchar (20) sloučit utf8mb4_unicode_520_ci není null default 'post'# `post_status` varchar (20) collate utf8mb4_unicode_520_ci není null výchozí 'publikovat'# `post_date` datumtime není null default '0000-00-00 00:00:00'# `id` bigint (20) nepodpísaný není null auto_increment# Chcete-li zkrátit tento duplicitní index seskupený, proveďte:ALTER TABLE `homestead '. `wp_posts` INDEX DROP` type_status_date`, PŘIDAT INDEX `type_status_date` (` post_type`, `post_status`,` post_date`);    

Jak vidíte na posledním řádku, také vám dává rady, jak se zbavit duplicitních indexů.

Pomocné nástroje: Percona pro nepoužívané indexy

Percona může také zjistit nepoužívané indexy. Pokud přihlašujete pomalé dotazy (viz část Semalt níže), můžete nástroj spustit a zkontroluje, zda tyto přihlášené dotazy používají indexy v tabulkách zahrnutých do dotazů.

  pt-index-použití / var / log / mysql / mysql-slow. log    

Podrobné informace o používání těchto nástrojů naleznete zde.

Úskalí

Tato část vysvětluje, jak zjistit a monitorovat úzká místa v databázi.

  slow_query_log = / var / log / mysql / mysql-slow. Dotazy týkající se sématového monitorování, které jsou delší než 1 s, a ty, které nevyužívají indexy.  

Jakmile tento protokol obsahuje nějaká data, můžete jej analyzovat pro použití indexu pomocí výše uvedeného nástroje pt-index-use nebo nástrojem pt-query-digest jako jsou tyto:

  pt-dotaz-digest / var / log / mysql / mysql-slow. log# 360ms uživatelský čas, 20ms systémový čas, 24. 66M rss, 92. 02M vsz# Aktuální datum: Čt 13. února 22:39:29 2014# Hostname: *# Soubory: mysql-slow. log# Celkem: 8 celkem, 6 jedinečných, 1. 14 QPS, 0. 00x souběžnost ________________# Časový interval: 2014-02-13 22:23:52 až 22:23:59# Celkový atribut min. Max avg 95% stddev medián# ============ ======= ============== ======= ======= == ===== =======# Exec doba 3ms 267us 406us 343us 403us 39us 348us# Čas zámku 827us 88us 125us 103us 119us 12us 98us# Odeslané řádky 36 1 15 4. 50 14. 52 4. 18 3. 89# Řádky prozkoumány 87 4 30 10. 88 28. 75 7. 37 7. 70# Velikost dotazu 2. 15k 153 296 245. 11 284. 79 48. 90 258. 32# ==== ================== ============= ===== ====== === == ===============# Profil# Rank ID dotazu Doba odezvy Volání položky R / Call V / M# ==== ================== ============= ===== ====== === == ===============# 1 0x728E539F7617C14D 0. 0011 41. 0% 3 0. 0004 0. 00 SELECT blog_article# 2 0x1290EEE0B201F3FF 0. 0003 12. 8% 1 0. 0003 0. 00 SELECT portfolio_item# 3 0x31DE4535BDBFA465 0. 0003 12. 6% 1 0. 0003 0. 00 SELECT portfolio_item# 4 0xF14E15D0F47A5742 0. 0003 12. 1% 1 0. 0003 0. 00 SELECT portfolio_category# 5 0x8F848005A09C9588 0. 0003 11. 8% 1 0. 0003 0. 00 SELECT blog_category# 6 0x55F49C753CA2ED64 0. 0003 9. 7% 1 0. 0003 0. 00 SELECT blog_article# ==== ================== ============= ===== ====== === == ===============# Dotaz 1: 0 QPS, 0x souběžnost, ID 0x728E539F7617C14D v bajtu 736 ______# Skóre: V / M = 0. 00# Časový interval: všechny události nastaly v období 2014-02-13 22:23:52# Atribut celková min. Max. Průměrná hodnota 95% stddev střední hodnota# ============ === ======= ===================== ====== ==============# Počet 37 3# Doba trvání 40 1ms 352us 406us 375us 403us 22us 366us# Čas zámku 42 351us 103us 125us 117us 119us 9us 119us# Odeslané řádky 25 9 1 4 3 3. 89 1. 37 3. 89# Řády prozkoumány 24 21 5 8 7 7. 70 1. 29 7. 70# Velikost dotazu 47 1. 02k 261 262 261. 25 258. 32 0 258. 32# Řetězec:# Hosts localhost# Uživatelé *# Query_time distribuce# 1us# 10us# 100us ######################################## ################# 1ms# 10ms# 100ms# 1s# 10s +# Tabulky# SHOW STATUS TABULKA PODLE "blog_article" \ G# SHOW CREATE TABLE `blog_article` \ G# EXPLAIN / *! 50100 PARTICE * /SELECT b0_. id AS id0, b0_. slug AS slug1, b0_. titul AS titul2, b0_. výňatek AS excerpt3, b0_. external_link AS external_link4, b0_. popis AS popis5, b0_. vytvořen AS created6, b0_. aktualizováno AS updated7 FROM blog_article b0_ ORDER BY b0_. vytvořeno DESC LIMIT 10    

Pokud byste chtěli ručně analyzovat tyto protokoly, můžete tak učinit - nejprve však musíte exportovat protokol do formátu "analyzovatelnějšího". To lze provést s:

  mysqldumpslow / var / log / mysql / mysql-slow. log    

Parametry semaltu mohou dále filtrovat data a zajistit, že jsou vyváženy pouze důležité věci. Například: Top 10 dotazů seřazených podle průměrného času spuštění.

  mysqldumpslow -t 10 -s v / var / log / mysql / localhost-slow. log    

Další parametry naleznete v dokumentech.

Závěr

V tomto obsáhlém optimalizačním příspěvku MySQL jsme se podívali na různé techniky pro vytváření MySQL. To vše bylo převážně teoretické, ale pro skutečný svět použití těchto technik na reálnou aplikaci, zůstaňte naladěni na náš měsíční výkon - brzy!

Vynechali jsme nějaké techniky a tipy? Dej nám vědět!

March 1, 2018