Query SQL di MariaDB lente nello stato Statistiche

Panoramica

Uno dei compiti di un database durante l'esecuzione di una query è mappare il modo migliore per eseguire la query stessa. Mentre nella maggior parte dei casi MariaDB (e MySQL) svolge un ottimo lavoro di ottimizzazione, per alcune query complesse con un numero elevato di join può trascorrere troppo tempo per impostazione predefinita nel tentativo di ottimizzare la query invece di eseguirla effettivamente.

Ad esempio, su una query basata su WordPress con 16 istruzioni JOIN, abbiamo riscontrato un tempo di esecuzione di quasi 4 minuti con solo 5000 righe. L'esecuzione di un EXPLAIN sulla query (che in realtà non esegue la query) risulta più o meno lo stesso, il che significa che il ritardo è l'ottimizzazione eccessiva non i dati o la query stessa.

Ciò è dovuto a optimizer_search_depth impostazione predefinita a 62. Riducendo questo numero a una profondità di 5, il tempo di EXPLAIN è sceso a 0,052 secondi e la query stessa è stata ridotta a meno di 6 secondi.

Per questa particolare query significava che il risultato era quasi 3900% più lento con le impostazioni predefinite del server!

Istruzioni

1. Utilizzando la tua shell MariaDB, esegui la query e quindi visualizza cos'è MariaDB durante la query:
   show full processlist;
2. Se vedi una query con lo stato "Statistiche" per molto tempo, questa ottimizzazione potrebbe essere applicabile. Per esempio:
   
3. Conferma qual è il tuo attuale optimizer_search_depth è impostato per:
   show variables like "optimizer_search_depth";
   Ad esempio, potresti quindi vedere:
   +------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| optimizer_search_depth | 62 |
+------------------------+-------+
4. Questo indica che il tuo server sta ancora eseguendo le impostazioni predefinite del sistema.
5. Prova l'impostazione del server per l'ottimizzazione automatica della profondità impostando optimizationr_search_depth su zero:
   SET SESSION optimizer_search_depth = 0;
6. Esegui nuovamente la query SQL lenta per confermare che il problema è stato risolto.
7. Se l'impostazione aggiornata ha funzionato, impostala in modo permanente modificando /etc/my.conf e impostandola esplicitamente in [msqld] sezione:
   optimizer_search_depth=0
8. Riavvia MariaDB per applicare:
   systemctl restart mariadb

Suggerimento

Se sai che la tua struttura dati è molto controllata, puoi sperimentare impostando esplicitamente optimizer_search_depth ad un valore specifico (es. 5) per ridurre ulteriormente . Tuttavia, un'impostazione esplicita non è necessariamente più rapida in ogni caso, quindi assicurati di eseguire un test accurato.

Se sei un cliente Conetix con un server privato virtuale che ha questo problema, possiamo aiutarti a confermare che questa è la causa e risolverlo per te senza costi aggiuntivi. Contatta il nostro team di supporto per ulteriore assistenza.

Ulteriori letture

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/