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_hits on Qcache_hits + Com_select
เนื่องจากเมื่อประมวลผลคำขอ ตัวนับ Qcache_hits จะเพิ่มขึ้น (หากคำขอได้รับการประมวลผลจากแคช) หรือ Com_select (หากคำขอไม่ได้แคชไว้) วิธีนี้แนะนำใน "Mysql Performance Optimization" O'reilly
มีช่องทางออนไลน์อีกช่องทางหนึ่ง
qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)
หากค่านี้คือ > 0.8 แสดงว่า 80% ของคำขอของคุณถูกแคช ซึ่งเป็นตัวบ่งชี้ที่ดีมาก
หาก%
การเข้าถึงแคชต่ำ คุณต้องเพิ่ม query_cache_size
ค่า
สามารถดูมูลค่าปัจจุบันได้ดังนี้:
SHOW VARIABLES LIKE 'query_cache_size';
คำถามเกิดขึ้นอีกครั้ง: จะเลือกค่าที่เหมาะสมได้อย่างไรquery_cache_size?
สิ่งนี้จะQcache_lowmem_prunes
ช่วย ตัวแปรนี้เก็บจำนวนคำขอที่ถูกลบออกจากแคชเนื่องจากจำเป็นต้องแคชคำขอใหม่ มีความจำเป็นต้องดิ้นรนเพื่อให้ได้ขนาดแคชซึ่งQcache_lowmem_prunes
จะเพิ่มขึ้นเพียงเล็กน้อย ในการทำเช่นนี้ขอแนะนำให้เปรียบเทียบความแตกต่างของค่าQcache_lowmem_prunes
ต่อชั่วโมงและจำนวนคำขอที่ mysql ได้รับในชั่วโมงเดียวกัน
“ในทางปฏิบัติ สามารถใช้หนึ่งใน 2 สูตรในการคำนวณ query_cache_size:
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_cache
MySQL เก็บแคชในบล็อก สำหรับ 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 ที่เหมาะสมสามารถปรับปรุงประสิทธิภาพของฐานข้อมูลได้หลายเท่า สิ่งนี้จะไม่เพียงเพิ่มความเร็วของแอปพลิเคชัน แต่ยังรองรับการโหลดจำนวนมากอีกด้วย