Latest article
Home >> Database >> สาเหตุ การแก้ไขไฟล์ ibdata1 บน MySQL มีขนาดใหญ่
สาเหตุ การแก้ไขไฟล์ ibdata1 บน MySQL มีขนาดใหญ่

สาเหตุ การแก้ไขไฟล์ ibdata1 บน MySQL มีขนาดใหญ่

คำถามยอดฮิตของ MySQL คำถามหนึ่ง คือ

  1. ทำไมเวลา drop table หรือ database แล้วพื้นที่ไม่ลดลงหรือลดลงน้อยกว่าพื้นที่จริง
  2. ไฟล์ ibdata1 ใน MySQL สามารถลดขนาดได้หรือไม่

ไม่น่าเชื่อว่าคำถามทั้งสองนี้มีความเกี่ยวข้องกัน เพราะว่าพื้นที่ที่ถูกใช้โดย InnoDB’s shared tablespace บน MySQL (ibdata1) จะไม่สามารถคืนพื้นที่ได้ และถ้าหากไม่มีการเปิดการใช้งาน innodb_file_per_table ด้วยแล้วพื้นที่จะสามารถเรียกคืนได้น้อยมากหรือไม่ได้เลย

InnoDB’s shared tablespace (ibdata1) คืออะไร

InnoDB’s shared tablespace (ibdata1) คือ พื้นที่ที่ใช้เก็บข้อมูล tablespace (ทั้งหมด), data dictionary (metadata), change buffer, doublewrite buffer และ undo logs และถ้าหากเปิดใช้งาน innodb_file_per_table จะถูกแบ่งออกเป็น 2 ส่วน คือ

  1. ส่วนที่จัดเก็บ tablespace ของ table จะถูกจัดเก็บแยกออกมาและถูกเก็บเป็น TABLENAME.ibd ไว้ที่ folder database นั้นๆ
  2. ส่วน tablespace บางส่วน, data dictionary (metadata), change buffer, doublewrite buffer และ undo logs* จะถูกเก็บไว้ที่ InnoDB’s shared tablespace (ไฟล์ ibdata1) เช่นเดิม
undo logs มีการใช้พื้นที่สำหรับเก็บ transaction ที่ยังไม่ได้ถูก commit ดังนั้นหากมีจำนวน undo log มากจะทำให้ไฟล์ ibdata1 โตขึ้นเรื่อๆได้ และใน MySQL 5.6 ก็มี option สำหรับแยก undo logs ให้เก็บข้อมูลแยกออกมาจาก ibdata1

เราสามารถทำให้ไฟล์ ibdata1 มีขนาดเล็กลงได้ไหม?

ไม่สามารถทำได้ เนื่องจากว่าพื้นที่ที่ถูกลบไปจะถูก marked as deleted เท่านั้นไม่ได้ลบออกจริงๆ และพื้นที่ดังกล่าวจะถูกนำกลับไปใช้ใหม่อีกครั้ง

วิธีแก้ไขไฟล์ ibdata1 โตขึ้นเรื่อยๆ

วิธีที่ 1
เปิดใช้งาน innodb_file_per_table ซึ่งหลังจากเปิดใช้งานแล้ว MySQL จะนำข้อมูลไปเก็บไว้ที่ไฟล์ .ibd แทน แต่วิธีนี้จะไม่สามารถลดขนาดของไฟล์ ibdata1 ได้

วิธีนี้จะทำให้ performance drop ลงไปเล็กน้อยเนื่องจากทำให้เกิด fragment บน data แต่เมื่อเทียบกับการดูแลรักษาที่ง่ายกว่าแล้ว ก็คุ้มค่าที่จะใช้งาน 🙂

วิธีที่ 2
ทำการ export ข้อมูลทั้งหมดออกมาด้วยคำสั่ง mysqldump, ลบข้อมูล และ import ข้อมูลเข้าไปใหม่

Incoming search terms:

  • ibdata1 mysql คือ
  • ibdata1 คืออะไร
  • ibdata1 ใหญ่
  • transaction log file ทำไมไม่โต
  • ทำให้ mysql เล็กลง
  • ลดขนาด database mysql
  • ลดขนาด mysql
  • วิธีจำกัดขนาด export mysql
  • วิธีเคลีย ibdata1
The following two tabs change content below.
Error: Unable to create directory wp-content/uploads/2018/11. Is its parent directory writable by the server?
I'm interested in: Database: MySQL and MariaDB, WebScaleSQL, SQL Tuning, Database sharding Search engine: Elasticsearch Web: Apache tuning, web optimization and SEO Platform: log platform
Error: Unable to create directory wp-content/uploads/2018/11. Is its parent directory writable by the server?
Facebook Iconfacebook like buttonTwitter Icontwitter follow button
Copy Protected by Chetan's WP-Copyprotect.