Archive for the 'วิธีแก้ปัญหาเขียนโปรแกรม' Category

Tuning MySQL : Case Study OBVOC/OBHUNTER

Published by DominixZ on September 13, 2011 under VPS, วิธีแก้ปัญหาเขียนโปรแกรม, เคล็คลับซอฟต์แวร์

เนื่องจากผมไม่รู้จะยก 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 มากๆเป็นเวลานาน

My.cnf ที่ Tuning ในครั้งแรกของ OBVOC
My.cnf ที่ลองใช้ตาม Internet แต่ไม่ได้ผลใน case นี้
My.cnf ตัวสุดท้ายที่ใช้อยู่​ณ ปัจจุบันนี้

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

Published by DominixZ on September 11, 2011 under VPS, วิธีแก้ปัญหาเขียนโปรแกรม, เคล็คลับซอฟต์แวร์

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

โดย 2 ตัวล่างเราต้องมี SSH เพื่อเข้าไปสั่งมันรันด้วย perl กับ sh ได้นะครับ โดยวิธีใช้ก็เรียกชื่อไฟล์นั้นตรงๆ (ข้ออนุญาติไม่สอนแต่มันไม่ยากเลยนะ :D ) เสร็จแล้วเราจะเจออะไรทำนองแบบใน ลิงค์นี้ ทีนี้เราต้องทำความเข้าใจว่าทำไม 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 ที่เขาให้มาถือว่าดีแล้ว แต่จะเหมาะกับเราหรือไม่ต้องตรวจสอบดูกันเองนะครับ

Next Page »