Tuning MySQL : Case Study OBVOC/OBHUNTER

เนื่องจากผมไม่รู้จะยก Case ไหนดีมาเป็นตัวอย่างการทดลองให้ดู ผมก็เลยยก Case ที่พึงเกิดขึ้นกับตัวสดๆร้อนๆคือการ Tuning MySQL สำหรับระบบ Bot Monitoring บนโลก Online โดยคราวนี้หนักกว่าเดิมคือปกตินั้น Server จะมีแค่ OBVOC กับ Sodia เท่านั้นที่ดูแลแต่วันนี้มันมี OBHUNTER ซึ่งตอนแรกอยู่เครื่องภายในแต่วันนี้มันได้มาอยู่บน Server จริงๆแล้ว อธิบายง่ายๆเหมือนมี Transaction เพิ่มขึ้นมากกว่าเดิม 3 เท่า !! ดังนั้นเรื่องที่ผมจะยกขึ้นมาวันนี้คือเรื่องนี้ครับ โดยสภาพแวดล้อมคร่าวๆมีดังนี้

  • OS : Centos 5 with DirectAdmin
  • PHP 5.3.8 , MySQL 5.1
  • Server : CPU 8 Core , Ram 16GB , HDD 250GB

ผมโชคดีอย่างหนึ่งที่ได้ Server ใหม่มา ไม่งั้นไม่มีทางทำสำเร็จได้แน่ในคราวนี้ โดยปัจจัยแรกที่สำคัญสำหรับระบบที่ใหญ่ขึ้นคือ “Server” แต่เชื่อเถอะว่า ถึงแม้มันจะพร้อมแต่บางครั้งคุณก็ไม่สามารถปรับ Performance ให้สุดได้เพราะ Ram นั้นต่อให้มี 128GB ถ้าเรา config มั่วๆก็หมดได้อยู่ดีครับ ลองดู Case ผมเป็นตัวอย่างแล้วกันครับ โดยผมจะบอกเรื่องราวก่อนและหลัง แล้วตอนท้ายไปชม Video กันนะครับ

ก่อนหน้านี้ที่มี OBVOC ตัวเดียว

  • มีการอัพเดต Row ที่ขนาด 10kb ขึ้นไปเพราะเป็นการดูดกระทู้ เช่น Pantip , Sanook เข้าระบบ
  • ใช้ MyISAM Fulltext เพราะ Implement ได้ง่ายกว่า Sphinx , Solr แต่ปัญหาคือ Index จำนวนมาก
  • ข้อมูลเริ่มเต็ม Table เดียวก็ 16gb แล้ว
  • ถึงแม้จะ insert / update บ่อยแต่ก็ยังรับไว้ในเครื่อง ram 3gb
  • เวลาจะดู Dashboard ในหน้า OBVOC บางครั้งใช้เวลาถึง 30–60 วินาที

หลังมี OBVOC และ OBHUNTER ร่วมกัน

  • Transaction มากกว่าเดิม 3 เท่า
  • มีการ Access Disk มากกว่าเดิมมากๆ เพราะ OBHUNTER เป็นการอัพเดตข้อมูลไม่เล็กไม่ใหญ่ แต่ถี่ๆ เช่นตัด comment จาก pantip ออกมา insert
  • Table ที่โตวันโตคืนเป็นคู่แข่ง table ของ obvoc ทำให้ไฟล์ใหญ่ขึ้นมาก
  • OBHUNTER ได้ใช้สถาปัตยกรรมคล้ายๆกับ OBVOC ก็คือใช้ Fulltext ซึ่งปัญหาคือ Index มันมากๆๆๆ กว่าเดิมเข้าไปอีก
  • ระบบทั้งหมดช้าลง แล้วเกิดอาการ Hang เพราะมีการ Access Disk เยอะจนไม่สามารถแม้กระทั่งเข้า FTP/SSH (Write Access 99%)
  • บางครั้งต้อง Repair พร้อมกับเปิด Service เพราะกลัวเรื่องข้อมูลเก็บได้ไม่หมดจากบน Social Media

ปัญหาที่เกิดระหว่างการ Tuning

  • ลองผิดลองถูก ทำให้บางครั้งการแก้ปัญหาไม่ถูกจุดทำให้ระบบแย่กว่าเดิมต้อง Restart VM กันเลย
  • เจอ Bug ที่ประหลาดมากนั้นคือ “Crond” เกิดขึ้นมาเป็นจำนวนมากกว่า > 1000 เคยหลับไปแล้วตื่นขึ้นมา ram 16gb เต็ม งงกันไปเลย
  • Table ขนาด 16gb พังแค่กด stop start mysql ! ได้บทเรียนว่าก่อน start stop ควร kill ทุก process ที่เข้ามาใน db ไม่งั้นมีโอกาสพัง confirm !! (โดยพี่ @icez บอกเพิ่มอีกนิดว่าอาจจะเป็น Distro ของผมที่ใช้ Centos with DirectAdmin ทำให้มันรวนได้ง่ายขนาดนี้ ดังนั้นใครลง Distro แนะนำว่าลงเองดีกว่า !! ถ้าคิดการใหญ่)
  • การ Repair Table แบบผิดวิธีคือ เข้าใจผิดว่า Repairing by Keycache นั้นเร็วกว่า Repairing by Sorting จริงๆผิดครับ Sorting เร็วกว่า แล้วถ้าจะให้ดีต้องมี Thread ด้วย ดังนั้นก่อน Repair ควรปรับ config key_buffer_size=2048M sort_buffer_size=2048M read_buffer_size 512 แล้วก็ write_buffer_size 512 ไม่งั้นช้ามาก + ผลพวงที่ไม่ทำแบบนี้อาจทำให้เกิด Write Access 99% ทำให้ server พังเดี้ยงได้ อาจะเพิ่มเติมได้ที่ ลิงค์นี้
  • ระหว่าง config มั่วๆถ้าไม่ set connection timeout แล้วละก็ ram จะหมดรวดเร็วอย่างไม่รู้ตัว (ของผมหมดเร็วเห็นได้ชัดเพราะบอทมันกระหน่ำเข้ามา) โดยตอนนี้ ผมเลยปรับ config เป็นแบบนี้เลย interactive_timeout=100 , wait_timeout=100 ,connect_timeout=10
  • การปรับ Tuning MySQL ไม่ดีดูง่ายๆ ถ้าปิด MySQL แล้วระบบมีการ Write Access เท่าไร แล้วเมื่อเปิด MySQL เท่าไร ถ้าเลขมันมากหลังเปิด MySQL นั้นและชัดเจน !
  • ถ้ายังอยากใช้ MySQL ต่อไปต้องเข้าใจ InnoDB กับ MySQL โดยผมเจอปัญหาหนึ่งที่แก้เท่าไรก็ไม่ได้สุดท้ายเลยต้องเปลี่ยน Storage Engine ถึงบรรเท่าปัญหานี้ได้
  • low_priority_updates = 1 นี้คือท่าสุดท้าย สำหรับคนใช้ MyISAM ถ้าแก้ปัญหาไม่ได้สักที ลองเปลี่ยน config นี้ดูครับ โดยเป็นการให้ความสำคัญกับ UPDATE SQL น้อยลงครับ โดยปกติแล้ว UPDATE จะสำคัญกว่า SELECT ที่มันน่าเซ็งก็คือ ถ้า UPDATE มาแล้วมี SELECT ตาม UPDATE จะช้าลงครับ (มันคือเรื่องจริงครับลองมาเยอะแล้วสำหรับ MyISAM) โดยถ้าปกติมีอย่างใดอย่างหนึ่งจะเร็วทั้งคู่แต่ถ้ามาพร้อมกันเมื่อไร ช้าเลย Lock ด้วย ผมเลยแก้ปัญหาคือให้ SELECT ไปก่อน เพราะถ้าเรา Tuning ดีแล้ว SELECT จะเร็วปรี๊ดแล้วระบบก็จะได้เอาเวลาไปทำ UPDATE ได้เร็วกว่าเดิม ลองแล้ว confirm ว่าเร็วกว่าให้มันเป็นแบบปกติใน Table MyISAM แต่มีผลเสียคือ “บางครั้ง User กด Update ไปแล้ว แต่มันไม่ได้ทำงานทันที เลยเห็นเป็นข้อมูลเก่าอยู่ๆทั้งที่ส่ง request ไปแล้ว”
  • Log คือศัตรูตัวฉกาจสำหรับระบบที่มีการ Insert/Update เยอะๆ เพราะมันคืออีก 1 ตัวที่ทำให้เกิด Write Access ว่ากระนั้นผมเลยปิด Log หมดเลย (แต่ปิดตอนที่เริ่ม Tuning เริ่มดีแล้วนะ ไม่ใช่ปิดแต่เริ่ม) ผมปิดหมดเลยครับทั้ง log-queries-not-using-indexes , log , log-slow-queries , log_error , log_warnings ปิดหมด แต่ตัวที่ห้ามปิดเด็ดขาดคือ log_bin เพราะเจ้านี้สำคัญมากเวลาจะกู้ข้อมูลจากการ Repair ดังนั้นอย่าปิดยอมๆไป
  • MySQL.. ERROR! Manager of pid-file quit without updating file. เวลา restart mysql นั้นส่วนใหญ่มาจากเขียน config ซะมากกว่า pid ตัวนั้นค้างอยู่ในระบบ
  • Read Buffer Size เป็นตัวเดียวที่ใส่เป็นแค่ 8MB ก็เพียงพอแล้ว ที่เหลือต้องแล้วแต่ระบบ
  • การทำ Index เยอะใช่ว่าจะดี เพราะมันคือการเพิ่ม Write Access !!!! ดังนั้นถ้าตัวไหนไม่ค่อยได้ใช้ให้ Remove Index ทิ้งซะ ผมดันสร้างตัวทำ autoindex มันเลยทำ index ให้ทุก field เลยผลที่ได้ช่วงแรกคือเร็ว แต่พอระบบใหญ่ก็เป็นดังที่เห็น >.<’
  • หลงผิดเชื่อในค่าตัวเลข ที่ฝรั่งแนะนำว่าอย่าเกินเท่านี้นะ มันจะมีผลต่อ Performance Issue ทำให้เราไม่กล้าปรับ จนสุดท้ายมันก็ต้องปรับ แล้วมันก็ดีขึ้นจริงๆด้วย !!
  • กด Repair Table ไปแล้ว Bug Crond มันเกิดขึ้น ไม่มีหน้าจอที่เข้า SSH ไว้สุดท้ายเลยต้องมานั่ง Kill มือเพราะมันเกิดตอนจังหวะ Repair Table ไปแล้ว 3 ชั่วโมง !! + ทำใหม่มาหลายรอบเลยใช้ DirectAdmin ช่วยแล้วก็ใช้ jQuerify ของ Firefox เข้าช่วยโดย login เป็น root แล้วเปิดไปหน้าที่มี crond เยอะๆแล้วพิมพ์ตัวนี้ลงไปใน firebug console “$(“tr td.list:contains(‘crond’)”).each(function(){ $(“input”,$(this).parent()).attr(“checked”,true) });$(“tr td.list2:contains(‘crond’)”).each(function(){ $(“input”,$(this).parent()).attr(“checked”,true) });” เสร็จแล้วคลิก kill แล้วกดลบ
  • Optimize Table ทำให้เร็วขึ้นมหาศาลอย่างเห็นได้ชัด แต่ถ้ายัง Tuning ไม่นิ่งก็อย่าพึง Optimize เลยเพราะถ้า Alter หรือ Repair ก็ต้องกดใหม่อยู่ดี

ความโชคดีที่ผมหาความต้องการของระบบผมเจอ

  • ระบบผมมีคนใช้แค่ 10–20 คนเท่านั้น ที่เหลือเป็นบอท ทำให้ Max Connection สามารถปรับลดลงได้
  • ผมมี Key Size ทั้งหมด 6.8GB โดยผมลองปรับเรื่อยๆจนมาลงตัวที่ 3GB ซึ่งคลอบคลุม 95% ของระบบแล้ว ซึ่งพอแล้วเอา Ram ไปเพิ่มตัวอื่น
  • ผมรู้ว่าระบบผมมีการ JOIN และ SORT บ่อยมาก ผมเลยค่อยๆเพิ่มค่า Buffer และ Test จนลงตัว
  • Coding บางส่วนมีผลต่อ Performance ของระบบมาก โชคดีที่ผมเขียนมันขึ้นมาเองเลยจำได้ว่าอยู่ส่วนไหน เลยแก้ได้ถูกจุด

บทสรุปของการ Tuning ในครั้งนี้ คือเราต้องคอย Monitor ตัวเลขต่างๆ ค่อยๆปรับทีละนิดละนิด โดยแต่ละครั้งที่จะ stop อย่าลืม kill ตัวที่มันมา access database ก่อน แล้วคอยใช้คำสั่ง top ของ linux ดูด้วยว่า write access ลดลงแล้วหรือยังกับการ insert/update ปริมาณเดิม ถ้าไม่ลดต้องปรับใหม่ โดยอย่าให้มัน Write Access ทุก Core เยอะกว่า 30% (แต่ถ้ามัน 99% ไม่เกินครึ่งหนึ่งของจำนวน CPU core ก็ปล่อยๆมันไปได้) สุดท้ายท้ายสุดก็คืออย่าใช้ทั้ง Server เกิน 60–70% ของ RAM (ต้องลอง run สัก 24 ชั่วโมงด้วย) เพราะถ้าเหลือพื้นที่ RAM ไว้น้อยกว่านี้เมื่อเกิดมี process ตัวใดเกิดขึ้นแล้วกิน ram เราไปจนหมด เราเข้า SSH/FTP แถม Write Access 99% ผลสุดท้ายคือต้องโทรไปบอกทาง Server ว่ากด Restart ที่เครื่องให้หน่อย ทำให้เกิดความล่าช้าในการ Tuning แถมระบบก็ล่มหมดอีกคราวนี้ ไม่คุ้มกันนะ ที่เหลือก่อนไปชม video ด้านล่างกันผมขอพูดคำๆหนึ่งเลยว่า

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

ข้อควรจำ : อย่าใช้ RAM เกิน 70% โดยไม่จำเป็น , อย่าให้เกิด Write Access มากๆเป็นเวลานาน


Originally published at dominixz.com.

Show your support

Clapping shows how much you appreciated DominixZ’s story.