Ako vypnúť protokol pomalých dotazov. Profilovanie dotazov v MySQL

Profilovanie dotazov MySQL je užitočná technika na analýzu celkového výkonu databázových aplikácií. Pri vývoji stredne veľkých až veľkých aplikácií sú zvyčajne stovky dopytov rozmiestnených vo veľkej kódovej základni a databáza spracováva veľa dopytov za sekundu. Bez profilovania dopytov je veľmi ťažké určiť miesto a príčiny úzkych miest aplikácie. Tento tutoriál popisuje niektoré užitočné techniky profilovania dotazov pomocou vstavaných nástrojov MySQL.

Log pomalých dotazov MySQL

Protokol pomalých dotazov MySQL (alebo protokol pomalých dotazov) je protokol, do ktorého MySQL odosiela pomalé a potenciálne problematické dotazy.

Táto funkcia sa dodáva s MySQL, ale je predvolene vypnutá. MySQL určuje, ktoré dotazy by mali byť zahrnuté do tohto protokolu pomocou špeciálnych premenných, ktoré vám umožňujú profilovať dotaz na základe požiadaviek na výkon aplikácie. Do tohto denníka sa zvyčajne zapisujú dotazy, ktorých spracovanie trvá dlhšie, a dotazy, ktoré majú nesprávne indexy.

Profilovanie premenných

Základné serverové premenné na konfiguráciu protokolu pomalých dotazov MySQL sú:

slow_query_log global
slow_query_log_file global
long_query_time global/session
log_queries_not_using_indexes global
min_examined_row_limit global/session

slow_query_log – logická premenná na povolenie alebo zakázanie protokolu pomalých dotazov.

slow_query_log_file – absolútna cesta k súboru protokolu dotazov. Adresár súborov musí byť vo vlastníctve používateľa mysqld a musí mať príslušné povolenia na čítanie a zápis. Démon mysql bude s najväčšou pravdepodobnosťou spustený ako mysql, ale pre istotu spustite príkaz v termináli Linux:

ps -ef | grep bin/mysqld | rez -d" " -f1

Výstup zobrazí aktuálneho používateľa a používateľa mysqld.

cd /var/log
mkdir mysql
chmod 755 mysql
chown mysql:mysql mysql

• long_query_time – čas v sekundách na kontrolu dĺžky dotazu. Ak je hodnota 5, zaprotokolujú sa všetky požiadavky, ktorých spracovanie trvá dlhšie ako 5 sekúnd.
• log_queries_not_using_indexes – boolovská hodnota, ktorý určuje, či sa majú protokolovať dotazy, ktoré nepoužívajú indexy. Pri analýze sú takéto otázky dôležité.
• min_examined_row_limit – definuje minimálny počet riadkov, ktoré sa majú analyzovať. Pri hodnote 1 000 budú ignorované všetky dotazy, ktoré analyzujú menej ako 1 000 riadkov.

Premenné servera MySQL je možné nastaviť v konfiguračnom súbore MySQL alebo dynamicky pomocou užívateľské rozhranie alebo príkazový riadok MySQL. Ak sú premenné nastavené v konfiguračnom súbore, zostanú zachované aj po reštarte servera, ale na ich aktiváciu je potrebné server reštartovať. Konfiguračný súbor MySQL sa zvyčajne nachádza v /etc/my.cnf alebo /etc/mysql/my.cnf. Ak chcete nájsť konfiguračný súbor, zadajte (možno budete musieť rozšíriť vyhľadávanie do iných koreňových adresárov):

nájsť /etc -name my.cnf
nájsť /usr -name my.cnf

Keď nájdete konfiguračný súbor, pridajte požadované premenné do sekcie:

….
protokol pomalého dotazu = 1
slow-query-log-file = /var/log/mysql/localhost-slow.log
long_query_time = 1
log-queries-not-using-indexes

Aby sa zmeny prejavili, musíte reštartovať server. Ak je potrebné zmeny aktivovať okamžite, nastavte premenné dynamicky:

mysql> SET GLOBAL slow_query_log = "ON";
mysql> SET GLOBAL slow_query_log_file = "/var/log/mysql/localhost-slow.log";
mysql> SET GLOBAL log_queries_not_using_indexes = "ON";
mysql> SET SESSION long_query_time = 1;
mysql> SET SESSION min_examined_row_limit = 100;

Ak chcete skontrolovať hodnoty premenných:

mysql> ZOBRAZIŤ GLOBÁLNE PREMENNÉ AKO "slow_query_log";
mysql> ZOBRAZIŤ PREMENNÉ RELÁCIE AKO "long_query_time";

Jednou z nevýhod dynamicky sa meniacich premenných MySQL je, že sa tieto premenné stratia, keď sa server reštartuje. Preto by sa do súboru mali pridať všetky dôležité premenné, ktoré je potrebné uložiť.

Generovanie profilovacieho dotazu

Teraz ste oboznámení s nastaveniami denníka pomalých dotazov. Skúste vygenerovať údaje dopytu na profilovanie.

Poznámka: Tu uvedený príklad bol spustený na spustenej inštancii MySQL bez nakonfigurovaných pomalých protokolov dotazov. Tieto testovacie dotazy je možné spustiť cez GUI alebo príkazový riadok MySQL.

Pri monitorovaní protokolu pomalých dopytov je užitočné otvoriť dve terminálové okná: jedno pripojenie na odosielanie príkazov MySQL a druhé na prezeranie denníka dopytov.

Prihláste sa na server MySQL pomocou konzoly ako používateľ s oprávneniami SUPER ADMIN. Ak chcete začať, vytvorte testovaciu databázu a tabuľku, pridajte do nej fiktívne údaje a povoľte pomalé zaznamenávanie dotazov.

Poznámka: V ideálnom prípade je tento príklad najlepšie spustiť v prostredí bez akýchkoľvek iných aplikácií používajúcich MySQL, aby sa predišlo preplneniu denníka dotazov.

$> mysql -u -p
mysql> CREATE DATABASE profile_sampling;

mysql> USE profile_sampling;

mysql> CREATE TABLE užívateľov (id TINYINT PRIMARY KEY AUTO_INCREMENT, názov VARCHAR(255));

mysql> INSERT INTO users (name) VALUES ("Walter"),("Skyler"),("Jesse"),("Hank"),("Walter Jr."),("Marie"),("Saul "),("Gustavo"),("Hector"),("Mike");

mysql> SET GLOBAL slow_query_log = 1;

mysql> SET GLOBAL slow_query_log_file = "/var/log/mysql/localhost-slow.log";

mysql> SET GLOBAL log_queries_not_using_indexes = 1;

mysql> SET long_query_time = 10;

mysql> SET min_examined_row_limit = 0;

Teraz máte testovaciu databázu a tabuľku s niektorými údajmi. Je povolený protokol pomalých dopytov. Zámerne sme nastavili čas spracovania požiadavky na vysokú hodnotu a zakázali sme kontrolu počtu riadkov. Ak chcete zobraziť denník, zadajte:

cd /var/log/mysql
ls -l

Zatiaľ by v priečinku nemal byť protokol pomalých požiadaviek, pretože momentálne neboli žiadne žiadosti. Ak takýto protokol už existuje, znamená to, že databáza už zaznamenala pomalé dotazy, pretože ste povolili podporu pre protokol pomalých dotazov. To môže skresliť výsledky tohto príkladu. Vráťte sa na kartu MySQL a spustite:

mysql> USE profile_sampling;
mysql> SELECT * FROM users WHERE id = 1;

Vykonaný dotaz jednoducho načíta údaje a použije index prvého kľúča z tabuľky. Tento dotaz bol rýchly a používal index, takže nie je zaznamenaný v protokole pomalých dotazov. Vráťte sa do adresára a uistite sa, že nebol vytvorený žiadny protokol dotazov. Teraz sa vráťte do okna MySQL a spustite:

mysql>

Tento dotaz nepoužíva index. Teraz by sa niečo také malo objaviť v protokole /var/log/mysql/localhost-slow.log:

# Čas: 140322 13:54:58

použite profile_sampling;
SET timestamp=1395521698;

Ďalší príklad. Zvýšte minimálny počet riadkov na analýzu a odošlite požiadavku, ako je táto:

mysql> SET min_examined_row_limit = 100;
mysql> SELECT * FROM užívateľov WHERE name = "Walter";

Údaje nebudú pridané do denníka, pretože počas požiadavky bolo analyzovaných menej ako 100 riadkov.

Poznámka: Ak údaje neboli pridané do protokolu, musíte skontrolovať niekoľko faktorov. Najprv skontrolujte oprávnenia adresára, v ktorom je vytvorený protokol. Musí byť vo vlastníctve používateľa/skupiny mysqld a mať privilégiá chmod 755. Potom by ste mali skontrolovať, či na serveri nie sú iné nastavenia pomalého dotazu, ktoré prepisujú vaše nastavenia. Obnovením predvolených nastavení odstránite všetky premenné pomalých požiadaviek z konfiguračného súboru a reštartujete server. Môžete tiež dynamicky nastaviť globálne premenné na ich predvolené hodnoty. Ak robíte zmeny dynamicky, odhláste sa a znova prihláste do MySQL, aby ste aktualizovali nastavenia.

Analýza údajov profilovania dopytov

Zvážte nasledujúce údaje:

# Čas: 140322 13:54:58
#User@Host: root@localhost
# Query_time: 0,000303 Lock_time: 0,000090 Rows_sent: 1 Rows_examined: 10
použite profile_sampling;
SET timestamp=1395521698;
SELECT * FROM užívateľov WHERE meno = "Jesse";

Tento záznam zobrazuje:

• Čas vykonania dotazu
• Kto to poslal
• Ako dlho trvalo spracovanie žiadosti?
• Dĺžka
• Koľko riadkov sa vrátilo
• Koľko riadkov bolo analyzovaných

Je to užitočné, pretože každá požiadavka, ktorá porušuje požiadavky na výkon špecifikované v premenných, skončí v protokole. To umožňuje vývojárovi alebo správcovi rýchlo vystopovať požiadavky, ktoré nefungujú. Údaje profilovania dotazov vám navyše môžu pomôcť určiť, aké okolnosti spôsobujú slabý výkon vašej aplikácie.

Pomocou mysqldumpslow

Profilovanie môže byť zahrnuté do databázových aplikácií, aby sa zabezpečil mierny tok údajov.

S rastúcou veľkosťou protokolu je ťažké analyzovať všetky údaje a problematické dotazy sa v ňom môžu ľahko stratiť. MySQL ponúka nástroj s názvom mysqldumpslow, ktorý pomáha vyhnúť sa tomuto problému rozdelením protokolu pomalých dopytov. Binárny súbor je prepojený s MySQL (v systéme Linux), takže môžete jednoducho spustiť príkaz:

mysqldumpslow -t 5 -s na /var/log/mysql/localhost-slow.log

Príkaz môže akceptovať rôzne parametre na prispôsobenie jeho výstupu. Vyššie uvedený príklad zobrazí prvých 5 dopytov zoradených podľa priemerného času dopytu. Takéto reťazce sú čitateľnejšie a sú tiež zoskupené podľa požiadaviek.

Počet: 2 Čas = 68,34 s (136 s) Uzamknutie = 0,00 s (0 s) Riadky = 39892974,5 (79785949), root@localhost
SELECT PL.pl_title, P.page_title
ZO strany P
Odkazy na stránku INNER JOIN PL
ON PL.pl_namespace = P.page_namespace
WHERE P.page_namespace = N
…

Výstup zobrazuje nasledujúce údaje:

• Počet: koľkokrát bola žiadosť zaznamenaná.
• Čas: priemerný a celkový čas požiadavky (v zátvorkách).
• Zámok: čas uzamknutia stola.
• Riadky: Počet vrátených riadkov.

Príkaz vylučuje číselné a reťazcové hodnoty, takže identické dotazy s rôznymi podmienkami WHERE sa považujú za rovnaké. Nástroj mysqldumpslow eliminuje potrebu neustáleho prehliadania denníka pomalých dopytov, čo vám umožňuje namiesto toho spúšťať pravidelné automatické kontroly. Možnosti príkazu mysqldumpslow vám umožňujú spúšťať zložité výrazy.

Rozdelenie žiadosti

Ďalším nástrojom na profilovanie, ktorý treba mať na pamäti, je nástroj Complex Query Breakdown Tool. Umožňuje vám identifikovať problematické dotazy v protokole pomalých dotazov a spustiť ich v MySQL. Najprv musíte povoliť profilovanie a potom spustiť dotaz:

mysql> SET SESSION profilovanie = 1;
mysql> USE profile_sampling;
mysql> SELECT * FROM užívateľov WHERE meno = "Jesse";
mysql> ZOBRAZIŤ PROFILY;

Keď je profilovanie povolené, SHOW PROFILES zobrazí tabuľku, ktorá spája Query_ID s výrazom SQL. Nájdite Query_ID zodpovedajúce spustenému dotazu a spustite nasledujúci dotaz (nahraďte # vaším Query_ID):

mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=#;

Príkaz vráti tabuľku:

STATE je krok v procese vykonávania dotazu a DURATION je čas potrebný na dokončenie tohto kroku v sekundách. Toto nie je veľmi užitočný nástroj, ale môže pomôcť určiť, ktorá časť vykonávania dotazu spôsobuje najväčšiu latenciu.

Poznámka Poznámka: Tento nástroj by sa nemal používať v produkčnom prostredí.

Pomalý výkon denníka dotazov

Zostáva len zistiť, ako pomalý protokol dotazov ovplyvňuje výkon. Vo všeobecnosti je bezpečné spúšťať pomalé protokoly dotazov v produkčnom prostredí; Nemalo by to byť ovplyvnené ani CPU, ani I/O. Mali by ste však mať stratégiu na monitorovanie veľkosti protokolu, aby ste sa uistili, že protokol nebude príliš veľký súborový systém. Okrem toho, pri spustení pomalého protokolu dotazov v produkčnom prostredí by ste mali nastaviť long_query_time na hodnotu 1 alebo vyššiu.

Záver

Pomalý protokol dotazov vám môže pomôcť identifikovať problematické dotazy a vyhodnotiť celkový výkon dotazov. Zároveň môže vývojár získať podrobné informácie o tom, ako aplikácia funguje MySQL dotazy. Nástroj mysqldumpslow vám umožňuje spravovať pomalé protokoly dotazov a jednoducho ich začleniť do vášho vývojového procesu. Identifikáciou problematických dotazov môžete optimalizovať spracovanie dotazov na zlepšenie výkonu.

koncepcia

Denníky servera (súbory denníka, denník servera)- súbory uložené na serveri, ktoré obsahujú systémové informácie servery, ako aj zaznamenávanie všetkých možných údajov o návštevníkovi webového zdroja.

Protokoly používajú správcovia systému na analýzu návštevníkov, štúdium vzorcov správania určitých skupín používateľov, ako aj získavanie rôznych informácií o nich, ako napríklad: použitý prehliadač, IP adresa, údaje o geografickej polohe klienta a mnohé ďalšie. Okrem analýzy môžete týmto spôsobom zistiť neoprávnený prístup na stránku, presnejšie zistiť, kto presne bol vykonaný, a preniesť údaje o v tomto prípade príslušným orgánom.

Údaje v protokolovom súbore vo svojej čistej forme nebudú zrozumiteľné pre bežných používateľov, ktorí v tom všetkom uvidia iba množinu znakov v nezrozumiteľnom poradí. Ale pre správcov systému a webových vývojárov, toto je úplne čitateľný text a celkom užitočné informácie.

Zakaždým, keď klient pristúpi k webovému zdroju, spustí sa niekoľko udalostí naraz, ktorých postupnosť si povieme.

1. Vytvorenie žiadosti o stránku. Keď zadáte adresu do riadku prehliadača, alebo keď nasledujete aktívny webový odkaz, napríklad zo stránky s výsledkami vyhľadávacieho nástroja, prehliadač vyhľadá a pripojí sa k serveru, na ktorom sa stránka nachádza, a požiada oň. Zároveň odošle serveru nasledujúce informácie:
- IP adresa klientskeho počítača, ktorý požaduje stránku (ak používate proxy server, IP adresa vášho proxy);
- adresa internetovej stránky požadovaná používateľom (IP adresa);
- presný čas a dátum, kedy bola žiadosť podaná;
- údaje o skutočnej polohe klienta (ak sa používa proxy server, tak skutočná adresa proxy);
- informácie o prehliadači, ktorý klient používa (názov, verzia atď.);
- údaje o webovej stránke, z ktorej klient prešiel.

2. Prenos požadovaných údajov. Požadované údaje (webová stránka, súbory, cookies atď.) sa prenášajú zo servera do počítača používateľa.

3. Zapíšte do denníka servera. Po všetkom sa objaví záznam denníka, ktorý označuje všetky údaje, ktoré sa objavili v posledných dvoch udalostiach. Toto sú všetky informácie odoslané v prvom odseku, ako aj informácie o prenášaných údajoch.

Ako zobraziť denníky servera

Súbory denníka sú uložené v súbore access.log bez ohľadu na to, aký typ webového servera používate (Apache, Nginx, squid proxy atď.) Tento súbor je textový dokument, v každom riadku ktorého je napísané jedno odvolanie. Formáty nahrávania v access.log pomerne veľa, ale najobľúbenejší je kombinovaný, v ktorom má záznam nasledujúcu formu a postupnosť:

Kód: %h %l %u %t \"%r\" %>s %b \"%(Referer)i\" \"%(User-Agent)i\"
kde:

%h- adresa hostiteľa/IP, z ktorej bola žiadosť podaná;
%t- čas požiadavky na server a časové pásmo servera;
%r- verzia, obsah a typ žiadosti;
%s- stavový kód HTTP;
%b- počet bajtov odoslaných serverom;
%(Referer)- zdroj URL požiadavky;
%(User-Agent)- hlavička HTTP s informáciami o požiadavke (klientska aplikácia, jazyk atď.);
%(Hostiteľ)- názov virtuálneho hostiteľa, ku ktorému sa pristupuje.

Po dokončení tento riadok vyzerá asi takto:

127.0.0.1 - - "GET /index.php HTTP/1..0 (kompatibilný; MSIE 7.0; Windows NT 5.1)"

Ručné čítanie denníkov zaberie pomerne veľa času a úsilia. Preto skúsení webmasteri používajú špeciálny softvér nazývaný “Log File Analyzers”. Analyzujú všetky údaje, ktoré sú pre ľudí dosť ťažko čitateľné, a vytvárajú štruktúrované údaje. Ide o programy ako: Analog, WebAnalizer, Webalizer, Awstats, Webtrends atď. Druhy špeciálnych softvér pomerne veľa, medzi nimi sú platené aj bezplatné programy. Preto som si istý, že každý si nájde to svoje.

Kde nájsť denníky stránok

Ak máte pravidelný hosting, potom s najväčšou pravdepodobnosťou budete musieť napísať svojmu hostiteľovi a vyžiadať si od neho protokoly. Pomerne často si ich môžete vyžiadať aj prostredníctvom hostiteľského panelu. Rôzni hostitelia to robia inak. Ak chcete napríklad požiadať môjho hostiteľa, stačí kliknúť na domovskej stránke panely:

Protokol chýb error.log

Error.log- súbor, v ktorom sa uchovávajú aj protokoly. Nie však návštevníci, ale chyby, ktoré sa vyskytli na serveri. Ako je to v prípade access.log, každý riadok súboru je zodpovedný za jednu chybu, ktorá sa vyskytla. Záznam sa vykonáva s prihliadnutím na také informácie, ako sú: presný dátum a čas výskytu chyby, IP adresa, na ktorú bola chyba zaslaná, typ chyby, ako aj dôvod jej výskytu.

Záver

Protokoly sú dosť výkonný a informatívny nástroj na prácu. V súčasnosti ich však nahrádzajú nástroje ako Yandex.Metrica, Google Analytics atď., čím si zjednodušujeme život. Ak sa však plánujete rozvíjať, rásť a učiť sa niečo nové, určite vám odporúčam túto tému bližšie spoznať.

Protokoly udalostí sú prvým a najjednoduchším nástrojom na určenie stavu systému a identifikáciu chýb. V MySQL sú štyri hlavné protokoly:

• Protokol chýb— štandardný protokol o chybách, ktorý sa zhromažďuje počas chodu servera (vrátane spustenia a zastavenia);
• Binárny denník— protokol všetkých príkazov na úpravu databázy potrebných na replikáciu a zálohovanie;
• Všeobecný denník dotazov— protokol hlavného dotazu;
• Denník pomalých dopytov— denník pomalých požiadaviek.

Protokol chýb

Tento protokol obsahuje všetky chyby, ktoré sa vyskytli počas behu servera, vrátane kritické chyby, ako aj zastavenie, zapnutie servera a upozornenia. Tu by ste mali začať v prípade zlyhania systému. Štandardne sa všetky chyby vypisujú do konzoly (stderr), chyby môžete zaznamenať aj do syslogu (predvolené v Debiane) alebo do samostatného súboru denníka:

Log_error=/var/log/mysql/mysql_error.log

# Chyby budú zapísané na mysql_error.log

Odporúčame ponechať tento protokol povolený, aby ste mohli rýchlo identifikovať chyby. A aby sme pochopili, čo tá alebo tá chyba znamená, MySQL má nástroj na chyby:

Shell> perror 13 64 Kód chyby OS 13: Povolenie odmietnuté Kód chyby OS 64: Počítač nie je v sieti

# Vysvetľuje význam chybových kódov

Binárny (alias binárny) log

Všetky príkazy na zmenu databázy sú zaznamenané v binárnom logu, užitočné pre replikácie a zotavenie.

Zapína sa takto:

Log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 5 max_binlog_size = 500 miliónov

# Určuje umiestnenie, životnosť a maximálnu veľkosť súboru

Upozorňujeme, že ak sa nechystáte škálovať systém a implementovať odolnosť voči chybám, potom je lepšie nepovoliť binárny protokol. Je to náročné na zdroje a znižuje výkon systému.

Vyžiadať denník

Tento protokol obsahuje všetky prijaté SQL dotazy a informácie o pripojeniach klientov. Môže byť užitočný pri analýze indexu a optimalizácii, ako aj pri identifikácii chybných dopytov:

General_log_file = /var/log/mysql/mysql.log všeobecný_log = 1

# Obsahuje protokol a označuje umiestnenie súboru

Môžete ho tiež povoliť/zakázať, keď je spustený server MySQL:

SET GLOBAL general_log = "ON"; SET GLOBAL general_log = "OFF";

# Aby ste ho mohli používať, nemusíte reštartovať server

Pomalý denník žiadostí

Protokol je užitočný na identifikáciu pomalých, teda neefektívnych dopytov. Prečítajte si viac v tento článok.

Prezeranie denníkov

Ak chcete zobraziť denníky Debianu (Ubuntu), musíte spustiť:

# Chyba log tail -f /var/log/syslog # Query log tail -f /var/log/mysql/mysql.log # Zaznamenajte pomalé požiadavky chvost -f /var/log/mysql/mysql-slow.log

# Ak protokoly nie sú špecifikované samostatne, nachádzajú sa v /var/lib/mysql

Rotácia denníka

Nezabudnite komprimovať (archivovať, otáčať) protokolové súbory, aby zaberali menej miesta na serveri. Ak to chcete urobiť, použite pomôcku logrotateúpravou konfiguračného súboru /etc/logrotate.d/mysql-server:

# - Všetko som vložil do jedného bloku a pridal zdieľané skripty, takže mysql dostane # flush-logs"d iba raz. # V opačnom prípade by sa binárne záznamy automaticky zvýšili o n-krát každý deň. # - Protokol chýb je zastaraný, správy teraz idú do syslogu./var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log( denne striedať 7 chýbaok vytvoriť 640 mysql adm skomprimovať zdieľané skripty postrotate test -x /usr/bin/mysqladmin || exit 0 # Ak to zlyhá, skontrolujte debian.conf! MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf" if [ -z "`$MYADMIN ping 2>/dev/null`" ]; potom # Naozaj žiadny mysqld alebo skôr chýbajúci používateľ debian-sys-maint? # Ak sa to stane a nie je to chyba, nahláste chybu. #if ps cax | grep -q mysqld; potom if killall -q -s0 -umysql mysqld; potom ukončite 1 fi else $MYADMIN flush-logs fi endscript )

# Komprimuje a archivuje potrebné protokoly, čistí súbory

Denník DDL

MySQL tiež vedie denník jazyka údajov. Zhromažďuje údaje z operácií ako DROP_TABLE a ALTER_TABLE. Protokol sa používa na obnovu po zlyhaniach, ktoré sa vyskytli počas takýchto operácií. DDL Log je binárny súbor a nie je určený na čítanie používateľom, preto ho neupravujte ani neodstraňujte.

Najdôležitejšie

Vždy zapnite protokol chýb, použite protokol dotazov na kontrolu pripojenia aplikácie k databáze, kontrolu dotazov a prevádzky. Protokol pomalých dopytov je užitočný na optimalizáciu výkonu MySQL.

Profilovanie dopytov v Mysql slúži na vyhodnotenie výkonu vašej aplikácie. Pri vývoji stredných až veľkých aplikácií sa musíte vysporiadať so stovkami požiadaviek distribuovaných vo vašom kóde, ktoré sa vykonávajú každú sekundu. Bez techník profilovania dotazov môže byť veľmi ťažké zistiť, čo spôsobuje zhoršenie výkonu vašej aplikácie.

Čo je protokol pomalých dotazov v MySQL?

MySQL Slow Query Log – protokol, ktorý označuje pomalé a potenciálne problematické dotazy. MySQL štandardne podporuje túto funkciu, ale je zakázaná. Nastavením určitých serverových premenných môžeme určiť, o ktoré požiadavky máme záujem. Najčastejšie potrebujeme dotazy, ktoré vyžadujú určitý čas na dokončenie, alebo dotazy, ktoré nespracúvajú indexy správne.

Nastavenie premenných profilovania

Hlavné premenné pre nastavenie denníka dotazov:

Slow_query_log G slow_query_log_file G long_query_time G / S log_queries_not_using_indexes G min_examined_row_limit G / S

Komentujte: G - globálne premenné, S - systémové premenné

• slow_query_log - logická hodnota vrátane log
• slow_query_log_file - absolútna cesta k súboru protokolu. Vlastníkom adresára musí byť používateľ mysqld a adresár musí mať správne oprávnenia na čítanie a zápis. Démon mysql sa najčastejšie spúšťa ako používateľ mysql.

Ak chcete skontrolovať, spustite nasledujúce príkazy:

Ps-ef | grep bin/mysqld | rez -d" " -f1

Výstup príkazu vám poskytne meno aktuálneho používateľa a používateľa mysqld. Príklad nastavenia adresára /var/log/mysql:

Cd /var/log sudo mkdir mysql sudo chmod 755 mysql sudo chown mysql:mysql mysql

• long_query_time – čas v sekundách na kontrolu trvania dotazu. Napríklad pri hodnote 5 bude zaznamenaná každá požiadavka trvajúca viac ako 5 sekúnd.
• log_queries_not_using_indexes - boolovská hodnota, umožňuje ukladanie dotazov, ktoré nepoužívajú indexy. Takéto otázky sú pri analýze veľmi dôležité.
• min_examined_row_limit – určuje minimálnu hodnotu pre počet riadkov údajov, ktoré sa majú analyzovať. Hodnota 1000 bude ignorovať dopyty, ktoré vrátia menej ako 1000 riadkov hodnôt.

Tieto premenné je možné nastaviť v konfiguračnom súbore MySQL dynamicky cez GUI MySQL alebo príkazový riadok MySQL. Ak sú premenné špecifikované v konfiguračnom súbore, server ich nainštaluje pri ďalšom spustení. Tento súbor sa zvyčajne nachádza v /etc, /usr, /etc/my.cnf alebo /etc/mysql/my.cnf. Tu sú príkazy na vyhľadanie konfiguračného súboru (niekedy by ste mali rozšíriť vyhľadávanie do iných koreňových adresárov):

Nájsť /etc -name my.cnf nájsť /usr -name my.cnf

Keď nájdete súbor, pridajte požadované premenné v sekcii:

; ... slow-query-log = 1 slow-query-log-file = /var/log/mysql/localhost-slow.log long_query_time = 1 log-queries-not-using-indexes ; tu netreba zmysel

Zmeny sa prejavia až pri ďalšom spustení MySQL, ak potrebujete dynamicky meniť parametre, použite na nastavenie premenných iné metódy:

Mysql> SET GLOBAL slow_query_log = "ON"; mysql> SET GLOBAL slow_query_log_file = "/var/log/mysql/localhost-slow.log"; mysql> SET GLOBAL log_queries_not_using_indexes = "ON"; mysql> SET SESSION long_query_time = 1; mysql> SET SESSION min_examined_row_limit = 100;

Hodnoty premenných môžete skontrolovať takto:

Mysql> ZOBRAZIŤ GLOBÁLNE PREMENNÉ AKO "slow_query_log"; mysql> ZOBRAZIŤ PREMENNÉ RELÁCIE AKO "long_query_time";

Hlavnou nevýhodou dynamickej inštalácie je, že pri spustení systému sa hodnoty stratia. Odporúča sa špecifikovať dôležité parametre v konfigurácii MySQL.

Poznámka: Syntax dynamického nastavenia parametrov pomocou príkazu SET a použitia konfiguračného súboru je mierne odlišná, napríklad slow_query_log / slow-query-log . V oficiálnej dokumentácii DBMS nájdete úplný popis syntax. Pre konfiguračný súbor sa používa formát Option-File, System Variable Name - názvy premenných kedy dynamická inštalácia hodnoty.

Generovanie údajov pre profilovanie dopytov

Prešli sme si hlavné body nastavenia profilovania, teraz vytvoríme otázky, ktoré nás zaujímajú. Tento príklad bol použitý pri behu MySQL server bez akéhokoľvek predvoľby log. Vzorové dopyty je možné spustiť prostredníctvom GUI MySQL aj príkazových nástrojov DBMS. Pri monitorovaní denníka pomalých dopytov je bežné otvárať dve okná s pripojením: jedno na spúšťanie dopytov, druhé na prezeranie denníka.

$> mysql -u -p mysql> CREATE DATABASE profile_sampling; mysql> USE profile_sampling; mysql> CREATE TABLE užívateľov (id TINYINT PRIMARY KEY AUTO_INCREMENT, názov VARCHAR(255)); mysql> INSERT INTO users (name) VALUES ("Walter"),("Skyler"),("Jesse"),("Hank"),("Walter Jr."),("Marie"),("Saul "),("Gustavo"),("Hector"),("Mike"); mysql> SET GLOBAL slow_query_log = 1; mysql> SET GLOBAL slow_query_log_file = "/var/log/mysql/localhost-slow.log"; mysql> SET GLOBAL log_queries_not_using_indexes = 1; mysql> SET long_query_time = 10; mysql> SET min_examined_row_limit = 0;

Teraz máme databázu s testovacími údajmi. Spustili sme profilovanie, ale čas odozvy a počet riadkov sme zámerne nastavili na malé. Ak chcete zobraziť denník, použite príkaz:

Cd /var/log/mysql ls -l

Teoreticky by súbor denníka ešte nemal existovať, pretože sme nevykonali dopyty do databázy. Ak existuje, znamená to, že profilovanie bolo nakonfigurované skôr, čo môže skresliť výsledky príkladu. Spustite v konzole:

Mysql> USE profile_sampling; mysql> SELECT * FROM users WHERE id = 1;

Náš dotaz používa index primárneho kľúča z tabuľky. Požiadavka spracovaná veľmi rýchlo pomocou indexu, takže by sa nemala prejaviť v protokole. Upozorňujeme, že súbor denníka nemal byť vytvorený.

Teraz urobte nasledovné:

Mysql> SELECT * FROM užívateľov WHERE meno = "Jesse";

Tu sme indexy nepoužili. Teraz by sme mali vidieť túto požiadavku v denníku:

Sudo cat /var/log/mysql/localhost-slow.log # Čas: 140322 13:54:58 # User@Host: root @ localhost # Query_time: 0,000303 Lock_time: 0,000090 Rows_sent: 1 Rows_examined_sampling: 10 použitie SET timestamp=1395521698; SELECT * FROM užívateľov WHERE meno = "Jesse";

Pozrime sa na ďalší príklad. Zdvihnite lištu počtu riadkov v odpovedi a spustite nasledujúci dotaz:

Mysql> SET min_examined_row_limit = 100; mysql> SELECT * FROM užívateľov WHERE name = "Walter";

Požiadavka sa v protokole neprejaví, pretože v odpovedi na požiadavku sme neprekročili 100 riadkov.

Poznámka: Ak sa údaje nezobrazujú v protokole, musíte najskôr zvážiť nasledujúce faktory. Prvým sú práva k adresáru, kde je uložený log súbor. Skupina a používateľ musia zodpovedať používateľovi mysqld, práva musia byť chmod 755. Po druhé, profilovanie mohlo byť nakonfigurované skôr. Odstráňte všetky existujúce hodnoty premenných profilovania z konfiguračného súboru a reštartujte server alebo nastavte premenné dynamicky. Ak ste použili dynamickú metódu, ukončíte a prihlásite sa späť do konzoly MySQL.

Analýza údajov o profilovaní dopytov

Zvážte vyššie uvedený príklad:

# Čas: 140322 13:54:58 # User@Host: root @ localhost # Query_time: 0,000303 Lock_time: 0,000090 Rows_sent: 1 Rows_examined: 10 use profile_sampling; SET timestamp=1395521698; SELECT * FROM užívateľov WHERE meno = "Jesse";

Tu vidíme:

• Čas, kedy bola požiadavka spustená
• Používateľ, ktorý podal žiadosť
• Žiadosti o otváracie hodiny
• Trvanie uzamknutia
• Počet vybratých riadkov
• Počet analyzovaných riadkov

Tieto údaje sú veľmi užitočné, pretože s ich pomocou môžeme nájsť a odstrániť príčinu spomalenia systému. Tiež vývojár alebo správca MySQL bude vždy môcť vidieť problematické dotazy a rád by som poznamenal, že ich nájdenie tu je oveľa rýchlejšie ako pri štúdiu programový kód aplikácie. Vďaka dlhodobému profilovaniu môžete sledovať prevádzkové podmienky pri nízkej rýchlosti.

Pomocou mysqldumpslow

Protokol neustále zaznamenáva údaje, spravidla oveľa viac zapisuje, ako sa z neho číta. o veľká veľkosť log, čítanie sa stáva problematické. MySQL obsahuje nástroj s názvom mysqldumpslow, ktorý pomáha udržiavať integritu protokolov. Samotný program je kombinovaný s MySQL (zap Linuxové systémy). Ak ho chcete použiť, spustite potrebný príkaz a odovzdajte mu cestu k súboru denníka:

Sudo mysqldumpslow -t 5 -s na /var/log/mysql/localhost-slow.log

Existuje množstvo parametrov, ktoré vám pomôžu prispôsobiť výstup príkazu. V nižšie uvedenom príklade uvidíme posledných päť žiadostí zoradených podľa priemerného trvania. V dôsledku toho je čítanie denníka oveľa pohodlnejšie. (výstup upravený tak, aby zobrazoval skutočné hodnoty protokolu):

Počet: 2 Čas = 68,34 s (136 s) Uzamknutie = 0,00 s (0 s) Riadky = 39892974,5 (79785949), root@localhost VYBERTE PL.pl_title, P.page_title ZO stránky P VNÚTORNÉ PRIJAŤ odkazy na stránku PL ON PL.priestor_názvov_pl = P.page WHERE P.page_namespace = N ...

Čo vidíme:

• Počet – počet výskytov požiadavky v protokole
• Čas – priemerný a celkový čas požiadavky
• Zámok - čas uzamknutia stola
• Riadky – počet vybratých riadkov

Príkaz vylučuje numerické a reťazcové údaje dotazu, čo znamená, že dotazy s rovnakou klauzulou WHERE sa budú považovať za rovnaké. Vďaka tomuto nástroju sa nemusíte neustále pozerať do denníka. Kvôli veľké množstvo parametre príkazu, môžete výstup triediť ako chcete. Existuje aj vývoj tretích strán s podobnou funkcionalitou, napríklad pt-query-digest.

Rozdelenie žiadosti

Mali by ste venovať pozornosť ďalšiemu nástroju, ktorý vám umožní rozložiť zložité otázky. Najčastejšie musíte vziať dotaz z denníka a potom ho spustiť priamo v konzole MySQL. Najprv musíte povoliť profilovanie a potom spustiť dotaz:

Mysql> SET SESSION profilovanie = 1; mysql> USE profile_sampling; mysql> SELECT * FROM užívateľov WHERE meno = "Jesse"; mysql> ZOBRAZIŤ PROFILY;

Po povolení profilovania, SHOW PROFILES zobrazí tabuľku spájajúcu Query_ID a SQL výraz. Nájdite zodpovedajúce Query_ID a spustite nasledujúci dotaz (nahraďte # vaším Query_ID):

Mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=#;

Príklad výstupu:

TRVANIE STAVU SEQ 1 začiatok 0,000046 2 kontrola povolení 0,000005 3 otvorenie tabuliek 0,000036

ŠTÁT- krok v procese vykonávania požiadavky, DURATION- trvanie kroku v sekundách. Tento nástroj sa nepoužíva príliš často, ale niekedy môže byť mimoriadne užitočný pri určovaní príčiny pomalého výkonu dotazu.

Podrobný popis stĺpcov:

Podrobný popis krokov:

Poznámka: Tento nástroj by sa nemal používať v produkčnom režime servera, s výnimkou analýzy špecifických dopytov.

Pomalý výkon denníka dotazov

Poslednou otázkou je, ako profilovanie ovplyvňuje výkon servera ako celku. V produkčnom režime servera môžete takéto protokolovanie celkom bezpečne používať, nemalo by to mať vplyv na CPU ani I/O. Je však potrebné venovať pozornosť veľkosti súboru denníka, ktorý by nemal byť príliš veľký. Zo skúsenosti by som tiež rád poznamenal, že nastavenie hodnoty premennej long_query_time na 1 sekundu alebo vyššiu.

Dôležité: Profilovací nástroj - SET profilovanie = 1 - by ste nemali používať na zaznamenávanie všetkých požiadaviek, t.j. Neodporúča sa používať premennú general_log v režime produktu a pri veľkom zaťažení.

Záver

Profilovanie dopytov vám môže veľmi pomôcť pri izolácii problematického dopytu a hodnotení celkového výkonu. Vývojár môže tiež študovať, ako fungujú dopyty MySQL jeho aplikácie. Nástroj mysqldumpslow vám pomáha prezerať a spracovávať protokoly dotazov. Po identifikácii problémových dopytov už zostáva len vyladiť ich na maximálny výkon.