เพิ่มความเร็วการ SELECT ด้วย MySQL Query Cache

เคยแนะนำวิธีการ สร้าง index ในฐานข้อมูล MySQL เพื่อเพิ่มความเร็วในการค้นหาฟิลด์ที่ต้องการได้เร็วขึ้น แต่ต้องมีการแก้ไขโครงสร้างของ table ในฐานข้อมูลโดยใช้คำสั่ง ALTER TABLE เลือกฟิลด์ที่ต้องการทำเป็นคีย์ (ADD KEY)

บทความนี้ขอแนะนำอีกวีธีแต่จะมีผลทั้งเซิร์ฟเวอร์ฐานข้อมูล (Database Server) เลย ด้วยการเปิดคุณสมบัติ Query Cache ของ MySQL ให้มีการเก็บ SELECT statement และผลลัพธ์ที่ได้ไว้ใน cache ซึ่งถ้าเรารันคำสั่ง SELECT ครั้งต่อไปที่มี statement เหมือนกัน MySQL จะดึงผลลัพธ์การ query มาจาก cache โดยตรง ไม่ต้องไป query จาก table มาใหม่ ทำให้ผลการค้นหาเร็วขึ้นมาก

ถ้าข้อมูลใน table มีการเปลี่ยนแปลง cache ที่เก็บไว้จะถูกลบออกไป เพื่อให้ผลลัพธ์ในการค้นหาครั้งต่อไปถูกต้อง

ทดสอบก่อนการเปิดใช้ MySQL Query Cache

ในบทความนี้จะทดสอบกับ Fedora 10 โดย MySQL ที่ติดตั้งมาในแผ่นดีวีดี ไม่ได้เปิดคุณสมบัติ Query Cache ไว้ สามารถตรวจสอบได้จากคำสั่ง SHOW Variables ใน mysql

mysql> SHOW Variables WHERE Variable_name RLIKE 'query_cache';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.00 sec)

การที่จะใช้ Query Cache ได้นั้น ตัวแปร have_query_cache ต้องเท่ากับ ‘YES’  ตัวแปร ‘query_cache_type’ เท่ากับ ‘ON’ นอกจากนี้ต้องคอนฟิกค่า query_cache_size ด้วย เพื่อจองขนาด memory เพื่อใช้เก็บ query cache  ค่า 0 คือการปิดคุณสมบัติ query cache

ทดสอบรัน SELECT ก่อนการเปิด query cache

หมายเหตุ

• ตัวอย่างที่ทดสอบนี้ ไม่มีการสร้าง INDEX หรือคีย์ของฟิลด์ที่ชื่อ item_name

• table ที่ใช้ทดสอบมีข้อมูลประมาณ 300,000 rows

mysql> SELECT item_id, item_name FROM items WHERE item_name = 'GK809A0';
+---------+-----------+
| item_id | item_name |
+---------+-----------+
|  261351 | GK809A0   |
+---------+-----------+
1 row in set (0.21 sec)

ลองรัน SELECT หลายๆ ครั้ง ด้วยคำสั่งเหมือนกัน เวลาที่ใช้จะใกล้เคียงกันประมาณ 0.21 วินาที

คอนฟิกค่า query_cache_size

แก้ไขไฟล์ /etc/my.cnf โดยเพิ่มคอนฟิก query_cache_size ลงไป ให้อยู่ภายใต้คอนฟิกของ [mysqld] เช่นต้องการจอง memory ขนาด 32 Mbytes สำหรับทำเป็น cache ตัวอย่างคอนฟิกไฟล์จะเป็นดังนี้

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
query_cache_size=32M

หลังจากแก้ไขคอนฟิกไฟล์ /etc/my.cnf รันคำสั่ง service เพื่อรีสตาร์ต MySQL Server ใหม่

[root@db-server ~]# service mysql restart
Shutting down MySQL.                                       [  OK  ]
Starting MySQL.                                            [  OK  ]

ทดสอบรัน SELECT ครั้งแรกหลังเปิดการใช้ query cache

mysql> SELECT item_id, item_name FROM items WHERE item_name = 'GK809A0';
+---------+-----------+
| item_id | item_name |
+---------+-----------+
|  261351 | GK809A0   |
+---------+-----------+
1 row in set (0.23 sec)

ยังคงใช้เวลา 0.23 วินาที เพราะว่าครั้งแรกนี้ยังไม่มีข้อมูลใน cache เลย

สามารถใช้คำสั่ง SHOW STATUS เพื่อดูสถิติการใช้ cache ของ MySQL

mysql> SHOW STATUS WHERE Variable_name RLIKE 'Qcache';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 33535344 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0.00 sec)

ค่าตัวแปร Qcache_not_cached เพิ่มเป็น 1 คือการ SELECT ครั้งนี้ไม่ได้ดึงข้อมูลจาก cache และค่า Qcache_inserts เป็น 1 คือเริ่มมีการใส่ผลลัพธ์เข้าไปใน cache

ทดลองรัน SELECT อีกครั้งนึง โดยพิมพ์ statement ให้เหมือนเดิมทุกอย่าง ทั้งตัวพิมพ์ใหญ่ พิมพ์เล็ก การเว้นวรรค ต้องเหมือนกันหมด  ย้ำอีกที ต้องเหมือนกันหมด เพราะ MySQL ใช้ในการเปรียบเทียบกับ statement ที่เก็บไว้ใน cache

mysql> SELECT item_id, item_name FROM items WHERE item_name = 'GK809A0';
+---------+-----------+
| item_id | item_name |
+---------+-----------+
|  261351 | GK809A0   |
+---------+-----------+
1 row in set (0.00 sec)

เวลาที่ใช้กลายเป็น 0.00 วินาทีไปเลย เพราะว่าผลลัพธ์จากการ SELECT ครั้งนี้ MySQL ไปดึงมาจาก cache แทน

เราสามารถตรวจสอบว่าผลลัพธ์จากการ SELECT มาจาก cache โดยดูค่า Qcache_hits ที่เพิ่มขึ้น จากคำสั่ง SHOW STATUS

mysql> SHOW STATUS WHERE Variable_name RLIKE 'Qcache';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 33535344 |
| Qcache_hits             | 1        |
| Qcache_inserts          | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0.00 sec)

ข้อมูลอ้างอิง

• สร้าง index ในฐานข้อมูล MySQL
• ติดตั้ง MySQL 5.1 บน Fedora 10
• MySQL 5.1 Reference Manual :: Query Cache Configuration