9 min read

Cara Mengoptimalkan MySQL dengan Query Cache di Ubuntu 18.04

Iman Sugirman

Introduction

Cache permintaan adalah fitur MySQL yang menonjol yang mempercepat pengambilan data dari basis data. Itu mencapai ini dengan menyimpan pernyataan MySQL SELECT bersama-sama dengan catatan yang diambil diatur dalam memori, maka jika klien meminta pertanyaan yang identik itu 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.

Dalam tutorial ini, Anda pertama-tama akan mengkonfigurasi MySQL tanpa cache query dan menjalankan query untuk melihat seberapa cepat mereka dieksekusi. Kemudian Anda akan mengatur cache permintaan dan menguji server MySQL Anda dengan itu diaktifkan untuk menunjukkan perbedaan dalam kinerja.

Prasyarat

Sebelum Anda mulai, Anda perlu yang berikut:

  • Satu server Ubuntu 18.04 dikonfigurasi dengan firewall dan pengguna non-root. Anda dapat merujuk ke Pengaturan Server Awal dengan Ubuntu 18.04 panduan untuk mengkonfigurasi server Anda.
  • Server MySQL yang diatur seperti terinci dalam tutorial Cara Menginstal MySQL di Ubuntu 18.04 ini. Pastikan Anda menetapkan kata sandi root untuk server MySQL.

Memeriksa Ketersediaan Cache Kueri

Sebelum mengatur cache permintaan, Anda akan memeriksa apakah versi MySQL Anda mendukung fitur ini. Pertama, ssh ke server Ubuntu 18.04 Anda:

ssh user_name@your_server_ip

Kemudian, jalankan perintah berikut untuk masuk ke server MySQL sebagai pengguna root:

sudo mysql -u root -p

Masukkan kata sandi root server MySQL Anda ketika diminta dan kemudian tekan ENTER untuk melanjutkan.

Gunakan perintah berikut untuk memeriksa apakah cache permintaan didukung:

show variables like 'have_query_cache';

Anda harus mendapatkan output yang mirip dengan yang berikut:

Output
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.01 sec)

Anda dapat melihat nilai have_query_cache diatur 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.

Memeriksa Variabel Cache Kueri Default

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:

show variables like 'query_cache_%' ;

Anda akan melihat variabel yang tercantum dalam output Anda:

Output
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | OFF      |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)

Nilai query_cache_limit menentukan ukuran maksimum hasil permintaan 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 variabel query_cache_min_res_unit. Nilai default adalah 4096 byte atau 4KB.

query_cache_size mengontrol 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_size membutuhkan 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 variabel query_cache_type. Menyetel nilai ini ke 0 atau OFF mencegah caching atau pengambilan kueri yang di-cache. Anda juga dapat mengaturnya ke 1 untuk mengaktifkan caching untuk semua kueri kecuali yang dimulai dengan pernyataan SELECT SQL_NO_CACHE. Nilai 2 memberitahu MySQL hanya untuk permintaan cache yang dimulai dengan perintah SELECT SQL_CACHE.

Variabel query_cache_wlock_invalidate mengontrol apakah MySQL harus mengambil hasil dari cache jika tabel yang digunakan pada kueri dikunci. Nilai standarnya adalah OFF.

Setelah meninjau variabel sistem yang mengontrol cache kueri MySQL, Anda sekarang akan menguji kinerja MySQL tanpa terlebih dahulu mengaktifkan fitur.

Menguji Server MySQL Anda Tanpa Cache Kueri

Tujuan dari tutorial ini adalah untuk mengoptimalkan server MySQL Anda dengan menggunakan fitur cache permintaan. Untuk melihat perbedaan kecepatan, Anda akan menjalankan kueri dan melihat kinerjanya sebelum dan sesudah mengimplementasikan fitur.

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_db dengan menjalankan perintah berikut:

Create database sample_db;

Outputnya :

Output
Query OK, 1 row affected (0.00 sec)

Kemudian beralih ke database:

Use sample_db;
Output
Database changed

Buat tabel dengan dua bidang (customer_id dan customer_name) dan beri nama itu customer:

Create table customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;

Outputnya :

Output
Query OK, 0 rows affected (0.01 sec)

Kemudian, jalankan perintah berikut untuk memasukkan beberapa data sampel:

Insert into customers(customer_id, customer_name) values ('1', 'JANE DOE');
Insert into customers(customer_id, customer_name) values ('2', 'JANIE DOE');
Insert into customers(customer_id, customer_name) values ('3', 'JOHN ROE');
Insert into customers(customer_id, customer_name) values ('4', 'MARY ROE');
Insert into customers(customer_id, customer_name) values ('5', 'RICHARD ROE');
Insert into customers(customer_id, customer_name) values ('6', 'JOHNNY DOE');
Insert into customers(customer_id, customer_name) values ('7', 'JOHN SMITH');
Insert into customers(customer_id, customer_name) values ('8', 'JOE BLOGGS');
Insert into customers(customer_id, customer_name) values ('9', 'JANE POE');
Insert into customers(customer_id, customer_name) values ('10', 'MARK MOE');
Output
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
...

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:

SET profiling = 1;
Output
Query OK, 0 rows affected, 1 warning (0.00 sec)

Kemudian, jalankan kueri berikut untuk mengambil semua pelanggan:

Select * from customers;

Anda akan menerima output berikut:

Output
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|           1 | JANE DOE      |
|           2 | JANIE DOE     |
|           3 | JOHN ROE      |
|           4 | MARY ROE      |
|           5 | RICHARD ROE   |
|           6 | JOHNNY DOE    |
|           7 | JOHN SMITH    |
|           8 | JOE BLOGGS    |
|           9 | JANE POE      |
|          10 | MARK MOE      |
+-------------+---------------+
10 rows in set (0.00 sec)

Kemudian, jalankan perintah SHOW PROFILES untuk mengambil informasi kinerja tentang kueri SELECT yang baru saja Anda jalankan:

SHOW PROFILES;

Anda akan mendapatkan hasil yang serupa dengan yang berikut:

Output
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.00044075 | Select * from customers |
+----------+------------+-------------------------+
1 row in set, 1 warning (0.00 sec)

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

Selanjutnya, keluar dari Antarmuka Baris Perintah MySQL.

quit;

Anda telah menjalankan kueri dengan MySQL sebelum mengaktifkan cache kueri dan mencatat Durasi atau waktu yang dihabiskan untuk mengambil catatan. Selanjutnya, Anda akan mengaktifkan cache permintaan dan melihat apakah ada peningkatan kinerja saat menjalankan permintaan yang sama.

Menyiapkan Cache Kueri

Pada langkah sebelumnya, Anda membuat data sampel dan menjalankan pernyataan SELECT sebelum Anda mengaktifkan cache kueri. Pada langkah ini, Anda akan mengaktifkan cache permintaan dengan mengedit file konfigurasi MySQL.

Gunakan nano untuk mengedit file:

sudo nano /etc/mysql/my.cnf

Tambahkan informasi berikut ke akhir file Anda:

...
[mysqld]
query_cache_type=1
query_cache_size = 10M
query_cache_limit=256K

Di sini Anda telah mengaktifkan cache permintaan dengan menetapkan query_cache_type ke 1. Anda juga mengatur ukuran batas kueri individual menjadi 256K dan menginstruksikan MySQL untuk mengalokasikan 10 megabita ke cache permintaan dengan mengatur nilai query_cache_size ke 10M.

Simpan dan tutup file dengan menekan CTRL + X, Y, lalu ENTER. Kemudian, restart server MySQL Anda untuk mengimplementasikan perubahan :

sudo systemctl restart mysql

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.

Menguji Server MySQL Anda dengan Query Cache 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:

sudo mysql -u root -p

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:

show variables like 'query_cache_%' ;

Anda akan melihat output berikut:

Output
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 262144   |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 10485760 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.01 sec)

Variabel query_cache_type diatur ke ON; ini mengonfirmasi bahwa Anda mengaktifkan cache permintaan dengan parameter yang ditentukan pada langkah sebelumnya.

Beralih ke database sample_db yang Anda buat sebelumnya.

Use sample_db;

Mulai profiler MySQL:

SET profiling = 1;

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:

Select * from customers;
Select * from customers;

Kemudian, daftarkan informasi profil:

SHOW PROFILES;

Anda akan menerima output yang mirip dengan yang berikut:

Output
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.00049250 | Select * from customers |
|        2 | 0.00026000 | Select * from customers |
+----------+------------+-------------------------+
2 rows in set, 1 warning (0.00 sec)

Seperti yang Anda lihat waktu yang diperlukan untuk menjalankan kueri telah 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:

SHOW PROFILE FOR QUERY 1;
Output
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000025 |
| Waiting for query cache lock   | 0.000004 |
| starting                       | 0.000003 |
| checking query cache for query | 0.000045 |
| checking permissions           | 0.000008 |
| Opening tables                 | 0.000014 |
| init                           | 0.000018 |
| System lock                    | 0.000008 |
| Waiting for query cache lock   | 0.000002 |
| System lock                    | 0.000018 |
| optimizing                     | 0.000003 |
| statistics                     | 0.000013 |
| preparing                      | 0.000010 |
| executing                      | 0.000003 |
| Sending data                   | 0.000048 |
| end                            | 0.000004 |
| query end                      | 0.000006 |
| closing tables                 | 0.000006 |
| freeing items                  | 0.000006 |
| Waiting for query cache lock   | 0.000003 |
| freeing items                  | 0.000213 |
| Waiting for query cache lock   | 0.000019 |
| freeing items                  | 0.000002 |
| storing result in query cache  | 0.000003 |
| cleaning up                    | 0.000012 |
+--------------------------------+----------+
25 rows in set, 1 warning (0.00 sec)

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

SHOW PROFILE FOR QUERY 2;
Output
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000024 |
| Waiting for query cache lock   | 0.000003 |
| starting                       | 0.000002 |
| checking query cache for query | 0.000006 |
| checking privileges on cached  | 0.000003 |
| checking permissions           | 0.000027 |
| sending cached result to clien | 0.000187 |
| cleaning up                    | 0.000008 |
+--------------------------------+----------+
8 rows in set, 1 warning (0.00 sec)

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 pada QUERY 2, status pengiriman hasil di-cache ke klien menunjukkan bahwa data dibaca dari cache dan tidak ada tabel yang dibuka karena status Tabel pembukaan tidak ada.

Dengan fitur cache kueri MySQL diaktifkan di server Anda, sekarang Anda akan mengalami peningkatan kecepatan baca.