Tips for speeding up dump and import data in MySQL

Posted In Database - By Tum. On Monday, July 8th, 2013 With 0 Comments

mysql-logo
เมื่อต้อง import ข้อมูล SQL จำนวนมหาศาลเข้าไปยัง MySQL server โดยปกติแล้วจะใช้เวลาในการ import ค่อนข้างนาน แต่ก็สามารถช่วยเร่ง speed การ import ให้ไวมากขึ้นกว่าเดิมได้โดยใช้ tip เล็กๆ น้อยๆ ดังต่อไปในในการ import ข้อมูล SQL จำนวนมหาศาล

ปิดการใช้งาน index ชั่วคราว (Disable index)
การปิดการใช้งาน index ในระหว่างการ import ข้อมูลจะช่วยเพิ่มความไวในการ import มากๆ และยังช่วยลดการทำงานของ MySQL server ด้วย ทำให้ระบบไม่ได้รับผลกระทบเมื่อระบบ online และจำเป็นต้องทำการ import ข้อมูลไปด้วย

ก่อนทำการ import ข้อมูลให้ทำการ Disable index ด้วยคำสั่ง

หลังจาก import ข้อมูลเรียบร้อยแล้วให้ทำการ Enaable index ด้วยคำสั่ง

* แทน table_name ด้วย ชื่อ table ที่มี index

Tip สำหรับ table ที่ใช้ MyISAM
ให้ใช้ INSERT DELAYED แทนการใช้ INSERT แบบทั่วไป ซึ่งจะช่วยให้ไม่ต้องรอการตอบกลับจากระบบเมื่อทำการ insert เสร็จแล้ว จึงช่วยเพิ่มความรวดเร็วในการ import ได้ในระดับหนึ่ง

ถ้าหากเป็น table ที่ใช้ InnoDB จำเป็นจะต้องรันคำสั่งเพิ่ม
ให้ปิดการ autocommit mode เพื่อป้องกันไม่ให้ log flush ไปยัง disk ทุกๆการ imsert ข้อมูล
ถ้าหากมี UNIQUE constraint ก็สามารถปิดการใช้งานชั่วคราวเพื่อช่วยเพิ่ม speed ในการ import ข้อมูล ระบบจะได้ไม่ต้องตรวจสอบข้อมูลในขณะที่ import ข้อมูล
ถ้าหากมี FOREIGN KEY constraints ในตาราง ให้ปิดการใช้งานชั่วคราวเพื่อช่วยเพิ่ม speed ในการ import ข้อมูล ระบบจะได้ไม่ต้องตรวจสอบ foreign key ในขณะที่ import ข้อมูล

ให้รันคำสั่งต่อไปนี้ก่อน import ข้อมูล

หลังจาก import ข้อมูลเรียบร้อยแล้วให้รันคำสั่งดังนี้

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

*