การ optimize table กับการแก้ปัญหา overhead ของตารางเก็บข้อมูล

ทุกท่านทราบหรือไม่ว่า overhead ในฐานข้อมูลคืออะไร  

overhead จะเกิดขึ้นในเวลาที่เราลบ record ของข้อมูลครับ ครับ เพราะ DBMS มันไม่ลบข้อมูลออกจริง ๆ แค่ไปมาร์คที่ record ว่าได้ลบ record นี้แล้ว ทั้งนี้ก็เพื่อลด  I/O ที่จะเกิดขึ้น และเวลามี insert มันก็มา reuse พื้นที่ตรงนี้ล่ะครับ ถ้าหากมีการปรับปรุง migrate ระบบแบบหนัก ๆ ครั้งนึง ก็สมควรที่จะ OPTIMIZE ครับ

ยกตัวอย่าง     : คัดลอกบางส่วนมาจาก  http://www.narisa.com/forums/index.php?showtopic=22474

มี text file อยู่ไฟล์นึง มีข้อกำหนดว่า แต่ละบรรทัดมีจำนวนไบต์เท่ากัน คือ 40 ไบต์ ถ้าไม่ใช้ก็ใส่ช่องว่างเข้าไป (เพื่อจะได้ seek ง่าย ๆ)

001 | this is a DBMS record002 | this is another DBMS record003 | this one will be deleted soon004 | this one won't be deleted
005 | another record

เราจะลบ record 003 เราจะทำอย่างไรครับ

001 | this is a DBMS record002 | this is another DBMS record004 | this one won't be deleted
005 | another record

- ลบบรรทัดนั้นทิ้งไป ย้ายบรรทัดล่าง ๆ ขึ้นมา
เหมือนจะง่าย แต่ถ้าเรามีสัก 1 ล้าน record แล้วเรากำลังลบ record ที่ 3 แปลว่าเราอ่านและเขียนข้อมูล 1,000,000-3 = 999,997 บรรทัด หรือ 39,999,880 ไบต์ รวม 79,999,760 ไบต์เพราะต้องอ่านและเขียน วิธีนี้ไม่สวยแน่ ๆ

001 | this is a DBMS record002 | this is another DBMS record
004 | this one won't be deleted
005 | another record

- เปลี่ยนเนื้อหาบรรทัด 3 เป็นช่องว่างให้หมด
เราต้องเขียนค่า 0 ลงในบรรทัด 3 ทั้งหมด 40 ไบต์ ดีขึ้นเยอะ

001 | this is a DBMS record002 | this is another DBMS record005 | another record004 | this one won't be deleted

- ย้ายบรรทัดสุดท้ายมาแทนบรรทัดที่ 3
เราต้องอ่านบรรทัดสุดท้าย 40 ไบต์ เขียนทับบรรทัด 3 อีก 40 ไบต์ และลบบรรทัดสุดท้ายทิ้งโดยการลดขนาดไฟล์อีก 4 ไบต์ รวม 83 ไบต์ และเสียความสามารถในการเรียงลำดับไป

และวิธีสุดท้าย
เปลี่ยน ID ของบรรทัด 3 ให้เป็นค่าที่เป็นไปไม่ได้ เพื่อเขียนคราวหลังจะได้มาทับตรงนี้

001 | this is a DBMS record002 | this is another DBMS record?03 | this one will be deleted soon004 | this one won't be deleted
005 | another record

ไบต์เดียวเอง 

น่าจะพอทำความเข้าใจ เรื่อง  I/O กันแล้วนะครับ 

คราวนี้มาดูตัวอย่างกันบ้าง ผมยกตัวอย่างใน Database   HOSxP ให้ดู นะครับ
ให้ใช้คำสั่ง   show table status  

เราจะเห็น สถานะตารางข้อมูลดังภาพ จะบอกถึง ประเภท Table (MyISAM , InnoDB)  จำนวน Rows และขนาดของข้อมูล Data Length  คราวนี้สิ่งที่เราสนใจจะอยู่ที่  Field Data free ซึ่งก็คือ overhead ที่เรากำลังกล่าวถึงกัน  แสดงว่ายังมีการ จองพื้นที่ตรงนี้อยู่ทั้งที่มีการเปลี่ยนแปลงหรือลบไปแล้ว จึงทำให้เสียพื้นที่ Hard disk ไป  โดย ใน 1 ฐานข้อมูล ค่า overhead ในแต่ละ table มันอิสระ ต่อกัน เช่น ใน table a มีค่า overhead มาก ก็มีผลต่อ การ insert del เฉพาะ table นี้เท่านั้น table อื่นๆในฐานข้อมูลเดียวกันไม่เกี่ยวข้องด้วย ดังนั้นหากท่านพบข้อมูลดังกล่าวแล้ว มีวิธี แก้ปัญหาดังนี้ ....

การ OPTIMIZE ก็คือไปจัดการตรงนี้ให้มันลบจริง ๆ จะได้ประหยัดพื้นที่

จากตัวอย่าง ผมต้องการ OPTIMIZE  table  stock_item_unit_trx  ที่มี Data free ซึ่งก็คือ overhead นั่นเอง

 ให้ใช้คำสั่ง  OPTIMIZE TABLE stock_item_unit_trx ;

หมายเหตุ การ OPTIMIZE TABLE จะทำการ Lock Table นะครับ เพราะฉนั้น หากมีการ ใช้งาน ตารางข้อมูลก็จะถูก Lock ดั้งนั้นวางแผนให้ดีนะครับ

ตารางที่สามารถทำการ optimize ได้คือประเภท MyISAM, InnoDB, และ Archive

หลังจาก OPTIMIZE TABLE ลองทดสอบดูโดยใช้คำสั่ง    show table status 

จะได้ผลดังรูป  

สมมุติว่ามีข้อมูลอยู่ 5 ล้าน Record การ Optimize จะใช้เวลาเท่ากับ 5 ล้าน Record ดังนั้นระยะเวลาการทำ จะขึ้นอยู่กับข้อมูลด้วย หลังจากที่ทำการ Optimize จะเห็นว่าค่า  overhead ในฐานข้อมูลลดลง และทำให้ H/D มีพื้นที่เพิ่มขึ้นรวมทั้ง ทำให้ Performance ของระบบดีขึ้นด้วยครับ แต่สำหรับ รพ. ที่มีขนาดใหญ่ การกระทำดังที่กล่าวมาแล้วทั้งหมดก็วางแผนให้ดีด้วยครับ ...เพราะมีผลกระทบหน้างานด้วย..เพราะมีการใช้งานตลอดเวลา(การ OPTIMIZE TABLE จะทำการ Lock Table นะครับ เพราะฉนั้น หากมีการ ใช้งาน ตารางข้อมูลก็จะถูก Lock).... สำหรับตอนนี้ เอาไว้แค่นี้ก่อน บทความหน้าเรามาดูระบบการจัดการ ตาราง Log ทั้งหลาย ....(02-04-2557)


...การทดสอบต่างๆมีความเสียง ควรทำด้วยความระมัดระวัง และให้ทดสอบกับข้อมููลทดสอบก่อน เห็นผลจริงค่อยทำกับระบบจริง การจะทำหรือไม่ทำขึ้นอยู่กับบริบทแต่ละที่ครับ หากระบบทำงานได้ดีไม่มีปัญหา ก็ไม่มีความจำเป็นที่จะต้องทำ ...