แนะนำวิธีการเพิ่มประสิทธิภาพ MYSQL จากหลาย ๆ ด้านเพื่อให้ได้ประสิทธิภาพ การปรับให้เหมาะสมที่สุดเป็นงานที่ซับซ้อน เนื่องจากท้ายที่สุดแล้วจำเป็นต้องมีความเข้าใจเกี่ยวกับระบบทั้งหมด แม้ว่าจะเป็นไปได้ที่จะทำการเพิ่มประสิทธิภาพเฉพาะที่โดยมีความรู้เพียงเล็กน้อยเกี่ยวกับระบบ/แอปพลิเคชันของคุณ แต่ยิ่งคุณต้องการทำให้ระบบของคุณปรับให้เหมาะสมมากขึ้นเท่าไร คุณต้องรู้ว่า นอกจากนี้ บทนี้จะพยายามอธิบายและยกตัวอย่างวิธีต่างๆ ในการเพิ่มประสิทธิภาพ MySQL แต่จำไว้ว่ายังมีวิธีที่แน่นอน (ยากขึ้นเรื่อยๆ) ที่จะทำเช่นนั้นได้เร็วกว่าสำหรับระบบ ส่วนที่สำคัญที่สุดในการทำให้ระบบของคุณเร็วขึ้นคือการออกแบบขั้นพื้นฐาน คุณยังต้องรู้ด้วยว่าระบบของคุณจะทำสิ่งนี้ และนั่นคือปัญหาคอขวดที่พบบ่อยที่สุดคือ:
การค้นหาดิสก์ เวลาที่ใช้ดิสก์ในการค้นหาชิ้นส่วนข้อมูล เวลาเฉลี่ยสำหรับดิสก์สมัยใหม่ที่ใช้ในปี 1999 มักจะน้อยกว่า 10 มิลลิวินาที ดังนั้นตามทฤษฎีแล้วเราสามารถค้นหาได้ประมาณ 1,000 ครั้งต่อวินาที ดิสก์และวัดได้ยาก วิธีเพิ่มประสิทธิภาพตารางเดียวคือการกระจายข้อมูลไปยังดิสก์หลายตัวเมื่อดิสก์อยู่ในตำแหน่งที่ถูกต้องซึ่งเราต้องการอ่านข้อมูล การถ่ายโอนดิสก์หนึ่งครั้งมีค่าประมาณ 10-20Mb/s ซึ่งจะต้องพยายามปรับให้เหมาะสมได้ง่ายขึ้น เนื่องจากคุณสามารถอ่านจากดิสก์หลายตัวในรอบ CPU แบบขนาน เมื่อเราอ่านข้อมูลลงในหน่วยความจำ (หรือหากมีอยู่แล้ว) เราจำเป็นต้องประมวลผลเพื่อให้บรรลุผล ผลลัพธ์ของเรา เมื่อ นี่เป็นปัจจัยจำกัดที่พบบ่อยที่สุดเมื่อเรามีตารางที่มีหน่วยความจำค่อนข้างเล็ก แต่ความเร็วของตารางเล็ก ๆ มักจะไม่เป็นปัญหา ในแคชของ CPU นี่เป็นปัญหาคอขวดที่ไม่ปกติในระบบส่วนใหญ่ แต่คุณควรระวัง 10.2 การปรับแต่งระบบ/เวลาคอมไพล์ และพารามิเตอร์การบูต เราเริ่มต้นด้วยเนื้อหาระดับระบบ เนื่องจากการตัดสินใจบางอย่างเหล่านี้เกิดขึ้นเร็วมาก ในบางครั้ง การดูส่วนนี้อย่างรวดเร็วอาจเพียงพอแล้ว เนื่องจากไม่ได้สำคัญต่อผลกำไรมหาศาล แต่ก็ดีเสมอที่จะรู้สึกว่ากำไรนั้นใหญ่แค่ไหนในระดับนี้ CPU หลายตัวในระดับหนึ่ง คุณควรใช้ Solaris (เพราะเธรดทำงานได้ดีมาก) หรือ Linux (เนื่องจากคอร์ 2.2 มีการรองรับ SMP ที่ดีจริงๆ) และบนเครื่อง 32 บิต Linux จะมีการจำกัดขนาดไฟล์ไว้ที่ 2G ตามค่าเริ่มต้น หวังว่าสิ่งนี้จะได้รับการแก้ไขในเร็วๆ นี้เมื่อมีการเปิดตัวระบบไฟล์ใหม่ (XFS) เนื่องจากเราไม่ได้ใช้งาน MySQL ที่ใช้งานจริงบนหลายแพลตฟอร์ม เราขอแนะนำให้คุณทดสอบแพลตฟอร์มที่คุณต้องการใช้งานก่อนตัดสินใจเลือก
ข้อเสนอแนะอื่นๆ:
หากคุณมี RAM เพียงพอ คุณสามารถลบอุปกรณ์สลับทั้งหมดได้ ในบางกรณี ระบบปฏิบัติการจะใช้อุปกรณ์ SWAP แม้ว่าคุณจะมีหน่วยความจำว่างก็ตาม ใช้ตัวเลือก --skip -locking MySQL เพื่อหลีกเลี่ยงการล็อกภายนอก ฟังก์ชันการทำงานของ MySQL ตราบใดที่ทำงานบนเซิร์ฟเวอร์เดียว เพียงอย่าลืมหยุดเซิร์ฟเวอร์ (หรือล็อคส่วนที่เกี่ยวข้อง) ก่อนที่คุณจะรัน myisamchk ในบางระบบ สวิตช์นี้จำเป็นเนื่องจากการล็อคภายนอกไม่สามารถใช้งานได้ในทุกกรณี เมื่อคอมไพล์ด้วย MIT-pthreads ตัวเลือก --skip-locking จะมีค่าเริ่มต้นเป็น on (on) เนื่องจาก MIT-pthreads ไม่รองรับ flock() อย่างสมบูรณ์บนทุกแพลตฟอร์ม กรณีเดียวคือเมื่อคุณใช้งานเซิร์ฟเวอร์ MySQL (ไม่ใช่ ไคลเอนต์) บนข้อมูลเดียวกัน คุณไม่สามารถใช้ --skip-locking ไม่เช่นนั้นให้รัน myisamchk บนโต๊ะโดยไม่ต้องล้างหรือล็อคเซิร์ฟเวอร์ mysqld ก่อน คุณยังสามารถใช้ LOCK TABLES/UNLOCK TABLES ได้ แม้ว่าคุณจะใช้ --skip ก็ตาม -ล็อค
การคอมไพล์และลิงก์ส่งผลต่อความเร็วของ MySQL อย่างไร
การทดสอบต่อไปนี้ส่วนใหญ่ดำเนินการบน Linux และใช้เกณฑ์มาตรฐาน MySQL แต่ควรให้ข้อบ่งชี้บางประการสำหรับระบบปฏิบัติการและปริมาณงานอื่น ๆ คุณจะได้รับการดำเนินการที่เร็วที่สุดเมื่อคุณเชื่อมโยงกับ -static ซ็อกเก็ต Unix การเชื่อมต่อกับฐานข้อมูลแทน TCP /IP อาจให้ประสิทธิภาพที่ดีกว่า บน Linux คุณจะได้รับโค้ดที่เร็วที่สุดเมื่อคอมไพล์ด้วย pgcc และ -O6 หากต้องการคอมไพล์ "sql_yacc.cc" ด้วยตัวเลือกเหล่านี้ คุณจะต้องใช้หน่วยความจำประมาณ 200M เนื่องจาก gcc/pgcc ต้องใช้หน่วยความจำจำนวนมาก หน่วยความจำเพื่อทำให้ฟังก์ชันทั้งหมดเป็นแบบอินไลน์ เมื่อกำหนดค่า MySQL คุณควรตั้งค่า CXX=gcc เพื่อหลีกเลี่ยงการรวมไลบรารี libstdc++ (ไม่จำเป็น) เพียงใช้ตัวเลือกคอมไพเลอร์ที่ดีกว่าหรือคอมไพเลอร์ที่ดีกว่า คุณก็จะได้ 10 การเร่งความเร็ว -30% ในแอปพลิเคชันของคุณ นี่เป็นสิ่งสำคัญอย่างยิ่งหากคุณคอมไพล์เซิร์ฟเวอร์ SQL ด้วยตัวเอง! บน Intel คุณควรใช้ pgcc หรือคอมไพเลอร์ Cygnus CodeFusion รับความเร็วสูงสุด ปราศจากข้อผิดพลาดเพียงพอที่จะเพิ่มประสิทธิภาพการคอมไพล์ MySQL
นี่คือเอกสารการวัดบางส่วนที่เราจัดทำ:
หากคุณใช้ pgcc กับ -O6 และคอมไพล์สิ่งใด ๆ เซิร์ฟเวอร์ mysqld จะเร็วกว่า gcc 11% (ด้วยเวอร์ชันสตริง 99) หากคุณลิงก์แบบไดนามิก (ไม่มี -static) ผลลัพธ์จะช้าลง 13% ยังคงสามารถใช้ไลบรารี MySQL ที่เชื่อมโยงแบบไดนามิกได้ เฉพาะเซิร์ฟเวอร์เท่านั้นที่สำคัญต่อประสิทธิภาพ หากคุณใช้ TCP/IP แทนซ็อกเก็ต Unix ผลลัพธ์จะช้าลง 7.5% บน Sun SPARCstation 10 นั้น gcc2.7.3 จะเร็วกว่า Sun Pro C++ 4.2 เร็วขึ้น 13% บน Solaris 2.5.1 MIT-pthreads จะช้ากว่า Solaris 8-12% ด้วยเธรดดั้งเดิมบนโปรเซสเซอร์ตัวเดียว ด้วยโหลด/cpus ที่มากขึ้น ความแตกต่างจะยิ่งใหญ่ยิ่งขึ้น การแจกจ่าย Linux ได้รับการคอมไพล์ด้วย pgcc และเชื่อมโยงแบบคงที่
ดังที่กล่าวไว้ข้างต้น การค้นหาดิสก์ถือเป็นปัญหาคอขวดด้านประสิทธิภาพอย่างมาก ปัญหานี้ชัดเจนมากขึ้นเรื่อยๆ เมื่อข้อมูลเริ่มมีขนาดใหญ่ขึ้น ซึ่งจะทำให้การแคชเป็นไปไม่ได้ สำหรับฐานข้อมูลขนาดใหญ่ ซึ่งคุณจะต้องสุ่มมากขึ้นหรือน้อยลง เพื่อเข้าถึงข้อมูล คุณสามารถทำได้ ขึ้นอยู่กับข้อเท็จจริงที่ว่าคุณจะต้องมีดิสก์อย่างน้อยหนึ่งตัวในการค้นหาและหลายดิสก์พยายามเขียน เพื่อลดปัญหานี้ ให้ใช้ดิสก์ที่มีเวลาในการค้นหาต่ำ เพื่อเพิ่มจำนวนแกนหมุนของดิสก์ที่มีอยู่ (และด้วยเหตุนี้จึงลดค่าใช้จ่ายในการค้นหา) เป็นไปได้ที่จะเชื่อมโยงไฟล์ไปยังดิสก์อื่นหรือแยกดิสก์ การใช้ symlink หมายความว่าคุณเชื่อมโยงไฟล์ดัชนี/ข้อมูลจากไดเร็กทอรีข้อมูลปกติไปยังดิสก์อื่นในเชิงสัญลักษณ์ (ซึ่งสามารถแยกได้) ซึ่งทำการค้นหาและอ่าน ดีขึ้นเท่าตัว (หากไม่ได้ใช้ดิสก์เพื่อสิ่งอื่น) ดูที่ 10.2.2.1 การใช้ symlink ไปยังฐานข้อมูลและตาราง แยกหมายความว่าคุณมีดิสก์จำนวนมากและวางบล็อกแรกไว้ในดิสก์หนึ่ง บล็อกที่สองอยู่บนดิสก์ที่สอง และบล็อกที่ n อยู่บนดิสก์ (n mod number_of_disks)th ฯลฯ ซึ่งหมายความว่าหากขนาดข้อมูลปกติของคุณเล็กกว่าขนาดการแยก (หรือสมบูรณ์แบบ (จัดเรียงตามนั้น) คุณจะได้รับประสิทธิภาพที่ดีขึ้นเล็กน้อย โปรดทราบว่าการแยก ขึ้นอยู่กับ OS และขนาดแยก ดังนั้นทดสอบแอปพลิเคชันของคุณด้วยขนาดแยกที่แตกต่างกัน ดู 10.8 การใช้เกณฑ์มาตรฐานของคุณเอง พารามิเตอร์และจำนวนดิสก์คุณสามารถรับคำสั่งที่มีขนาดต่างกันได้ โปรดทราบว่าคุณต้องเลือกที่จะปรับให้เหมาะสมสำหรับการเข้าถึงแบบสุ่มหรือตามลำดับ เพื่อความน่าเชื่อถือคุณอาจต้องการใช้การโจมตี RAID 0+ 1 (แยก + มิเรอร์) ในกรณีนี้คุณจะต้องมีไดรฟ์ 2*N เพื่อเก็บข้อมูลของไดรฟ์ N หากคุณมีเงิน นี่อาจเป็นตัวเลือกที่ดีที่สุด! อย่างไรก็ตาม คุณอาจต้องลงทุนในซอฟต์แวร์การจัดการโวลุ่มเพื่อจัดการอย่างมีประสิทธิภาพ ตัวเลือกที่ดีคือการมีข้อมูลที่สำคัญน้อยกว่า (ซึ่งสามารถทำซ้ำได้) บนดิสก์ RAID 0 และข้อมูลที่สำคัญจริงๆ (เช่น ข้อมูลโฮสต์และไฟล์บันทึก) บนดิสก์ RAID 0+ 1 หรือ RAID N หากคุณมีข้อมูลจำนวนมาก ของการเขียนเนื่องจากการอัปเดตบิตพาริตี N อาจเป็นปัญหาได้ คุณยังสามารถตั้งค่าพารามิเตอร์สำหรับระบบไฟล์ที่ใช้โดยฐานข้อมูลได้ การเปลี่ยนแปลงที่ง่ายดายคือเมานต์ระบบไฟล์ด้วยตัวเลือก noatime นี่คือเวลาเข้าถึงล่าสุด ไอโหนดที่ข้ามการอัปเดต และสิ่งนี้จะช่วยหลีกเลี่ยงการค้นหาดิสก์บางอย่าง
คุณสามารถย้ายตารางและฐานข้อมูลจากไดเร็กทอรีฐานข้อมูลไปยังตำแหน่งอื่นและแทนที่ด้วยสัญลักษณ์ที่ลิงก์ไปยังตำแหน่งใหม่ ตัวอย่างเช่น คุณอาจต้องการย้ายฐานข้อมูลไปยังระบบไฟล์ที่มีพื้นที่ว่างมากขึ้น โปรดทราบว่าตารางคือลิงก์สัญลักษณ์ซึ่งจะแก้ไขลิงก์สัญลักษณ์และใช้ตารางที่ชี้ไปจริง ๆ มันทำงานได้กับทุกระบบที่รองรับการเรียก realpath() (อย่างน้อย Linux และ Solaris รองรับ realpath()) บนระบบ ที่ไม่รองรับ realpath() บนระบบของคุณ คุณไม่ควรเข้าถึงตารางผ่านทั้งเส้นทางจริงและลิงก์สัญลักษณ์ในเวลาเดียวกัน! หากคุณทำเช่นนั้น ตารางจะไม่สอดคล้องกันหลังจากการอัพเดตใด ๆ MySQL ไม่รองรับลิงก์ฐานข้อมูล ตราบใดที่คุณไม่สร้างลิงก์สัญลักษณ์ระหว่างฐานข้อมูล ทุกอย่างจะทำงานได้ดี สมมติว่าคุณมีฐานข้อมูล db1 ในไดเร็กทอรีข้อมูล MySQL และสร้างลิงก์สัญลักษณ์ db2 ที่ชี้ไปที่ db1:
เชลล์&> cd /path/to/datadir
เชลล์&> ln -s db1 db2
ในตอนนี้ สำหรับตาราง tbl_a ใน db1 ก็จะมีตาราง tbl_a ใน db2 ด้วย หากเธรดหนึ่งอัปเดต db1.tbl_a และอีกเธรดอัปเดต db2.tbl_a จะเกิดปัญหาขึ้น หากคุณต้องการสิ่งนี้จริงๆ คุณจะต้องใช้โค้ดต่อไปนี้ "mysys/mf_format.c" ต้องมีการเปลี่ยนแปลง:
if (!lstat(to,&stat_buff)) /* ตรวจสอบว่าเป็นลิงก์สัญลักษณ์หรือไม่ */
ถ้า (S_ISLNK(stat_buff.st_mode) && realpath(ถึง,บัฟ))
เปลี่ยนรหัสเป็น:
ถ้า (realpath (ถึง, บัฟ))