การแคชใน MySQL

7.1 การแคชบนฝั่ง DB

MySQL ใช้อัลกอริทึมที่ปรับขนาดได้สูงเมื่อทำงานกับตาราง ดังนั้น MySQL จึงสามารถทำงานได้แม้มีหน่วยความจำน้อย โดยปกติแล้ว เพื่อประสิทธิภาพที่ดีขึ้น คุณต้องมี RAM เพิ่มขึ้น

หากต้องการดูการตั้งค่าปัจจุบัน ให้เชื่อมต่อกับฐานข้อมูล

#mysq -u root -p

mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 134217728 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
5 rows in set (0.00 sec)

ขั้นแรก ตรวจสอบให้แน่ใจว่าเปิดใช้งานการแคชแล้ว ตัวแปร:

• query_cache_typeต้องเป็น ON (1) หรือ DEMAND (2)
• query_cache_limit- กำหนดขนาดสูงสุดของผลลัพธ์ที่จะเข้าสู่แคช
• query_cache_sizeแตกต่างจากศูนย์ เมื่อใช้ DEMAND เฉพาะคำขอที่มีคำสั่งเท่านั้นที่จะถูกแคชSQL_CACHE;
• query_cache_min_res_unitขนาดต่ำสุดของบล็อกหน่วยความจำที่จัดสรรสำหรับเก็บผลลัพธ์ของคิวรีแคช MySQL ไม่เก็บแคชไว้ในหน่วยความจำขนาดใหญ่ก้อนเดียว แต่จะจัดสรรบล็อกที่มีขนาดต่ำสุดquery_cache_min_res_unit(=4KB โดยค่าเริ่มต้น) ตามความต้องการ บล็อกดังกล่าวสุดท้ายจะถูกตัดให้เหลือขนาดข้อมูล และหน่วยความจำที่เหลือจะถูกทำให้ว่าง

ผลของการแคชคือเมื่อเซิร์ฟเวอร์ได้รับคำขอ มันจะดูว่าแฮชของคำขอนั้นอยู่ในแคชหรือไม่ หากแฮชตรงกัน - เซิร์ฟเวอร์จะส่งคืนผลลัพธ์ทันที - โดยไม่ต้องแยกวิเคราะห์คำขอ ปรับให้เหมาะสม ฯลฯ ค่าโสหุ้ย - มาพร้อมกับกลไกการแคช - เรียกดูแคช เขียนผลลัพธ์การค้นหาไปยังแคช ฯลฯ

และหากคุณมีคำขอขนาดเล็กจำนวนมากในแคช สิ่งนี้อาจนำไปสู่การแยกส่วนหน่วยความจำเนื่องจากมีบล็อกว่างจำนวนมาก และในทางกลับกัน ทำให้รายการแคชถูกลบเนื่องจากหน่วยความจำไม่เพียงพอ ในกรณีนี้ คุณควรลดค่าquery_cache_min_res_unitของ หากข้อความค้นหาส่วนใหญ่ของคุณสร้างผลลัพธ์จำนวนมาก การเพิ่มการตั้งค่านี้สามารถปรับปรุงประสิทธิภาพได้

ลองประเมินผลกระทบ เราดูว่าตัวนับการเข้าถึงแคชเปลี่ยนแปลงอย่างไร (Qcahe_hits) จำนวนคำขอที่ประกาศไม่ถูกต้องเนื่องจากหน่วยความจำไม่เพียงพอ (Qcache_lowmem_prunes) จำนวนคำขอทั้งหมดของประเภท SELECT (และเฉพาะที่แคชเท่านั้น):

#mysq -u root -p
mysql> show status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 715       |
| Qcache_free_memory      | 130369640 |
| Qcache_hits             | 24209     |
| Qcache_inserts          | 16215     |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 444       |
| Qcache_queries_in_cache | 1740      |
| Qcache_total_blocks     | 4225      |
+-------------------------+-----------+
8 rows in set (0.00 sec)

7.2 สถานะแคชปัจจุบัน

ในการตรวจสอบแคชแบบสอบถามใช้SHOW STATUS:

mysql> SHOW STATUS LIKE 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 10       |
| Qcache_free_memory      | 16755496 |
| Qcache_hits             | 49812    |
| Qcache_inserts          | 103999   |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 180      |
| Qcache_queries_in_cache | 6        |
| Qcache_total_blocks     | 28       |
+-------------------------+----------+
8 rows in set (0.00 sec)

• Qcache_free_blocksแสดงจำนวนบล็อกว่างในแคช (จะลดลงเมื่อคำขอแคชเพิ่มขึ้น)
• Qcache_total_blocks— จำนวนบล็อกที่ถูกครอบครอง
• Qcache_free_memory- แสดงหน่วยความจำ "ว่าง" ฟรีสำหรับการแคช
• Qcache_hits- จำนวนคำขอซึ่งผลลัพธ์นำมาจากแคชโดยไม่ต้องเข้าถึงฐานข้อมูลจริง
• Qcache_inserts- จำนวนคำขอที่เพิ่มไปยังแคช
• Qcache_lowmem_prunes- จำนวนคำขอที่ถูกลบออกจากแคชเนื่องจากหน่วยความจำไม่เพียงพอ
• Qcache_not_cached- จำนวนคำขอที่ไม่ได้เขียนไปยังแคชเนื่องจากการใช้ฟังก์ชันการจัดการเวลา ฯลฯ
• Qcache_queries_in_cache- จำนวนคำขอที่อยู่ในแคช

คุณสามารถดูจำนวนทั้งหมดของแบบสอบถาม SELECT:

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 16719 |
+---------------+-------+
1 row in set (0.00 sec)

7.3 ประสิทธิภาพการแคช

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

สามารถดูมูลค่าปัจจุบันได้ดังนี้:

SHOW VARIABLES LIKE 'query_cache_size';

คำถามเกิดขึ้นอีกครั้ง: จะเลือกค่าที่เหมาะสมได้อย่างไรquery_cache_size?

สิ่งนี้จะQcache_lowmem_prunesช่วย ตัวแปรนี้เก็บจำนวนคำขอที่ถูกลบออกจากแคชเนื่องจากจำเป็นต้องแคชคำขอใหม่ มีความจำเป็นต้องดิ้นรนเพื่อให้ได้ขนาดแคชซึ่งQcache_lowmem_prunesจะเพิ่มขึ้นเพียงเล็กน้อย ในการทำเช่นนี้ขอแนะนำให้เปรียบเทียบความแตกต่างของค่าQcache_lowmem_prunesต่อชั่วโมงและจำนวนคำขอที่ mysql ได้รับในชั่วโมงเดียวกัน

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
query_cache_size = (ปริมาณการเข้าชมใน 10 นาที) * 1.2 "

สิ่งนี้จะแคชคำขอเป็นเวลา 10 นาที + ให้หน่วยความจำเพิ่มเติม 20% สำหรับการกระจายตัวของแคชและการสำรองแคชเพิ่มเติม

คุณสามารถคำนวณจำนวนและปริมาณเฉลี่ยของการตอบกลับคำขอโดยใช้ ตัวแปร Bytes_sentตามลำดับ

ดังนั้นquery_cache_sizeเราจึงเพิ่มค่า หลังจากนั้นคุณควรใส่ใจกับค่าQcache_total_blocksและQcache_free_blocks. Qcache_queries_in_cacheMySQL เก็บแคชในบล็อก สำหรับ 1 คำขอ จำเป็นต้องมี 2 บล็อก: บล็อกหนึ่งสำหรับข้อความคำขอ บล็อกที่สองสำหรับผลลัพธ์

ถ้าเราพิจารณาตารางจากค่าQcache%

จำนวนบล็อกแคชทั้งหมดQcache_total_blocks – 28

คำขอ 6 ถูกแคชในขณะนี้ ซึ่งหมายความว่า 6 * 2 = 12 บล็อกไม่ว่าง

บล็อกQcache_free_blocks – 10ฟรี ยิ่งไม่ได้ใช้งานมากQcache_free_blocksระดับของ "การแยกส่วน" ของแคชก็จะยิ่งมากขึ้นเท่านั้น

หากข้อความค้นหาส่วนใหญ่มีข้อมูลผลลัพธ์จำนวนน้อย คุณควรลดขนาดบล็อกแคชขั้นต่ำquery_cache_min_res_unitซึ่งโดยค่าเริ่มต้นคือ 4 KB

หากคำขอส่วนใหญ่ส่งคืนข้อมูลจำนวนมาก คุณควรเพิ่มขนาดของบล็อกแคช

สิ่งสำคัญคือการบรรลุมูลค่าขั้นต่ำQcache_free_blocks.

หากตัวนับQcache_not_cachedมีขนาดใหญ่ คุณสามารถลองเพิ่มตัวแปรได้query_cache_limitซึ่งจะช่วยให้คุณสามารถเพิ่มขีดจำกัดและแคชผลลัพธ์ของข้อความค้นหาที่ "ไม่เหมาะสม"

ตัวแปรคอนฟิกูเรชันต่อไปนี้รับผิดชอบการใช้แคชคิวรี:

• query_cache_size— ขนาดของแคชแบบสอบถาม query_cache_size = 0ปิดการใช้งานแคช
• query_cache_limit- ขนาดของตัวอย่างสูงสุดที่จัดเก็บไว้ในแคช
• query_cache_wlock_invalidate- กำหนดว่าข้อมูลจะถูกนำมาจากแคชหรือไม่หากตารางที่พวกเขาเป็นเจ้าของถูกล็อคไว้สำหรับการอ่าน
=

หากต้องการเปิดใช้งานการแคชแบบสอบถาม mysql เพียงเพิ่มบรรทัดต่อไปนี้ในmy.cnf (ส่วน [mysqld]):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

และเริ่มบริการใหม่

7.4 ในกรณีที่ไม่ได้ใช้แคช

ไม่แคช:

• คำขอร้องจากSQL_NO_CACHE
• แบบสอบถามที่เตรียมไว้(Prepared statements);
• แบบสอบถามที่เป็นแบบสอบถามย่อยของแบบสอบถามภายนอก
• สืบค้นภายในขั้นตอนและฟังก์ชันที่เก็บไว้
• ข้อความค้นหาที่ใช้ฟังก์ชัน:

  BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), โดย ไม่มี ข้อ โต้แย้ง, , , , , , , , , DATABASE (), , ,ENCRYPT ()FOUND_ROWS ()GET_LOCK () LAST_INSERT_ID ()LOAD_FILE ()MASTER_POS_WAIT ()NOW ()RAND ()RELEASE_LOCK ()SLEEP ()SYSDATE ()UNIX_TIMESTAMP ()USER ()UUID ();

• สืบค้นโดยใช้ฟังก์ชันที่เก็บไว้ ตัวแปรผู้ใช้ หรือการอ้างอิงตารางใน ฐานข้อมูลระบบ mysqlหรือINFORMATION_SCHEMA
• • คำขอในแบบฟอร์มต่อไปนี้:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• แบบสอบถามกับตารางชั่วคราวหรือไม่ใช้ตารางเลย
• คำขอสร้างคำเตือน (คำเตือน);

คุณสามารถจัดระเบียบแคชด้วยคำสั่ง:

mysql>flush query cache;

ล้าง - คำสั่ง:

mysql>flush query cache;

ที่สำคัญที่สุด

ไม่เคยทำงานในการผลิตด้วยการตั้งค่าเริ่มต้น. ซึ่งจะส่งผลให้ทรัพยากรส่วนใหญ่ของเซิร์ฟเวอร์ไม่ถูกใช้งาน การปรับแต่ง MySQL ที่เหมาะสมสามารถปรับปรุงประสิทธิภาพของฐานข้อมูลได้หลายเท่า สิ่งนี้จะไม่เพียงเพิ่มความเร็วของแอปพลิเคชัน แต่ยังรองรับการโหลดจำนวนมากอีกด้วย