Query Cache merupakan fitur MySQL yang digunakan untuk mempercepat pengambilan data dari database. Cara kerjanya dengan menyimpan query SELECT bersamaan dengan record lain
yang pernah diambil ke memory, kemudian jika client ingin mengambil data dengan query yang sama, maka prosesnya menjadi lebih cepat tanpa menjalankan perintahnya lagi
di database.
Checking the Availability of Query Cache #
Sebelum memulai pastikan Query Cache support pada MySQL yang Anda gunakan.
MariaDB [(none)]> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
1 row in set (0.001 sec)
Jika value NO Anda bisa menggunakan alternatif lain seperti ProxySQL atau menggantinya dengan MySQL 5.7 / MariaDB 10.6
Testing MySQL Server Without Query Cache #
Saya asumsikan Anda sudah memiliki database beserta datanya.
Akses database dengan mysql CLI.
mysql sample_db
Gunakan MySQL profiler untuk analisis query.
SET profiling = 1;
Lalu jalankan query dengan SELECT statement.
SELECT * from post;
Jalankan SHOW profiles untuk mengecek durasi dari query yang telah dijalankan.
MariaDB [sample_db]> SHOW profiles;
+----------+------------+--------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------+
| 1 | 0.00201653 | SELECT * from post |
+----------+------------+--------------------+
1 row in set (0.000 sec)
Untuk melihat prosesnya lebih detail gunakan perintah.
SHOW PROFILE FOR QUERY 1;
+------------------------+----------+
| Status | Duration |
+------------------------+----------+
| Starting | 0.000181 |
| checking permissions | 0.000016 |
| Opening tables | 0.000035 |
| After opening tables | 0.000006 |
| System lock | 0.000005 |
| table lock | 0.000010 |
| init | 0.000078 |
| Optimizing | 0.000017 |
| Statistics | 0.000023 |
| Preparing | 0.000019 |
| Executing | 0.000002 |
| Sending data | 0.001526 |
| End of update loop | 0.000014 |
| Query end | 0.000003 |
| Commit | 0.000009 |
| closing tables | 0.000005 |
| Unlocking tables | 0.000002 |
| closing tables | 0.000012 |
| Starting cleanup | 0.000002 |
| Freeing items | 0.000008 |
| Updating status | 0.000039 |
| Reset for next command | 0.000005 |
+------------------------+----------+
22 rows in set (0.001 sec)
Setting Up Query Cache #
Edit file /etc/my.cnf lalu tambahkan baris berikut.
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M
query_cache_strip_comments = 1
symbolic-links = 0
local-infile = 0
sql_mode = ''
innodb_file_per_table = 1
innodb_doublewrite = 0
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 1
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_open_files = 80000
innodb_io_capacity = 80000
innodb_max_dirty_pages_pct = 20
innodb_force_recovery = 0
key_buffer_size = 1G
max_allowed_packet = 256M
open_files_limit = 80000
wait_timeout = 300
interactive_timeout = 300
max_write_lock_count = 16
thread_cache_size = 160
table_open_cache = 4000
max_heap_table_size = 64M
tmp_table_size = 64M
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 16M
myisam_sort_buffer_size = 128M
concurrent_insert = 2
transaction_isolation = READ-COMMITTED
Restart service MySQL.
systemctl restart mysqld
Atau bisa juga mengaturnya dari MySQL CLI.
set global query_cache_type=1;
set global query_cache_size=268435456;
set global query_cache_limit=2097152;
set global query_cache_strip_comments=1;
Cek kembali variable untuk memastikan perubahan sudah diterapkan.
MariaDB [(none)]> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 268435456 |
| query_cache_strip_comments | ON |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
6 rows in set (0.001 sec)
Jika server didedikasikan khusus untuk service MySQL/MariaDB, Anda dapat mengedit konfigurasi service seperti berikut.
# /usr/lib/systemd/system/mariadb.service
[Service]
[...]
BlockIOWeight=1000
LimitMEMLOCK=2M
Restart service MySQL.
systemctl daemon-reload
systemctl restart mysqld
Verifikasi perubahan LimitMEMLOCK dengan perintah.
grep 'locked memory' /proc/$(pidof -s mariadbd)/limits
Pertimbangkan juga untuk membatasi resource user apabila diperlukan.
Terakhir edit file /etc/security/limits.conf untuk mengatur limit open file.
* hard nofile 500000
* soft nofile 500000
root hard nofile 500000
root soft nofile 500000
Testing MySQL Server With Query Cache #
Akses database dengan mysql CLI.
mysql sample_db
Gunakan MySQL profiler untuk analisis query.
SET profiling = 1;
Lalu jalankan query dengan SELECT statement minimal 2x untuk triger cache.
SELECT * from post;
SELECT * from post;
Jalankan SHOW profiles untuk mengecek durasi dari query yang telah dijalankan.
MariaDB [sample_db]> SHOW profiles;
+----------+------------+--------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------+
| 1 | 0.00299712 | SELECT * from post |
| 2 | 0.00024004 | SELECT * from post |
+----------+------------+--------------------+
2 rows in set (0.000 sec)
Untuk melihatnya lebih detail.
MariaDB [sample_db]> SHOW PROFILE FOR QUERY 2;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| Starting | 0.000028 |
| Waiting for query cache lock | 0.000002 |
| starting | 0.000026 |
| Checking query cache for query | 0.000078 |
| checking privileges on cached | 0.000007 |
| checking permissions | 0.000012 |
| Sending cached result to clien | 0.000079 |
| Updating status | 0.000005 |
| Reset for next command | 0.000003 |
+--------------------------------+----------+
9 rows in set (0.001 sec)
Referensi: