การปรับแต่งตัวแปรของระบบ MySQL เพื่อประสิทธิภาพสูง

สำหรับนักพัฒนาแอพพลิเคชั่นส่วนใหญ่ฐานข้อมูลเป็นแท่นบูชาเทพอสูรที่ยังไม่ได้ผ่านการคัดสรร แต่มันไม่จำเป็นต้องเป็นอย่างนั้น!


สิ่งอื่น ๆ ที่เท่ากันระดับความสะดวกสบายที่นักพัฒนามีกับฐานข้อมูลพื้นฐานจะกำหนดระดับอาวุโสของพวกเขา ฐานข้อมูลขนาดเล็กและประสบการณ์การเขียนโค้ดน้อย = ผู้พัฒนารุ่นเยาว์ ฐานข้อมูลขนาดเล็กและประสบการณ์การเขียนโปรแกรมที่ดี = ผู้พัฒนาระดับกลาง ฐานข้อมูลที่ดีและประสบการณ์การเขียนโปรแกรมที่ดี = นักพัฒนาอาวุโส.

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

ทำไม?

น่าแปลกที่เหตุผลไม่ใช่ความเกียจคร้าน (แม้ว่าในบางส่วนจะเป็น).

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

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

อยากรู้อยากเห็น? มาดำน้ำกันเถอะ!

ไม่อยากรู้อยากเห็น? เอาล่ะดำน้ำเพราะอาชีพของคุณขึ้นอยู่กับมัน! ��

ปรับแคชคิวรีของ MySQL ให้เหมาะสม

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

แต่เดี๋ยวก่อนแม้แต่ฐานข้อมูล MySQL ที่ยากจนก็มีแคชคิวรีของตัวเอง! นั่นคือทุกครั้งที่คุณค้นหาบางสิ่งและข้อมูลยังคงค้างอยู่ MySQL จะแสดงผลแคชเหล่านี้แทนที่จะเรียกใช้แบบสอบถามอีกครั้งทำให้แอปเร็วขึ้นอย่างน่าขัน.

คุณสามารถตรวจสอบว่าคุณมีแคชแบบสอบถามอยู่หรือไม่ (หมายเหตุมีอยู่ไม่เปิดใช้งาน) ในฐานข้อมูลของคุณหรือไม่โดยเรียกใช้คิวรีนี้ในคอนโซลฐานข้อมูล:

MariaDB [(ไม่มี)]> SHOW VARIABLES LIKE ‘have_query_cache’;
+——————+——-+
| Variable_name | ค่า |
+——————+——-+
| have_query_cache | ใช่ |
+——————+——-+

ดังนั้นคุณจะเห็นได้ว่าฉันกำลังใช้งาน MariaDB อยู่และฉันเปิดใช้งานแคชคิวรีไว้แล้ว เป็นไปได้ยากมากที่คุณจะปิดมันหากคุณใช้การติดตั้ง MySQL แบบมาตรฐาน.

ตอนนี้มาดูกันว่าฉันเปิดแคชคิวรีจริงหรือไม่:

MariaDB [(ไม่มี)]> แสดงความหลากหลายเช่น ‘query_cache_type’;
+——————+——-+
| Variable_name | ค่า |
+——————+——-+
| query_cache_type | ON |
+——————+——-+

ใช่ฉันทำ. แต่ในกรณีที่คุณไม่สามารถเปิดได้โดยพูดว่า:

MariaDB [(ไม่มี)]> SET GLOBAL query_cache_type = ON;

ที่น่าสนใจคือตัวแปรนี้ยังยอมรับค่าที่สามซึ่งระบุว่า“ ตามต้องการ” ซึ่งหมายความว่า MySQL จะแคชเฉพาะข้อความค้นหาที่เราบอกเท่านั้น แต่เราจะไม่เข้าไปที่นี่.

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

ตัวแปรที่น่าสนใจอื่น ๆ ที่นี่คือ query_cache_size ซึ่งฟังก์ชันอธิบายตนเองได้:

MariaDB [(ไม่มี)]> แสดงความหลากหลายเช่น ‘query_cache_size’;
+——————+———-+
| Variable_name | ค่า |
+——————+———-+
| query_cache_size | 16777216 |
+——————+———-+

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

สมมติว่าคุณตั้งค่าขนาดแคชแบบสอบถามเป็น 500 KB:

MariaDB [(ไม่มี)]> SET GLOBAL query_cache_size = 500000;

ทำสิ่งนี้มากพอหรือไม่? ไม่ใช่เพราะเครื่องมือค้นหาจะสิ้นสุดการทำงานอย่างไรขึ้นอยู่กับสองสามสิ่งเพิ่มเติม:

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

คุณจะรู้ได้อย่างไรว่าแคชมีการแยกส่วน? ตรวจสอบจำนวนบล็อกทั้งหมดในแคชดังนี้:

MariaDB [(ไม่มี)]> แสดงสถานะเช่น ‘Qcache_total_blocks’;
+———————+——-+
| Variable_name | ค่า |
+———————+——-+
| Qcache_total_blocks | 33 |
+———————+——-+

หากตัวเลขสูงมากแคชจะแตกแฟรกเมนต์และจำเป็นต้องล้างออก.

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

เธรดเธรดพูลการรอและการหมดเวลา

นี่อาจเป็นส่วนที่น่าสนใจที่สุดของการทำงานของ MySQL และทำให้ถูกต้องหมายถึงการทำให้แอปของคุณเร็วขึ้นหลายเท่า!

Threading

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

เธรดพูล

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

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

ariaDB [(ไม่มี)]> แสดงตัวแปรเช่น ‘thread_pool_size’;
+——————+——-+
| Variable_name | ค่า |
+——————+——-+
| thread_pool_size | 4 |
+——————+——-+

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

อย่างไรก็ตามการตั้งค่าเกิน 16 เป็นความคิดที่แย่มากหากคุณไม่มีเครื่อง 32-core เนื่องจากประสิทธิภาพลดลงอย่างมาก หลุมกระต่ายของกลุ่มเธรดใน MySQL ลึกลงไป แต่ถ้าคุณสนใจ, นี่คือ การอภิปรายรายละเอียดเพิ่มเติม.

รอและหมดเวลา

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

MariaDB [(ไม่มี)]> แสดงตัวแปรเช่น ‘รอ%’
+—————+——-+
| Variable_name | ค่า |
+—————+——-+
| wait_timeout | 28800 |
+—————+——-+

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

ปรับแต่งตารางชั่วคราว

เริ่มต้นด้วยตารางชั่วคราวใน MySQL.

สมมติว่าเรามี MySQL ที่มีโครงสร้างดังนี้: ตาราง A UNION (ตาราง B เข้าร่วม C) นั่นคือเราสนใจที่จะเข้าร่วมตาราง B และ C จากนั้นทำการรวมผลลัพธ์กับตาราง A ตอนนี้ MySQL จะดำเนินการต่อเพื่อเข้าร่วมตาราง B และ C แต่ก่อนที่จะสามารถดำเนินการสหภาพได้ เพื่อเก็บข้อมูลนี้ที่ไหนสักแห่ง นี่คือที่มาของตารางชั่วคราว – MySQL ใช้เพื่อจัดเก็บข้อมูลในระยะกลางในการสืบค้นที่ซับซ้อนชั่วคราวและเมื่อแบบสอบถามสิ้นสุดลงตารางชั่วคราวนี้จะถูกยกเลิก.

ทีนี้คำถามคือทำไมเราต้องใส่ใจกับสิ่งเหล่านี้?

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

มีตัวแปรสองตัวที่ควบคุมพฤติกรรมนี้:

MariaDB [(ไม่มี)]> แสดงตัวแปรเช่น ‘MariaDB [(ไม่มี))> แสดงตัวแปรเช่น ‘tmp_table_size’;
+—————-+———-+

| Variable_name | ค่า |

+—————-+———-+

| tmp_table_size | 16777216 |

+—————-+———-+
‘;
+———————+———-+
| Variable_name | ค่า |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(ไม่มี)]> แสดงตัวแปรเช่น ‘tmp_table_size’;
+—————-+———-+
| Variable_name | ค่า |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

max_heap_table_size ตัวแรกบอกให้เราทราบว่าสามารถใช้ RAM ได้มากน้อยเพียงใดในตาราง MySQL (“ heap” ที่นี่หมายถึงโครงสร้างข้อมูลที่ใช้ในการจัดสรรและการจัดการ RAM – อ่านเพิ่มเติม ที่นี่) ในขณะที่ตัวที่สองคือ tmp_table_size จะแสดงขนาดสูงสุดของตารางชั่วคราว ในกรณีของฉันทั้งคู่ถูกตั้งค่าเป็น 16 MB แม้ว่าจุดที่ฉันพยายามทำให้เพิ่มขึ้นเพียง tmp_table_size จะไม่ทำงานโดยรวม แต่ MySQL ก็ยังถูก จำกัด โดย max_table_heap_size.

ตอนนี้มาถึงจุด: ถ้าตารางชั่วคราวที่สร้างขึ้นมีขนาดใหญ่กว่าขีด จำกัด ที่อนุญาตโดยตัวแปรเหล่านี้ MySQL จะถูกบังคับให้เขียนลงในฮาร์ดดิสก์ส่งผลให้ประสิทธิภาพต่ำมาก งานของเราตอนนี้ง่าย: ทำอย่างดีที่สุดเพื่อคาดเดาขนาดข้อมูลที่ถูกต้องที่สุดสำหรับตารางชั่วคราวและปรับแต่งตัวแปรเหล่านี้ให้ถึงขีด จำกัด นั้น อย่างไรก็ตามฉันต้องการระมัดระวังเรื่องไร้สาระ: ตั้งค่าขีด จำกัด นี้เป็น 16 GB (สมมติว่าคุณมี RAM มาก) เมื่อตารางชั่วคราวส่วนใหญ่ของคุณมีขนาดน้อยกว่า 24 MB นั้นเป็นเรื่องโง่เขลา – คุณเพียงแค่เสีย RAM ที่สามารถ ‘ เคยถูกใช้โดยการสอบถามหรือส่วนอื่น ๆ ของระบบ (เช่นแคช).

ข้อสรุป

เป็นไปไม่ได้ที่จะครอบคลุมตัวแปรระบบทั้งหมดในบทความเดียวหรือแม้แต่สิ่งที่สำคัญทั้งหมดในบทความเดียวเมื่อเอกสาร MySQL ครอบคลุมหลายพันคำ ในขณะที่เราครอบคลุมตัวแปรสากลบางอย่างที่นี่ฉันขอแนะนำให้คุณดูตัวแปรระบบสำหรับเครื่องมือที่คุณใช้ (InnoDB หรือ MyISAM).

ผลลัพธ์ที่พึงประสงค์ที่สุดของฉันสำหรับการเขียนบทความนี้สำหรับคุณที่จะกำจัดสิ่งที่สาม:

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

ตัวแปรระบบโปรดของคุณคือการปรับจูน ��

Tags:

  • ฐานข้อมูล

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me
    Like this post? Please share to your friends:
    Adblock
    detector
    map