Percona Toolkit a analýza MySQL slow logu na Windows

09. 02. 2018 | 552 slov

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:

  1. Nainstalujeme si Ubuntu subsystém.
  2. Do něj MySQL server.
  3. Ten nakonfigurujeme - změníte mu port, abychom jej mohli používat vedle současného serveru i z WAMPu z Windows!
  4. Nainstalujeme Percona Toolkit.
  5. Do něj nahrajeme slow query log z produkčního serveru.
  6. Stáhneme si nástroj Anemometer.
  7. 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.

Windows 10 - Ubuntu Bash

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.

Box Anemometer Windows 10

Ohledně optimalizace MySQL doporučuji knížku High Performace MySQL, kterou shodou okolností prodávám za nejnižší cenu v Česku.