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

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

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

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

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

MYSQL

1. SHOW variables LIKE 'query_cache%';

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

TEXT

1. +------------------------------+-----------+
2. | Variable_name | Value |
3. +------------------------------+-----------+
4. | query_cache_limit | 1048576 |
5. | query_cache_min_res_unit | 1024 |
6. | query_cache_size | 33554432 |
7. | query_cache_type | ON |
8. | query_cache_wlock_invalidate | OFF |
9. +------------------------------+-----------+

ค่าต่างๆคือ

• 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

1. SHOW status LIKE 'qcache_free_memory';

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

TEXT

1. +--------------------+-----------+
2. | Variable_name | Value |
3. +--------------------+-----------+
4. | Qcache_free_memory | 10388616 |
5. +--------------------+-----------+

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

33554432-10388616 = 23165816 (~22 MB)

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

fill ratio = ( used memory / query_cache_size ) * 100

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

(23165816 / 33554432) * 100 = ~69%

ซึ่งถ้า Fill ratio มีค่า <>

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

MYSQL

1. SHOW status LIKE 'qcache_lowmem_prunes';

TEXT

1. +----------------------+--------+
2. | Variable_name | Value |
3. +----------------------+--------+
4. | Qcache_lowmem_prunes | 507199 |
5. +----------------------+--------+

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

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

( Qcache_free_blocks / Qcache_total_blocks ) * 100

MYSQL

1. SHOW status LIKE 'qcache_free_blocks';

TEXT

1. +--------------------+-------+
2. | Variable_name | Value |
3. +--------------------+-------+
4. | Qcache_free_blocks | 2692 |
5. +--------------------+-------+

MYSQL

1. SHOW status LIKE 'qcache_total_blocks';

TEXT

1. +---------------------+-------+
2. | Variable_name | Value |
3. +---------------------+-------+
4. | Qcache_total_blocks | 37271 |
5. +---------------------+-------+

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

(2692 / 37271) * 100 = ~7%

ถ้าอัตราการกระจายตัว > 20% ควรมีการใช้คำสั่ง FLUSH QUERY 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)