Tuning SQL [Part 1]

หลังจากที่ผมได้เปลี่ยนงานใหม่มาได้ประมาณ 2 เดือน มีประสบการณ์หลายอย่างที่ได้ทำในบริษัทแห่งหนึ่ง เป็นสิ่งที่ท้าท้ายมาก

เริ่มด้วยการแก้ปัญหาของ Stored Procedure ที่เป็นปัญหามายาวนาน คำถามแรกที่ผมมีคือ ถ้าแก้ง่าย ทำไมไม่มีใครแก้ หรือบางทีอาจจะแก้ง่ายแต่ไม่อยากทำ regression test “ข้อมูลใน database มีข้อมูลวิ่งเข้าเป็นหลักล้านต่อวัน”

Problem

  • Large Data จำนวน record ต่อวันที่มีเยอะมาก ประมาณ 2 ล้าน record ต่อวัน ข้อมูล หรืออาจจะมากกว่านั้น
  • Execute แล้ว DB Procedure ตาย ต้อง run ซ่อม

Root Cause

  • Query Not optimize: ไม่มีการระบุ partition ในการ query ทำให้ในการ query แต่ละครั้งมีค่า Cost ที่สูง ทำกิน resouce ของ DB Server ค่อนข้างเยอะ สุดท้ายส่งผลให้ response time สูงขึ้นเป็นเงาตามตัว

Solve

  • ระบุ partition ใน query เพื่อเพิ่มประสิทธิภาพในการ query (way 1)

แบบไม่ใช้ partition

mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
-> FROM employees ORDER BY lname;

แบบใช้ partition

mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
-> FROM employees PARTITION (p0) ORDER BY lname;

Partition มันมีดีอย่างไร

Database Engine เวลา query จะรู้ว่า data ชุดดังกล่าวอยู่ตรงส่วนใดของ Table ลองมองนึกภาพ หากมีข้อมูลจำนวน 100 ล้าน rows ถ้าไม่มีการจัดการเรื่อง partition Query จะหาจาก 100 ล้าน rows แต่ถ้าหากมี partition ก็อาจจะหาจากแค่ 8.33 ล้าน (100/12) rows ก็อาจเป็นได้

Table with out Partition VS with Partition

Notes

  • หากออกแบบ Database schema ดี จะไม่เป็นภาระแก่ลูกหลาน ควรออกแบบเผื่อคนที่จะ Query ควรรู้ว่าเวลานำไปใช้ จะใช้อะไรบ้าง
  • Partition ใน database จะถูกจัดการให้อัตโนมัติ ตาม condition ในการสร้าง แต่เวลาใช้งาน ต้องระบุ partition ใน query เพื่อให้ Database Engine รู้ว่า data ที่คุณต้องการค้นหา อยู่ในส่วนไหน ของ Table
  • หาก Database เป็น DB เป็นลักษณะ remote link จะไม่สามารถ query โดยใช้ชื่อ Partition ได้ ต้องระบุเป็น เป็น Partition ID แทน “case นี้เจอใน oracle”
  • อยากรู้ว่าช้าเพราะอะไรใช้ Explain plan
  • Index ใส่มั่วซั่ว วันนี้อาจจะเร็ว แต่วันข้างหน้า จะมีปัญหา เลือกใช้กับจุดที่ควรใช้
  • PostgreSQL version เก่าๆจะมีปัญหา ในการใช้ count แค่ count ก็ช้าได้ ถ้าเป็นข้อมูลที่ไม่ซีเรียจมาก ก็ใช้ ลองใช้ count_estimate
อีกวิธีในการแก้ปัญหา
แก้ด้วยเงิน เปลี่ยน Harddisk ของ DB Server เป็น SSD เพื่อให้มีค่า IOPS สูง
Knowledge ส่วนนี้ต้องขอบคุณ บริษัทคนไทยรายใหญ่แถวๆ เมืองเอก , บริษัทคนญีปุ่นแห่งหนึ่งย่านสีลม และบริษัทเอเจนทัวร์แห่งหนึ่งในภูเก็ต สุดท้ายต้องขอบคุณบริษัทแห่งนี้ ที่ให้โอกาสเรา