Cara Mengoptimalkan MySQL dengan Query Cache di Ubuntu 18.04

Cache permintaan adalah fitur MySQL yang menonjol yang mempercepat pengambilan data dari basis data. Ini mencapai ini dengan menyimpan SELECTpernyataan MySQL bersama dengan catatan yang diambil yang diatur dalam memori, maka jika klien meminta pertanyaan yang sama, ia dapat melayani data lebih cepat tanpa mengeksekusi perintah lagi dari database.

Dibandingkan dengan data yang dibaca dari disk, data yang di-cache dari RAM (Memori Akses Acak) memiliki waktu akses yang lebih singkat, yang mengurangi latensi dan meningkatkan operasi input / output (I / O). Sebagai contoh, untuk situs WordPress atau portal e-commerce dengan panggilan baca tinggi dan perubahan data yang jarang, cache kueri dapat secara drastis meningkatkan kinerja server database dan membuatnya lebih terukur.

Sebelum mengatur cache permintaan, Anda akan memeriksa apakah versi MySQL Anda mendukung fitur ini. Pertama, sshke server Ubuntu 18.04 Anda:
Kemudian, jalankan perintah berikut untuk masuk ke server MySQL sebagai pengguna root:

Masukkan kata sandi root server MySQL Anda ketika diminta dan kemudian tekan ENTER untuk melanjutkan.
Gunakan perintah berikut untuk memeriksa apakah cache permintaan didukung:

Anda harus mendapatkan output yang mirip dengan yang berikut:

Anda dapat melihat nilai have_query_cachediatur ke YES dan ini berarti cache permintaan didukung. Jika Anda menerima output yang menunjukkan bahwa versi Anda tidak mendukung cache kueri, silakan lihat catatan di bagian Pendahuluan untuk informasi lebih lanjut.
Sekarang Anda telah memeriksa dan mengonfirmasi bahwa versi MySQL Anda mendukung cache permintaan, Anda akan beralih ke memeriksa variabel yang mengontrol fitur ini di server database Anda.

Di MySQL, sejumlah variabel mengontrol cache kueri. Pada langkah ini, Anda akan memeriksa nilai-nilai default yang dikirimkan dengan MySQL dan memahami apa yang masing-masing variabel kontrol.
Anda dapat memeriksa variabel-variabel ini menggunakan perintah berikut:

Anda akan melihat variabel yang tercantum dalam output Anda:

The query_cache_limitnilai menentukan ukuran maksimum hasil query individu yang dapat di-cache. Nilai standarnya adalah 1.048.576 byte dan ini setara dengan 1MB.
MySQL tidak menangani data cache dalam satu potongan besar; alih-alih ditangani dalam blok. Jumlah minimum memori yang dialokasikan untuk setiap blok ditentukan oleh query_cache_min_res_unitvariabel. Nilai standarnya adalah 4.096 byte atau 4KB.

query_cache_sizemengontrol jumlah total memori yang dialokasikan untuk cache kueri. Jika nilainya diatur ke nol, artinya cache permintaan dinonaktifkan. Dalam kebanyakan kasus, nilai default dapat diatur ke 16.777.216 (sekitar 16MB). Juga, perlu diingat bahwa query_cache_sizemembutuhkan setidaknya 40KB untuk mengalokasikan strukturnya. Nilai yang dialokasikan di sini disejajarkan dengan blok 1024 byte terdekat. Ini berarti nilai yang dilaporkan mungkin sedikit berbeda dari yang Anda tetapkan.

MySQL menentukan permintaan untuk melakukan cache dengan memeriksa query_cache_typevariabel. Menetapkan nilai ini ke 0atau OFFmencegah caching atau pengambilan kueri yang di-cache. Anda juga dapat mengaturnya 1agar mengaktifkan caching untuk semua permintaan kecuali yang dimulai dengan SELECT SQL_NO_CACHEpernyataan. Nilai 2memberitahu MySQL hanya untuk permintaan cache yang dimulai dengan SELECT SQL_CACHE perintah.

Variabel query_cache_wlock_invalidatemengontrol apakah MySQL harus mengambil hasil dari cache jika tabel yang digunakan pada kueri dikunci. Nilai standarnya adalah OFF.
Pada langkah ini Anda akan membuat database contoh dan menyisipkan beberapa data untuk melihat kinerja MySQL tanpa cache permintaan.
Saat masih masuk ke server MySQL Anda, buat database dan beri nama sample_dbdengan menjalankan perintah berikut:

Kemudian beralih ke database:

Buat tabel dengan dua bidang ( customer_iddan customer_name) dan beri nama customers:

Kemudian, jalankan perintah berikut untuk memasukkan beberapa sampel data:

Langkah selanjutnya adalah memulai profiler MySQL , yang merupakan layanan analisis untuk memantau kinerja permintaan MySQL. Untuk mengaktifkan profil untuk sesi saat ini, jalankan perintah berikut, atur ke 1, yang aktif:

Kemudian, jalankan kueri berikut untuk mengambil semua pelanggan:

Anda akan menerima output berikut:

Lalu, jalankan SHOW PROFILES perintah untuk mengambil informasi kinerja tentang SELECTkueri yang baru saja Anda jalankan:

Anda akan mendapatkan hasil yang serupa dengan yang berikut:

Output menunjukkan total waktu yang dihabiskan oleh MySQL ketika mengambil catatan dari database. Anda akan membandingkan data ini pada langkah selanjutnya ketika cache kueri diaktifkan, jadi catat Duration. Anda dapat mengabaikan peringatan di dalam output karena ini hanya menunjukkan bahwa SHOW PROFILESperintah akan dihapus dalam rilis MySQL di masa depan dan diganti dengan Skema Kinerja .
Selanjutnya, keluar dari Antarmuka Baris Perintah MySQL.

Anda telah menjalankan kueri dengan MySQL sebelum mengaktifkan cache kueri dan mencatat Durationwaktu yang dihabiskan untuk mengambil catatan. Selanjutnya, Anda akan mengaktifkan cache permintaan dan melihat apakah ada peningkatan kinerja saat menjalankan permintaan yang sama.
Pada langkah sebelumnya, Anda membuat sampel data dan menjalankan SELECTpernyataan sebelum Anda mengaktifkan cache kueri. Pada langkah ini, Anda akan mengaktifkan cache permintaan dengan mengedit file konfigurasi MySQL.
Gunakan nanountuk mengedit file:

Tambahkan informasi berikut ke akhir file Anda:

Di sini Anda telah mengaktifkan cache permintaan dengan mengatur query_cache_typeto 1. Anda juga telah mengatur ukuran batas kueri individual ke 256Kdan menginstruksikan MySQL untuk mengalokasikan 10megabita ke cache permintaan dengan menetapkan nilai query_cache_sizeto 10M.
Simpan dan tutup file tersebut dengan menekan CTRL+ X, Y, kemudian ENTER. Kemudian, restart server MySQL Anda untuk mengimplementasikan perubahan:

Anda sekarang telah mengaktifkan cache permintaan.
Setelah mengonfigurasi cache kueri dan me-restart MySQL untuk menerapkan perubahan, Anda akan melanjutkan dan menguji kinerja MySQL dengan fitur yang diaktifkan.
Pada langkah ini, Anda akan menjalankan kueri yang sama dengan yang Anda jalankan di Langkah 3 sekali lagi untuk memeriksa bagaimana cache kueri telah mengoptimalkan kinerja server MySQL Anda.
Pertama, sambungkan ke server MySQL Anda sebagai pengguna root :

Masukkan kata sandi root Anda untuk server database dan tekan ENTER untuk melanjutkan.
Sekarang konfirmasikan konfigurasi Anda yang diatur pada langkah sebelumnya untuk memastikan Anda mengaktifkan cache permintaan:

Anda akan melihat output berikut:

Variabel query_cache_typediatur ke ON; ini mengonfirmasi bahwa Anda mengaktifkan cache permintaan dengan parameter yang ditentukan pada langkah sebelumnya.
Beralih ke sample_dbdatabase yang Anda buat sebelumnya.

Mulai profiler MySQL:

Kemudian, jalankan kueri untuk mengambil semua pelanggan setidaknya dua kali untuk menghasilkan informasi profil yang cukup.
Ingat, setelah Anda menjalankan kueri pertama, MySQL akan membuat cache hasil dan karenanya, Anda harus menjalankan kueri dua kali untuk memicu cache:

Kemudian, daftarkan informasi profil:

Anda akan menerima output yang mirip dengan yang berikut:

Seperti yang Anda lihat, waktu yang diperlukan untuk menjalankan kueri berkurang secara drastis dari 0.00044075(tanpa cache kueri pada Langkah 3) menjadi 0.00026000(kueri kedua) pada langkah ini.
Anda dapat melihat pengoptimalan dari mengaktifkan fitur cache kueri dengan memetakan kueri pertama secara terperinci:

Jalankan perintah berikut untuk menampilkan informasi profil untuk permintaan kedua, yang di-cache:

Output dari profiler menunjukkan bahwa MySQL mengambil lebih sedikit waktu pada kueri kedua karena ia dapat mengambil data dari cache kueri alih-alih membacanya dari disk. Anda bisa membandingkan dua set output untuk masing-masing kueri. Jika Anda melihat informasi profil aktif QUERY 2, status sending cached result to clientmenunjukkan bahwa data dibaca dari cache dan tidak ada tabel yang dibuka karena Opening tablesstatusnya hilang.
Dengan fitur cache kueri MySQL diaktifkan di server Anda, sekarang Anda akan mengalami peningkatan kecepatan baca.