ปัจจุบัน นักพัฒนายังคงพัฒนาและปรับใช้แอปพลิเคชันอย่างต่อเนื่องโดยใช้สถาปัตยกรรม LAMP (Linux®, Apache, MySQL และ PHP/Perl) อย่างไรก็ตาม ผู้ดูแลระบบเซิร์ฟเวอร์มักจะควบคุมแอปพลิเคชันได้เพียงเล็กน้อย เนื่องจากบุคคลอื่นเป็นผู้เขียนแอปพลิเคชัน ซีรีส์สามส่วนนี้จะกล่าวถึงปัญหาการกำหนดค่าเซิร์ฟเวอร์จำนวนหนึ่งที่อาจส่งผลต่อประสิทธิภาพของแอปพลิเคชัน บทความนี้ซึ่งเป็นส่วนที่สามซึ่งเป็นส่วนสุดท้ายของชุดนี้จะเน้นไปที่การปรับแต่งเลเยอร์ฐานข้อมูลให้มีประสิทธิภาพสูงสุด
เกี่ยวกับการปรับแต่ง MySQL
มีสามวิธีในการเร่งความเร็วการทำงานของเซิร์ฟเวอร์ MySQL ลำดับของประสิทธิภาพจากต่ำไปสูงคือ:
เปลี่ยนฮาร์ดแวร์ที่มีปัญหา ปรับแต่งการตั้งค่าของกระบวนการ MySQL ปรับแบบสอบถามให้เหมาะสม
การเปลี่ยนฮาร์ดแวร์ที่มีปัญหามักเป็นข้อพิจารณาอันดับแรกของเรา เนื่องจากฐานข้อมูลอาจใช้ทรัพยากรจำนวนมาก แต่โซลูชันนี้ไปไกลเท่านั้น ในความเป็นจริง คุณมักจะสามารถเพิ่มความเร็วของหน่วยประมวลผลกลาง (CPU) หรือดิสก์ของคุณได้เป็นสองเท่า และเพิ่มหน่วยความจำได้ 4 ถึง 8 เท่า
วิธีที่สองคือการปรับแต่งเซิร์ฟเวอร์ MySQL (หรือที่เรียกว่า mysqld) การปรับแต่งกระบวนการนี้หมายถึงการจัดสรรหน่วยความจำอย่างเหมาะสม และแจ้งให้ mysqld ทราบว่าจะต้องโหลดประเภทใด การเร่งความเร็วการทำงานของดิสก์ไม่สำคัญเท่ากับการลดจำนวนการเข้าถึงดิสก์ที่จำเป็น ในทำนองเดียวกัน การตรวจสอบให้แน่ใจว่ากระบวนการ MySQL ทำงานอย่างถูกต้องหมายความว่ากระบวนการจะใช้เวลาในการให้บริการสืบค้นมากกว่างานเบื้องหลัง เช่น การทำงานกับตารางดิสก์ชั่วคราว หรือการเปิดและปิดไฟล์ การปรับแต่ง mysqld เป็นจุดเน้นของบทความนี้
แนวทางที่ดีที่สุดคือตรวจสอบให้แน่ใจว่าแบบสอบถามได้รับการปรับให้เหมาะสมแล้ว ซึ่งหมายความว่ามีการใช้ดัชนีที่เหมาะสมกับตารางและการสืบค้นจะถูกเขียนในลักษณะที่ใช้ประโยชน์จากความสามารถของ MySQL อย่างเต็มที่ แม้ว่าบทความนี้จะไม่ครอบคลุมถึงการปรับแต่งแบบสอบถาม (หัวข้อที่กล่าวถึงในหนังสือหลายเล่ม) แต่ก็กำหนดค่า mysqld ให้รายงานการค้นหาที่อาจต้องมีการปรับแต่ง
แม้ว่าจะมีการมอบหมายคำสั่งซื้อให้กับงานเหล่านี้แล้ว แต่คุณยังคงต้องใส่ใจกับการตั้งค่าฮาร์ดแวร์และ mysqld เพื่อปรับแต่งการสืบค้นอย่างเหมาะสม ไม่เป็นไรหากเครื่องทำงานช้า ฉันเห็นว่าเครื่องที่เร็วมากล้มเหลวเนื่องจากมีภาระงานหนักเมื่อเรียกใช้คิวรีที่ออกแบบมาอย่างดี เนื่องจาก mysqld มีงานยุ่งมากและไม่สามารถให้บริการคิวรีได้
การบันทึกการสืบค้นที่ช้า
ในเซิร์ฟเวอร์ SQL ตารางข้อมูลจะถูกจัดเก็บไว้ในดิสก์ ดัชนีช่วยให้เซิร์ฟเวอร์มีวิธีในการค้นหาแถวข้อมูลที่ต้องการในตารางโดยไม่ต้องค้นหาทั้งตาราง เมื่อต้องค้นหาทั้งตาราง จะเรียกว่าการสแกนตาราง โดยทั่วไป คุณอาจต้องการรับชุดย่อยของข้อมูลในตารางเท่านั้น ดังนั้นการสแกนตารางแบบเต็มจะสิ้นเปลืองดิสก์ I/O จำนวนมาก และส่งผลให้เสียเวลามาก ปัญหานี้เกิดขึ้นเมื่อต้องรวมข้อมูล เนื่องจากต้องเปรียบเทียบข้อมูลหลายแถวทั้งสองด้านของการรวม
แน่นอนว่าการสแกนตารางไม่ได้ทำให้เกิดปัญหาเสมอไป บางครั้งการอ่านทั้งตารางจะมีประสิทธิภาพมากกว่าการเลือกชุดย่อยของข้อมูล (ตัววางแผนคิวรีในกระบวนการเซิร์ฟเวอร์จะใช้ในการตัดสินใจเหล่านี้) หากใช้ดัชนีอย่างไม่มีประสิทธิภาพหรือไม่สามารถใช้งานได้เลย จะทำให้การสืบค้นช้าลง และปัญหานี้จะมีนัยสำคัญมากขึ้นเมื่อโหลดบนเซิร์ฟเวอร์และขนาดของตารางเพิ่มขึ้น ข้อความค้นหาที่ใช้เวลาดำเนินการนานกว่าช่วงเวลาที่กำหนดเรียกว่าข้อความค้นหาที่ช้า
คุณสามารถกำหนดค่า mysqld ให้บันทึกการสืบค้นที่ช้าเหล่านี้ไปยังบันทึกการสืบค้นที่ช้าที่มีชื่ออย่างเหมาะสม ผู้ดูแลระบบจะตรวจสอบบันทึกนี้เพื่อช่วยพิจารณาว่าส่วนใดของแอปพลิเคชันที่จำเป็นต้องมีการตรวจสอบเพิ่มเติม รายการที่ 1 แสดงการกำหนดค่าที่ต้องทำใน my.cnf เพื่อเปิดใช้งานการบันทึกการค้นหาที่ช้า
รายการ 1. การเปิดใช้งานบันทึกการสืบค้นที่ช้าของ MySQL
[mysqld]; เปิดใช้งานบันทึกการสืบค้นที่ช้า โดยค่าเริ่มต้น 10 วินาทีบันทึกการสืบค้นช้า; การสืบค้นบันทึกที่ใช้เวลานานกว่า 5 วินาทีlong_query_time = 5; แบบสอบถามที่ไม่ได้ใช้ดัชนี
การตั้งค่าทั้งสามนี้ใช้ร่วมกันเพื่อบันทึกแบบสอบถามที่ใช้เวลาดำเนินการมากกว่า 5 วินาทีและไม่ใช้ดัชนี โปรดทราบคำเตือนเกี่ยวกับ log-queries-not-using-indexes: คุณต้องใช้ MySQL 4.1 หรือสูงกว่า บันทึกการสืบค้นที่ช้าจะถูกบันทึกไว้ในไดเร็กทอรีข้อมูล MySQL และตั้งชื่อเป็นชื่อโฮสต์-slow.log หากคุณต้องการใช้ชื่อหรือเส้นทางอื่น คุณสามารถใช้ log-slow-queries = /new/path/to/file ใน my.cnf เพื่อให้บรรลุเป้าหมายนี้ได้
การอ่านบันทึกการสืบค้นที่ช้าทำได้ดีที่สุดผ่านคำสั่ง mysqldumpslow ด้วยการระบุเส้นทางไปยังไฟล์บันทึก คุณสามารถดูรายการเรียงลำดับของการสืบค้นที่ช้า พร้อมด้วยจำนวนครั้งที่เกิดขึ้นในไฟล์บันทึก คุณสมบัติที่มีประโยชน์มากคือ mysqldumpslow จะลบข้อมูลที่ผู้ใช้ระบุก่อนที่จะเปรียบเทียบผลลัพธ์ ดังนั้นการเรียกที่แตกต่างกันไปยังแบบสอบถามเดียวกันจะนับเป็นหนึ่งเดียว ซึ่งสามารถช่วยระบุแบบสอบถามที่ต้องการการทำงานมากที่สุด
การแคชคิวรี
แอปพลิเคชัน LAMP จำนวนมากอาศัยฐานข้อมูลเป็นจำนวนมาก แต่ดำเนินการคิวรีเดียวกันซ้ำแล้วซ้ำเล่า ทุกครั้งที่ดำเนินการค้นหา ฐานข้อมูลจะต้องทำงานเดียวกัน - วิเคราะห์แบบสอบถาม กำหนดวิธีดำเนินการ โหลดข้อมูลจากดิสก์ และส่งผลลัพธ์กลับไปยังไคลเอนต์ MySQL มีฟีเจอร์ที่เรียกว่าแคชคิวรี ซึ่งเก็บผลลัพธ์คิวรี (ซึ่งจะใช้ในภายหลัง) ไว้ในหน่วยความจำ ในหลายกรณี สิ่งนี้จะช่วยปรับปรุงประสิทธิภาพได้อย่างมาก อย่างไรก็ตาม ปัญหาก็คือว่าการแคชแบบสอบถามถูกปิดใช้งานตามค่าเริ่มต้น
เพิ่ม query_cache_size = 32M ไปที่ /etc/my.conf เพื่อเปิดใช้งานแคชการสืบค้นขนาด 32MB
การตรวจสอบแคชแบบสอบถาม
หลังจากเปิดใช้งานแคชแบบสอบถาม สิ่งสำคัญคือต้องเข้าใจว่ามีการใช้งานอย่างมีประสิทธิภาพหรือไม่ MySQL มีตัวแปรหลายตัวที่คุณสามารถดูได้เพื่อทำความเข้าใจว่าเกิดอะไรขึ้นในแคช รายการ 2 แสดงสถานะของแคช
รายการ 2. การแสดงสถิติแคชแบบสอบถาม
mysql> แสดงสถานะเช่น 'qcache%';+-------------------------+------------+ |. Variable_name |. ค่า |+-------------+------------+| |. Qcache_free_memory |. 14640664 ||. Qcache_hits |. 7 || 042 |+------------- -------- ---+----------------+8 แถวในชุด (0.00 วินาที)
คำอธิบายของรายการเหล่านี้แสดงไว้ในตารางที่ 1
ตารางที่ 1. คำอธิบายชื่อตัวแปรตัวแปรแคชแบบสอบถาม MySQL
Qcache_free_blocks จำนวนบล็อกหน่วยความจำที่อยู่ติดกันในแคช จำนวนมากแสดงว่าอาจมีเศษชิ้นส่วน FLUSH QUERY CACHE จัดเรียงข้อมูลแคชเพื่อรับบล็อกว่าง
Qcache_free_memory หน่วยความจำว่างในแคช
Qcache_hits จะเพิ่มขึ้นทุกครั้งที่มีการสืบค้นในแคช
Qcache_inserts จะเพิ่มขึ้นทุกครั้งที่มีการแทรกเคียวรี อัตราส่วนที่พลาดคือจำนวนการเข้าชมหารด้วยจำนวนการแทรก ลบค่านี้ออกจาก 1 เพื่อให้ได้อัตราการเข้าชม ในตัวอย่างข้างต้น ประมาณ 87% ของข้อความค้นหาเข้าสู่แคช
Qcache_lowmem_prunes จำนวนครั้งที่แคชหน่วยความจำไม่เพียงพอ และต้องถูกล้างข้อมูลเพื่อให้มีที่ว่างสำหรับการสืบค้นเพิ่มเติม หมายเลขนี้จะดูได้ดีที่สุดในช่วงเวลาที่ยาวนาน หากตัวเลขเพิ่มขึ้น อาจบ่งบอกถึงการกระจายตัวที่รุนแรงหรือหน่วยความจำเหลือน้อย (free_blocks และ free_memory ด้านบนสามารถบอกคุณได้ว่าเป็นกรณีใด)
Qcache_not_cached จำนวนเคียวรีที่ไม่เหมาะสำหรับการแคช โดยทั่วไปเนื่องจากไม่ใช่คำสั่ง SELECT
Qcache_queries_in_cache จำนวนเคียวรี (และการตอบกลับ) ที่แคชไว้ในปัจจุบัน
Qcache_total_blocks จำนวนบล็อกในแคช
บ่อยครั้งที่สามารถเห็นความแตกต่างได้โดยการแสดงตัวแปรเหล่านี้ห่างกันไม่กี่วินาที ซึ่งสามารถช่วยพิจารณาว่าแคชถูกใช้อย่างมีประสิทธิภาพหรือไม่ การเรียกใช้สถานะล้างสามารถรีเซ็ตตัวนับบางตัวได้ ซึ่งจะมีประโยชน์มากหากเซิร์ฟเวอร์ทำงานมาระยะหนึ่งแล้ว
การใช้แคชการสืบค้นที่มีขนาดใหญ่มากและคาดว่าจะแคชทุกอย่างเป็นเรื่องที่น่าสนใจมาก เนื่องจาก mysqld ต้องทำการบำรุงรักษาแคช เช่น ดำเนินการตัดเมื่อหน่วยความจำเหลือน้อย เซิร์ฟเวอร์อาจจมอยู่กับการพยายามจัดการแคช ตามกฎแล้ว หาก FLUSH QUERY CACHE ใช้เวลานาน แสดงว่าแคชมีขนาดใหญ่เกินไป
การบังคับใช้ขีดจำกัด
คุณสามารถบังคับใช้ขีดจำกัดใน mysqld เพื่อให้แน่ใจว่าโหลดของระบบไม่ทำให้ทรัพยากรหมด รายการ 3 แสดงการตั้งค่าที่เกี่ยวข้องกับทรัพยากรที่สำคัญใน my.cnf
รายการ 3 การตั้งค่าทรัพยากร MySQL
ชุดตัวแปร=max_connections=500ชุดตัวแปร=wait_timeout=10max_connect_errors = 100
จำนวนการเชื่อมต่อสูงสุดได้รับการจัดการในบรรทัดแรก เช่นเดียวกับ MaxClients ใน Apache แนวคิดก็คือเพื่อให้แน่ใจว่ามีการเชื่อมต่อตามจำนวนที่บริการอนุญาตเท่านั้น หากต้องการกำหนดจำนวนการเชื่อมต่อสูงสุดที่สร้างไว้บนเซิร์ฟเวอร์ในปัจจุบัน ให้ดำเนินการ SHOW STATUS LIKE 'max_used_connections'
บรรทัดที่ 2 บอกให้ mysqld ยุติการเชื่อมต่อใดๆ ที่ไม่ได้ใช้งานนานกว่า 10 วินาที ในแอปพลิเคชัน LAMP เวลาที่ใช้ในการเชื่อมต่อกับฐานข้อมูลมักจะเป็นเวลาที่เว็บเซิร์ฟเวอร์ใช้ในการประมวลผลคำขอ บางครั้งหากโหลดหนักเกินไป การเชื่อมต่อจะหยุดทำงานและใช้พื้นที่ตารางการเชื่อมต่อ หากคุณมีผู้ใช้แบบโต้ตอบหลายคนหรือใช้การเชื่อมต่อฐานข้อมูลอย่างต่อเนื่อง ไม่แนะนำให้ตั้งค่านี้ให้ต่ำลง!
บรรทัดสุดท้ายเป็นวิธีที่ปลอดภัย หากโฮสต์มีปัญหาในการเชื่อมต่อกับเซิร์ฟเวอร์และลองใหม่หลายครั้งก่อนที่จะยอมแพ้ โฮสต์จะถูกล็อคและไม่สามารถทำงานได้จนกว่าจะหลังจาก FLUSH HOSTS ตามค่าเริ่มต้น ความล้มเหลว 10 ครั้งก็เพียงพอที่จะทำให้เกิดการล็อกได้ การเปลี่ยนค่านี้เป็น 100 จะทำให้เซิร์ฟเวอร์มีเวลาเพียงพอในการกู้คืนจากปัญหา หากไม่สามารถสร้างการเชื่อมต่อได้หลังจากลองอีกครั้ง 100 ครั้ง การใช้ค่าที่สูงกว่าจะไม่ช่วยอะไรมากนักและอาจไม่เชื่อมต่อเลย
บัฟเฟอร์และการแคช
MySQL รองรับการตั้งค่าที่ปรับได้มากกว่า 100 รายการ แต่โชคดีที่การเรียนรู้บางอย่างจะสนองความต้องการส่วนใหญ่ได้ หากต้องการค้นหาค่าที่ถูกต้องสำหรับการตั้งค่าเหล่านี้ คุณสามารถดูตัวแปรสถานะผ่านคำสั่ง SHOW STATUS ซึ่งสามารถระบุได้ว่า mysqld ทำงานตามที่เราคาดหวังหรือไม่ หน่วยความจำที่จัดสรรให้กับบัฟเฟอร์และแคชต้องไม่เกินหน่วยความจำที่มีอยู่ในระบบ ดังนั้นการปรับแต่งมักจะต้องมีการประนีประนอมบางประการ
การตั้งค่าที่ปรับแต่งได้ของ MySQL สามารถนำไปใช้กับกระบวนการ mysqld ทั้งหมดหรือกับแต่ละเซสชันของไคลเอ็นต์ได้
การตั้งค่าฝั่งเซิร์ฟเวอร์
แต่ละตารางสามารถแสดงเป็นไฟล์บนดิสก์ ซึ่งจะต้องเปิดก่อนแล้วจึงอ่าน เพื่อเร่งกระบวนการอ่านข้อมูลจากไฟล์ mysqld จะแคชไฟล์ที่เปิดอยู่เหล่านี้จนถึงจำนวนสูงสุดที่ระบุโดย table_cache ใน /etc/mysqld.conf รายการที่ 4 แสดงวิธีการแสดงกิจกรรมที่เกี่ยวข้องกับการเปิดโต๊ะ
รายการ 4. การแสดงกิจกรรมที่เปิดตาราง
mysql> แสดงสถานะเช่น 'open%tables';+---------------+--------| Variable_name |. ------+-------+|. Open_tables |. 5000 ||. Open_tables |. +2 แถวในชุด (0.00 วินาที)
รายการ 4 แสดงว่าขณะนี้มี 5,000 ตารางที่เปิดอยู่ และ 195 ตารางจำเป็นต้องเปิด เนื่องจากไม่มีตัวอธิบายไฟล์ในแคช (เนื่องจากสถิติถูกล้างก่อนหน้านี้ จึงอาจมีเพียง 5,000 ตารางที่เปิดอยู่) . หาก Opened_tables เพิ่มขึ้นอย่างรวดเร็วด้วยการรันคำสั่ง SHOW STATUS อีกครั้ง แสดงว่าอัตราการเข้าถึงแคชไม่เพียงพอ หาก Open_tables เล็กกว่าการตั้งค่า table_cache มาก แสดงว่าค่านั้นใหญ่เกินไป (แต่การมีที่ว่างให้เติบโตก็ไม่ใช่เรื่องเลวร้าย) ตัวอย่างเช่น ใช้ table_cache = 5000 เพื่อปรับแคชของตาราง
เช่นเดียวกับแคชของตาราง นอกจากนี้ยังมีแคชสำหรับเธรดอีกด้วย mysqld วางไข่เธรดตามความจำเป็นเมื่อรับการเชื่อมต่อ บนเซิร์ฟเวอร์ไม่ว่างซึ่งการเชื่อมต่อเปลี่ยนแปลงอย่างรวดเร็ว การแคชเธรดเพื่อใช้ในภายหลังสามารถเร่งความเร็วการเชื่อมต่อเริ่มต้นได้
รายการที่ 5 แสดงวิธีการตรวจสอบว่ามีแคชเธรดเพียงพอหรือไม่
รายการ 5. การแสดงสถิติการใช้งานเธรด
mysql> แสดงสถานะเช่น 'เธรด%';+-------------------+--------+| Variable_name |. -----+--+| Threads_cached |. 27 ||. Threads_connected |. ---------------+-------+4 แถวในชุด (0.00 วินาที)
ค่าสำคัญที่นี่คือ Threads_created ค่านี้จะเพิ่มขึ้นทุกครั้งที่ mysqld ต้องการสร้างเธรดใหม่ หากตัวเลขนี้เพิ่มขึ้นอย่างรวดเร็วเมื่อดำเนินการคำสั่ง SHOW STATUS ต่อเนื่องกัน คุณควรพยายามเพิ่มแคชของเธรด ตัวอย่างเช่น คุณสามารถใช้ thread_cache = 40 ใน my.cnf เพื่อให้บรรลุเป้าหมายนี้
บัฟเฟอร์คีย์เก็บบล็อกดัชนีของตาราง MyISAM ตามหลักการแล้ว คำขอสำหรับบล็อกเหล่านี้ควรมาจากหน่วยความจำมากกว่าจากดิสก์ รายการที่ 6 แสดงวิธีการกำหนดจำนวนบล็อกที่ถูกอ่านจากดิสก์ และจำนวนบล็อกที่ถูกอ่านจากหน่วยความจำ
รายการ 6. การกำหนดประสิทธิภาพของคำหลัก
mysql> แสดงสถานะเช่น '%key_read%';+-------------------+-----------+| Value |+ -------------------+-----+|. Key_read_requests |. 163554268 ||. Key_reads |. -----------+-----------+2 แถวในชุด (0.00 วินาที)
Key_reads แสดงถึงจำนวนคำขอที่ส่งถึงดิสก์ และ Key_read_requests คือจำนวนทั้งหมด อัตราส่วนที่พลาดคือจำนวนคำขออ่านที่เข้าถึงดิสก์หารด้วยจำนวนคำขออ่านทั้งหมด ในกรณีนี้ ข้อผิดพลาดในหน่วยความจำประมาณ 0.6 รายการสำหรับทุก ๆ 1,000 คำขอ หากจำนวนการเข้าถึงดิสก์เกิน 1 ต่อคำขอ 1,000 รายการ คุณควรพิจารณาเพิ่มบัฟเฟอร์คำหลัก ตัวอย่างเช่น key_buffer = 384M จะตั้งค่าบัฟเฟอร์เป็น 384MB
สามารถใช้ตารางชั่วคราวในการสืบค้นขั้นสูง โดยจะต้องบันทึกข้อมูลลงในตารางชั่วคราวก่อนประมวลผลเพิ่มเติม (เช่น ส่วนคำสั่ง GROUP BY) ตารางชั่วคราวจะถูกสร้างขึ้นในหน่วยความจำ แต่หากตารางชั่วคราวมีขนาดใหญ่เกินไป จะต้องเขียนลงดิสก์ รายการ 7 ให้สถิติที่เกี่ยวข้องกับการสร้างตารางชั่วคราว
รายการ 7. การพิจารณาการใช้ตารางชั่วคราว
mysql> แสดงสถานะเช่น 'created_tmp%';+--------------------------+--+| Variable_name | |. +--------------+--+|. Create_tmp_tables | |. +--------------------+--+3 แถวในชุด (0.00 วินาที)
Create_tmp_tables จะเพิ่มขึ้นทุกครั้งที่ใช้ตารางชั่วคราว; Create_tmp_disk_tables จะเพิ่มขึ้นสำหรับตารางแบบอิงดิสก์ด้วย ไม่มีกฎเกณฑ์ที่เข้มงวดสำหรับอัตราส่วนนี้ เนื่องจากขึ้นอยู่กับคำค้นหาที่เกี่ยวข้อง การดู Create_tmp_disk_tables เมื่อเวลาผ่านไปจะแสดงอัตราส่วนของตารางดิสก์ที่สร้างขึ้น และคุณสามารถกำหนดประสิทธิภาพของการตั้งค่าของคุณได้ ทั้ง tmp_table_size และ max_heap_table_size ควบคุมขนาดสูงสุดของตารางชั่วคราว ดังนั้นตรวจสอบให้แน่ใจว่าได้ตั้งค่าทั้งสองค่าใน my.cnf
การตั้งค่าต่อเซสชัน
การตั้งค่าต่อไปนี้มีไว้สำหรับแต่ละเซสชันโดยเฉพาะ โปรดใช้ความระมัดระวังเป็นอย่างยิ่งเมื่อตั้งค่าตัวเลขเหล่านี้ เพราะเมื่อคูณด้วยจำนวนการเชื่อมต่อที่มีอยู่ ตัวเลือกเหล่านี้จะแสดงหน่วยความจำจำนวนมาก! คุณสามารถแก้ไขตัวเลขเหล่านี้ภายในเซสชั่นผ่านโค้ด หรือแก้ไขการตั้งค่าเหล่านี้ใน my.cnf สำหรับทุกเซสชั่น
เมื่อ MySQL ต้องเรียงลำดับ มันจะจัดสรรบัฟเฟอร์การเรียงลำดับเพื่อเก็บแถวข้อมูลในขณะที่อ่านจากดิสก์ หากข้อมูลที่จะจัดเรียงมีขนาดใหญ่เกินไป ข้อมูลจะต้องถูกบันทึกลงในไฟล์ชั่วคราวบนดิสก์และจัดเรียงอีกครั้ง หากตัวแปรสถานะ sort_merge_passes มีขนาดใหญ่ แสดงว่ากิจกรรมของดิสก์ รายการ 8 แสดงข้อมูลตัวนับสถานะบางส่วนที่เกี่ยวข้องกับการเรียงลำดับ
รายการ 8. การแสดงสถิติการเรียงลำดับ
mysql> แสดงสถานะเช่น "sort%";+-------------------+---------+| Variable_name |. -+---+|. Sort_merge_passes |. 1 ||. Sort_range |. 79192 ||. -------------------+---------+4 แถวในชุด (0.00 วินาที)
หาก sort_merge_passes มีขนาดใหญ่ หมายความว่าคุณต้องใส่ใจกับ sort_buffer_size ตัวอย่างเช่น sort_buffer_size = 4M ตั้งค่าบัฟเฟอร์การเรียงลำดับเป็น 4MB
MySQL ยังจัดสรรหน่วยความจำบางส่วนเพื่ออ่านตาราง ตามหลักการแล้ว ดัชนีจะให้ข้อมูลเพียงพอที่จะอ่านเฉพาะแถวที่คุณต้องการ แต่บางครั้งคิวรี (ออกแบบมาไม่ดีหรือเนื่องจากลักษณะของข้อมูล) จำเป็นต้องอ่านข้อมูลจำนวนมากจากตาราง เพื่อให้เข้าใจถึงลักษณะการทำงานนี้ คุณจำเป็นต้องทราบว่ามีการรันคำสั่ง SELECT กี่ครั้ง และจำเป็นต้องอ่านข้อมูลแถวถัดไปในตารางกี่ครั้ง (แทนที่จะเข้าถึงผ่านดัชนีโดยตรง) คำสั่งเพื่อให้บรรลุฟังก์ชันนี้จะแสดงอยู่ในรายการที่ 9
รายการ 9. การกำหนดอัตราส่วนการสแกนตาราง
mysql> แสดงสถานะเช่น "com_select";+---------------+--------+| Variable_name |. -+-------------+|. Com_select |. 318243 |+-----+---+1 แถวในชุด (0.00 วินาที) mysql> แสดงสถานะเช่น "handler_read_rnd_next";+-----------------------+------ +| Variable_name | |+-----------------------+-----------+|. -------------------+----------+1 แถวในชุด (0.00 วินาที)
Handler_read_rnd_next / Com_select ส่งผลให้มีอัตราส่วนการสแกนตาราง - ในกรณีนี้คือ 521:1 หากค่าเกิน 4000 คุณควรตรวจสอบ read_buffer_size เช่น read_buffer_size = 4M หากตัวเลขนี้เกิน 8M ก็ถึงเวลาหารือเกี่ยวกับการปรับแต่งคำค้นหาเหล่านี้กับนักพัฒนา!
3 เครื่องมือสำคัญ
แม้ว่าคำสั่ง SHOW STATUS จะมีประโยชน์มากเมื่อเข้าใจการตั้งค่าเฉพาะ แต่คุณจำเป็นต้องมีเครื่องมือบางอย่างเพื่อตีความข้อมูลจำนวนมากที่ mysqld ให้มา มีเครื่องมือสามอย่างที่ฉันพบว่าจำเป็น คุณสามารถดูลิงก์ไปยังเครื่องมือเหล่านั้นได้ในส่วนแหล่งข้อมูล
ผู้ดูแลระบบส่วนใหญ่คุ้นเคยกับคำสั่งด้านบน ซึ่งให้มุมมองที่อัปเดตอย่างต่อเนื่องของ CPU และหน่วยความจำที่ใช้โดยงานต่างๆ mytop จำลองด้านบน โดยให้มุมมองของไคลเอนต์ที่เชื่อมต่อทั้งหมดและการสืบค้นที่พวกเขากำลังรันอยู่ mytop ยังให้ข้อมูลสดและประวัติเกี่ยวกับบัฟเฟอร์คำหลักและประสิทธิภาพของแคชแบบสอบถาม เช่นเดียวกับสถิติเกี่ยวกับการเรียกใช้แบบสอบถาม นี่เป็นเครื่องมือที่มีประโยชน์ในการดูว่าเกิดอะไรขึ้นในระบบของคุณ (เช่นภายใน 10 วินาที) คุณสามารถดูข้อมูลสถานภาพเซิร์ฟเวอร์และแสดงการเชื่อมต่อใด ๆ ที่ทำให้เกิดปัญหา
mysqlard เป็น daemon ที่เชื่อมต่อกับเซิร์ฟเวอร์ MySQL ซึ่งมีหน้าที่รวบรวมข้อมูลทุกๆ 5 นาที และจัดเก็บไว้ในฐานข้อมูล Round Robin ในเบื้องหลัง มีเว็บเพจที่แสดงข้อมูล เช่น การใช้แคชของตาราง ประสิทธิภาพของคำหลัก ไคลเอนต์ที่เชื่อมต่อ และการใช้งานตารางชั่วคราว แม้ว่า mytop จะให้ภาพรวมของข้อมูลสถานภาพของเซิร์ฟเวอร์ แต่ mysqlard ก็ให้ข้อมูลด้านสุขภาพในระยะยาว เป็นโบนัส mysqlard ใช้ข้อมูลบางส่วนที่รวบรวมไว้เพื่อให้คำแนะนำเกี่ยวกับวิธีการปรับแต่งเซิร์ฟเวอร์
เครื่องมืออีกอย่างในการรวบรวมข้อมูล SHOW STATUS คือ mysqlreport การรายงานมีความซับซ้อนมากกว่า mysqlard เนื่องจากต้องมีการวิเคราะห์ทุกแง่มุมของเซิร์ฟเวอร์ นี่เป็นเครื่องมือที่ยอดเยี่ยมสำหรับการปรับแต่งเซิร์ฟเวอร์ของคุณ เนื่องจากจะทำการคำนวณตัวแปรสถานะอย่างเหมาะสมเพื่อช่วยพิจารณาว่าปัญหาใดที่ต้องได้รับการแก้ไข
บทความนี้แนะนำ
ความรู้พื้นฐานเกี่ยวกับการปรับแต่ง MySQL และสรุปซีรี่ส์ 3 ส่วนเกี่ยวกับการปรับแต่งส่วนประกอบ LAMP การปรับแต่งส่วนใหญ่เกี่ยวข้องกับการทำความเข้าใจวิธีการทำงานของส่วนประกอบ การพิจารณาว่าส่วนประกอบทำงานอย่างถูกต้องหรือไม่ การปรับเปลี่ยนบางอย่าง และการประเมินใหม่ แต่ละองค์ประกอบ - Linux, Apache, PHP หรือ MySQL - มีข้อกำหนดที่แตกต่างกัน การทำความเข้าใจแต่ละองค์ประกอบแยกกันสามารถช่วยลดปัญหาคอขวดที่อาจทำให้การใช้งานของคุณช้าลง