Ikhtisar
Salah satu tugas database saat menjalankan kueri adalah memetakan cara terbaik untuk mengeksekusi kueri itu sendiri. Sementara dalam kebanyakan kasus MariaDB (dan MySQL) melakukan pekerjaan pengoptimalan yang bagus, untuk beberapa kueri kompleks dengan jumlah gabungan yang tinggi, ia dapat menghabiskan terlalu banyak waktu secara default untuk mencoba mengoptimalkan kueri alih-alih benar-benar menjalankannya.
Misalnya, pada kueri berbasis WordPress dengan 16 pernyataan GABUNG, kami melihat waktu eksekusi hampir 4 menit dengan hanya 5000 baris. Menjalankan EXPLAIN pada kueri (yang sebenarnya tidak menjalankan kueri) menghasilkan kira-kira sama, yang berarti penundaan adalah optimasi berlebihan bukan data atau kueri itu sendiri.
Ini karena pengoptimal_penelusuran_kedalaman pengaturan default ke 62. Dengan mengurangi angka ini ke kedalaman 5, waktu EXPLAIN turun menjadi 0,052 detik dan kueri itu sendiri berkurang menjadi di bawah 6 detik.
Untuk kueri khusus ini, artinya hasilnya hampir 3900% lebih lambat dengan default server!
Petunjuk
- Menggunakan shell MariaDB Anda, jalankan kueri, lalu lihat apa itu MariaDB selama kueri:
show full processlist; - Jika Anda melihat kueri dengan status "Statistik" untuk waktu yang lama, pengoptimalan ini dapat diterapkan. Sebagai contoh:
- Konfirmasi apa pengoptimal_penelusuran_kedalaman Anda saat ini diatur ke:
show variables like "optimizer_search_depth";
Misalnya, Anda mungkin melihat:
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| optimizer_search_depth | 62 |
+------------------------+-------+ - Ini menunjukkan bahwa server Anda masih menjalankan default sistem.
- Uji pengaturan server untuk mengoptimalkan kedalaman secara otomatis dengan menyetel optimizer_search_depth ke nol:
SET SESSION optimizer_search_depth = 0; - Jalankan ulang kueri SQL lambat untuk mengonfirmasi bahwa masalah telah diselesaikan.
- Jika setelan yang diperbarui berhasil, setel secara permanen dengan mengedit /etc/my.conf dan secara eksplisit setelan di [msqld] bagian:
optimizer_search_depth=0 - Mulai ulang MariaDB untuk menerapkan:
systemctl restart mariadb
Kiat
Jika Anda mengetahui bahwa struktur data Anda sangat terkontrol, Anda dapat bereksperimen dengan menyetel pengoptimal_kedalaman_penelusuran secara eksplisit ke nilai tertentu (misalnya 5) untuk lebih mengurangi . Namun, pengaturan eksplisit tidak selalu lebih cepat dalam setiap kasus, jadi pastikan Anda mengujinya secara menyeluruh.
Jika Anda adalah pelanggan Conetix dengan Virtual Private Server yang mengalami masalah ini, kami dapat membantu mengonfirmasi ini penyebabnya dan memperbaikinya untuk Anda tanpa biaya tambahan. Silakan hubungi tim dukungan kami untuk bantuan lebih lanjut.
Bacaan Lebih Lanjut
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/