เพิ่มประสิทธิภาพฐานข้อมูล mysql อย่างละเอียดภายใต้ภาระงานสูงบน Linux

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

หลังจากติดตั้ง mysql ไฟล์การกำหนดค่าควรอยู่ในไดเร็กทอรี /usr/local/mysql/share/mysql มีไฟล์การกำหนดค่าหลายไฟล์ รวมถึง my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf แน่นอนว่าเว็บไซต์ที่มีปริมาณการรับส่งข้อมูลและสภาพแวดล้อมเซิร์ฟเวอร์ที่มีการกำหนดค่าต่างกันจะต้องใช้ไฟล์การกำหนดค่าที่แตกต่างกัน

ภายใต้สถานการณ์ปกติ ไฟล์การกำหนดค่า my-medium.cnf สามารถตอบสนองความต้องการส่วนใหญ่ของเรา โดยทั่วไป เราจะคัดลอกไฟล์การกำหนดค่าไปที่ /etc/my.cnf และจำเป็นต้องแก้ไขไฟล์การกำหนดค่านี้เท่านั้น ใช้ตัวแปร mysqladmin ขยายสถานะ –u root –p สามารถดูพารามิเตอร์ปัจจุบันได้ มีพารามิเตอร์การกำหนดค่าสามตัวที่สำคัญที่สุด ได้แก่ key_buffer_size, query_cache_size, table_cache

key_buffer_size ใช้ได้กับตาราง MyISAM เท่านั้น

key_buffer_size ระบุขนาดของบัฟเฟอร์ดัชนี ซึ่งกำหนดความเร็วของการประมวลผลดัชนี โดยเฉพาะความเร็วในการอ่านดัชนี โดยทั่วไปเราตั้งค่าเป็น 16M ที่จริงแล้วตัวเลขนี้ยังไม่เพียงพอสำหรับไซต์ที่ใหญ่กว่าเล็กน้อยด้วยการตรวจสอบค่าสถานะ Key_read_requests และ Key_reads คุณจะทราบได้ว่าการตั้งค่า key_buffer_size นั้นสมเหตุสมผลหรือไม่ อัตราส่วน key_reads / key_read_requests ควรต่ำที่สุดเท่าที่เป็นไปได้ อย่างน้อย 1:100, 1:1000 จะดีกว่า (สามารถรับค่าสถานะข้างต้นได้โดยใช้ SHOW STATUS LIKE 'key_read%') หรือหากคุณติดตั้ง phpmyadmin คุณสามารถดูได้ผ่านสถานะการทำงานของเซิร์ฟเวอร์ ผู้เขียนแนะนำให้ใช้ phpmyadmin ในการจัดการ mysql ค่าสถานะต่อไปนี้เป็นตัวอย่างการวิเคราะห์ที่ฉันได้รับผ่าน phpmyadmin:

เซิร์ฟเวอร์นี้เปิดใช้งานมาเป็นเวลา 20 วันแล้ว
คีย์_บัฟเฟอร์_ขนาด – 128M
key_read_requests - 650759289
คีย์_อ่าน - 79112
อัตราส่วนเกือบ 1:8000 และสุขภาพก็ดีมาก

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

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

ด้วยการปรับพารามิเตอร์ต่อไปนี้ คุณจะทราบได้ว่าการตั้งค่า query_cache_size สมเหตุสมผลหรือไม่
ส่วนแทรก Qcache
Qcache ฮิต
ลูกพรุน Qcache lowmem
บล็อก Qcache ฟรี
บล็อกรวม Qcache

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

ฉันตั้ง:
query_cache_size = 32M
query_cache_type= 1

รับค่าสถานะต่อไปนี้:
แบบสอบถาม Qcache ในแคช 12737 ระบุจำนวนรายการที่ถูกแคชในปัจจุบัน
ส่วนแทรก Qcache 20649006
Qcache มีจำนวนถึง 79060095 ดูเหมือนว่าอัตราการสืบค้นซ้ำค่อนข้างสูง
Qcache lowmem prunes 617913 มีหลายครั้งที่แคชต่ำเกินไป
Qcache ไม่ได้แคชไว้ 189896
หน่วยความจำว่าง Qcache 18573912 พื้นที่แคชที่เหลืออยู่ในปัจจุบัน
Qcache free บล็อก 5328 ตัวเลขนี้ดูใหญ่และกระจัดกระจายเล็กน้อย
Qcache บล็อกทั้งหมด 30953
หากหน่วยความจำอนุญาต 32M คุณควรเพิ่มอีก

table_cache ระบุขนาดของแคชตาราง เมื่อใดก็ตามที่ MySQL เข้าถึงตาราง หากมีที่ว่างในบัฟเฟอร์ตาราง ตารางนั้นจะถูกเปิดและวางไว้ในนั้น ช่วยให้สามารถเข้าถึงเนื้อหาตารางได้เร็วขึ้น ด้วยการตรวจสอบค่าสถานะ Open_tables และ Opened_tables ในช่วงเวลาเร่งด่วน คุณสามารถตัดสินใจได้ว่าจำเป็นต้องเพิ่มค่าของ table_cache หรือไม่ หากคุณพบว่า open_tables เท่ากับ table_cache และ open_tables กำลังเพิ่มขึ้น คุณจะต้องเพิ่มค่าของ table_cache (สามารถรับค่าสถานะข้างต้นได้โดยใช้ SHOW STATUS LIKE 'Open%tables') โปรดทราบว่า table_cache ไม่สามารถตั้งค่าเป็นค่าขนาดใหญ่แบบสุ่มสี่สุ่มห้าได้ หากตั้งค่าสูงเกินไป อาจทำให้ตัวอธิบายไฟล์ไม่เพียงพอ ส่งผลให้ประสิทธิภาพไม่เสถียรหรือการเชื่อมต่อล้มเหลว

สำหรับเครื่องที่มีหน่วยความจำ 1G ค่าที่แนะนำคือ 128-256

การตั้งค่าผู้เขียน
table_cache = 256

รับสถานะต่อไปนี้:
เปิดตาราง 256
เปิดตาราง 9046
แม้ว่า open_tables จะเท่ากับ table_cache อยู่แล้ว ซึ่งสัมพันธ์กับเวลาทำงานของเซิร์ฟเวอร์ แต่ก็ทำงานมาเป็นเวลา 20 วันแล้ว และค่าของ open_tables ก็ต่ำมากเช่นกัน ดังนั้นการเพิ่มค่าของ table_cache จึงควรมีประโยชน์เพียงเล็กน้อย หากค่าข้างต้นปรากฏขึ้นหลังจากทำงานเป็นเวลา 6 ชั่วโมง คุณควรพิจารณาเพิ่ม table_cache

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

log_bin ระบุไฟล์บันทึก หากไม่มีการระบุชื่อไฟล์ MySQL จะสร้างชื่อไฟล์เริ่มต้นเอง MySQL จะเพิ่มดัชนีตัวเลขหลังชื่อไฟล์โดยอัตโนมัติ และจะสร้างไฟล์ไบนารีใหม่ทุกครั้งที่เริ่มบริการ นอกจากนี้ ให้ใช้ log-bin-index เพื่อระบุไฟล์ดัชนี ใช้ binlog-do-db เพื่อระบุฐานข้อมูลสำหรับการบันทึก ใช้ binlog-ignore-db เพื่อระบุฐานข้อมูลที่จะไม่ถูกบันทึก หมายเหตุ: binlog-do-db และ binlog-ignore-db ระบุเพียงฐานข้อมูลเดียวในแต่ละครั้ง นอกจากนี้ MySQL จะเปลี่ยนชื่อฐานข้อมูลทั้งหมดเป็นตัวพิมพ์เล็ก คุณต้องใช้ชื่อตัวพิมพ์เล็กทั้งหมดเมื่อระบุฐานข้อมูล มิฉะนั้นจะไม่ทำงาน

หากต้องการปิดฟังก์ชันนี้ เพียงเพิ่มเครื่องหมาย # ข้างหน้าฟังก์ชัน
#log-bin

เปิดบันทึกการสืบค้นที่ช้า (บันทึกการสืบค้นที่ช้า)

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

หากต้องการเปิดใช้งานบันทึกการสืบค้นที่ช้า คุณต้องตั้งค่าพารามิเตอร์ log_slow_queries, long_query_times และ log-queries-not-using-indexes

log_slow_queries ระบุไฟล์บันทึก หากไม่มีการระบุชื่อไฟล์ MySQL จะสร้างชื่อไฟล์เริ่มต้นด้วยตัวเอง long_query_times ระบุเกณฑ์การค้นหาที่ช้า ค่าเริ่มต้นคือ 10 วินาที log-queries-not-using-indexes เป็นพารามิเตอร์ที่นำมาใช้หลังเวอร์ชัน 4.1.0 ซึ่งบ่งชี้ว่าแบบสอบถามที่ไม่ได้ใช้ดัชนีจะถูกบันทึกไว้ ผู้เขียนตั้งค่า long_query_time=10

การตั้งค่าผู้เขียน:

sort_buffer_size = 1M
สูงสุด_การเชื่อมต่อ=120
wait_timeout = 120
back_log=100
read_buffer_size = 1M
thread_cache=32
Interactive_timeout=120
thread_concurrency = 4

คำอธิบายพารามิเตอร์:

back_log
จำนวนการเชื่อมต่อที่ MySQL จำเป็นต้องมี วิธีนี้ใช้งานได้เมื่อเธรด MySQL หลักได้รับคำขอเชื่อมต่อจำนวนมากในช่วงเวลาสั้น ๆ จากนั้นเธรดหลักจะใช้เวลาระยะหนึ่ง (แม้ว่าจะเป็นเวลาสั้นๆ) ในการตรวจสอบการเชื่อมต่อและเริ่มเธรดใหม่ ค่า back_log ระบุจำนวนคำขอที่สามารถจัดเก็บในสแต็กในช่วงเวลาสั้นๆ ก่อนที่ MySQL จะหยุดตอบคำขอใหม่ชั่วคราว เฉพาะในกรณีที่คุณคาดว่าจะมีการเชื่อมต่อจำนวนมากในช่วงเวลาสั้นๆ เท่านั้น คุณจะต้องเพิ่มการเชื่อมต่อ กล่าวคือ ค่านี้คือขนาดของคิวการฟังสำหรับการเชื่อมต่อ TCP/IP ขาเข้า ระบบปฏิบัติการของคุณมีขีดจำกัดของตัวเองสำหรับขนาดคิวนี้ man page สำหรับการเรียกของระบบ Unix Listen(2) ควรมีรายละเอียดเพิ่มเติม ตรวจสอบเอกสารระบบปฏิบัติการของคุณเพื่อดูค่าสูงสุดของตัวแปรนี้ การพยายามตั้งค่า back_log ให้สูงกว่าขีดจำกัดของระบบปฏิบัติการของคุณจะไม่มีผลใดๆ
max_connections
จำนวนการเชื่อมต่อพร้อมกันสูงสุดคือ 120 หากเกินค่านี้ ระบบจะกู้คืนโดยอัตโนมัติและปัญหาจะได้รับการแก้ไขโดยอัตโนมัติ

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

thread_concurrency
#ตั้งค่าเป็นจำนวน cpu x2 ของคุณ เช่น ถ้ามี cpu ตัวเดียว ดังนั้น thread_concurrency=2
#มี2 cpu แล้ว thread_concurrency=4

ข้าม innodb
#ลบการรองรับ Innodb