Jak zrychlit databázi – praktický průvodce pro rychlejší web

Každý web, který pracuje s databází, dřív nebo později narazí na limity výkonu. Stránky se začnou načítat pomaleji, dotazy trvají déle a při špičce se server začne zadýchávat. A dost často to není tím, že „server je slabý“ nebo že MySQL je špatně nastavená –⁠⁠⁠⁠⁠⁠ ale tím, jak aplikace databázi používá.

V tomhle článku projdeme praktické kroky, jak databázi reálně zrychlit: od rychlé kontroly zdrojů (CPU, RAM, disk, síť) přes chytré indexování a optimalizaci dotazů až po nasazení cache (Redis) nebo vyhledávání (Elasticsearch). Bez nekonečného tunění konfigurace –⁠⁠⁠⁠⁠⁠ zato s měřitelným výsledkem.

Co pomůže:

• Co databáze potřebuje (CPU, memory, disk(IO), network).
• Nevytvářejte fronty na MySQL dotazy.
• Nesoustřeďte se tolik na konfiguraci.
• Pozor na pagination queries (dotazování na počet stránek).
• Věnujte pozornost indexování.
• Phpmyadmin … ten má fajn poradce pro začátečníky.
• Slow query log.
• Elasticsearch.
• Redis.

Co databáze potřebuje

Pro rychlý chod databáze potřebujete mít v pořádku 4 hlavní zdroje:

• CPU – pro výpočty a zpracování dotazů.
• RAM – pro cache a in-memory operace.
• Disk / IO – pro čtení a zápis dat.
• Síťovou konektivitu – obzvlášť při oddělené DB.

Stačí, aby jeden z nich nestíhal – a výkonnostní problémy jsou na světě.

Nedělejte z MySQL skladiště dat v čekárně

Typický příklad: odesílání e-mailů, které se označí jako „čekající“, ale zpracovávají se postupně a zůstávají ve frontě. Výsledek? Zpomalený výkon, zahlcené tabulky databáze a nemožnost paralelního zpracování.

Nesoustřeďte se tolik na konfiguraci

Přílišné zásahy do my.cnf výkon spíš zhorší. Raději věnujte energii kvalitnímu indexování, dotazům a samotné aplikaci.

Online nástroje, které se zrychlením databáze pomohou

Percona Configuration Wizard 🧙‍♂️

Prvním z nich je Percona Configuration Wizard for MySQL. V 7 krocích si v něm nastavíte parametry svého serveru a získáte návrh optimálního nastavení MySQL/MariaDB.

Percona Query Advisor

Percona Query Advisor for MySQL je jedoduchý nástroj, kam zadáte svůj SQL dotaz → získáte zpětnou vazbu a návrh, jak ho zrychlit.

PhpMyAdmin – Poradce

Ve Stav > Poradce najdete nejčastější problémy databáze spolu s návrhy na jejich řešení. Zobrazují se zde i serverové proměnné s odkazy do dokumentace.

Každý problém má vlastní chybovou hlášku a hned vedle doporučení, jak ho vyřešit. Po kliknutí se otevře okno s podrobnostmi.

Pozor na pagination queries

Neboli na dotazování na počet stránek. Při zobrazování stránky s výsledky a s odkazem na další stránku se aplikace obvykle seskupují a třídí způsoby, které nemohou používat indexy, a používají LIMIT, aby kompenzovali škody, které serveru způsobují, protože provádí spousty práce.

Dotazy typu LIMIT 100000, 50 jsou extrémně náročné, protože se musí projít všechny předchozí řádky.

Místo toho:

• Zobrazujte pouze odkaz na další stránku.
• Omezte možnost listovat hluboko.
• Uvažujte o cursor-based stránkování.

Věnujte pozornost indexování

Indexování je pravděpodobně nepochopené téma v databázích, protože existuje příliš mnoho způsobů, jak se nechat zmást. Proto se podíváme, jak vlastně fungují a jak je server používá.

Pokud jsou indexy správně navržené, slouží databázovému serveru k 3 hlavním účelům:

1. Vyhledávání skupin řádků místo jednotlivých: Mnoho lidí si myslí, že index slouží jen k nalezení jednoho řádku. Jenže to vede k náhodným I/O operacím, které jsou velmi pomalé. Efektivnější je vyhledat rovnou skupinu sousedních řádků.
2. Vyhnete se třídění: Indexy umožňují číst data v požadovaném pořadí, takže se obejdete bez nákladného třídění. Čtení rovnou správně seřazených řádků je mnohem rychlejší.
3. Krycí indexy: Někdy dokáže index odpovědět na dotaz úplně sám, bez přístupu k tabulce. Této technice se říká krycí index (covering index). Když indexy a dotazy navrhnete chytře, můžete výkon dotazů posunout o několik levelů výš.

Příkaz EXPLAIN MySQL ukáže, jak bude jednotlivý dotaz zpracován databází. Můžete jej použít vždy na začátku dotazu. Vyzkoušejte jej v konzoli přímo v PhpMyAdmin nebo po připojení na SSH a k databázi.

EXPLAIN SELECT * FROM clanky WHERE skupina

Skvělé příklady a ukázky právě okolo použití indexu najdete na webu php.vrána.cz

Další pomůcka, o které lidi moc neví, je takový menší “monitoring”, který hlídá serverové proměnné a jejich hodnoty a pokud překročí normu, zobrazí se červeně.

Výhoda je, že hned vedle proměnných máte odkaz na dokumentaci, kde se dozvíte, co znamenají a v popisu krátký vysvětlení problému.

Slow query log

Jak už název trochu napovídá, jedná se o speciální log, který v sobě uchovává informace o pomalých dotazech. Odkdy se mají pomalé dotazy logovat si můžete nastavit v konfiguračním souboru k MySQL/MariaDB.

Na našich serverech s VPS centrem najdete config na /etc/mysql/mariadb.conf.d/60-local.cnf , kde musíte odkomentovat tyto řádky a restartovat MySQL.

Druhou možností je spuštění pouze dočasně a to za pomocí SSH konzole. Takové spuštění se po restartu serveru vrátí do původního (vypnutého) stavu. Tuto možnost uvedeme do procesu následovně:

přihlásíme se k MySQL přes SSH

mysql -u root -p

# spustíme logování

SET GLOBAL slow_query_log = 'ON';

# nastavíme časový limit, kdy je dotaz považován za pomalý

SET GLOBAL long_query_time = 10;

Poté můžete na příkazové řádce spustit příkaz:

mysqldumpslow –⁠⁠⁠⁠⁠⁠ Případně můžete přidat [options] [log_file]

Po spuštění příkazu vám vyjedou veškeré dotazy, které přesahují limit, které jste v configu nastavili.

Freelo - Nástroj na řízení úkolů a projektů

Přidej se, pozvi svůj tým a klienty, rozděl práci a sleduj, jak se úkoly dají do pohybu.

Elasticsearch

Je fulltextový vyhledávač, který disponuje s RESTful rozhraním a nabízí vysokou dostupnost, rychlost a škálovatelnost.

Pokud se vám líbí stránky, kde při vybrání filtru okamžitě uvidíte výsledky, tak je to pravděpodobně díky Elasticsearch. Jedná se o bezschémovou databázi a není nutné definovat strukturu, protože se vytvoří sama na základě vložených dat.

Komunikace probíhá přes HTTP požadavky i odpovědí se posílají ve formátu JSON, takže můžete Elasticsearch využít v kombinaci s jakýmkoliv programovacím jazykem.

Pokud si chodíte do databáze často, určitě stojí za to vyzkoušet o kolik % se vaše stránky zrychlí. Určitě nezpomalí. 🙂

VPS Centrum

Vyzkoušejte zdarma naši aplikaci pro správu serveru a domén. Budete si připadat jako zkušený administrátor.

Redis

Redis je extrémně rychlá in-memory databáze, která se často používá jako cache vrstva mezi aplikací a MySQL. Díky tomu dokáže výrazně zrychlit opakované dotazy a snížit zátěž hlavní databáze.

Typicky se v něm uchovávají často načítaná data – například seznamy produktů, nastavení, nebo výsledky složitějších SQL dotazů. Při každém požadavku se aplikace nejprve zeptá Redis, a pokud data existují, nemusí sahat do MySQL vůbec.

Redis dokáže fungovat i jako fronta, počítadlo nebo dočasné úložiště pro sessions a tokeny. Vše běží v paměti, takže reakce jsou téměř okamžité.

Mrkněte na oficiální dokumentaci: https://redis.io/docs

Pár slov závěrem

Zrychlení databáze nestojí na jednom parametru, ale na komplexní disciplíně: měřte → optimalizujte dotazy a indexy → teprve pak sahejte po nástrojích.

Pojďme si zrekapitulovat vše, co jsme se dnes dozvěděli, ať vám nic neuteče:

1. Při potížích se nejprve zaměřte na 4 hlavní zdroje – CPU, RAM, IO a síť.
2. Zastavte fronty dotazů na MySQL – dejte práci do queue/batchů.
3. Netravte zbytečně moc času nad úpravami konfiguračního souboru, bez prvotního měření.
4. Upravte pagination (keyset/cursor, neboli mělké listování).
5. Dodejte indexy (a ověřte je EXPLAINem).
6. Pusťte slow query log a opravte top „hříšníky“.
7. Kde to dává smysl, zapojte Redis (cache) a Elasticsearch (fulltext/filtry).
   Takhle posunete odezvu o řád níž – a bez zbytečných kouzel v my.cnf.

Chcete to dotáhnout až do úplného konce? Mrkněte na naše další průvodce zrychlením serveru + analýza a monitoring serveru → a máte svižný web od infrastruktury až po UX.