Zabití pomalých dotazů
Školení, která pořádám 
MySQL kupodivu nemá žádnou proměnnou, která by zajistila automatické sestřelení dotazů pomalejších než stanovená hranice. Já takové chování potřebuji v Admineru, kdy dotazy pro zjištění seznamu databází nebo celkového počtu výsledků (bez obojího se dá žít) mohou trvat nesnesitelně dlouho.
Než popíšu řešení tohoto problému, tak podotknu, že by se k tomu dalo přistoupit i z druhé strany – místo čekání na pomalý dotaz bychom stránku zobrazili bez něj a dočetli ho AJAXem. Tento přístup má několik nevýhod:
- Bez JavaScriptu se dotaz nikdy nenačte, i když je třeba bleskurychlý (to dopředu nevíme).
- Nemám příliš v lásce, když se mi stránka mění pod rukama – po načtení kostry stránky na ní začnou přiskakovat další části.
- Dotaz zbytečně zatěžuje server, i když se jeho výsledek možná už nikomu nezobrazí.
Tento přístup Adminer používá třeba pro získání stavu tabulek v jejich seznamu, ale tam to dost dobře jinak nejde – seznam potřebujeme získat rychle, je nepostradatelný (takže dotaz nemůžeme sestřelit) a pozdější načtení stavu JavaScriptem se dá přežít.
Řešení
Takže jak omezit délku trvání dotazu? Zaregistrujeme si funkci, kterou po uplynutí stanoveného času spustíme, a předáme jí ID aktuálního spojení. Tato funkce jednoduše provede KILL. Jediný zádrhel je v tom, že PHP nemá všude dostupný mechanismus pro spuštění procesu na pozadí, takže si musíme vypomoci AJAXem. Funkce může vypadat nějak takhle:
<script type="text/javascript">
var timeout = setTimeout(function () {
jQuery.post('kill.php?id=<?php echo mysql_thread_id(); ?>', {
csrf_token: '<?php echo $_SESSION["csrf_token"]; ?>'
});
}, 5 * 1000);
</script>
<?php
ob_flush();
flush();
?>
Po vypsání tohoto kódu do stránky je potřeba vysypat výstup, aby si ho prohlížeč ihned všiml.
Dalším krokem je samotné spuštění dotazu, např. funkcí mysql_query. Když se provede, můžeme časovač zrušit:
<script type='text/javascript'>
clearTimeout(timeout);
</script>
<?php
ob_flush();
flush();
?>
Skript kill.php je triviální:
<?php if ($_SESSION["csrf_token"] == $_POST["csrf_token"]) { mysql_query("KILL " . (+$_GET["id"])); } ?>
Pokud nás skript opravdu zabije, tak ztratíme připojení k databázi. Příkaz KILL se totiž nevztahuje na dotaz, ale na připojení, které ho spustilo. V takovém případě se můžeme připojit znovu nebo je možná lepší pomalý dotaz rovnou spouštět na vyhrazeném připojení. Od MySQL 5.0 lze použít příkaz KILL QUERY.
Na co si dát pozor
Pokud používáte session proměnné s výchozím souborovým handlerem, který zamyká soubory, tak je potřeba session před provedením dotazu uzavřít. Jinak totiž skript kill.php bude čekat na doběhnutí hlavního skriptu a bude tím pádem k ničemu. Adminer session zavírá prakticky hned po spuštění od verze 2.2.1.
Závěr
Problém, který bych chtěl řešit nastavením jedné proměnné, vyžaduje komunikaci na pozadí pomocí JavaScriptu, vynucení okamžitého poslání výstupu do prohlížeče a netriviální znalost chování session proměnných.
Diskuse
Miloslav Ponkrác:
SQL příkaz KILL QUERY zabije pouze dotaz aniž by ukončoval spojení.
Kromě toho příkaz KILL může rozbít vnitřní integritu diskových souborů, které tvoří databázové tabulky a tak zničit některé databázové tabulky, pokud se Vám podaří killnout některé specifické SQL dotazy. Pak je třeba je opravit příkazem REPAIR TABLE do té doby budou rozbité, nepoužitelné a nejde s nimi pracovat.
Pokud killnete příkaz UPDATE nebo DELETE, pak zůstanete někde uprostřed operace, nejste-li v transakci, část se provedla, část nikoli. Protože MySQL testuje kill-flag na threadu vždy mezi zápisem/smazáním řádku, tak vždy to bude na hraně celistvého počtu řádků.
Pro MySQL je těžké korektně obsloužit KILL ve všech případech. Musí totiž zabíjený thread nebo dotaz dotáhnout nejdříve do bezpečného bodu a bezpečného stavu, ze kterého je schopen zabití threadu ustát. Proto KILL nezabíjí hned, ale pouze nastavuje příznak, že tento thread má být zabit. Pokud se nachází MySQL v bezpečném bodu, pak se podívá MySQL zda má thread příznak smrti. A zabije ho.
V některých případech na to MySQL rezignoval a některé případy MySQL rozpsypou.
Miloslav Ponkrác: ppp:
Což by šlo kombinovat s předáním si hodnoty pro timeout např. pomocí přidání do where ("hodnota=xxxx or true) a následně to nějakým démonem parsovat výstup selectu z information_schema a porovnávat s time. Nakonec podle toho rozhodovat, zda to sestřelit nebo ne.Způsob popsaný v článku zdá se mi poněkud nešťastným...
Jakub Vrána 
:
Přidat si do dotazu poznámku (formou komentáře) mě napadlo jako první, ale vzhledem k tomu, že si žádného démona nemůžu dovolit, tak je práce rovnou s ID připojení mnohem elegantnější.
Jakub Vrána : Miloslav Ponkrác:
PHP s mysqlnd driverem mají možnost vykonávat SQL dotazy asynchronně.
Můžete si tedy pustit SQL dotaz aniž by se běh PHP skriptu zastavil. Provádění SQL dotazu pak běží na pozadí (to je ten poptávaný proces na pozadí).
PHP od verze 5.3 může být zkompilováno buď oproti libmysql nebo oproti mysqlnd.
---
Pan Vrána řeší vpravdě Sisyfovskou úlohu, protože bez threadů nebo asynchronních dotazů se to čistě vyřešit nejspíše nedá.
Miloslav Ponkrác: Jakub Vrána :
Díky za upozornění na KILL QUERY, to jsem neznal. V Admineru ho použít bohužel nemůžu, protože stále podporuji MySQL 4.1, kde tento příkaz není.
Co se možnosti poškození tabulek týče, tak dokumentace na http://dev.mysql.com/doc/mysql/en/kill.html se zmiňuje jen o příkazech REPAIR TABLE a OPTIMIZE TABLE.
Jakub Vrána : Miloslav Ponkrác:
A tohle znáte?
http://bugs.mysql.com/bug.php?id=52528
Kromě toho MySQL nezaručuje, že KILL operace bude trvat krátce. Ono killnutí threadu může klidně čas od času trvat třeba 30 sekund.
Kill operace není prováděna synchronně, ale asynchronně. Jinak řečeno, po zaslání KILL příkazu čekáte na to, než nastane příhodná konstelace a než MySQL dokončí určité vnitřní operace.
V případě transakcí pak KILL je prodloužena vcelku automaticky.
KILL není zárukou, že dostanete rychle zpátky řízení do svých rukou. Někdy může KILL a čekání na vykonání KILL trvat výjimečně i déle, než kdybyste nechal dotaz doběhnout normálně.
---
Ale znovu říkám, řešíte něco, k čemu tandem + php nenabízí za Vámi požadovaných verzí a předpokladů rozumné prostředky, takže to nemůžete udělat čistě. Dobré řešení nejspíše neexistuje.
Miloslav Ponkrác: Jakub Vrána :
Je nějaká situace, kdy se SHOW DATABASES nebo SELECT COUNT(*) FROM t WHERE x nemusí zastavit hned?
Jakub Vrána : Miloslav Ponkrác:
Pokud server pustíte s --skip-show-databases, pak příkaz SHOW DATABASE nemusí projít, přesněji potřebujete na to práva.
Kill v tomhle případě nic nezpůsobí, protože je to prostý seznam podadresářů na filesystému. Tedy do databáze se ani nesáhne.
SELECT COUNT(*) je zase plnohodnotným příkazem a jako takovým náchylným na integritu dat.
Miloslav Ponkrác: Miloslav Ponkrác:
Jinak možnost autozabíjení dlouhých dotazů existuje. Ale ne na MySQL 4.1.
Můžete si vytvořit nad MySQL EVENT (CREATE EVENT bla bla bla), kterou načasujete třeba za 10 sekund a které spustí příkaz KILL.
Následně pustíte Váš SQL příkaz. Pokud překročí 10 sekund, tak EVENT se spustí a vykonávání příkazu zabije.
Pokud skončí dříve, pomocí příkazu DROP EVENT killnutí zrušíte, nebo pomocí ALTER EVENT nastavíte znovu 10 sekund a vrháte se na další SQL příkaz.
Miloslav Ponkrác: Jakub Vrána :
Tento přístup naráží navíc (kromě požadavků na verzi) na to, že události jsou na drtivé většině hostingů zakázané.
Jakub Vrána : Jiří Prokeš:
Doufám, že jde jen o hravé intelektuální cvičení ve smyslu "jak by to možná šlo" a tahle zběsilost se v Admineru neobjeví.Protože stavět nějakou pravidelnou funkčnost na příkazu kill, to je jak vypínat televizi cihlou hozenou doprostřed obrazovky.
Jakub Vrána :
Tahle „zběsilost“ už v Admineru je. Používám to pro zastavení příkazu SHOW DATABASES (který se kešuje, takže se spouští jen jednou za sezení) a pomalých dotazů pro výpočet celkového počtu řádku dotazu. Žádné problémy nepozoruji.
V druhém případě se mi dost ulevilo – občas chci v obří tabulce vyhledat ukázku dat bez indexu (což je v pohodě, protože používám LIMIT), ale kvůli zjištění celkového počtu řádků se aplikace zasekla.
Jakub Vrána : Miloslav Ponkrác:
Souhlasím s předřečníkem, že používání KILL na běžné situace je prasečina.
Zjištění celkového počtu řádků lze odhadnout velmi rychlým SQL příkazem SHOW TABLE STATUS FROM jmeno_databaze LIKE ?
Ve výsledném sloupci Rows je odhad počtu řádků, který je u MyISAM přesný, u InnoDB odadnutý.
KILL je pro řešení kritických a výjimečných situací a jen tam by se měl používat.
Miloslav Ponkrác: Karlos:
Pankráci, ty jsi tady taky?! Fascinuje mě, s jakou vervou tady mudruješ. Mužeš mít i pravdu, nicméně ten zbytečně agresivně/konfrontační tón to shazuje...K věci - to jen ukazuje, že MySQL je pro seriózní sazazení téměř nepoužitelné. To co je na jiných RDBMS dlouholetou samozřejmostí, tady chybí. Nechápu, jak některé rádoby enterprise řešení mohou běžet nad MySQL (třeba CRM InTouch).
Miloslav Ponkrác:
Jaký konfrontační tón?
MySQL je pro seriózní nasazení velmi dobře použitelná.
Miloslav Ponkrác: Jakub Vrána :
Souhlasím.
Jakub Vrána : Jakub Vrána :
Respektuj prosím ostatní účastníky diskuse.
Problém, který řeším, není nijak specifický pro MySQL a týká se všech databází. Ukaž mi databázi, která dokáže rychle vyhodnotit dotaz SELECT COUNT(*) FROM table_with_billion_rows WHERE non_index_column = 1.
Jakub Vrána : Miloslav Ponkrác:
Obecně to není problém žádné databáze. Je na klientském programu, aby se zařídil. Tedy aby vykonával pomalé dotazy buď v samostatném threadu nebo použil asynchronní dotazy nebo si počkal.
Problém je v nedostatečných prostředcích PHP, které nenabízí dostatečné nástroje k serióznímu řešení.
Miloslav Ponkrác: Jakub Vrána :
SHOW TABLE STATUS Adminer samozřejmě dávno používá. To ale nejde použít u dotazů, které nevrací všechny řádky.
Jakub Vrána : Jiří Prokeš:
Teď jsem tedy trochu znejistěn. Výše někdo uvádí, že to může v náhodný okamžik rozstřelit celou databázi...To bych byl opravdu nerad.
Nešlo by to ověřit?
Jakub Vrána :
U některých dotazů to nelze vyloučit. Ale Adminer to používá pouze u dotazů SHOW DATABASES a SELECT COUNT(*) FROM t, jejichž sestřelení je neškodné.
Jakub Vrána : Diskuse je zrušena z důvodu spamu.
© 2005-2024 