Mysql innodb การแยกเก็บ ibdata ในกรณีขนาดใหญ่มาก + optimize table innodb

[golfreeze]

Andreas got this answer first in terms of what to do. +1 for Andreas !!!

I would like clarify why that answer is the only way and how to do it.

By default ibdata1 normally houses four types of information

Table Data
Table Indexes
MVCC (Multiversioning Concurrency Control) Data
Table Metadata
Running OPTIMIZE TABLE against an InnoDB table stored ibdata1 will make things worse because here is what it does:

Makes the table's data and indexes contiguous inside ibdata1
It makes ibdata1 grow because the contiguous data is appended to ibdata1
You can segregate Table Data and Table Indexes from ibdata1 and manage them independently using innodb_file_per_table. That way, only MVCC and Table MetaData would reside in ibdata1.

If you already use it, then you must have a high-write environment that stores lots of MVCC to support transaction isolation. Once the transactions holding the MVCC is done, the space is simply abandoned for re-use.

To shrink ibdata1 once and for all you must do the following:

STEP 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)

STEP 02) Drop all databases (except mysql schema)

STEP 03) service mysql stop

STEP 04) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

STEP 05) rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile

At this point, there should only be the mysql schema in /var/lib/mysql

STEP 06) service mysql start

This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each

STEP 07) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)
ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable; and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

[golfreeze]

reference : http://dba.stackexchange.com/questions/16747/mysql-clean-ibdata1/16759#16759
https://www.percona.com/blog/2013/09/25/how-to-reclaim-space-in-innodb-when-innodb_file_per_table-is-on/
http://yoshinorimatsunobu.blogspot.com/2013/10/making-full-table-scan-10x-faster-in.html

[golfreeze]

ทำการเชค engine ว่า Tables ไหนใน database เรา เป็น InnoDB หรือ Myisam บ้าง
mysql> SELECT TABLE_SCHEMA, ENGINE FROM information_schema.TABLES group by TABLE_SCHEMA, ENGINE order by TABLE_SCHEMA, ENGINE;

[golfreeze]

ปกติถ้า database ใช้เรางานกับ Innodb structure เวลาเก็บข้อมูลจะเก็บในส่วนของ ibdata1
ซึ่งในเหตุการณ์ปกติ ถ้าเรามีหลาย database มันก็จะเก็บลงไฟล์ ibdata1 และทำให้ไฟล์นี้ใหญ่ขึ้นเรื่อยๆ
และเวลาจะทำการ optimized database นั้น ทำให้ใช้เวลานานมากๆ อีกทั้งเวลา จะ track ปัญหาว่าเกิดปัญหากับ db ไหนนั้น
วิเคราะห์ได้ยากขึ้น วันนี้จะมาแนะนำวิธีการแก้ไข แยก data ออกเก็บในแต่ละ database ครับผม และทำการ clear ibdata1 ที่ใหญ่มากๆครับ

STEP 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)

STEP 02) Drop all databases (except mysql schema)

STEP 03) service mysql stop

STEP 04) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

STEP 05) rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile

At this point, there should only be the mysql schema in /var/lib/mysql

STEP 06) service mysql start

This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each

STEP 07) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

หลังจากแก้ไขเรียบร้อยจะทำให้การ optimized db นั้นทำได้ง่ายมากขึ้นครับ และเป็นการลดขนาดไฟล์ ibdata1 ที่ใหญ่ได้ด้วยครับผม : )
ทีมงานแพ็คเกตเลิฟ ไอทีเซอร์วิส แอนด์คอนซัลติ้ง จำกัด
กอล์ฟ [at] packetlove.com

[golfreeze]

###Delete bin-log for sync below command will delete all binlog before  mysql-bin.000150
mysql> PURGE BINARY LOGS TO 'mysql-bin.000150';

[golfreeze]

เราสามารถทำการ optimize table ในรูปแบบของ innodb engine ได้หรือเปล่า?

=> คำตอบคือสามารถทำได้ครับ
ในกรณีนี้ถ้ามีปัญหาเรื่องพื้นที่ ของ .ibd ที่กินพื้นที่เยอะ เราต้องทำการแยก database ออกเป็นก้อนๆก่อนนะครับ ถึงจะทำการ optimize ได้
หลังจากที่เสร็จสิ้นการแยก db ออกแล้ว ก็สามารถ optimize table ได้โดย

ในตัวอย่างเป็น db = abc , table ที่ต้องการ optimize ชื่อ = abctable นะครับ
#mysql -p
mysql>use abc;
mysql>OPTIMIZE TABLE abctable;

  ไม่ยากเลยใช่ปะครับ ถ้าtable ใหญ่ๆกว่า 10GB ก็ใช้เวลาประมาณ ไม่เกิน 10 นาทีครับ
ทั้งนี้ขึ้นอยู่กับ transaction กับ load server ด้วยนะครับผม : )

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index. This is displayed in the output of OPTIMIZE TABLE when you run it on an InnoDB table, as shown here:

mysql> OPTIMIZE TABLE foo;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+
This operation does not use fast index creation. Secondary indexes are not created as efficiently because keys are inserted in the order they appeared in the primary key. See Section 14.16.6, “Limitations of Fast Index Creation”.

InnoDB stores data using a page-allocation method and does not suffer from fragmentation in the same way that legacy storage engines (such as MyISAM) will. When considering whether or not to run optimize, consider the workload of transactions that your server will process:

Some level of fragmentation is expected. InnoDB only fills pages 93% full, to leave room for updates without having to split pages.

Delete operations might leave gaps that leave pages less filled than desired, which could make it worthwhile to optimize the table.

Updates to rows usually rewrite the data within the same page, depending on the data type and row format, when sufficient space is available. See Section 14.12.5, “How Compression Works for InnoDB Tables” and Section 14.14.1, “Overview of InnoDB Row Storage”.

High-concurrency workloads might leave gaps in indexes over time, as InnoDB retains multiple versions of the same data due through its MVCC mechanism. See Section 14.6, “InnoDB Multi-Versioning”.