Percona Toolkit a analýza MySQL slow logu na Windows
Analýza MySQL slow logu je jedním ze základních a zároveň nejmocnějších způsobů, jak zrychlit web a předejít přetěžování databázového serveru. Jde o to, podívat se na SQL dotazy, které trvaly příliš dlouho a zároveň se často spouštěly a ty pak optimalizovat (indexy nebo jejich přepsáním, sdružením, rozdělením atd).
Používám k tomu nástroj Percona Toolkit, který je ale dostupný pouze pro Linux. Jak se s tím poprat ve Windows 10? Připravil jsem podrobný návod.
Ve zkratce:
- Nainstalujeme si Ubuntu subsystém.
- Do něj MySQL server.
- Ten nakonfigurujeme - změníte mu port, abychom jej mohli používat vedle současného serveru i z WAMPu z Windows!
- Nainstalujeme Percona Toolkit.
- Do něj nahrajeme slow query log z produkčního serveru.
- Stáhneme si nástroj Anemometer.
- A budeme analyzovat!
1. Instalace Ubuntu bash
Nejprve si nainstalujeme Ubuntu z Microsoft Store.
Poté je třeba povolit Windows subsystém - Ovládací panely -> Programy a funkce -> Zapnout nebo vypnout funkce systému Windows. Poté restartujeme počítač.
2. Instalace MySQL
Po prvním spuštění Ubuntu musíme chvíli počkat, než se doinstaluje. Následně zvolíme login a heslo k nově vytvořenému uživateli.
Následně se přepneme na root uživatele:
sudo su
Aktualizujeme seznam balíčků:
apt-get update
Nainstalujeme MySQL server:
apt-get install mysql-server
3. Konfigurace MySQL
Nyní změníme našemu nově nainstalovanému MySQL serveru port, abychom ho mohli používat současně s MySQL serverem nainstalovaným přímo ve Windows (předpokládám, že máme nějaký WAMP). Ten bude mít nejspíš port 3306. Ubuntu MySQL serveru nastavíme třeba 5123. Nejprve si otevřeme konfigurační soubor (může být i jinde…je potřeba hledat):
nano /etc/mysql/mysql.conf.d/mysqld.cnf
Změníme port na 5123. Dále v něm nastavíme sql_mode="":
sql-mode=""
A soubor uložíme (Ctrl+X, Y). Nastartujeme MySQL server:
service mysql start
Nyní můžeme používat nově nainstalovaný MySQL server skrz Ubuntu Bash. Co kdybychom k němu chtěli přistupovat skrz GUI nástroj, třeba Adminer, který už máme na WAMPu? Je to jednoduché, při přihlášení specifikujeme port:
4. Instalace Percona Toolkitu
Na stránce Percona Toolkitu najdeme .deb balíček pro naši platformu, zkopírujeme si jeho URL a vrátíme se do Ubuntu Bashe. Tam si jej stáhneme:
wget https://www.percona.com/downloads/percona-toolkit/3.0.6/binary/debian/xenial/x86_64/percona-toolkit_3.0.6-1.xenial_amd64.deb
A instalujeme:
dpkg -i percona-toolkit_3.0.6-1.xenial_amd64.deb
Oops, chybí nám nějaké závislosti, takže ještě zavoláme:
apt-get install -f
A pak raději znovu instalujeme:
dpkg -i percona-toolkit_3.0.6-1.xenial_amd64.deb
5. Nahrání slow logu do Percony
Stáhneme si nebo si od správce serveru vyžádáme MySQL slow log. Uložíme si ho třeba na plochu sem:
C:\Users\skoul\Desktop\slow-query\slow-query.log
V Ubuntu se přepneme do této složky (všimněte si cesty /mnt/c/ - takto se dostaneme z Ubuntu k Windows):
cd /mnt/c/Users/skoul/Desktop/slow-query
A jedeme. Tímto příkazem nahrajeme surový slow log do databáze Percona Toolkitu (ta se vytvoří automaticky). Chvíli to potrvá.
pt-query-digest –user=root –password=VASEHESLO –review h=localhost,D=slow_query_log,t=global_query_review –history h=localhost,D=slow_query_log,t=global_query_review_history –no-report –limit=0% –filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" slow-query.log
6. Instalace nástroje Box Anemometer
A konečně, nainstalujeme Anemometer, ve kterém budeme logy analyzovat. Někam na náš WAMP si stáhneme a rozbalíme skript.
Nyní ve složce conf přejmenujte sample.config.inc.php na config.inc.php.
Dále ve stejné složce upravte soubor datasource_localhost.inc.php - stačí změnit port Ubuntu MySQL server (5123) a vyplnit heslo.
7. Analýza
Anemometer nám pomalé dotazy hezky seskupí podle jejich footprintu (dotaz bez parametrů) a dovolí nám si je seřadit nejčastěji asi podle “Query_time_sum DESC” - tedy podle celkového času, který tento dotaz trval v součtu všech jeho spuštění. Zde je třeba vidět, jak nutně potřebuje první dotaz optimalizovat.
Ohledně optimalizace MySQL doporučuji knížku High Performace MySQL, kterou shodou okolností prodávám za nejnižší cenu v Česku.