Pomalé dotazy MariaDB SQL ve stavu Statistiky

Přehled

Jednou z úloh databáze při provádění dotazu je zmapovat nejlepší způsob provedení samotného dotazu. Zatímco ve většině případů MariaDB (a MySQL) odvádí skvělou práci při optimalizaci, u některých složitých dotazů s vysokým počtem spojení může ve výchozím nastavení strávit příliš mnoho času pokusy o optimalizaci dotazu namísto jeho skutečného provedení.

Například u dotazu založeného na WordPressu s 16 příkazy JOIN jsme viděli dobu provedení téměř 4 minuty pouze s 5000 řádky. Spuštění příkazu EXPLAIN na dotaz (který ve skutečnosti dotaz neprovede) má zhruba stejný výsledek, což znamená, že zpoždění je přílišná optimalizace nikoli samotná data nebo dotaz.

Důvodem je optimizer_search_depth nastavení, které je výchozí na 62. Snížením tohoto čísla na hloubku 5 se doba EXPLAIN zkrátila na 0,052 sekundy a samotný dotaz se zkrátil pod 6 sekund.

Pro tento konkrétní dotaz to znamenalo, že výsledek byl téměř o 3900 % pomalejší s výchozími nastaveními serveru!

Pokyny

1. Pomocí svého prostředí MariaDB spusťte dotaz a poté během dotazu zobrazte, co je MariaDB:
   show full processlist;
2. Pokud po dlouhou dobu vidíte dotaz se stavem „Statistika“, lze tuto optimalizaci použít. Například:
   
3. Potvrďte aktuální hodnotu optimizer_search_depth je nastaven na:
   show variables like "optimizer_search_depth";
   Můžete proto vidět například:
   +------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| optimizer_search_depth | 62 |
+------------------------+-------+
4. To znamená, že váš server stále používá výchozí nastavení systému.
5. Otestujte nastavení serveru tak, aby automaticky optimalizoval hloubku nastavením optimizer_search_depth na nulu:
   SET SESSION optimizer_search_depth = 0;
6. Znovu spusťte pomalý SQL dotaz a potvrďte, že je problém vyřešen.
7. Pokud aktualizované nastavení fungovalo, nastavte jej trvale úpravou souboru /etc/my.conf a explicitním nastavením v [msqld] sekce:
   optimizer_search_depth=0
8. Restartujte MariaDB a použijte:
   systemctl restart mariadb

Tip

Pokud víte, že vaše datová struktura je velmi kontrolovaná, můžete experimentovat s explicitním nastavením optimizer_search_depth na konkrétní hodnotu (např. 5) pro další snížení . Explicitní nastavení však nemusí být nutně rychlejší v každém případě, takže se ujistěte, že provedete důkladné testování.

Pokud jste zákazníkem společnosti Conetix s virtuálním privátním serverem, který má tento problém, můžeme vám pomoci potvrdit, že je to příčina, a opravit jej za vás bez dalších nákladů. Pro další pomoc prosím kontaktujte náš tým podpory.

Další čtení

https://www.percona.com/blog/2012/04/20/joining-many-tables-in-mysql-optimizer_search_depth/

https://mariadb.com/resources/blog/setting-optimizer-search-depth-in-mysql/