เคล็ดลับประสิทธิภาพที่สำคัญ 10 ประการสำหรับ MySQL

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

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

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

นี่คือเคล็ดลับ 10 ประการในการรับประสิทธิภาพที่ยอดเยี่ยมจาก MySQL

เคล็ดลับประสิทธิภาพ MySQL ข้อที่ 1: กำหนดโปรไฟล์ปริมาณงานของคุณ

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

วิธีที่ดีที่สุดในการกำหนดโปรไฟล์ปริมาณงานของคุณคือการใช้เครื่องมือเช่นตัววิเคราะห์แบบสอบถามของ MySQL Enterprise Monitor หรือ pt-query-Digest จาก Percona Toolkit เครื่องมือเหล่านี้จะรวบรวมคำค้นหาที่เซิร์ฟเวอร์เรียกใช้และส่งคืนตารางงานที่จัดเรียงตามลำดับเวลาตอบสนองที่ลดลงทำให้งานที่แพงที่สุดและใช้เวลานานขึ้นไปด้านบนทันทีเพื่อให้คุณสามารถดูว่าจะมุ่งเน้นไปที่จุดใด

เครื่องมือทำโปรไฟล์เวิร์กโหลดจัดกลุ่มคำค้นหาที่คล้ายกันเข้าด้วยกันช่วยให้คุณเห็นการสืบค้นที่ช้าเช่นเดียวกับการสืบค้นที่รวดเร็ว แต่ดำเนินการหลายครั้ง

เคล็ดลับประสิทธิภาพ MySQL ข้อที่ 2: ทำความเข้าใจทรัพยากรพื้นฐานทั้งสี่

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

การทำความเข้าใจทรัพยากรพื้นฐานมีความสำคัญใน 2 ด้านโดยเฉพาะ ได้แก่ การเลือกฮาร์ดแวร์และการแก้ไขปัญหา

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

อีกตัวอย่างที่ดีของสมดุลนี้เกี่ยวข้องกับซีพียู ในกรณีส่วนใหญ่ MySQL จะทำงานได้ดีกับ CPU ที่รวดเร็วเนื่องจากแต่ละแบบสอบถามทำงานในเธรดเดียวและไม่สามารถขนานกันระหว่าง CPU ได้

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

เคล็ดลับประสิทธิภาพ MySQL ข้อที่ 3: อย่าใช้ MySQL เป็นคิว

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

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

เคล็ดลับประสิทธิภาพ MySQL ข้อที่ 4: กรองผลลัพธ์โดยถูกที่สุดก่อน

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

ตัวอย่างเช่นสมมติว่าคุณกำลังมองหาบางสิ่งที่อยู่ในรัศมีของจุดทางภูมิศาสตร์ที่กำหนด เครื่องมือแรกในกล่องเครื่องมือของโปรแกรมเมอร์หลายคนคือสูตรวงกลมใหญ่ (Haversine) สำหรับการคำนวณระยะทางตามพื้นผิวของทรงกลม ปัญหาของเทคนิคนี้คือสูตรต้องใช้การดำเนินการตรีโกณมิติจำนวนมากซึ่งใช้ CPU มาก การคำนวณแบบวงกลมใหญ่มักจะทำงานช้าและทำให้การใช้งาน CPU ของเครื่องพุ่งสูงขึ้น

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

เคล็ดลับประสิทธิภาพ MySQL ข้อที่ 5: รู้จักกับดักมรณะที่ปรับขนาดได้สองแบบ

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

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

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

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

เคล็ดลับประสิทธิภาพ MySQL ข้อที่ 6: อย่าให้ความสำคัญกับการกำหนดค่ามากเกินไป

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

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

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

เคล็ดลับประสิทธิภาพ MySQL ข้อที่ 7: ระวังการค้นหาเลขหน้า

แอปพลิเคชั่นที่เลขหน้ามักจะทำให้เซิร์ฟเวอร์คุกเข่าลง ในการแสดงหน้าผลลัพธ์พร้อมลิงก์เพื่อไปยังหน้าถัดไปโดยทั่วไปแอปพลิเคชันเหล่านี้จะจัดกลุ่มและจัดเรียงในลักษณะที่ไม่สามารถใช้ดัชนีได้และพวกเขาใช้ a LIMITและoffsetทำให้เซิร์ฟเวอร์ทำงานได้มาก จากนั้นทิ้งแถว

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

ในด้านแบบสอบถามแทนที่จะใช้LIMITร่วมกับoffsetคุณสามารถเลือกได้มากกว่าหนึ่งแถวที่คุณต้องการและเมื่อผู้ใช้คลิกลิงก์ "หน้าถัดไป" คุณสามารถกำหนดแถวสุดท้ายนั้นเป็นจุดเริ่มต้นสำหรับผลลัพธ์ชุดถัดไปได้ ตัวอย่างเช่นหากผู้ใช้ดูหน้าที่มีแถว 101 ถึง 120 คุณจะต้องเลือกแถว 121 เช่นกัน ในการแสดงผลหน้าถัดไปคุณต้องสอบถามเซิร์ฟเวอร์สำหรับแถวที่มากกว่าหรือเท่ากับ 121 ขีด จำกัด 21

เคล็ดลับประสิทธิภาพ MySQL ข้อที่ 8: บันทึกสถิติอย่างกระตือรือร้นแจ้งเตือนอย่างไม่เต็มใจ

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

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

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

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

เคล็ดลับประสิทธิภาพ MySQL ข้อที่ 9: เรียนรู้กฎสามข้อของการสร้างดัชนี

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

ดัชนีเมื่อได้รับการออกแบบอย่างเหมาะสมจะตอบสนองวัตถุประสงค์สำคัญสามประการในเซิร์ฟเวอร์ฐานข้อมูล:

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

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

เคล็ดลับประสิทธิภาพ MySQL ข้อที่ 10: ใช้ประโยชน์จากความเชี่ยวชาญของเพื่อนร่วมงานของคุณ

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

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

สำหรับผู้ที่มองหาเครื่องมือเพื่อเสริมเคล็ดลับเหล่านี้คุณสามารถตรวจสอบ Percona Configuration Wizard สำหรับ MySQL, Percona Query Advisor สำหรับ MySQL และ Percona Monitoring Plugins (หมายเหตุ: คุณจะต้องสร้างบัญชี Percona เพื่อเข้าถึงลิงก์สองลิงก์แรกฟรี) วิซาร์ดการกำหนดค่าสามารถช่วยคุณสร้างไฟล์ my.cnf พื้นฐานสำหรับเซิร์ฟเวอร์ใหม่ที่เหนือกว่าไฟล์ตัวอย่างที่มาพร้อมกับ เซิร์ฟเวอร์ ที่ปรึกษาการสืบค้นจะวิเคราะห์ SQL ของคุณเพื่อช่วยตรวจจับรูปแบบที่อาจไม่ถูกต้องเช่นแบบสอบถามการแบ่งหน้า (หมายเลข 7) Percona Monitoring Plugins คือชุดของปลั๊กอินการตรวจสอบและสร้างกราฟเพื่อช่วยให้คุณบันทึกสถิติได้อย่างกระตือรือร้นและแจ้งเตือนอย่างไม่เต็มใจ (หมายเลข 8) เครื่องมือทั้งหมดนี้สามารถใช้ได้อย่างอิสระ