MySQL profesionálně

Optimalizace, zálohy, škálovatelnost replikace, zotavení, bezpečnost

MySQL profesionálně optimalizace pro vysoký výkon Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz a Derek J. Balling

MySQL profesionálně Optimalizace pro vysoký výkon

Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz a Derek J. Balling

High Performance MySQL, Second Edition Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz, and Derek J. Balling © ZONER software, a.s., 2009. Authorized translation of the English edition of High Performance MySQL, 2nd Edition, ISBN 9780596101718 © 2008 O'Reilly Media, Inc. This translation is published and sold by permision of O'Reilly Media, Inc., the owner of all rights to publish and sell the same.

No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording or by any information storage retrieval system, without permission from O'Reilly Media, Inc. © ZONER software, a.s., 2009. Autorizovaný překlad originálního anglického vydání knihy High Performance MySQL, 2nd Edition, ISBN 9780596101718 © 2008 O'Reilly Media, Inc. Překlad je vydán a prodáván s výslovným svolením O'Reilly Media, Inc., vlastníkem veškerých práv na vydání i prodej tohoto titulu. Žádná část této publikace nesmí být reprodukována nebo předávána žádnou formou nebo způsobem, elektronicky ani mechanicky, včetně fotokopií, natáčení ani žádnými jinými systémy pro ukládání bez výslovného svolení O'Reilly Media, Inc.

MySQL profesionálně – optimalizace pro vysoký výkon Autor: Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz a Derek J. Balling.

Copyright © ZONER software, a.s. Vydání první v roce 2009. Všechna práva vyhrazena.

Zoner Press Katalogové číslo: ZR844 ZONER software, a.s.

Nové sady 18, 602 00 Brno Překlad: RNDr. Jan Pokorný Odpovědný redaktor: Miroslav Kučera Šéfredaktor: Ing. Pavel Kristián DTP: Miroslav Kučera © Ilustrace na obálce: O'Reilly Media, Inc. Informace, které jsou v této knize zveřejněny, mohou byt chráněny jako patent. Jména produktů byla uvedena bez záruky jejich volného použití. Při tvorbě textů a vyobrazení bylo sice postupováno s maximální péčí, ale přesto nelze zcela vyloučit možnost výskytu chyb. Vydavatelé a autoři nepřebírají právní odpovědnost ani žádnou jinou záruku za použití chybných údajů a z toho vyplývajících důsledků. Všechna práva vyhrazena. Žádná část této publikace nesmí být reprodukována ani distribuována žádným způsobem ani prostředkem, ani reprodukována v databázi či na jiném záznamovém prostředku či v jiném systému bez výslovného svolení vydavatele, s výjimkou zveřejnění krátkých částí textu pro potřeby recenzí.

Veškeré dotazy týkající se distribuce směřujte na: Zoner Press ZONER software, a.s Nové sady 18, 602 00 Brno tel.: 532 190 883, fax: 543 257 245 e-mail: knihy@zoner.cz www.zonerpress.cz

ISBN 978-80-7413-035-9

3

Obsah Předmluva

8

Úvod

8

Poděkování ke druhému vydání

14

Poděkování k prvnímu vydání

16

Kapitola 1

19

Architektura MySQL

Logická architektura MySQL

19

Řízení souběžného zpracování

21

Transakce

24

MVCC (řízení souběžného zpracování s více verzemi)

30

Úložné enginy MySQL

32

Kapitola 2

Hledání úzkých hrdel: testy výkonnosti a profilace

51

K čemu jsou dobré testy výkonnosti?

52

Strategie testů výkonnosti

52

Taktiky testů výkonnosti

56

Nástroje pro testy výkonnosti

61

Ukázky testů výkonnosti

64

Profilace

74

Profilace operačního systému

97

Kapitola 3

Optimalizace schématu a indexování

101

Volba optimálních datových typů

102

Základy indexování

117

Indexové strategie pro vysoký výkon

128

Případová studie s indexy

154

Údržba indexů a tabulek

159

Normalizace a denormalizace

162

Jak urychlit ALTER TABLE

170

Několik poznámek k úložným enginům

173

Kapitola 4

Optimalizace výkonu dotazu

Základní příčiny pomalého dotazu: optimalizujte přístup k datům

177 177

4 Restrukturalizace dotazu

182

Základní informace o vykonávání dotazu

185

Omezení optimalizátoru dotazu MySQL

205

Optimalizace specifických typů dotazů

215

Pokyny pro optimalizátor dotazu

222

Uživatelsky definované proměnné

224

Opatrně s upgradem MySQL

230

Kapitola 5

Pokročilé funkce MySQL

231

Cache dotazů MySQL

231

Ukládání kódu dovnitř MySQL

243

Kurzory

250

Připravené příkazy

251

Uživatelsky definované funkce

256

Pohledy

258

Znakové sady a kolace

263

Fulltextové hledání

270

Omezení cizích klíčů

279

Tabulky Merge a členěné tabulky

280

Distribuované (XA) transakce

290

Kapitola 6

Optimalizace serverových nastavení

293

Základy konfigurace

294

Obecně k ladění výkonu

299

Ladění I/O chování MySQL

309

Vylaďování souběžnosti MySQL

322

Ladění zaměřené na pracovní zátěž

324

Vylaďování voleb na úrovni připojení

330

Kapitola 7

Optimalizace operačního systému a hardwaru

331

Co omezuje výkon MySQL?

332

Jak vybírat jednotky CPU pro MySQL

332

Vybalancování paměťových a diskových zdrojů

335

Výběr hardwaru pro repliku

342

Optimalizace výkonu RAID

343

Sítě úložných oblastí a úložiště připojené přes síť

350

5 Používání více diskových svazků

351

Konfigurace sítě

353

Volba operačního systému

355

Volba souborového systému

356

Jak se zachází s vlákny

359

Swapování

359

Stav operačního systému

361

Kapitola 8

Replikace

369

Replikace přehledně

369

Příprava replikace

373

Replikace pod kapotou

381

Replikační topologie

388

Replikace a plánování kapacit

402

Administrace a údržba replikace

403

Replikační problémy a řešení

413

Jak rychlá je replikace?

430

Přísliby MySQL replikace do budoucna

433

Kapitola 9

Škálovatelnost a vysoká dostupnost

435

Terminologie

436

Škálování MySQL

437

Rozložení zátěže

462

Vysoká dostupnost

472

Kapitola 10

Optimalizace na úrovni aplikace

481

Stručné pojednání o výkonu aplikace

481

Záležitosti týkající se webových serverů

484

Práce s cache

487

Rozšiřování MySQL

494

Alternativy k MySQL

495

Kapitola 11

Zálohy a zotavení

497

Přehled

498

Úvahy a kompromisy

502

6 Správa a zálohování binárních logů

511

Zálohování dat

513

Zotavení ze zálohy

524

Rychlost zálohování a zotavení

535

Zálohovací nástroje

536

Zálohovací skripty

543

Kapitola 12

Bezpečnost

547

Terminologie

547

Základní informace o účtech

548

Bezpečnost operačního systému

567

Šifrování dat

576

MySQL v prostředí změněného roota

580

Kapitola 13

Stav MySQL serveru

583

Systémové proměnné

583

SHOW STATUS

584

SHOW INNODB STATUS

590

SHOW PROCESSLIST

605

SHOW MUTEX STATUS

606

Stav replikace

607

INFORMATION_SCHEMA

608

Kapitola 14

Nástroje pro vysoký výkon

611

Nástroje rozhraní

611

Monitorovací nástroje

613

Analytické nástroje

623

Utility MySQL

625

Další zdroje informací

627

Příloha A

Přenášení velkých souborů

629

Testy výkonnosti metod pro kopírování souborů

632

Příloha B

Příkaz EXPLAIN

Jak se spouští příkaz EXPLAIN

633 633

7 Sloupce ve výstupu z EXPLAIN

636

Vizuální EXPLAIN

648

Příloha C

Sphinx v součinnosti s MySQL

Přehled: typické hledání Sphinx

649 649

Architektonický přehled

661

Speciální funkce

663

Ukázky praktických implementací

669

Závěr

674

Příloha D

Ladění zámků

677

Čekání na zámky na úrovni serveru

677

Čekání na zámky v úložných enginech

683

Rejstřík

687

8

Předmluva Znám Petera, Vadima a Arjena už dlouho, takže jsem na vlastní oči viděl, jak ve svých projektech používají MySQL i jak ho dokážou vyladit pro spoustu různě zaměřených atraktivních zákazníků.

Baron navíc napsal klientský software, který rozšiřuje použitelnost MySQL. Zázemí autorů se jasně odráží na kompletně přepracovaném druhém vydání knihy MySQL profesionálně – optimalizace pro vysoký výkon. Není to kniha, která by pouze sdělovala, jak máte optimalizovat svou práci, abyste používali MySQL lépe než kdykoliv předtím. Autoři si dali značnou práci, aby svá doporučení a závěry podpořili testy výkonnosti, které provedli a zde publikovali.

Vy, čtenáři, tak dostáváte spoustu cenných příležitostí pochopit, jak funguje MySQL uvnitř, což se v žádné jiné knize tak snadno nedozvíte. To vám postupně v budoucnu umožní vyvarovat se mnoha chyb a omylů, které by měly za následek neoptimální výkon.

Tuto knihu doporučuji jak novým uživatelům MySQL, kteří si už trochu pohráli s tímto databázovým serverem a chystají se na své první aplikace z reálného světa, tak i ostříleným uživatelům, již už mají dobře vyladěné aplikace založené na MySQL, ale rádi by z nich "vymačkali" něco navíc.

Michael Widenius březen 2008

Úvod Když jsme se zamýšleli nad náplní této knihy, honilo se nám hlavou několik cílů. Mnohé z nich se odvozovaly od touhy po bájné, perfektní knize o MySQL, kterou nikdo z nás nikdy nečetl, a již jsme neustále hledali v regálech knihkupectví. Další se odvíjely z mnoha našich zkušeností, když jsme pomáhali jiným uživatelům rozhýbávat MySQL v jejich prostředích.

Chtěli jsme, aby tato kniha nebyla pouze úvodem do SQL. Představovali jsme si knihu s takovým názvem, v němž nebude ani na začátku, ani na konci běžně používaná fráze s nějakým časovým rámcem ("...ve třiceti dnech", "V sedmi dnech k lepšímu..."), která by znevažovala čtenáře. Ale ze všeho nejvíce jsme chtěli knihu, s jejíž pomocí byste své stávající dovednosti povznesli na vyšší úroveň a uměli s MySQL budovat rychlé, spolehlivé systémy – takovou knihu, která by dokázala odpovídat na otázky jako je: "Jak mám připravit cluster MySQL serverů, jež by zvládly milióny miliónů dotazů a aby bylo zajištěno, že poběží i tehdy, když dva ze serverů spadnou?"

Rozhodli jsme se, že napíšeme knihu, která se bude soustřeďovat nejenom na potřeby vývojáře aplikací MySQL, ale také na oprávněné nároky pečlivého administrátora MySQL, jenž potřebuje udržet systém v chodu bez ohledu na to, co programátoři nebo uživatelé vymyslí. Tím jsme vlastně řekli, že předpokládáme, že už máte poměrně slušné zkušenosti s MySQL a – což by bylo ideální – přečetli jste si o něm už nějakou úvodní knihu. Dále předpokládáme jisté zkušenosti s všeobecnou systémovou administrací, se sítěmi a s operačními systémy na způsob Unixu.

Toto revidované a rozšířené druhé vydání zahrnuje hlubší pokrytí všech témat z prvního vydání a také spoustu nových témat. Je to částečně reakce na změny, k nimž došlo od data, kdy byla kniha

9 poprvé publikována. MySQL je nyní mnohem objemnější a mnohem složitější software. Stejně tak je důležitý fakt, že se prudce zvýšila jeho popularita. Narostla i komunita uživatelů MySQL a velké korporace už dnes používají MySQL v aplikacích, které jsou pro jejich podnikání kritické. Od prvního vydání této knihy ušel MySQL hodně cesty a stal se dostatečně vyspělým systémem pro podnikání1. Lidé ho také stále víc a víc používají v aplikacích vystavovaných na internetu, kde se prostoje a podobné problémy nedají ani zatajit, ani tolerovat.

Z toho vyplývá, že toto druhé vydání, které právě držíte v ruce, se soustřeďuje na trochu jiné věci než první vydání. Klademe stejný důraz na spolehlivost a bezchybnost jako na výkon, zčásti proto, že i my sami jsme používali MySQL pro aplikace, v nichž se značné množství finančních prostředků vynakládalo na databázový server. Máme také bohaté zkušenosti ve webových aplikacích, v nichž se MySQL stal velmi populárním. Druhé vydání se vztahuje ke světu MySQL, který v době, kdy jsme psali první vydání, neexistoval v takové podobě, jakou má dnes.

Jak je tato kniha uspořádána Do knihy se nám podařilo vměstnat hodně komplikovaných témat. Zde vysvětlujeme, jak jsme je umístili k sobě, abyste je mohli snadněji zvládnout.

Stručný přehled Kapitola 1, Architektura MySQL, se věnuje základům – věcem, které musíte dobře znát předtím, než začnete kopat do hloubky. Potřebujete vědět, jak je MySQL uspořádán, abyste s ním mohli pracovat efektivně. V této kapitole se vysvětluje architektura MySQL a klíčová fakta o jeho úložných enginech. Tato kapitola vám pomůže dostat se do obrazu, pokud ještě perfektně neovládáte všechny nezbytné věci vztahující se k relačním databázím, například transakce. První kapitola bude také užitečná jako úvod do MySQL pro ty čtenáře, kteří ho ještě neznají, nicméně se vyznají v nějaké jiné databázi, jako je třeba Oracle.

Budování solidních základů Další čtyři kapitoly pokrývají téma, ke kterému se budete znova a znova vracet, až budete v praxi používat MySQL. V kapitole 2, Hledání úzkých hrdel: testy výkonnosti a profilace, prodiskutujeme základy testů výkonnosti a profilace – tj. jaký druh pracovní zátěže může server zvládnout, jak rychle dokáže provádět jisté úlohy atd. Svou aplikaci byste měli prohnat nějakým testem výkonnosti jak před libovolnou podstatnou změnou v ní, tak i po této změně, abyste byli schopni posoudit, jak efektivní vaše změny byly. To, co vypadalo jako pozitivní změna, se totiž může změnit v cosi úplně opačného, když se dostane do nelehkých podmínek skutečného světa. Uvědomte si, že se nikdy nedozvíte, co v aplikaci vlastně způsobuje, že má tak mizerný výkon, dokud ji přesně neproměříte.

1

My tuto frázi považujeme jen za marketingový blábol, ale zdá se, že pro řadu lidí má jistou důležitost.

10 V kapitole 3, Optimalizace schématu a indexování, probíráme různé nuance datových typů, návrhu tabulky a indexů. Dobře navržené schéma pomáhá MySQL v lepším fungování. Mnoho z věcí, které popisujeme v dalších kapitolách, úzce závisí na tom, jak dobře aplikace uvádí do chodu indexy MySQL, takže pokud chcete MySQL používat skutečně efektivně, neobejdete se bez důkladných znalostí o indexech a o tom, jak je používat dobře. Z tohoto důvodu předpokládáme, že se budete k této kapitole opakovaně vracet.

V kapitole 4, Optimalizace výkonu dotazu, se vysvětluje, jak MySQL vykonává dotazy a jak můžete těžit ze silných stránek jeho optimalizátoru dotazu. Budete-li dobře znát a správně chápat, jak pracuje optimalizátor dotazu, dokážete se svými dotazy hotové zázraky, přičemž také lépe porozumíte indexům. (Indexování a optimalizace dotazu je něco jako problém, co bylo dřív, zdali vejce nebo slepice. Neuškodí vám, když si po přečtení kapitoly 4 ještě jednou přečtete kapitolu 3.) V této kapitole se také demonstrují specifické příklady prakticky všech možných tříd dotazů a předvádí se, kde MySQL pracuje dobře. Také se dozvíte, jak transformovat dotazy do takových forem, aby mohly využít své silné stránky.

Do toho okamžiku jsme probírali základní témata, která lze aplikovat na jakoukoliv databázi: tabulky, indexy, data a dotazy. V kapitole 5, Pokročilé funkce MySQL, už překračujeme hranice základů. Ukážeme vám, jak pracují pokročilé funkce MySQL. Prozkoumáme například cache dotazů, uložené procedury, triggery, znakové sady a spoustu dalších věcí. MySQL implementuje tyto funkce jinak než ostatní databáze. Jakmile jim dobře porozumíte, otevřou se před vámi nové možnosti ohledně vylepšování výkonu, takové, které by vám jinak možná ani nepřišly na mysl.

Vylaďování aplikací V dalších dvou kapitolách se dozvíte, jaké změny můžete udělat, abyste pozvedli výkon aplikací opírajících se o MySQL. V kapitole 6, Optimalizace serverových nastavení, diskutujeme o tom, jak vyladit MySQL tak, aby vyždímal co nejvíc z hardwaru a pracoval co nejlépe ve prospěch konkrétní aplikace. V následující kapitole 7, Optimalizace operačního systému a hardwaru, pak vysvětlujeme, jakým způsobem zužitkovat daný operační systém a hardware. Doporučíme vám také hardwarové konfigurace, které by mohly poskytnout lepší výkon u rozsáhlejších aplikací.

Aplikace poroste, škálujte Jeden server vždy nestačí. V kapitole 8, Replikace, budeme probírat replikaci, kterou se dociluje toho, že se data automaticky kopírují na několik serverů. Když ji zkombinujete s lekcemi o škálování, rozložení zátěže a vysoké dostupnosti z kapitoly 9, Škálovatelnost a vysoká dostupnost, budete vybaveni solidními základy, abyste mohli své aplikace škálovat, jakmile taková potřeba vyvstane.

Rozsáhlá aplikace, která se provozuje na backendu MySQL, často poskytuje významné příležitosti, jak sama sebe optimalizovat. Existují lepší a horší způsoby, jak navrhovat rozsáhlé aplikace. I když to není primárním cílem této knihy, nechceme, abyste se po celý čas soustřeďovali pouze na MySQL. Kapitola 10, Optimalizace na úrovni aplikace, vám pomůže, abyste ve vaší obecné architektuře dosáhli na nízko visící hrozny, zejména tehdy, pokud se jedná o webovou aplikaci.

11

Spolehlivější aplikace Ani ta nejvíce škálovatelná architektura a s návrhem ze všech nejlepších na celém širém světě rozhodně není dobrá, pokud nepřežije výpadky proudu, zlovolné útoky, chyby v aplikacích, programátorské omyly nebo nějaké jiné pohromy.

V kapitole 11, Zálohy a zotavení, prodiskutujeme různé zálohovací a zotavovací strategie pro databáze MySQL. Tyto strategie vám pomohou minimalizovat dobu, kdy budete mimo provoz v důsledku nenadálého výpadku hardwaru, přičemž zajistí, že data takové pohromy přežijí.

V kapitole 12, Bezpečnost, získáte solidní znalosti ohledně několika záležitostí týkajících se bezpečnosti, které souvisejí s provozováním MySQL serveru. Důležitější ovšem je, že zde nabízíme spoustu doporučení, která, pokud se jimi budete řídit, pomohou zabránit vnějším vetřelcům, aby nějak uškodili serverům, s nimiž jste si dali takovou dobu takovou práci, abyste je dobře nakonfigurovali a optimálně vyladili. Vysvětlujeme některé jen výjimečně zkoumané oblasti databázové bezpečnosti a ukážeme vám, jaké zisky přinášejí a jaké dopady na výkon mají různé bezpečnostní praktiky. Z hlediska výkonu se vyplatí udržet bezpečnostní zásady co možná nejjednodušší.

Různá užitečná témata V posledních dvou kapitolách a v přílohách se ponoříme do několika témat, která se buď nehodila do problematiky předchozích kapitol, nebo jsme se na ně v těchto kapitolách neodkazovali dost podrobně, takže si zasluhují špetku speciální pozornosti.

V kapitole 13, Stav MySQL serveru, vám ukážeme, jak můžete zkoumat MySQL server. Ačkoliv je důležité vědět, jak získat stavové informace ze serveru, je ještě důležitější vědět, co tyto informace vlastně znamenají. Konkrétně, podrobně probereme příkaz SHOW INNODB STATUS, protože poskytuje hluboký vhled do fungování transakčního úložného enginu InnoDB. V kapitole 14, Nástroje pro vysoký výkon, se probírají nástroje, které umožňují efektivněji spravovat MySQL. Patří sem monitorovací a analytické nástroje, nástroje pomáhající psát dotazy atd.

V této kapitole se probírají nástroje soupravy Maatkit, které vytvořil Baron. Rozšiřují funkcionalitu MySQL a usnadňují vám život, pokud se nacházíte na pozici databázového administrátora. Také se zde předvádí program innotop, který Baron napsal jako snadno použitelné rozhraní k tomu, co právě dělá MySQL server. Tento program funguje v mnohém podobně jako unixový příkaz top a může být cenný ve všech fázích procesu, kdy vylaďujete server, protože můžete monitorovat, co všechno se děje uvnitř MySQL a jeho úložných enginů.

V příloze A, Přenášení velkých souborů, ukazujeme, jak efektivně kopírovat velmi velké soubory z jednoho místa na jiné – což je věc, kterou rozhodně musíte umět, pokud hodláte spravovat velké objemy dat. V příloze B, Příkaz EXPLAIN, ukazujeme, jak v praxi používat a chápat tento nesmírně důležitý příkaz. Příloha C, Sphinx v součinnosti s MySQL, slouží jako úvodu do Sphinxu, což je vysoce výkonný fulltextový indexovací systém, jímž můžete doplnit funkcionalitu MySQL. A konečně, v příloze D, Ladění zámků, ukazujeme, jak rozluštit, co se děje, když dotazy požadují takové zámky, které si vzájemně překážejí.

12

Verze softwaru a dostupnost MySQL je pohybující se terč. Za všechny ty roky od chvíle, kdy Jeremy napsal první osnovu prvního vydání knihy, se objevila četná vydání MySQL. MySQL 4.1 a 5.0 byly k dispozici pouze jako verze alfa, když šlo první vydání knihy do tisku. Dnes už jsou tyto verze léta v ostrém provozu a tvoří páteř mnoha rozsáhlých online aplikací. Když jsme dokončovali druhé vydání této knihy, byly na spadnutí MySQL verze 5.1 a 6.0. (MySQL 5.1 jako kandidát na vydání a 6.0 jako alfa.)

V této knize jsme se nespoléhali na jediné vydání MySQL. Kladli jsme důraz na naše rozsáhlé kolektivní znalosti MySQL ze skutečného světa. Jádro knihy se soustřeďuje na MySQL 5.0, protože tu považujeme za "aktuální" verzi. Ve většině příkladů se předpokládá, že provozujete nějakou rozumně vyzrálou verzi MySQL 5.0, jako je MySQL 5.0.40 nebo novější. Usilovali jsme se, abychom vždy připomněli ty funkce, které nejspíše neexistovaly ve starších verzích, nebo jež možná existují pouze v nadcházející sérii 5.1 a novější. Rozhodujícím zdrojem odkazů mapujících funkce na konkrétní verze byla samozřejmě dokumentace samotného MySQL. Očekáváme, že při čtení knihy budete čas od času navštěvovat zmiňovanou online dokumentaci (http://dev.mysql.com/doc/).

Dalším skvělým rysem MySQL je to, že běhá na všech současných populárních platformách: Mac OS X, Windows, GNU/Linux, Solaris, FreeBSD a kdovíjakých jiných! My ovšem upřednostňujeme GNU/Linux a další operační systémy podobné Unixu. Uživatelé Windows tu a tam narazí na jisté drobné odlišnosti. Například jsou úplně jiné cesty k souborům. Také se odkazujeme na standardní unixové utility příkazového řádku a předpokládáme, že znáte odpovídající příkazy ve Windows1.

Perl je dalším ožehavým místem, když jde o MySQL pod Windows. MySQL se dodává s několika užitečnými utilitami, jež jsou napsány v jazyce Perl a některé kapitoly v této knize obsahují ukázky skriptů napsaných v Perlu, jež pak tvoří základnu složitějších nástrojů, které budete sestavovat.

Sada nástrojů Maatkit je také napsána v Perlu. Perl se ovšem s Windows standardně nedodává. Pokud chcete používat tyto skripty, budete si muset stáhnout verzi Perlu pro Windows z ActiveState a nainstalovat nezbytné moduly doplňků (DBI a DBD::mysql) pro přístup k MySQL.

Jak používat ukázky kódu Kniha je zde k tomu, aby vám pomáhala při práci řešit úlohy. Všeobecně se dá říci, že kód z této knihy můžete používat ve svých programech a své dokumentaci. Nemusíte nás kontaktovat o povolení, pokud nereprodukujete významnou část kódu. Pokud například píšete program, v němž použijete několik málo úseků kódu z knihy, nepotřebujete povolení. Pokud ovšem prodáváte nebo distribuujete CD-ROM s příklady z knih vydavatelství O’Reilly, povolení již potřebujete. Pokud odpovídáte někomu na jeho otázku tím, že budete citovat tuto knihu a příklad v ní uvedený, nepotřebujete povolení. Chcete-li začlenit významnou část kódu příkladu z této knihy do dokumentace nějakého svého produktu, potřebujete povolení.

1

Kompatibilní verze utilit Unixu pro Windows jsou k mání na adrese http://unxutils.sourceforge.net nebo na adrese http://gnuwin32.sourceforge.net.

13 Příklady se udržují na http://www.highperfmysql.com a čas od času zde budou aktualizovány.

Nepředpokládejte ale, že budeme aktualizovat a testovat kód pro každé dílčí vydání MySQL. Máte-li pocit, že hodláte používat příklady kódu tak, že to spadá mimo rámec povolení uvedeného výše, kontaktujte nás na permissions@oreilly.com.

Konvence používané v knize V knize se používají následující tři typografické konvence: Neproporcionální písmo. Indikuje prvky kódu, názvy souborů, konfigurační volby, názvy databází a tabulek, proměnné a jejich hodnoty, funkce, moduly, obsah souborů, výstup příkazů atd.

Tučné neproporcionální písmo. Indikuje příkazy nebo jiný text, který musí doslova zadat uživatel. Tímto způsobem se také zvýrazňuje výstup příkazu.

Text tohoto šedého rámečku obsahuje upozornění, tip, doporučení, nebo obecnou poznámku.

Sdělte nám svůj názor Jako čtenáři této knihy se stáváte těmi nejdůležitějšími kritiky a komentátory. Vážíme si vašeho názoru a chtěli bychom vědět, co děláme správně, co bychom mohli dělat lépe, ve kterých oblastech bychom měli publikovat, a také vaše další podnětné myšlenky, o které jste ochotni se podělit.

Jako odborný redaktor Zoner Press vítám vaše názory. Můžete mi psát – poslat e-mail nebo dopis – a sdělit mi, co se vám v této knize líbilo nebo nelíbilo, stejně tak, co bychom měli udělat, aby naše další knihy byly lepší. Pokud mi napíšete, nezapomeňte, prosím, připojit název knihy, ISBN, jméno autora, vaše jméno, telefon, fax nebo e-mail. Pozorně zhodnotím vaše názory a poskytnu je všem lidem, kteří pracovali na této knize.

Prosím, vězte, že nemohu pomoci s technickými problémy, které se týkají obsahu knihy, a že díky velkému množství e-mailů, jež dostávám, nemohu zaručit odpověď na každou zprávu.

E-mail: miroslav.kucera@zoner.cz nebo knihy@zoner.cz.

Adresa: ZonerPress, ZONER software, a.s, Miroslav Kučera, Nové sady 18, 602 00 Brno.

Kontakt s autory Pokud to z nějakého důvodu potřebujete, můžete vejít ve styk přímo s autory.

14 Baronův weblog je na http://www.xaprb.com.

Peter a Vadim udržují dva weblogy, populární na http://www.mysqlperformanceblog.com a novější na http://www.webscalingblog.com. Web jejich firmy, Percona, můžete najít na adrese http://www.percona.com.

Arjenova firma, OpenQuery, má web na http://openquery.com.au. Arjen dále provozuje weblog na http://arjen-lentz.livejournal.com a osobní web na http://lentz.com.au.

Poděkování ke druhému vydání Přílohu C, Sphinx v součinnosti s MySQL, napsal vývojář Sphinxu, Andrew Aksyonoff. Rádi bychom poděkovali nejprve jemu za zasvěcený výklad.

Když jsme psali tuto knihu, neocenitelně nám pomohlo mnoho lidí. Není možné zde vypsat úplně všechny, kdo nám podali pomocnou ruku – opravdu dlužíme velké díky celé komunitě MySQL a všem v MySQL AB. Následuje však seznam osob, které přispěly přímo, přičemž se omlouváme, pokud jsme někoho opomněli: Tobias Asplund, Igor Babaev, Pascal Borghino, Roland Bouman, Ronald Bradford, Mark Callaghan, Jeremy Cole, Britt Crawford a HiveDB Project, Vasil Dimov, Harrison Fisk, Florian Haas, Dmitri Joukovski a Zmanda (díky za diagram vysvětlující momentky LVM), Alan Kasindorf, Sheeri Kritzer Cabral, Marko Makela, Giuseppe Maxia, Paul McCullagh, B. Keith Murphy, Dhiren Patel, Sergey Petrunia, Alexander Rubin, Paul Tuckfield, Heikki Tuuri a Michael "Monty" Widenius.

Zvláštní poděkování zasluhují Andy Oram a Isabel Kunkle, náš korektor a pomocná korektorka u O’Reilly, a Rachel Wheeler, editorka. Díky patří také všem dalším lidem u O’Reilly.

Od Barona Rád bych poděkoval své ženě Lynn Rainville a našemu psu Carbonovi. Pokud jste někdy psali nějakou knihu, jsem si jist, že víte, jak moc jsem jim zavázán. Také dlužím obrovitou porci vděčnosti Alanu Rimm-Kaufmanovi a svým kolegům u Rimm-Kaufman Group za jejich podporu a dodávání odvahy během práce na projektu. Také děkuji Peterovi, Vadimovi a Arjenovi, že mi dali příležitost uskutečnit tento sen. A děkuji Jeremymu a Derekovi, že nám zkřížili cestu.

Od Petera Zabývám se už léta prezentacemi o výkonu MySQL a škálování. Protože poskytuji i odborné konzultace a vždy jsem chtěl mít širší publikum, velmi mě zaujalo, když mě Andy Oram informoval o možné spolupráci na této knize. Žádnou knihu jsem předtím nenapsal, takže jsem nebyl připraven na to, kolik času a úsilí to vyžaduje. Nejprve se hovořilo o aktualizaci prvního vydání, abychom pokryli nejnovější verze MySQL, ale nakonec jsme přidali tolik nového materiálu, že výsledkem byla přepsaná většina knihy.

15 Kniha je výsledkem opravdového týmového úsilí. Protože jsem byl velmi zaneprázdněn svépomocnými pracemi v Perconě, což je moje a Vadimova konzultační firma, a protože angličtina není můj rodný jazyk, měli jsme každý odlišné role. Já jsem dodal osnovu a odborný obsah. Poté jsem přezkoumával, revidoval a rozšiřoval kriticky důležitý materiál. Když se k projektu připojil Arjen (dřívější hlava dokumentačního týmu MySQL), začali jsme vyplňovat osnovu. Věci se ale opravdu pohnuly vpřed až poté, když přišel Baron, který dokázal šílenou rychlostí psát vysoce kvalitní texty.

Vadim nám ohromně pomohl s hloubkovými kontrolami zdrojového kódu MySQL a když jsme potřebovali podpořit naše tvrzení s testy výkonnosti a jiným výzkumem. Čím déle jsme na této knize pracovali, tím jsme nacházeli víc oblastí, které jsme chtěli do detailu prozkoumávat. Mnoho z témat knihy, jako třeba replikace, optimalizace dotazů, InnoDB nebo architektura, by snadno zaplnilo samostatnou knihu, takže jsme v jisté chvíli museli říci stop a ponechat nějaký materiál pro případné budoucí vydání nebo pro naše blogy, prezentace a články.

Velmi nám pomohli naši recenzenti, protože to jsou jedni z největších expertů na MySQL na celém světě, ať už byli z MySQL AB nebo odjinud. Předně zakladatel MySQL, Michael Widenius; zakladatel InnoDB, Heikki Tuuri; Igor Babaev, ředitel optimalizačního týmu MySQL a mnoho dalších.

Také bych rád poděkoval své ženě, Katyie Zaitsev, a svým dětem, Ivanovi a Naděždě, že mi dovolili trávit na knize veškerý ten čas, který jsem měl věnovat rodině. Děkuji také zaměstnancům společnosti Percona, že se starali o věci, když jsem se vypařil, abych mohl pracovat na knize. Dík také samozřejmě zasluhují Andy Oram a vydavatelství O’Reilly, že se to všechno dalo vůbec uskutečnit.

Od Vadima Především bych chtěl poděkovat Peterovi – pracoval jsem s ním s nadšením na této knize a těším se na spolupráci na dalších projektech. Dále Baronovi, který dával knihu dohromady a Arjenovi, s nímž byla práce zábavou. Děkuji také našemu editorovi, Andy Oramovi, že měl s námi takovou trpělivost. Děkuji týmu MySQL, že vytvořili tak skvělý software, a také našim klientům, kteří mi poskytli příležitost doladit mé znalosti MySQL. A velké díky nakonec patří mé ženě Valerii a našim synům Miroslavovi a Timurovi, kteří mě neustále povzbuzovali a pomáhali postupovat vpřed.

Od Arjena Rád bych poděkoval Andymu za jeho moudrost, rady a trpělivost. Děkuji Baronovi, že dokázal naskočit za jízdy do vlaku druhého vydání, a Peterovi a Vadimovi za solidní podkladové informace a testy výkonnosti. Dík také zasluhují Jeremy a Derek, kteří přivedli na svět první vydání.

Také děkuji všem svým dřívějším kolegům (a nyní přátelům) z MySQL AB, kde jsem nabyl většinu svých vědomostí o tomto tématu. V tomto kontextu se chci speciálně zmínit o Montym, jehož i nadále považuji za pyšného rodiče MySQL, i když jeho firma již působí jako součást Sun Microsystems. Rád bych také poděkoval všem ostatním z globální komunity MySQL. V neposlední řadě pak chci poděkovat své dceři Phoebe, které jsou v jejím mladém věku úplně ukradené věci jako MySQL a opravdu nemá ani páru o tom, kdo jsou The Wiggles! Pro některé

16 bytosti je nevědomost pravým požehnáním, protože díky nim se můžeme vzpamatovat a uvědomit si, co je v životě opravdu důležité. Vy všichni ostatní snad zjistíte, že tato kniha je užitečným příspěvkem do vaší poličky s referenčními příručkami. A nezapomínejte žít.

Poděkování k prvnímu vydání Kniha jako je tato, by nikdy nevznikla bez pomoci doslova desítek lidí. Bez jejich účasti by kniha, kterou teď držíte v ruce, byla patrně pouhým svazkem neuměle nalepených poznámek po stranách našich monitorů. Tohle je ta část knihy, kde můžeme říci cokoliv, co chceme, o lidech, kteří nám nějak pomohli a nemusíme se starat o to, že v pozadí už hraje hudba, jež nám nenápadně sděluje, abychom už sklapli a vypadli, jako je to často vidět v televizi při udělování cen něčeho za něco.

Tento projekt bychom nikdy nedokončili, kdyby nás náš editor Andy Oram neustále nepopoháněl, nedoprošoval se, úpěnlivě nežebronil a nepodporoval. Pokud existuje jedna jediná osoba, která je zodpovědná za knihu, již teď máte ve svých rukou, je to rozhodně Andy. Opravdu umíme ocenit ty každotýdenní porady, kde nás pravidelně sekýroval.

Není tu ale jenom Andy. U O’Reilly je celá banda lidí, kteří participovali na převodu oněch neuměle poslepovaných poznámek na soudržnou knihu, kterou jste ochotni číst, takže musíme poděkovat i lidem z produkce, těm, co dělali obrázky, i těm z marketingu, že pomáhali dát knihu dohromady.

A samozřejmě musíme poděkovat Timu O’Reillymu za jeho neustálou a naprostou oddanost úsilí produkovat jednu z nejkvalitnějších dokumentací populárního open source softwaru.

Nakonec bychom rádi vyjádřili obrovský dík lidem, kteří souhlasili s tím, že budou pročítat různé koncepty knihy a že nám budou říkat, co děláme špatně: našim recenzentům. V roce 2003 strávili část své dovolené tím, že prohlíželi zhruba naformátované verze tohoto textu, který obsahoval spoustu překlepů, zavádějících příkazů a zjevných matematických chyb. Konkrétně se jedná o tyto lidi: Brian "Krow" Aker, Mark "JDBC" Matthews, Jeremy "the other Jeremy" Cole, Mike "VBMySQL.com" Hillyer, Raymond "Rainman" De Roo, Jeffrey "Regex Master" Friedl, Jason DeHaan, Dan Nelson, Steve "Unix Wiz" Friedl a v neposlední řadě Kasia "Unix Girl" Trapszo.

Od Jeremyho Znovu bych chtěl poděkovat Andymu, že souhlasil se svou účastí na tomto projektu a že nás neustále zaplavoval hromadami materiálů ke kapitolám. Podstatná také byla pomoc Dereka s dokončením posledních 20–30 % knihy, takže pouze díky jemu jsme neprošvihli další poslední termín.

Díky, žes naskočil na palubu v pozdní fázi procesu a dokázal se vypořádat s mými sporadickými explozemi produktivity, a že jsi zpracoval několik kapitol, na které jsem už neměl sílu.

Samozřejmě musím poděkovat i svým rodičům, kteří mi před tolika lety koupili můj první počítač Commodore 64. Nejenže tolerovali všechno, co prvních deset let vypadalo jako celoživotní posedlost elektronikou a počítačovou technologií, ale také se stali fanoušky mé nikdy nekončící touhy dozvídat se a dělat víc.

17 Dále chci poděkovat partě lidí, s nimiž jsem měl to neobyčejné potěšení pracovat, když jsme během několika posledních let šířili na Yahoo! víru v MySQL. Jeffrey Friedl a Ray Goldberger mi dodávali kuráž a své názory na nejranější fáze tohoto podnikání. Společně s nimi snášeli Steve Morris, James Harvey a Sergey Kolychev mé neustálé experimentování na MySQL serverech Yahoo! Finance, i když jsem tím narušoval jejich důležitou práci. Děkuji také nesčetným dalším lidem u Yahoo!, kteří mi pomáhali nacházet pro MySQL zajímavé problémy a řešení. A co je nejdůležitější, děkuji jim za to, že měli víru ve mně, která byla zapotřebí, pokud se měl MySQL dostat do nejdůležitějších a nejviditelnějších částí byznysu Yahoo!.

Adam Goodman, vydavatel a vlastník Linux Magazine, mi ulehčil vstup do světa, kde se píše pro odborné publikum tím, že v roce 2001 publikoval mé první "celovečerní" články o MySQL. Od té doby mě o redaktorování a publikování naučil víc, než si myslí a povzbuzoval mě, abych v této cestě pokračoval a přispíval do časopisu každý měsíc. Díky, Adame.

Děkuji také Montymu a Davidovi za sdílení MySQL se světem. A když už mluvím o MySQL AB, děkuji všem ostatním skvělým lidem, kteří mě povzbuzovali, abych psal. Jedná se o tyto lidi: Kerry, Larry, Joe, Marten, Brian, Paul, Jeremy, Mark, Harrison, Matt a zbytek týmu. You guys rock.

A nakonec děkuji všem čtenářům mého weblogu, kteří mi dodávali kuráž psát denně neformálně o MySQL a dalších odborných tématech. A v neposlední řadě děkuji Goon Squad.

Od Dereka Podobně jako Jeremy, i já bych chtěl poděkovat své rodině, převážně ze stejných důvodů. Děkuji svým rodičům, že mě neustále vybízeli, že bych měl napsat nějakou knihu, i když tahle je hodně vzdálená té, kterou oni měli na mysli. Mí prarodiče mi umožnili dozvědět se dvě cenné věci – jaký je význam peněz a jak se dá zamilovat do počítačů, když mi půjčili peníze, abych si mohl koupit svůj první Commodore VIC-20.

Nemohu vyjádřit dostatek díků Jeremymu za to, že mě přizval na tuhle šílenou horskou dráhu psaní knih. Rozhodně to byly pro mne skvělé zážitky a bylo by mi potěšením s ním znovu v budoucnu spolupracovat.

Poděkování zaslouží také Raymond De Roo, Brian Wohlgemuth, David Calafrancesco, Tera Doty, Jay Rubin, Bill Catlan, Anthony Howe, Mark O’Neal, George Montgomery, George Barber a myriády dalších lidí, kteří trpělivě naslouchali, jak pořád proti něčemu remcám, a již byli ochotni posuzovat moje nápady, které jsem jim předkládal, abych viděl, zdali člověk z venku dokáže porozumět tomu, co tím chtěl básník říci, nebo se jen snažili vyloudit úsměv na mé tváři, když jsem to zatraceně potřeboval. Je možné, že i bez vás by se podařilo tuto knihu dokončit, ale než by byla hotova, téměř určitě by mě z ní trefil šlak.

18

KAPITOLA 2 Hledání úzkých hrdel: testy výkonnosti a profilace Dříve nebo později nastane chvíle, kdy budete potřebovat od MySQL vyšší výkon. Co byste se ale měli pokusit vylepšit jako první? Konkrétní dotaz? Schéma? Hardware? Jediný způsob, jak se to dozvědět, je změřit, co všechno systém dělá a otestovat jeho výkon za různých podmínek. Nejlepší strategie spočívá v nalezení nejslabšího článku v řetězu aplikačních komponent a jeho následném posílení. Tento přístup je užitečný zejména v situacích, kdy nevíte, co brání vyššímu výkonu (nebo co bude bránit vyššímu výkonu v budoucnu).

Testy výkonnosti (benchmarking) a profilace (profiling) jsou dva hlavní postupy, jimiž se hledají úzká hrdla. Ačkoliv mají podobné rysy, rozhodně neznamenají totéž. Test výkonnosti měří výkon systému. Může vám pomoci určit kapacitu systému, ukázat, na jakých změnách záleží a na kterých ne, nebo demonstrovat, jak se aplikace vypořádává s daty různého druhu. Profilace oproti tomu pomáhá zjistit, kde aplikace stráví nejvíce času nebo kde spotřebovává nejvíce zdrojů. Jinak řečeno – test výkonnosti odpovídá na otázku "Jak dobře se tohle dělá?", kdežto profilace odpovídá na otázku "Proč se tohle dělá tak, jak se to dělá?"

Tuto kapitolu jsme rozdělili na dvě části, první je o testech výkonnosti, druhá o profilaci. Výklad začneme důvody, proč vlastně dělat testy výkonnosti a popíšeme vám příslušné strategie. Poté přejdeme ke konkrétním taktikám používaným při těchto testech. Ukážeme vám, jak plánovat a navrhovat testy výkonnosti. Ukážeme vám, jakým způsobem je navrhnout, aby vám poskytly přesné odpovědi na vaše otázky. Také vám řekneme, jakým způsobem se testy výkonnosti spouštějí a jak se analyzují jejich výsledky. Tuto část kapitoly nakonec zakončíme tím, že si popíšeme několik nástrojů pro testy výkonnosti a ukážeme si příklady, v nichž se používají některé z těchto nástrojů.

Zbývající část kapitoly se pak věnuje profilaci aplikací a MySQL. Ukážeme vám profilační kód ze skutečného světa, který jsme používali v ostrém provozu pro usnadnění různých analýz výkonu aplikací. Dále vám předvedeme, jak se dotazy MySQL zaznamenávají do logu, jak se tyto logy analyzují, a jak se používají různé stavové čítače a další nástroje, které vám pomáhají zjistit, co MySQL vlastně dělá a jaké dotazy na něm spouštíte.

52

Kapitola 2 – Hledání úzkých hrdel: testy výkonnosti a profilace

K čemu jsou dobré testy výkonnosti?

V mnohých středně velkých a rozsáhlejších vývojářských týmech MySQL často najdeme partu lidí, jejíž hlavní náplní práce jsou testy výkonnosti. Základní principy a postupy, které jsou používány při testech výkonnosti, by ovšem měl dobře znát každý vývojář a databázový administrátor, protože mají v praxi velmi široké uplatnění. Podívejte se nyní na výčet několika věcí, s nimiž vám mohou testy výkonnosti pomoci: Měřit, jak si v současné době vede aplikace z hlediska výkonu. Pokud nevíte, jak rychle běhá teď, u žádné změny si nemůžete být jisti, že vám opravdu pomůže. Můžete také použít výsledky dřívějších testů výkonnosti, abyste diagnostikovali problémy, které jste nepředvídali.

Prověřovat škálovatelnost (scalability) systému. Prostřednictvím testu výkonnosti můžete nasimulovat mnohem vyšší zátěž, než jakou běžně zpracovávají vaše ostré systémy, například tisícinásobný nárůst počtu uživatelů.

Plánovat růst. Testy výkonnosti pomáhají odhadnout, kolik hardwaru, síťové kapacity a jiných zdrojů bude zapotřebí pro takové zatížení, jaké plánujete do budoucna. Tohle může snížit riziko během přechodu na novou verzi systému nebo u závažných změn v aplikaci.

Testovat způsobilost aplikace zvládat měnící se prostředí. Například můžete zjistit, jak se aplikace vypořádává sice se sporadickými, ale velmi silnými nárazovými špičkami v souběžném zpracování, nebo s různými konfiguracemi serverů. Také se můžete podívat, jak vaše aplikace zvládá různá rozložení dat.

Testovat různý hardware, software a konfigurace operačního systému. Je pro váš systém lepší RAID 5 nebo RAID 10? Jak se mění výkon nesekvenčních zápisů, když se přepnete z disků ATA na úložiště SAN? Škáluje kernel Linuxu verze 2.4 lépe než 2.6? Pomůže k lepšímu výkonu upgrade MySQL? A co kdybychom použili pro data jiný úložný engine? Na všechny tyto otázky dokážete odpovědět pomocí speciálních testů výkonnosti.

Testy výkonnosti se ale dají používat i pro mnohé jiné účely, například si můžete vytvořit sadu pro testování jednotek zdrojového kódu aplikace (tzv. unit testing), nicméně v této kapitole se ovšem soustřeďujeme pouze na aspekty vztahující se k výkonu.

Strategie testů výkonnosti Existují dvě hlavní strategie testů výkonnosti: testovat aplikaci jako celek, nebo izolovat MySQL. Tyto dvě strategie jsou známy pod názvy testování výkonu v úplném rozsahu (full-stack benchmarking) a testování výkonu jediné komponenty (single-component benchmarking). Zde je několik důvodů, proč měřit aplikaci jako celek, nikoliv pouze MySQL: Otestujete aplikaci kompletně, tj. včetně webového serveru, kódu aplikace a databáze. To je prospěšné, protože se nestaráte konkrétně o výkon MySQL – jde vám o výkon celé aplikace.

MySQL profesionálně – optimalizace pro vysoký výkon

53

Úzkým hrdlem aplikace nemusí být vždy MySQL a právě úplný test výkonnosti vám může pomoci v jeho odhalení.

Pouze při úplném testování aplikace můžete vidět chování cache pro jednotlivé části.

Testy výkonnosti jsou dobré právě z toho důvodu, že odrážejí skutečné chování celé aplikace.

Něčeho takového jen stěží docílíte, když budete testovat pouze část aplikace.

Na druhé straně je ovšem nutné říci, že úplné testy výkonnosti aplikace se vytvářejí obtížně a ještě hůře se správně připravují. Jestliže navrhnete test výkonnosti špatně, můžete na jeho základě učinit špatná rozhodnutí, protože výsledky nebudou odpovídat realitě.

Někdy se však vlastně nechcete dozvídat informace o celé aplikaci. Potřebujete pouze otestovat výkon MySQL, přinejmenším na počátku. Takový test výkonnosti je užitečný, jestliže: Chcete porovnat různá schémata nebo dotazy.

Chcete otestovat výkon konkrétního problému, který vidíte v aplikaci.

Chcete se vyvarovat dlouhých testů výkonnosti, takže raději dáte přednost kratším testům, čímž zrychlíte opravný cyklus spočívající v měření a následném provádění změn.

Velmi užitečné je také testovat MySQL v případech, kdy můžete zopakovat dotazy aplikace nad nějakou sadou dat, která odpovídá skutečnému světu. Jak data samotná, tak velikost sady dat by měla být realistická. Pokud je to možné, použijte momentku (snapshot) skutečných ostrých dat.

Bohužel – příprava realistického testu výkonnosti může být dosti komplikovanou a časově náročnou záležitostí, a pokud se vám podaří získat nějakou kopii ostré sady dat, můžete se považovat za dítě štěstěny. V některých případech to samozřejmě možné není – například tehdy, když vyvíjíte zbrusu novou aplikaci, která má jen několik uživatelů a málo dat. Pokud se chcete alespoň orientačně dozvědět, jak si povede, až hodně naroste, nemáte jinou možnost než nasimulovat jak větší objem dat, tak i větší pracovní zátěž.

Co měřit Je nezbytné, abyste identifikovali své cíle ještě předtím, než začnete s testy výkonnosti (a dokonce ještě předtím, než je vůbec začnete navrhovat). Tyto cíle určují, jaké nástroje a techniky použijete, abyste získali přesné smysluplné výsledky. Vaše cíle formulujte ve formě následujících otázek: "Je tento procesor lepší než tamten?", nebo "Pracují nové indexy lépe než stávající?" atd.

Možná to není zřejmé, ale někdy je potřeba použít různé přístupy pro měření různých věcí. Například, latence a propustnost mohou vyžadovat odlišné testy výkonnosti. Zamyslete se nad následujícími mírami výkonu a zvažte, jak zapadají do vašich cílů.

Počet transakcí za jednotku času. Toto je jeden z nejznámějších klasických testů výkonnosti databázových aplikací. Existují i standardizované testy výkonnosti, jako je například test TPC-C (viz http://www.tpc.org) a mnozí výrobci databází na nich intenzivně pracují.

Tyto testy výkonnosti měří výkon zpracování transakcí online (OLTP, online transaction

54

Kapitola 2 – Hledání úzkých hrdel: testy výkonnosti a profilace

processing) a jsou nejvíce užitečné pro interaktivní víceuživatelské aplikace. Obvyklou měrnou jednotkou je počet transakcí za sekundu.

Termín propustnost (throughput) obvykle znamená totéž co počet transakcí (nebo jiné jednotky práce) za jednotku času.

Doba odezvy (latence). Celková doba, po kterou byla požadována nějaká úloha. Podle druhu aplikace se udává v milisekundách, sekundách nebo minutách. Z ní můžete odvodit průměrnou, minimální nebo maximální dobu odezvy.

Maximální možná doba odezvy je málokdy k něčemu užitečná, protože čím déle test výkonnosti běží, tím pravděpodobně bude větší maximální doba odezvy. Navíc se nejedná o zopakovatelnou hodnotu, protože v jednotlivých testech bude tato hodnota s největší pravděpodobností pokaždé odlišná. Z tohoto důvodu mnoho lidí preferuje percentilovou dobu odezvy (percentile response times). Pokud je například 95. percentil doby odezvy 5 milisekund, s 95% pravděpodobností víte, že úloha bude dokončena do 5 milisekund.

Pokud výsledky testů zanesete do nějakého grafu, buď ve formě lomené čáry (například průměrný a 95. percentil), nebo jako histogram, budete schopni na první pohled vidět, jak jsou výsledky rozloženy v čase. Tyto grafy pomáhají vysledovat dlouhodobé chování testů výkonnosti.

Předpokládejme, že váš systém vždy v intervalu jedné minuty provádí pravidelné kontroly.

Po dobu této kontroly se systém pozastaví a nebude dokončena žádná transakce. 95. percentil doby odezvy neukáže okamžiky, kdy se zatížení prudce zvyšuje, takže získané výsledky vám nepomohou odhalit daný problém. Na grafu budou ovšem jasně vidět periodické prudké výkyvy doby odezvy. Ilustruje to obrázek 2-1.

Obrázek 2-1. Výsledky třicetiminutového běhu testu výkonnosti dbt2.

V tomto obrázku jsou na ose y vyneseny počty transakcí za minutu (NOTPM). Lomená čára, která je spojuje, ukazuje několik významných výkyvů, jež vám průměrná doba odezvy (čárkovaná rovná čára) neukáže. První vrchol je způsoben tím, že jsou utlumeny cache serveru.

MySQL profesionálně – optimalizace pro vysoký výkon

55

Druhý vrchol pak ukazuje dobu, kdy server tráví spoustu času intenzivním splachováním "ušpiněných stránek" (dirty pages) z bufferů na disk. No, uznejte sami – bez toho grafu byste tyto abnormality mohli odhalit jen velmi obtížně. Škálovatelnost. Měření škálovatelnosti je užitečné pro systémy, které musí zachovávat dobrý výkon i pod měnící se pracovní zátěží.

"Dobrý výkon i pod měnící se pracovní zátěží" je dost teoretický pojem. Výkon se typicky měří nějakou metrikou, jako je propustnost nebo doba odezvy, přičemž pracovní zátěž může kolísat na základě změn velikosti databáze, počtu simultánních připojení, nebo hardwaru.

Testy škálovatelnosti jsou dobré při plánování kapacit, protože mohou poukázat na takové slabiny ve vaší aplikaci, které by jiné testy výkonnosti nemusely odhalit. Pokud například navrhnete systém, který si dobře povede v testu výkonnosti doby odezvy při jediném připojení (špatná strategie testu výkonnosti), aplikace může mít špatný výkon v okamžiku, kdy dojde k jakémukoli stupni souběžného zpracování. Tento nedostatek v návrhu odhalí pouze takový test, který bude hledat konzistentní doby odezvy při zvyšujícím se počtu připojení.

Některé činnosti, jako jsou dávkové práce, jimiž se vytvářejí souhrnné tabulky z jemně zrnitých dat, prostě potřebují rychlé doby odezvy, tečka. Jistě neuškodí, když otestujete čistě jen jejich doby odezvy, nicméně nezapomínejte na to, v jakých interakcích jsou s jinými činnostmi. Dávkové práce mohou způsobit, že utrpí výkon interaktivních dotazů, nebo naopak.

Souběžnost. Souběžnost (concurrency) je velmi důležitá, ale často chybně používaná a mylně interpretovaná metrika. Populární statistikou je např. údaj, kolik uživatelů si právě nyní prohlíží daný web. HTTP je ovšem bezstavový protokol, takže většina uživatelů jednoduše čte pouze to, co mají zobrazeno ve svých prohlížečích, což nemá se souběžností na webovém serveru nic společného. A obdobně – souběžnost na webovém serveru je něco jiného než souběžnost na databázovém serveru; jediná věc, kterou mají společné, je kolik dat musí zvládnout úložný mechanismus vaší relace. Přesnější mírou souběžnosti na webovém serveru je to, kolik požadavků za sekundu vygenerují uživatelé ve špičce, kdy je zátěž nejvyšší.

Souběžnost se dá také měřit na různých místech v aplikaci. Vyšší souběžnost na webovém serveru může způsobit vyšší souběžnost na úrovni databáze, na to nicméně mají vliv jazyk a používaná sada nástrojů. Například Java s fondem připojení bude mít patrně za následek menší počet souběžných připojení k MySQL serveru, než PHP s trvalými připojeními.

Pro nás je ovšem pořád nejdůležitější počet připojení, na kterých v daném okamžiku běží dotazy. Dobře navržená aplikace může mít například otevřené stovky připojení k MySQL serveru, ale pouze na jejich nepatrném zlomku by měly současně běžet dotazy. To znamená, že web, na kterém "se právě teď nachází 50 000 uživatelů", by měl MySQL server zatěžovat pouze 10 či 15 simultánně běžícími dotazy!

Jinak řečeno – v praxi byste se měli hlavně starat o pracovní souběžnost (working concurrency), neboli o počet vláken či připojení, která pracují simultánně. Také byste měli měřit, zdali hodně klesá výkon, když narůstá souběžnost. Pokud tomu tak je, lze předpokládat, že vaše aplikace patrně nezvládne vrcholy prudce zvyšované zátěže. V tomto případě musíte za-

56

Kapitola 2 – Hledání úzkých hrdel: testy výkonnosti a profilace

jistit, aby výkon aplikace příliš neklesal, nebo navrhnout aplikaci tak, aby nevytvářela vysokou souběžnost v těch svých částech, které by nezvládly vrcholnou zátěž. Všeobecně asi budete chtít omezit souběžnost na MySQL serveru pomocí různých návrhů, jako je například aplikační fronta. Více informací k tomuto tématu najdete v kapitole 10.

Uvědomte si, že souběžnost je zcela něco jiného než doba odezvy a škálovatelnost. Není to výsledek nějakého testu výkonnosti, ale spíše vlastnost toho, jak jste test připravili. Neměříte, jaké souběžnosti je vaše aplikace schopna docílit – měříte výkon aplikace při různých úrovních souběžnosti.

Nakonec byste měli otestovat výkon všeho, co je důležité pro vaše uživatele. Testy výkonnosti sice měří výkon, ale "výkon" znamená pro různé lidi různé věci. Nashromážděte požadavky (ať už formálně nebo neformálně) ohledně toho, jak má být systém schopen škálovat, jaké jsou přijatelné doby odezvy, jaký druh souběžnosti se dá očekávat atd. Poté se pokuste navrhnout testy výkonnosti tak, aby braly všechny tyto požadavky v úvahu, aby neměly klapky na očích, a aby se nesoustřeďovaly pouze na některé věci a opomíjely jiné.

Taktiky testů výkonnosti Základy máme za sebou, takže přejděme ke specifikům designu a vykonávání testů výkonnosti.

Než ovšem začneme s výkladem, jak dobře dělat testy výkonnosti, podívejme se na několik běžných omylů, které vedou na nepoužitelné nebo nepřesné výsledky.

Použije se pouze malá podmnožina opravdových dat. Tím máme na mysli situaci, ve které se použije pouze gigabajt dat, zatímco aplikace bude muset zvládnout stovky gigabajtů.

Použijí se nesprávně rozložená data, například data s rovnoměrným rozložením, zatímco váš systém bude v realitě pracovat s různými shluky dat. (Náhodně vygenerovaná data často neodpovídají rozložení dat ze skutečného světa, s nimiž bude vaše aplikace pracovat.)

Použijí se nerealistické parametry, například to, že budete předpokládat, že všechny uživatelské profily se budou prohlížet stejně často.

U víceuživatelské aplikace se použije jednouživatelský scénář.

Testuje se výkon distribuované aplikace na jediném serveru.

Opomíjí se soulad s chováním skutečných uživatelů, například "čas na rozmyšlenou" na webové stránce. Uživatel ze skutečného světa požádá o stránku a poté si ji čte. Jinak řečeno: nepředpokládejte, že bude klikat bez rozmyslu na jeden odkaz za druhým.

V cyklu se spouštějí identické dotazy. Dotazy ve skutečném světě nebývají úplné identické, což znamená, že cache dotazů nebývá využita úplně naplno. Identické dotazy jsou obvykle plně, nebo alespoň částečně, vraceny z cache.

Zapomíná se kontroly chyb. Pokud výsledky nějakého testu výkonnosti náhle vypadají nesmyslně – například pomalá operace se zčistajasna dokončuje velmi rychle – zkontrolujte,

KAPITOLA 4 Optimalizace výkonu dotazu V předchozích kapitolách jsme vám vysvětlili, jak se má optimalizovat schéma, což je jedna z nutných podmínek vysokého výkonu. Nestačí ale pracovat pouze se schématem – musíte také dobře navrhovat dotazy. Pokud dotazy vaší aplikace nebudou dobře navrženy, ani optimálně navržené schéma s nimi mnoho nesvede – výkon rozhodně nebude ideální.

Optimalizace dotazu, optimalizace indexu a optimalizace schématu fungují ruku v ruce. Jak budete postupně získávat stále více zkušeností s psaním dotazů v MySQL, budete stále lépe vědět, jak navrhovat schémata, která budou podporovat efektivní dotazy. A obdobně – to, co se dozvíte o optimálním návrhu schématu, bude ovlivňovat, jaký druh dotazů napíšete. Protože se jedná o proces, který nějaký čas trvá, doporučujeme vám, abyste se průběžně vraceli k této a předchozí kapitole.

Tuto kapitolu začínáme všeobecnými úvahami o návrhu dotazu, což jsou první věci, nad kterými byste se měli zamyslet, pokud nějaký dotaz nemá příliš dobrý výkon. Poté se ponoříme hodně hluboko do optimalizace dotazů a do "vnitřních záležitostí" serveru. Předvedeme vám, jak zjistit, jakým způsobem MySQL vykonává konkrétní dotaz. Dozvíte se, jak změnit vykonávací plán dotazu.

A nakonec se podíváme na několik míst, kde MySQL neoptimalizuje dotazy dobře a prozkoumáme vzory pro optimalizaci dotazu, které napomáhají MySQL vykonávat dotazy efektivněji.

Naším cílem je, abyste důkladně porozuměli tomu, jak MySQL skutečně vykonává dotazy, takže byste měli být schopni posoudit, co je efektivní a co je neefektivní, maximálně těžit ze síly MySQL a vyhýbat se jeho slabinám.

Základní příčiny pomalého dotazu: optimalizujte přístup k datům Nejzákladnější příčinou nedobrého výkonu dotazu bývá to, že pracuje s příliš mnoha daty. Existují dotazy, které musejí projít obrovské množství dat. S nimi toho mnoho nenaděláme. Jedná se ovšem o dost neobvyklý případ, protože většina špatných dotazů se dá změnit tak, aby přistupovaly k men-

178

Kapitola 4 – Optimalizace výkonu dotazu

šímu objemu dat. V praxi jsme zjistili, že dotaz s nevalným výkonem je vhodné analyzovat ve dvou krocích.

1. Zjistit, zda aplikace nezískává víc dat, než je nezbytně nutné. Ačkoli to obvykle znamená, že přistupuje k příliš mnoha řádkům, může přistupovat i k příliš mnoha sloupcům.

2. Zjistit, zda server MySQL neanalyzuje více řádků, než je potřeba.

Nepožadujete z databáze data, která nepotřebujete?

Některé dotazy požadují víc dat, než potřebují, takže většinu z nich poté bez užitku odhodí. To znamená více práce pro server MySQL, vyšší režii na síti1, a samozřejmě také vyšší spotřebu paměti a zdrojů CPU na aplikačním serveru.

Následuje přehled několika typických chyb: Získává se víc řádků, než je nezbytně nutné. Jedním z běžných omylů je předpoklad, že MySQL poskytuje výsledky na přání, nikoliv že vypočítává a vrací úplnou výslednou sadu. Často to vidíme v aplikacích, které byly navrženy lidmi, již mají zkušenosti s jinými databázovými systémy. Tito vývojáři používají techniky, jako je například tato – vydají příkaz SELECT, který vrátí spoustu řádků, pak načtou prvních N řádků a uzavřou výslednou sadu (například načtou z databáze 100 nejnovějších článků pro nějaký zpravodajský web, ačkoliv na úvodní stránce se zobrazuje pouze 10 z nich). Domnívají se, že MySQL jim poskytne oněch 10 řádků a pak pozastaví vykonávání dotazu. MySQL ovšem ve skutečnosti vygeneruje kompletní výslednou sadu. Klientská knihovna pak načte všechna data a většinu z nich zahodí.

Zde je nejlepším řešením přidat do dotazu klauzuli LIMIT. Získávají se všechny sloupce z několika spojených tabulek. Chcete-li získat všechny herce účinkující v Academy Dinosaur, nepište dotaz v tomto stylu: mysql> SELECT * FROM sakila.actor -> INNER JOIN sakila.film_actor USING(actor_id) -> INNER JOIN sakila.film USING(film_id) -> WHERE sakila.film.title = 'Academy Dinosaur';

Takový dotaz totiž vrátí všechny sloupce ze všech tří tabulek. Napište dotaz raději takto: mysql> SELECT sakila.actor.* FROM sakila.actor...;

Získávají se všechny sloupce. Vždy byste se měli zarazit, pokud uvidíte příkaz SELECT *.

Opravdu potřebujete úplně všechny sloupce? Patrně ne. Když se získávají všechny sloupce, mohou se tím potlačit různé optimalizace, jako jsou pokrývající indexy. O zvyšujících se nárocích na I/O, paměť a režii CPU pro server snad nemusíme ani hovořit.

1

Režie na síti je nejhorší, pokud je aplikace umístěna na jiném hostiteli než je server. Nicméně si uvědomte, že ani tehdy, když se aplikace a MySQL nachází na stejném serveru, není přenos dat mezi nimi zadarmo.

MySQL profesionálně – optimalizace pro vysoký výkon

179

Databázoví administrátoři kvůli těmto skutečnostem často zakazují použití příkazu SELECT *. Také i z toho důvodu, aby snížili riziko, že budou vznikat různé potíže v důsledku toho, že někdo změnil seznam sloupců tabulky.

Samozřejmě, když požadujete více dat, než momentálně potřebujete, není to vždycky na závadu.

V mnoha případech, které jsme prošetřovali, nám lidé říkali, že jejich "marnotratný" přístup zjednodušuje vývoj, a že vývojářům umožňuje použít stejný úsek kódu na více místech. To jsou rozumné argumenty, pokud ovšem víte, jak se to odrazí na nákladech z hlediska výkonu. Získávat více dat než je nutné může být také vhodné v situacích, kdy vaše aplikace používá nějaký druh cachování, nebo pokud z toho těžíte jiným způsobem. Získávání a následné cachování úplných objektů může být výhodné například tehdy, kdy spouštíte velké množství samostatných dotazů, které získávají pouze části těchto objektů.

Nezkoumá MySQL příliš mnoho dat?

Pokud jste si jisti, že vaše dotazy získávají pouze ta data, která opravdu potřebujete, hledejte dotazy, jež zkoumají příliš mnoho dat, zatímco generují výsledky. V MySQL jsou nejjednoduššími metrikami nákladnosti dotazů: Doba vykonávání.

Počet zkoumaných řádků.

Počet vrácených řádků.

Ačkoliv žádná z těchto metrik není perfektní mírou nákladnosti dotazu, alespoň orientačně odrážejí, k jakému množství dat musí MySQL interně přistoupit, aby vykonal dotaz. Můžete si je rovněž přeložit na "jak rychle dotaz poběží". Všechny tři metriky se zaznamenávají do logu pomalých dotazů, takže dívat se do tohoto logu je jeden z nejlepších způsobů, jak najít dotazy, které zkoumají příliš mnoho dat.

Doba vykonávání V kapitole 2 jsme diskutovali o tom, že standardní log pomalých dotazů má v MySQL 5.0 a předchozích verzích několik závažných omezení – například v něm postrádáme podporu pro jemnější zaznamenávání do logu. Naštěstí existují záplaty, s nimiž můžete zaznamenávat a měřit pomalé dotazy s rozlišením na mikrosekundy. Ačkoliv tyto záplaty byly zařazeny až do MySQL verze 5.1, není problém je v případě potřeby aplikovat i na starší verze serveru. Nenechte se ale strhnout přílišným nadšením pro dobu vykonávání dotazu. Ačkoliv je to hezká míra, protože je objektivní, není konzistentní při různých úrovních zátěže. Jiné faktory – jako jsou zámky úložného enginu (zámky na tabulky a na řádky tabulek), vysoká souběžnost, nebo použitý hardware – rovněž mohou mít značný dopad na dobu vykonávání dotazů. Tato metrika je užitečná, protože s ní dokážete najít dotazy, které mají největší dopad na rychlost odpovědi aplikace, nebo dotazy, jež nejvíce zatěžují server. Už vám ovšem neřekne, zdali to náhodou není tak, že skutečná doba vykonávání dotazu

180

Kapitola 4 – Optimalizace výkonu dotazu

jednoduše odpovídá jeho složitosti. (Doba vykonávání může být jak symptomem, tak i současně příčinou problémů, takže nemusí být vždy zřejmé, co vlastně indikuje.)

Počet zkoumaných řádků a počet vrácených řádků Když analyzujete dotazy, vyplatí se popřemýšlet o tom, kolik řádků musí dotaz prozkoumat, protože z toho se dá usoudit, jak efektivně dotaz hledá data, která potřebujete.

Podobně jako doba vykonávání dotazu, ani toto není perfektní metrika pro zjišťování špatných dotazů. Všechny přístupy k řádkům nejsou rovnocenné. Ke kratším řádkům se přistupuje rychleji a řádky z paměti se získávají mnohem rychleji, než když se data čtou z disku.

Ideální by bylo, kdyby počet zkoumaných řádků byl roven počtu vracených řádků, ale v praxi je tohle uskutečnitelné jen výjimečně. Pokud například sestrojujete řádky pomocí dotazů, v nichž se spojují tabulky, musíte přistoupit k několika řádkům, abyste vygenerovali jeden řádek výsledné sady. Ačkoliv poměr počtu zkoumaných řádků k počtu vrácených řádků je obvykle malý, řekněme od 1:1 k 10:1, v některých situacích může být i řádově větší.

Počet zkoumaných řádků a přístupové typy Když přemýšlíte o nákladnosti dotazu, posuďte, jaké jsou náklady na nalezení jediného řádku z tabulky. MySQL obsahuje několik přístupových metod pro nalezení a vrácení řádku. Některé metody požadují, aby se prozkoumalo mnoho řádků, v jiných se dají vygenerovat výsledky, aniž by se musel prozkoumat byť jen jediný řádek.

Přístupové metody se uvádějí ve sloupci type výstupu příkazu EXPLAIN. Přístupové typy mohou být různé – od kompletního průchodu celou tabulkou přes průchod indexy tabulky, průchod nějakým rozsahem, vyhledávání podle hodnot jedinečného indexu, až ke konstantám. Každý ze zde uvedených přístupových typů je rychlejší než ten, který je v seznamu před ním, protože čte méně dat. Ačkoliv se přístupové typy nemusíte učit nazpaměť, měli byste mít alespoň všeobecné povědomí o tom, co znamená průchod celou tabulkou, průchod jedním indexem, přístup přes rozsah a přístup přes jedinou hodnotu.

Pokud se vám zdá, že použitý přístupový typ není dobrý, nejlepší řešení obvykle spočívá v přidání vhodného indexu. Protože indexy jsme důkladně probírali v předchozí kapitole, předpokládáme, že už víte, proč jsou tak důležité pro optimalizaci dotazu. Indexy umožňují MySQL nacházet řádky s mnohem efektivnějším přístupovým typem, kdy se zkoumá mnohem méně dat.

Podívejte se například na jednoduchý dotaz obracející se na ukázkovou databázi Sakila: mysql> SELECT * FROM sakila.film_actor WHERE film_id = 1;

Tento dotaz vrátí 10 řádků a příkaz EXPLAIN nám ukáže, že MySQL pro vykonání dotazu použije přístupový typ ref na index idx_fk_film_id: mysql> EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G *************************** 1. row ***************************

MySQL profesionálně – optimalizace pro vysoký výkon

181

id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: const rows: 10 Extra: EXPLAIN ukazuje, že MySQL odhadl, že bude muset přistoupit pouze k 10 řádkům. Řečeno jinými slovy – optimalizátor dotazu věděl, že zvolený přístupový typ bude schopen vykonat dotaz efektivně. Co by se ovšem stalo, kdybychom neměli pro dotaz příhodný index? MySQL by musel použít méně optimální přístupový typ, což uvidíte, když odstraníte index a spustíte dotaz znovu: mysql> ALTER TABLE sakila.film_actor DROP FOREIGN KEY fk_film_actor_film; mysql> ALTER TABLE sakila.film_actor DROP KEY idx_fk_film_id; mysql> EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film_actor type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5073 Extra: Using where

Jak se dalo očekávat, přístupový typ se změnil na průchod celou tabulkou (ALL), přičemž MySQL nově odhadl, že bude muset prozkoumat celkem 5 073 řádků, aby splnil požadavky dotazu.

Text "Using where" ve sloupci Extra sděluje, že server MySQL bude prostřednictvím klauzule WHERE odhazovat řádky až poté, co je načte úložný engine.

MySQL může aplikovat klauzuli WHERE jedním ze tří způsobů, od nejlepšího k nejhoršímu: Aplikuje podmínky na operaci indexového vyhledávání podle hodnot klíče, aby eliminoval nevyhovující řádky. Tohle se děje na vrstvě úložného enginu.

Použije pokrývající index, aby zamezil přístupu k řádkům a odfiltroval nevyhovující řádky po získání jednotlivých výsledků z indexu (ve sloupci Extra bude uveden text "Using index"). Ačkoliv tohle se děje na vrstvě serveru, nevyžaduje to číst řádky z tabulky.

182

Kapitola 4 – Optimalizace výkonu dotazu

Získá řádky z tabulky a poté odfiltruje nevyhovující řádky (ve sloupci Extra bude uveden text "Using where"). Tohle se děje na vrstvě serveru a vyžaduje, aby server načetl řádky z tabulky předtím, než je bude schopen odfiltrovat.

Výše uvedený příklad ilustruje, jak je důležité mít dobré indexy. Dobré indexy pomáhají dotazům získat dobrý přístupový typ, takže jim stačí prozkoumat pouze ty řádky, které potřebují. Když ovšem přidáte nějaký index, neznamená to vždy, že bude stejný počet řádků, k nimž bude MySQL přistupovat i které bude vracet. Podívejte se na dotaz, ve kterém se volá agregační funkce COUNT()1: mysql> SELECT actor_id, COUNT(*) FROM sakila.film_actor GROUP BY actor_id;

Ačkoliv tento dotaz vrátil pouze 200 řádků, potřeboval načíst řádově tisíce řádků, aby mohl vybudovat výslednou sadu. V dotazech tohoto druhu není možné prostřednictvím nějakého indexu zredukovat počet zkoumaných řádků.

MySQL vám bohužel nesdělí kolik z řádků, k nimž přistoupil, bylo potřeba pro vybudování výsledné sady – sdělí pouze celkový počet řádků, k nimž přistupoval. Mnohé z těchto řádků lze eliminovat klauzulí WHERE, protože ničím nepřispívají do výsledné sady. Poté, co jsme v předchozím příkladu odstranili index z tabulky sakila.film_actor, dotaz postupně prošel všechny záznamy tabulky, ale klauzule WHERE téměř všechny odstranila, zbylo jich pouze 10. Pouze těchto zbylých 10 řádků se podílelo na budování výsledné sady. Pokud chcete porozumět tomu, ke kolika řádkům server přistoupil, a kolik z nich skutečně použil, musíte se nad dotazem zamyslet a zkusit to vydedukovat. Pokud zjistíte, že pro vyprodukování relativně malého počtu řádků výsledné sady bylo nutné prozkoumat obrovský počet řádků, můžete vyzkoušet tyto sofistikovanější opravy: Použijte pokrývající indexy, které ukládají data tak, aby úložný engine nemusel získávat kompletní řádky. (Detailně jsme je probrali v předchozí kapitole.)

Změňte schéma. Jako příklad mohou posloužit souhrnné tabulky. (Detailně jsme je probrali v předchozí kapitole.)

Přepište komplikovaný dotaz tak, aby ho optimalizátor MySQL mohl vykonat optimálnějším způsobem. (Tohle téma probereme později v této kapitole.)

Restrukturalizace dotazu Když optimalizujete problematické dotazy, vaším cílem by mělo být nalézt alternativní způsoby pro získání těch výsledků, které potřebujete – což nutně neznamená, že se musí jednat o stejnou výslednou sadu získanou z MySQL. Někdy získáte lepší výkon tím, že transformujete dotazy do nějaké jiné ekvivalentní formy. Měli byste také pouvažovat nad tím, zdali by nebylo lepší dotazy přepsat, aby vám poskytovaly odlišné výsledky, pokud to bude mít příznivý dopad na efektivitu. Je možné, že dosáhnete stejného cíle, když místo změn v dotazech změníte kód samotné aplikace. V tom-

1

Další informace k tomuto tématu viz "Optimalizace dotazu s COUNT()" na straně 215.

KAPITOLA 8 Replikace Zabudované vybavení, které má MySQL pro potřeby replikací, tvoří základy pro budování rozsáhlých, vysoce výkonných aplikací nad MySQL. Replikace umožňuje nakonfigurovat jeden nebo více serverů jako repliky ("otroky", slaves) jiného serveru. To není prospěšné pouze pro vysoce výkonné aplikace – hodí se také pro mnohé jiné úlohy, například pro sdílení dat se vzdáleným pracovištěm, pro "zahřátou" nakonfigurovanou rezervní jednotku, které se říká hot spare, nebo když udržujete server s kopií ostrých dat pro testovací či výukové účely. V této kapitole prozkoumáme všechny aspekty replikace. Začneme přehledem, jak funguje, pak se podíváme na základní přípravu serveru, na navrhování pokročilejších replikačních konfigurací, a neopomeneme správu a optimalizaci replikovaných serverů. Přestože se v této knize hlavně soustřeďujeme na výkon, v případě replikace se budeme stejnou měrou zabývat i bezchybností a spolehlivostí, takže si ukážeme, jak připravit replikaci tak, aby fungovala dobře. Podíváme se také na některé nadcházející změny a zdokonalení v replikaci MySQL, mezi něž patří několik zajímavých záplat, které vytvořil v Google.

Replikace přehledně Základní problém, který řeší replikace, je udržet data jednoho serveru synchronizovaná s daty jiného serveru. K jedinému "pánovi", master serveru, nebo zkráceně masteru, se může připojit několik "otroků", slave serverů, zkráceně replik, a replika zase může naopak pracovat jako master. Mastery a repliky můžete uspořádávat v mnoha různých topologiích. Můžete replikovat celý server, replikovat jen některé databáze, nebo dokonce určovat, které tabulky se mají replikovat.

MySQL podporuje dva druhy replikace: příkazovou a řádkovou. Příkazová (neboli "logická") replikace je k dispozici už od MySQL 3.23 a v ostrém provozu ji používá většina lidí. Řádková replikace je novinkou v MySQL 5.1. Oba druhy replikace pracují tak, že zaznamenávají změny v binárním logu mastera1 a přehrávají tento log na repliku. Obě jsou asynchronní – tj. u kopie dat na replice

1

Je-li pro vás pojem binárního logu nový, naleznete více informací v kapitole 6, v této kapitole, a v kapitole 11.

370

Kapitola 8 – Replikace

není zaručeno, že se v každém okamžiku bude jednat o "nejčerstvější data"1. Není zde ani žádná garance toho, jak dlouhá může být latence na replice. Rozsáhlé dotazy mohou způsobovat, že budou repliky zpožděny v řádu sekund, minut, nebo dokonce i hodin za masterem.

Replikace v MySQL je většinou zpětně kompatibilní. Tj. novější server obvykle může bez jakýchkoliv potíží sloužit jako replika staršího serveru. Ovšem starší verze serveru často nemohou sloužit jako repliky novějších verzí – nemusejí rozumět některým novým funkcím, nebo nové syntaxi SQL, jež používá novější server, a mohou zde být i odlišnosti ve formátech souborů, které se při replikaci používají. Například, nemůžete replikovat z mastera MySQL 5.0 na repliku MySQL 4.0. Vždy se vyplatí otestovat replikační konfiguraci, než podniknete upgrade z jedné hlavní verze na jinou, jako z 4.1 na 5.0, nebo z 5.0 na 5.1.

Replikace pro mastera obecně neznamená mnoho režie navíc. Požaduje sice, aby na masteru bylo zapnuto zaznamenávání do binárního logu, které může mít významnou režii, ale pro řádné zálohování je stejně potřebujete. Kromě binárního logu přidává během normálního fungování něco režie mastera i každá připojená replika (většinou se jedná o síťový I/O).

Replikace je poměrně dobrá pro škálování čtení, protože je můžete směrovat na repliku, ale není už tak dobrá pro škálování zápisů, pokud ji nenavrhnete opravdu dobře. Když k masteru připojíte hodně replik, jednoduše to způsobí, že zápisy se budou muset udělat mnohokrát, na každé replice jednou. Celý systém pak bude omezen počtem zápisů, které umí vykonat nejslabší část systému.

Replikace také znamená plýtvání, pokud používáte více než několik replik, protože se v podstatě zbytečně duplikuje spousta dat. Například, jediný master s 10 replikami znamená 11 kopií stejných dat a duplikaci většiny stejných dat v 11 různých cache. Je to analogické s 11 disky spojenými do RAID 1 na úrovni serveru. Ačkoliv to není hospodárné využití hardwaru, přesto až překvapivě často vidíme tento druh replikační konfigurace. Různé způsoby, jak se dá tento problém zmírnit, probíráme na různých místech této kapitoly.

Problémy, které řeší replikace Replikace se běžně používá při řešení následujících problémů: Distribuce dat. Replikace MySQL obvykle nezatěžuje příliš šířku pásma2 a můžete ji podle chuti zastavovat a startovat. Je užitečná pro udržování kopie dat v zeměpisně vzdálených lokalitách, jako jsou různá datová centra. Vzdálená replika může dokonce pracovat s připojením, které je přerušované (neúmyslně nebo jinak). Pokud ovšem chcete, aby vaše repliky měly velmi malé replikační zpoždění, budete potřebovat stabilní spoj s nízkou latencí.

Rozložení zátěže (load balancing). Replikace MySQL může pomoci s distribucí čtecích dotazů přes několik serverů, funguje velmi dobře u aplikací, které intenzivně čtou. Základního

1 2

Více k tomu viz "Synchronní MySQL replikace" na straně 476.

Jak uvidíte později, řádková replikace, která byla zavedena v MySQL 5.1, může používat mnohem více šířky pásma než tradičnější příkazová replikace.

MySQL profesionálně – optimalizace pro vysoký výkon

371

rozložení zátěže lze docílit s několika jednoduchými změnami kódu. V malém měřítku se dají použít zjednodušené přístupy, jako explicitně kódované názvy hostitelů nebo DNS používané "pořád dokola" (ve stylu "round-robin", které pomocí jediného názvu hostitele ukazují na několik IP adres). Nebo použijte sofistikovanější přístupy. Chcete-li zátěž distribuovat mezi MySQL servery, budou dobře fungovat standardní řešení pro rozložení zátěže, jako jsou různé produkty pro síťové rozložení zátěže. Dobře také funguje projekt Linux Virtual Server (LVS). Rozložení zátěže budeme detailněji probírat v kapitole 9.

Zálohování. Replikace je cenná technika, která může vypomoci se zálohováním, nicméně rozhodně nechápejte repliku ani jako zálohu, ani jako náhradu záloh.

Vysoká dostupnost a rychlá náhrada vypadlého serveru (failover). Replikace může zamezit, aby se MySQL stal tzv. kritickým místem výpadku (SPOF, single point of failure), neboli aby kvůli němu vypadla celá aplikace. Dobrý systém náhrady serverů, které vypadnou (failover system), jenž zahrnuje replikované servery, může pomoci významně zredukovat prostoje. Toto téma rovněž probereme podrobněji v kapitole 9.

Testování při upgradu MySQL. Je běžnou praxí připravit repliku s upgradovanou verzí MySQL a nějakou dobu ji používat, abyste se přesvědčili, že dotazy pořád pracují tak, jak očekáváte. Teprve poté upgradujete všechny ostatní instance.

Jak funguje replikace Než se pustíme do detailů přípravy replikace, podívejme se, jak MySQL skutečně replikuje data.

Na vysoké úrovni je replikace jednoduchý postup složený ze tří kroků: 1. Master zaznamená změny ve svých datech do svého binárního logu. (Těmto záznamům se říká události binárního logu, binary log events.)

2. Replika zkopíruje události binárního logu masteru do svého logu, říká se mu relay log.

3. Replika přehraje události nacházející se v relay logu a aplikuje změny na svá vlastní data.

Uvědomte si, že tohle je pouze přehled – každý z těchto kroků je dost složitý. Podrobněji je replikace znázorněna na obrázku 8-1.

První částí postupu je zaznamenávání do binárního logu na masteru (později vám ukážeme, jak tohle připravit). Těsně předtím, než se na masteru dokončí jakákoliv transakce, která aktualizuje data, master zaznamená její změny do svého binárního logu. MySQL zapisuje jednotlivé transakce do binárního logu jednu po druhé, i když se příkazy v transakcích během vykonávání střídaly. Poté, co master zapsal události do binárního logu, sdělí úložným enginům, aby transakce potvrdily.

V dalším kroku zkopíruje replika binární log mastera na svůj vlastní pevný disk, do relay logu. Začne tím, že nastartuje zpracovatelské vlákno, kterému se říká I/O vlákno repliky (I/O slave thread).

Toto I/O vlákno otevře obyčejné klientské připojení k masteru a následně nastartuje speciální proces binlog dump (není k němu odpovídající příkaz SQL). Proces binlog dump čte události z binár-

372

Kapitola 8 – Replikace

ního logu masteru. Nedotazuje se na události. Jakmile dostihne mastera, jde spát a čeká na signál od mastera, že jsou k dispozici nové události. I/O vlákno zapíše události do relay logu repliky.

Obrázek 8-1. Jak funguje replikace MySQL. Před MySQL 4.0 fungovala replikace v mnoha ohledech úplně jinak. Například – první replikační funkcionalita MySQL nepoužívala relay log, což znamenalo, že replikace používala jen dvě vlákna, ne tři.

Protože většina lidí provozuje novější verze serveru, v této kapitole už nebudeme zmiňovat jakékoliv další detaily o velmi starých verzích MySQL.

Poslední část celého postupu zpracovává SQL vlákno repliky (SQL slave thread). Toto vlákno čte a přehrává události z relay logu, což znamená, že aktualizuje data repliky tak, aby se shodovala s daty masteru. Dokud toto vlákno udrží krok s I/O vláknem, relay log obvykle setrvává v cache operačního systému, takže relay logy mají velmi nízkou režii. Události, které vykonává SQL vlákno repliky, mohou volitelně jít do vlastního binárního logu repliky, což se hodí ve scénářích, jež budeme zmiňovat později v této v kapitole.

Na obrázku 8-1 jsou znázorněna pouze dvě replikační vlákna, která běží na replice, nicméně ještě existuje jedno vlákno na masteru: podobně jako jakékoliv jiné připojení k MySQL serveru, to připojení, jež replika otevře k masteru, nastartuje vlákno na masteru.

Tato replikační architektura odděluje na replice procesy získávání a přehrávání událostí, takže mohou být asynchronní. Jinak řečeno: I/O vlákno může pracovat nezávisle na SQL vláknu. Vnáší také do replikačního postupu jistá omezení – nejdůležitější z nich je to, že na replice je replikace serializována. To znamená, že aktualizace, které možná na masteru probíhaly paralelně, v různých vláknech, nemohou být na replice paralelizovány. A jak uvidíme později, právě tohle tvoří úzké hrdlo výkonu pro mnohé pracovní zátěže.

MySQL profesionálně – optimalizace pro vysoký výkon

373

Příprava replikace Ačkoliv příprava replikace je v MySQL poměrně jednoduchá, existuje mnoho různých variant základních kroků, záleží na konkrétním scénáři. Základním scénářem je čerstvě nainstalovaný master a replika. Na vysoké úrovni vypadá postup takto: 1. Na každém ze serverů se připraví replikační účet.

2. Nakonfiguruje se master a replika.

3. Replice se vydá pokyn, aby se připojila k masteru a odtud replikovala.

Zde se předpokládá, že mnoho výchozích hodnot bude postačujících, což je pravda, pokud jste master a repliku právě nainstalovali a mají stejná data (výchozí databázi mysql). Postupně si předvedeme všechny kroky a budeme předpokládat, že se servery jmenují server1 (IP adresa 192.168.0.1) a server2 (IP adresa 192.168.0.2). Poté si vysvětlíme, jak se inicializuje replika ze serveru, který je už v provozu, a prozkoumáme doporučenou replikační konfiguraci.

Vytvoření replikačních účtů MySQL má několik speciálních oprávnění, která umožňují běh replikačních procesů. I/O vlákno repliky, které běží na replikačním serveru repliky, učiní TCP/IP připojení k masteru. To znamená, že na masteru musíte vytvořit nový uživatelský účet a přidělit mu patřičná oprávnění, aby se I/O vlákno mohlo připojit jako tento uživatel a číst binární log mastera. Podívejte se, jak se vytvoří takový uživatelský účet, který jsme pro účely této ukázky nazvali repl: mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* -> TO repl@'192.168.0.%' IDENTIFIED BY 'p4ssword';

Tento účet vytvoříme na masteru i na replice. Připomínáme, že jsme uživatele omezili na lokální síť, protože replikační účet není bezpečný. (Další informace k bezpečnosti viz kapitola 12.)

Replikační uživatel ve skutečnosti potřebuje na masteru jen oprávnění REPLICATION CLIENT, nepotřebuje oprávnění REPLICATION SLAVE ani na jednom ze serverů. Tak proč jsme toto oprávnění udělili na obou serverech? Ze dvou následujících důvodů: Účet, se kterým budete monitorovat a spravovat replikaci, potřebuje oprávnění REPLICATION SLAVE a je mnohem praktičtější, když se použije jediný účet pro tyto účely, než pro ně vytvářet samostatný uživatelský účet.

Připravíte-li účet na masteru a poté z něho naklonujete repliku, bude replika správně připravena, aby mohla fungovat jako master, pro případ, že byste chtěli prohodit role repliky a mastera.

374

Kapitola 8 – Replikace

Konfigurace mastera a repliky Dalším krokem je zapnout několik nastavení na masteru, o němž předpokládáme, že se jmenuje server1. Je potřeba, abyste zapnuli zaznamenávání do binárního logu a specifikovali ID serveru.

Zadejte do souboru my.cnf mastera následující řádky (nebo ověřte, zdali tam jsou): log_bin = mysql-bin server_id = 10

Přesné hodnoty záleží na vás. Ačkoliv my se zde vydáváme tou nejschůdnější cestou, vy si můžete pochopitelně připravit něco propracovanějšího.

Musíte explicitně specifikovat jedinečné ID serveru. Zvolili jsme 10, nikoliv 1, protože 1 je výchozí hodnota, kterou server typicky vybere, když není specifikována žádná hodnota. (Je to závislé na verzi; některé verze MySQL jednoduše vůbec nebudou pracovat.) Hodnota 1 tak může snadno zmást a být v konfliktu se servery, které nemají svá explicitní ID. Běžnou praxí je použít poslední oktet IP adresy serveru, za předpokladu, že se nebude měnit a že je jedinečný (tj. server spadá pouze do jedné podsítě).

Pokud ještě není v konfiguračním souboru masteru specifikováno zaznamenávání do binárního logu, budete muset restartovat MySQL. Abyste prověřili, zdali je na masteru vytvořený soubor binárního logu, vydejte příkaz SHOW MASTER STATUS a zkontrolujte, zdali jste dostali výstup obdobný následujícímu (MySQL přidá na konec názvu souboru nějaké číslice, takže neuvidíte soubor s přesně stejným názvem, který jste specifikovali): mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File

| Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+ | mysql-bin.000001 |

98 |

|

|

+------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)

Replika potřebuje ve svém konfiguračním souboru my.cnf něco podobného, jako má master. Nezapomeňte, že i na replice bude potřeba restartovat MySQL: log_bin

= mysql-bin

server_id

= 2

relay_log

= mysql-relay-bin

log_slave_updates = 1 read_only

= 1

Některé z těchto voleb nejsou v zásadě nutné a u jiných jsme prostě jen explicitně uvedli výchozí hodnotu. Ačkoliv se v praxi na replice vyžaduje pouze parametr server_id, zapnuli jsme také log_bin a specifikovali explicitní název pro soubor binárního logu. Výchozí název se totiž bere z hostitelského názvu serveru, což může dělat potíže, jestliže se hostitelský název změní. Dále

KAPITOLA 11 Zálohy a zotavení Snadno se stane, že se člověk tak soustředí na "skutečnou práci, která se má udělat", že zanedbává zálohování a nemyslí na zotavení. Ovšem to, na co se spěchá, nebývá často důležité, a to, co je opravdu důležité, to se zdá, že až tak nespěchá. Zálohy jsou důležité pro vysoký výkon i pro zotavení po nějaké katastrofě. Zálohování byste měli naplánovat a navrhnout pořádně už od samého začátku, aby nezpůsobovaly prostoje a nesnižovaly výkon.

Pokud nemáte žádný plán pro zálohy, takže je vyrábíte průběžně, až zbude nějaký čas, obvykle vytvoříte řešení, které vypadá jako ohyzdná přístavba. V okamžiku, kdy je hotové, zřejmě zjistíte, že jste dříve učinili rozhodnutí, která vylučují použít ten nejlepší způsob vysoce výkonného zálohování. Například jste už přichystali nějaký server a následně zjistili, že vlastně potřebujete LVM, abyste mohli pořizovat momentky (snapshots) souborového systému – na to už je ale příliš pozdě.

Také můžete přehlédnout některé důležité dopady na výkon, pokud nenakonfigurujete své systémy i s ohledem na zálohování. A pokud nebudete mít nějaký plán pro zotavovací operace, věřte nám, že určitě neproběhnou hladce, až dojde na lámání chleba a budete muset zotavovat ve stresu.

Se zálohovacími systémy je to jako s monitorovacími a signalizačními systémy: většina systémových administrátorů u nich dříve nebo později znovu vynalézá kolo. Je to ostuda, protože často je po ruce solidní, dobře podporovaný a flexibilní zálohovací software – a některý je dokonce zdarma.

Rozhodně doporučujeme, abyste používali ty části těchto systémů, které můžete rozumně využít.

V této kapitole nehodláme probírat veškeré části dobře navrženého zálohovacího a zotavovacího řešení. Tato problematika je totiž natolik obsáhlá, že by snadno vystačila na celou knihu.

A vskutku – několik takových knih existuje1. Některá témata zde přeskočíme a soustředíme se na řešení pro vysoce výkonný MySQL. Na rozdíl od prvního vydání knihy zde předpokládáme, že čtenáři této knihy používají úložný engine InnoDB, buď místo MyISAM, nebo společně s ním. Tím se ovšem poněkud komplikují některé zálohovací scénáře.

1

Myslíme si, že dobrou volbou je kniha Backup & Recovery, kterou napsal W. Curtis Preston (O'Reilly).

498

Kapitola 11 – Zálohy a zotavení

Přehled Kapitolu začneme revizí několika termínů a prodiskutujeme různé záležitosti, které byste měli vést v patrnosti, až budete plánovat svá řešení pro zálohování a zotavování, včetně potenciálních nároků na to či ono. Pak předložíme přehled různých technologií a metod, jimiž se vytvářejí zálohy, a prozkoumáme techniky pro obnovu dat a zotavování po haváriích. Nakonec probereme několik vybraných dostupných zálohovacích nástrojů, a tuto kapitolu uzavřeme několika příklady, jak vybudovat vlastní zálohovací utility.

Terminologie Než začneme, ujasněme si několik termínů. Zaprvé, zálohy se často v angličtině blíže specifikují přívlastky hot, warm a cold. Obvykle se jimi vyznačuje, jaký dopad bude mít zálohování. Například, při zálohování za chodu (hot backup) se nepředpokládá, že by to vyžadovalo nějaké prostoje serveru. Problém je v tom, že tyto termíny jsou vágní a pro každého znamenají něco trochu jiného.

Některé nástroje mají dokonce slovo hot ve svých názvech, přičemž rozhodně nedělají to, co my považujeme za zálohování za chodu. (Občas se používá i termín dynamické zálohy; termínem cold backup se někdy chápou offline zálohy). Proto se snažíme těmto termínům vyhýbat a místo nich uvádíme, do jaké míry nějaká konkrétní technika nebo nástroj narušuje chod serveru.

Další dvě slova, která se často pletou, jsou restore (obnovit) a recover (zotavit). V této kapitole je používáme ve specifickém smyslu. Obnovit znamená získat data z nějaké zálohy – buď je ručně načíst do MySQL, nebo je umístit tam, kde MySQL očekává, že mají být. Zotavit obvykle vyjadřuje celý proces záchrany systému (nebo části nějakého systému) poté, co se přihodilo něco katastrofálního. Do tohoto procesu patří nejenom obnova dat ze záloh, ale také všechny další nezbytné kroky, které je potřeba učinit, aby byl server znovu plně funkční, například restart MySQL, změna konfigurace, zahřátí (naplnění) cache serveru atd.

Pro mnoho lidi znamená termín zotavení (recovery) pouze opravu porušených tabulek po nějaké havárii. Není to totéž co zotavení celého serveru. Zotavení úložného enginu musí dát do souladu jeho data a soubory logu. Také musí zajistit, že datové soubory budou obsahovat pouze ty modifikace, které učinily potvrzené transakce a přehraje ze souborů logu ty transakce, jež ještě nebyly aplikovány na datové soubory. Používáte-li nějaký transakční úložný engine, možná je to součást obecného zotavovacího procesu, nebo dokonce součást zálohování. Není to ovšem stejné zotavení, jaké potřebujete udělat například poté, co omylem vydáte příkaz DROP TABLE.

Všechno se točí kolem zotavení Pokud všechno běží jako na drátkách, nikdy nemusíte myslet na zotavení. Ovšem v okamžiku, až budete potřebovat zotavovat, nepomůže vám ani ten nejlepší zálohovací systém na světě. Potřebujete totiž výtečný zotavovací systém.

MySQL profesionálně – optimalizace pro vysoký výkon

499

Hlavní problém spočívá v tom, že je podstatně snadnější docílit, aby hladce fungovaly zálohovací systémy, než vybudovat dobré zotavovací procesy a nástroje. Proč? To objasňuje následující výčet: Především musíte mít zálohy. Nebudete schopni vůbec nic zotavit, pokud jste si předtím neudělali zálohy, takže když budujete nějaký systém, soustřeďte svou pozornost logicky v prvé řadě na zálohy. Je velmi důležité s těmito situacemi počítat, takže si nejprve naplánujte zotavení. Věřte nám – opravdu nemůžete vybudovat zálohovací systémy, dokud nebudete vědět, jaké máte požadavky na zotavení.

Zálohování je rutinní záležitost. Tato skutečnost často zaměřuje vaši pozornost na automatizaci a dolaďování zálohovacího procesu. Ačkoliv vám pět minut přemýšlení o tom, jak přizpůsobit zálohovací proces, nepřipadá moc podstatné, zeptejte se sami sebe – věnujete každodenně stejnou pozornost i úvahám o zotavení? Zotavovací postup byste si měli procvičovat tak dlouho, dokud nebude zcela hladký a bezchybný, stejně jako zálohovací proces.

Zálohy se obvykle nedělají pod extrémními tlaky, ovšem zotavení je typicky krizová situace.

Je velmi důležité si tohle uvědomit.

Do hry často vstupuje bezpečnost. Pokud děláte zálohy mimo web, pravděpodobně data záloh šifrujete nebo podnikáte nějaká jiná opatření, abyste je chránili. Ovšem až příliš často se stává, že se soustředíte pouze na to, jaké škody by nadělalo, kdyby se někdo k těmto datům dostal a zneužil je, přičemž úplně zapomenete na to, jaké škody nastanou, až nebude nikdo schopen odemknout zašifrovaný svazek za účelem zotavení dat nebo až budete potřebovat extrahovat jediný soubor z obrovského monolitického zašifrovaného souboru.

Zálohy může klidně naplánovat, navrhnout a implementovat jeden člověk, zejména tehdy, pokud má k dispozici vynikající nástroje. Ovšem až dojde ke katastrofě, je možné, že tento člověk již nebude k dosažení. Je potřeba vytrénovat několik lidí a naplánovat pokrytí, aby data nemusela zotavovat nějaká nekvalifikovaná osoba.

Uveďme jeden příklad ze skutečného světa: jistý zákazník nás informoval o tom, jak se mu zálohy nebetyčně zrychlily, když s mysqldump uvedl volbu -d, a chtěl vědět, proč se nikde nepíše o tom, jak hodně může tato volba urychlit zálohovací proces. Kdyby se ovšem tento zákazník alespoň jednou pokusil o obnovu ze svých záloh, nemohl by si nevšimnout, proč je tomu tak: volba -d znamená, že se vůbec nedělá dump dat řádků! Zákazník byl zcela pohlcen zálohováním, nikoliv zotavením, takže se o této věci vůbec nedozvěděl. Když začnete myslet na zotavení, tak ještě předtím, než vůbec začnete cokoliv dělat, specifikujte všechny požadavky. Například byste měli vzít v úvahu toto: O kolik dat můžete přijít, aniž by to mělo nějaké závažné důsledky? Budete potřebovat zotavovat ke konkrétnímu časovému bodu, nebo lze akceptovat, že přijdete o všechno, co se událo od chvíle, kdy jste pořídili poslední zálohy? Jsou nějaké požadavky z hlediska zákonů?

Jak rychlé musí být zotavení? Jaký druh prostojů je akceptovatelný? Jaké dopady (například částečnou nedostupnost) budou akceptovat uživatelé a aplikace? Jakým způsobem zabudujete funkcionalitu, která vám umožní fungovat i ve chvíli, kdy nastanou tyto scénáře?

Co všechno potřebujete zotavovat? Mezi běžné požadavky patří celý server, jediná databáze, jediná tabulka, nebo pouze specifické transakce nebo příkazy.

500

Kapitola 11 – Zálohy a zotavení

Sepište si odpovědi na tyto otázky, přidejte je do dokumentace vašeho systému, a mějte je na paměti při četbě zbytku této kapitoly. Jakmile tento úkol splníte, budete se lépe soustředit na samotné zotavení, až začnete plánovat zálohy. A když se tyto odpovědi stanou nedílnou součástí vaší dokumentace, budete je mít pohotově po ruce, až se k nim budete potřebovat později vracet.

Mýtus číslo 1 o zálohách: "Jako zálohy používám replikaci."

Chyba, se kterou se setkáváme poměrně často. Server repliky není záloha. Ani pole RAID není záloha.

Abyste zjistili proč, uvažte toto: pomohou vám dostat zpět všechna data, když někdo omylem vydá příkaz DROP DATABASE na ostrou databázi? RAID ani replika vůbec neprojdou tímto primitivním testem.

Nejenže to nejsou zálohy, nejsou to ani vhodné náhrady za zálohy. Potřeby ohledně záloh nesplní nic jiného než právě samotné zálohy.

Témata, která neprobíráme Zálohování MySQL je v mnoha ohledech pouze specializovanější případ obecnějšího problému zálohování a zotavení. Ačkoliv se chceme soustředit na vysoce výkonný MySQL, bylo pro nás dost obtížné sem nezařadit materiály o spoustě dalších témat, zejména proto, že se setkáváme až s příliš velkým počtem lidí, kteří se stále potýkají se stejnými zálohovacími a zotavovacími problémy.

Tady máte seznam toho, co jsme se rozhodli sem nezařadit: Bezpečnost (kdo má přístup k zálohám, kdo má oprávnění obnovit data, šifrování záloh).

Kam se mají zálohy ukládat, včetně toho, jak mají být daleko od zdroje (na jiném disku, na jiném serveru, nebo někde úplně jinde), a jak se budou data přesouvat ze zdroje na cíl.

Retenční zásady, audity, požadavky vyplývající ze zákonů a související témata. Řešení úložišť a médií, komprimace a inkrementální zálohování.

Ukládací formáty (řekneme jen tohle: vyhýbejte se proprietárním zálohovacím formátům).

Monitorování záloh a informování o nich.

Zálohovací schopnosti, které jsou zabudovány do úložných vrstev nebo konkrétních zařízení, jako jsou prefabrikované souborové servery.

Pokud vám tato témata nejsou důvěrně známá, měli byste si přečíst nějakou knihu o zálohování.

Celkový obraz Než se pustíme velmi podrobně do všech možností, které jsou k dispozici, přečtěte si náš názor na to, co pravděpodobně potřebuje většina lidí pro řešení zálohovacího a zotavovacího procesu.

Tato doporučení můžete chápat jako startovní čáru nebo jako směr, kterým se můžete ubírat: Zálohování souborů (raw backups) je u velkých databází nezbytností. Je docela rychlé, což je velmi důležité. Ačkoliv naším favoritem jsou zálohy založené na momentkách databáze

MySQL profesionálně – optimalizace pro vysoký výkon

501

(snapshots), jako dobrá alternativa může posloužit i nástroj ibbackup InnoDB pro zálohování za chodu, za předpokladu, že používáte pouze tabulky InnoDB. Zálohujte binární logy pro potřeby zotavení k danému časovému bodu.

Udržujte několik generací záloh a udržujte binární logy dostatečně dlouho, abyste z nich mohli obnovovat.

Testujte pravidelně zálohovací a zotavovací proces tak, že kompletně si vyzkoušíte celý proces zotavení.

Vytvářejte pravidelně logické zálohy (kvůli efektivitě to patrně budete dělat ze zálohovaných souborů). Přesvědčte se, že máte uchováno dostatek binárních logů, abyste mohli zotavit z poslední logické zálohy.

Pokud to jde, otestujte zálohované soubory, abyste se ujistili, že z nich bude možné zotavovat.

Pokud můžete, testujte je v průběhu zálohovacího procesu, než je zkopírujete na cíl.

Intenzivně myslete na bezpečnost. Co se stane, když někdo napadne server – dostal by se útočník i k serveru, kde jsou zálohy, nebo naopak?

Monitorujte zálohy a zálohovací proces nezávisle na samotných zálohovacích nástrojích. Je potřeba externě ověřit, že jsou v pořádku.

Zvolte nějaký chytrý způsob, jak se budou soubory kopírovat mezi stroji. Existují totiž efektivnější způsoby kopírování než scp nebo rsync. Více o tom si můžete přečíst v příloze A.

Proč zálohovat?

Pokud budujete nějaký vysoce výkonný systém, který se spoléhá na MySQL, je velmi důležité zálohovat. A to hned z několika následujících důvodů: Zotavení po katastrofě. Zotavení po katastrofě je to, co budete muset udělat, až vypadne hardware, až nějaká ošklivá chyba poruší data, nebo až se stanou server a jeho data nedostupnými nebo nepoužitelnými z nějakého jiného důvodu (potenciálních možných příčin je celá řada s mnoha variantami – necháme to na vaší představivosti). Šance, že dojde ke konkrétní pohromě, je velmi malá, ale pravděpodobnost, že dojde k nějaké pohromě, je už podstatně větší. Měli byste být připraveni na všechno možné, počínaje tím, že se někdo připojí omylem k nesprávnému serveru a vydá nějaký příkaz ALTER TABLE1, přes možnost, že vyhoří budova, až k útočníkovi se zlými úmysly nebo k chybě v samotném MySQL. Lidé mění své názory. Byli byste až překvapeni, jak často se setkáváme s tím, že je potřeba zotavit alespoň některá data do přesně takového stavu, v jakém byla jistého konkrétního dne v jistý konkrétní okamžik. U některých aplikací k tomu může docházet i častěji než

1

Baron si stále pamatuje, jak se mu tohle stalo, když pracoval jako vývojář pro jeden web elektronického obchodování. Napsal prostě příkaz do špatného okna. Na vině byl samozřejmě tým DBA – neměli dát vývojářům taková oprávnění k ostrým serverům. Rozhodně ne!

502

Kapitola 11 – Zálohy a zotavení

k pohromám (například tehdy, když si nějaký významný zákazník omylem smaže nějaká data a chce je nazpátek).

Audity. Někdy se potřebujete dozvědět, jak data nebo schéma vypadaly v nějakém konkrétním časovém okamžiku v minulosti. Můžete se například stát účastníkem nějakého soudního řízení, nebo jste odhalili ve své aplikaci nějakou chybu a potřebujete zjistit, co všechno tehdy kód dělal (to, že máte v kód v nějakém nástroji pro řízení verzí, nemusí stačit).

Testování. Jedním z nejjednodušších způsobů, jak testovat na realistických datech, je periodicky kopírovat na testovací server nejnovější ostrá data. Jestliže si děláte zálohy, jednoduše to dělejte pomocí záloh.

Kontrolujte své předpoklady. Předpokládáte například, že poskytovatel sdíleného hostingu zálohuje MySQL server poskytovaný k vašemu účtu? Přestože sdílený hosting ve skutečnosti není relevantní pro vysoký výkon, chceme zdůraznit, že takové předpoklady se mohou pěkně vymstít. (Mnozí webhosteři totiž MySQL servery vůbec nezálohují, a jiní dělají pouze kopie souborů, zatímco server běží, takže pravděpodobně vznikne porušená záloha, která bude nepoužitelná.)

Úvahy a kompromisy Zálohování MySQL je těžší, než jak vypadá. Na té nejzákladnější úrovni je sice záloha pouze kopie dat, nicméně pořízení této kopie mohou ztěžovat potřeby aplikace, architektura úložného enginu MySQL i systémová konfigurace.

O co si můžete dovolit přijít?

Při vytváření strategie pro zálohování byste měli vycházet z toho, o kolik dat si můžete dovolit přijít. Budete potřebovat takové vybavení, abyste byli schopni zotavit k danému časovému bodu, nebo bude stačit, když zotavíte ze záloh pořízených minulou noc, takže přijdete o veškerou práci, která se od té doby udělala? Pokud potřebujete zotavovat k danému časovému bodu, postačí, když budete zálohovat pravidelně a ujistíte se, že je zapnuto zaznamenávání do binárního logu. Pak budete schopni obnovit ze zálohy a zotavit k danému časovému bodu tak, že přehrajete binární log.

Všeobecně řečeno: čím více dat můžete po případné havárii postrádat, tím snadnější bude zálohování. Pokud ovšem máte velmi striktní nároky, je podstatně obtížnější zajistit, aby se dalo zotavit opravdu všechno. Existují dokonce i různé varianty zotavení k danému časovému bodu. "Mírný" požadavek na zotavení k danému časovému bodu znamená, že s největší pravděpodobností budete schopni opětovně vytvořit data tak, že budou "dostatečně blízko" k místu, kde se vyskytl problém. Striktní požadavek na zotavení znamená, že nikdy nebudete moci tolerovat ztrátu potvrzené transakce, i kdyby se stalo něco opravdu hrozného (například to, že shoří celý server). Uvědomte si ovšem, že něco takového vyžaduje použití speciálních technik, například udržování binárního logu na samostatném svazku SAN, nebo používání diskové replikace DRBD. O těchto přístupech si můžete více přečíst v kapitole 9.