Optimizing MySQL Using Query Cache

Table of Contents

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

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_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_io_capacity = 2000

thread_cache_size = 100
table_open_cache = 4000
max_heap_table_size = 64M
tmp_table_size = 64M
sort_buffer_size = 4M
read_buffer_size = 2M

Untuk variable query_cache_strip_comments hanya tersedia pada Percona & MariaDB sampai sekarang.

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)

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: