การใช้งาน Query Cache ใน MySQL

ใน MySQL มีความสามารถอย่างหนึ่งที่บางคนไม่ได้รู้จักวิธีการใช้งาน นั่นคือ Query Cache ซึ่งเป็นกระบวนการ Cache คำสั่งที่ถูกแปลแล้วโดยตัวแปลภาษา (Parser) ของ MySQL และผลจากคำสั่ง ช่วยให้ไม่ต้องทำงานเดิมๆ ซ้ำบ่อยๆ

ในการใช้งาน Query Cache นั้นมีสิ่งที่ต้องคำนึงถึงด้วยคือ

ขนาดของ Query Cache
ขนาดเฉลี่ยนของผลลัพธ์
ขนาดของ Query ที่ไม่ต้องการให้ Cache

ก่อนอื่นทำการตรวจสอบว่า MySQL ได้เปิดการทำงาน Query Cache ไว้หรือไม่และตั้งค่าต่างๆ ไว้อย่างไร

MYSQL

SHOW variables LIKE ‘query_cache%’;

จะแสดงผลออกมาได้เป็น

+——————————+———–+
| Variable_name | Value |
+——————————+———–+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 1024 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+——————————+———–+

ค่าต่างๆคือ

  • query_cache_limit คือขนาดใหญ่สุดของผลลัพธ์ที่จะถูก Cache เอาไว้
  • query_cache_min_res_unit คือขนาดเล็กที่สุดที่ถูกจองโดย Query Cache
  • query_cache_size คือขนาดของ Query Cache ที่จองไว้เพื่อ Cache ผลลัพท์ทั้งหมด ถ้าเป็น 0 คือปิดการทำงาน Query Cache
  • query_cache_type คือรูปแบบของการ Cache มี 3 ค่าคือ
    – 0 ปิดการทำงาน Query Cache
    – 1 เปิดการทำงานให้ Cache ทุกคำสั่งที่สามารถ Cache ได้ยกเว้นมีการใช้คำสั่ง SQL_NO_CACHE
    – 2 เปิดการทำงานและจะ Cache ก็ต่อเมื่อใช้คำสั่ง SQL_CACHE
  • query_cache_wlock_invalidate คือเมื่อมีการเปลี่ยนแปลงในตารางระหว่างมีการ SELECT จะต้องรอให้การเปลี่ยนแปลงนั้นเสร็จก่อนเพื่อรอรับผลการ SELECT ที่เปลี่ยนไปด้วย

จะเห็นว่า Query Cache จะมีเรื่องของการจองหน่วยความจำเข้ามาเกี่ยวข้่องด้วย ดังนั้นจึงมีปัญหาว่าควรจะจองไว้เท่าไหร่ ไม่ให้มากหรือน้อยจนเกินไป ปริมาณการใช้งานหน่วยความจำหาได้จาก

used memory = query_cache_size – qcache_free_memory

MYSQL

SHOW status LIKE ‘qcache_free_memory’;

จะได้ผลลัพธ์ เช่น

+——————–+———–+
| Variable_name | Value |
+——————–+———–+
| Qcache_free_memory | 10388616 |
+——————–+———–+

จากตัวอย่างจะได้ปริมาณการใช้งานหน่วยความจำ

33554432-10388616 = 23165816 (~22 MB)

จากนั้นเราสามารถอัตราการใช้งานหน่วยความจำที่ถูกจองโดย Cache ได้จาก

fill ratio = ( used memory / query_cache_size ) * 100

จากตัวอย่างจะได้ fill ratio

(23165816 / 33554432) * 100 = ~69%

ซึ่งถ้า Fill ratio มีค่า < 25% แล้วถือว่า query_cache_size ใหญ่เกินไป

เราต้องพิจารณาร่วมกับ qcache_lowmem_prunes ร่วมด้วย

MYSQL

SHOW status LIKE ‘qcache_lowmem_prunes’;

+———————-+——–+
| Variable_name | Value |
+———————-+——–+
| Qcache_lowmem_prunes | 507199 |
+———————-+——–+

ถ้า Qcache_lowmem_prunes > 50 และ Fill Ratio > 80% ควรมีการเพิ่มขนาดของ query_cache_size

และอัตราการกระจาย (Fragment) ของ Cache

( Qcache_free_blocks / Qcache_total_blocks ) * 100

MYSQL

SHOW status LIKE ‘qcache_free_blocks’;

TEXT

+——————–+——-+
| Variable_name | Value |
+——————–+——-+
| Qcache_free_blocks | 2692 |
+——————–+——-+

MYSQL

SHOW status LIKE ‘qcache_total_blocks’;

TEXT

+———————+——-+
| Variable_name | Value |
+———————+——-+
| Qcache_total_blocks | 37271 |
+———————+——-+

จากตัวอย่างเราจะได้

(2692 / 37271) * 100 = ~7%

ถ้าอัตราการกระจายตัว > 20% ควรมีการใช้คำสั่ง FLUSH QUERY CACHE บ่อยๆ

credit : http://articles.modoeye.com/Design_and_Development/SQL/MySQL/%E0%B8%81%E0%B8%B2%E0%B8%A3%E0%B9%83%E0%B8%8A%E0%B9%89%E0%B8%87%E0%B8%B2%E0%B8%99_Query_Cache_%E0%B9%83%E0%B8%99_MySQL.html