Tuning MySQL : สำรวจตัวเองและเข้าใจตัวแปร

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

โดย 2 ตัวล่างเราต้องมี SSH เพื่อเข้าไปสั่งมันรันด้วย perl กับ sh ได้นะครับ โดยวิธีใช้ก็เรียกชื่อไฟล์นั้นตรงๆ (ข้ออนุญาติไม่สอนแต่มันไม่ยากเลยนะ) เสร็จแล้วเราจะเจออะไรทำนองแบบใน ลิงค์นี้ ทีนี้เราต้องทำความเข้าใจว่าทำไม Query เราช้าเป็นเพราะอะไร ดังนั้นก่อนจะสำรวจตัวเองคุณต้องเข้าใจเรื่องหลักๆของ MySQL หรือ RDBMS ทั่วไปและ Server กันก่อน ผมจะขอรวบรัดใครอยากรู้ลึกๆไปศึกษากันต่อนะครับ

  • เวลาเรา Query ข้อมูลปกตินั้นก็เหมือนเรามีกองเอกสารที่ไม่ได้มีการจัดเรียงวางกองๆไว้ เวลาจะหาเราก็เลยต้องรื้อถึงจะเจอ ทำให้ไม่มีประสิทธิภาพ
  • ถ้าเรานำเอกสารมาเรียงๆก็สามารถถูกหาได้ง่ายขึ้นกว่ากองเอกสารมั่วๆ สิ่งนี้ใน MySQL เรียกว่า “Optimize Table” โดย Script ที่ผมนำมาใช้คือ Optimize เฉพาะ Fragment หรืออันที่ไม่ได้มีการเรียงข้อมูล ซึ่งเราจะพบได้ง่ายมากใน Table ขนาดใหญ่ ยังไงก็ระวังกันนิดหนึ่งคือถ้าไป Optimize Table ใหญ่ก็เสียเวลาหน้าดู
  • ต่อให้เรียงแล้วแต่ถ้าเราอยากจะหาเอกสาร ABC ได้ง่ายขึ้นเราก็จะแปะ PostIt สีๆให้ออกมาข้างๆหรือใส่ที่คั่นหนังสือใน DB เราเรียกว่า “Index”
  • แต่การจะหยิบข้อมูลเอกสารพร้อมๆกันหลายๆอันได้นั้นจะต้องมีความจำที่เพียงพอว่าแต่ละเอกสารนั้นอยู่ที่ไหน (Index อยู่ไหน) ทำให้ต้องใช้ความจำเข้ามา แล้วถ้าเราจำที่อยู่ของแต่ละเอกสารนั้นไม่ได้ เราก็ต้องค่อยๆเปิดทีละกองสองกอง (Key Buffer < Key Index ) ทำให้ช้าลง
  • อีกเรื่องที่ทำให้ช้าได้คือ “พื้นที่โต๊ะไม่พอ” เหมือนกองเอกสารที่เราเก็บใส่แฟ้มแล้วไปว่างไว้ที่อื่น แต่ไม่ใช่บนโต๊ะเวลาจะนำมาใช้ก็ต้องเดินไปหยิบ ดังนั้นถ้าโต๊ะเราสามารถเก็บกองเอกสารทั้งหมดได้ เราก็ไม่ต้องเดินไปหยิบพอเต็มก็ทำให้เสร็จ แล้วก็เดินไปหยิบใหม่ นั้นเป็นที่มาของ “tmp_table_size”
  • ส่วนตัวแปรอื่นๆที่มีผลในการทำเอกสารนั้นประกอบด้วย การอ่านเอกสาร (read_buffer_size) , การเรียงเอกสาร (sort_buffer_size) , การรวมเอกสาร (join_buffer_size) และการอ่านเอกสารที่สุ่มมา (read_rnd_buffer_size) โดยอันสุดท้ายมักจะเยอะขึ้นถ้ามีการ ORDER BY เยอะๆ
  • สุดท้ายการจะทำงานได้เร็วคุณต้องทำงานเป็นทีมดังนั้น อย่าลืมจ้างพนักงานเพิ่ม​โดยต้องจ่ายเป็นเงินเดือน (CPU/Mem) โดยพนักงานมีชื่อเรียกใน DB ว่า (thread_concurrency) ถ้าเราอยากให้ผู้ช่วยเก่งๆ ก็ต้องฝึก การอ่านเอกสาร (read_buffer_size) , การเรียงเอกสาร (sort_buffer_size) , การรวมเอกสาร (join_buffer_size) และการอ่านเอกสารที่สุ่มมา (read_rnd_buffer_size) ซึ่งยิ่งพนักงานเก่งก็ต้องยิ่งจ่ายแพง (จ่ายแพงโดยเฉพาะ Memory)

หลักๆการ Tuning MySQL ก็จะมีเรื่องประมาณนี้ครับ โดยเราไปดูวิธีตรวจสอบว่าส่วนไหนของระบบเราช้ากันครับ โดยจะเป็นภาษาเทคนิค ถ้าฟังไม่เข้าใจก็ขออภัยนะครับ >.<

จบไปแล้วกับการอธิบายวิธีดูว่าส่วนไหนช้าและตัวแปรแต่ละตัวใน my.cnf เอาไว้ทำอะไร โดยผมเอาต้นแบบ config มาจาก Custom MySQL for ensure maximum performance ครับ โดยเท่าที่ลองดู Config แล้วมันเหมาะสำหรับเว็บทั่วไป , Blog , Webboard อะไรทำนองนี้ซึ่งข้อมูลแต่ละ Row ไม่ได้มีขนาดใหญ่มาก และไม่ได้ใช้ Fulltext แล้วเน้นไปที่มีคนเข้ามาใช้จำนวนมาก ซึ่ง Config ที่เขาให้มาถือว่าดีแล้ว แต่จะเหมาะกับเราหรือไม่ต้องตรวจสอบดูกันเองนะครับ


Originally published at dominixz.com.

Like what you read? Give DominixZ a round of applause.

From a quick cheer to a standing ovation, clap to show how much you enjoyed this story.