Mysql InnoDB Expansion and ibdata1 file slimming scheme complete Analysis _mysql

MySQL's InnoDB expansion
in order to add a data file to the tablespace, first close the MySQL database, edit the my.cnf file, confirm the InnoDB ibdata file and MY.CNF configuration is consistent, there are two situations:
Configuration of 1.MY.CNF

Innodb_data_file_path=ibdata1:10g;ibdata2:10g:autoextend 

If the current database is using IBDATA1, or if you are using IBDATA2, but the ibdata2 does not exceed 10G, the MY.CNF configuration is changed directly to:

Innodb_data_file_path=ibdata1:10g;ibdata2:10g;ibdata3:10g:autoextend 

2. If the last Ibdata automatic extension is set, it is possible that the last Ibdata occupies more space than the MY.CNF configuration space. For example:

 Mysql@test:/data1/mysqldata/innodb/data> LS-LH 

-RW-RW----1 mysql mysql 10737418240 2010-01-26 16:34 ibdata1-rw-rw 
----1 mysql mysql 16106127360 2010-01-26 ib Data2 

At this point, you need to accurately calculate the size of the Ibdata2 15360M, modify:

Innodb_data_file_path=ibdata1:10g;ibdata2:15360m;ibdata3:10g:autoextend 

Restart MySQL.
Note:
1, before the expansion of the attention to disk space is enough.
2, restart after the focus on whether to generate a new ibdata.
More Description:
If the last file is described as a keyword autoextend, in the process of editing my.cnf, you must check the size of the last file and close it to a multiple of 1024 * 1024 bytes (= 1 MB) (for example, now Autoextend Ta/ibdata1 is 18.5M, and in the old My.ini 10M, it needs to be modified to Innodb_data_file_path =/ibdata/ibdata1:19m; and must be 19M, if you specify 20M, you will get an error. and specify its dimensions explicitly in Innodb_data_file_path. Then you can add another data file. Remember that only the last file in Innodb_data_file_path can be specified as auto-extending.
One example: Assuming that at first there was only one auto-extending data file ibdata1, the file was close to 988 MB. The following is a possible example after adding another auto-extending data file.

Innodb_data_home_dir = 
Innodb_data_file_path =/ibdata/ibdata1:988m;/disk2/ibdata2:50m:autoextend 

Ibdata1 Thin Body

0. What's in the ibdata1?

When you enable innodb_file_per_table, tables are stored in their own table space, but shared tablespaces still store other InnoDB internal data:
(1) The data dictionary, which is the metadata of the InnoDB table
(2) Change buffer
(3) Double write buffer
(4) Cancellation log

Some of these can be configured on Percona servers to avoid growing too large. For example, you can set the maximum change buffer by innodb_ibuf_max_size or set the Innodb_doublewrite_file to store the double write buffer in a separate file.

MySQL version 5.6 You can also create external undo table space, so they can be placed in their own files to replace storage to ibdata1.

1. What causes ibdata1 to grow rapidly?

When MySQL comes up with a problem, the first command we need to execute is usually:

Show ENGINE INNODB status/g

This will show us some valuable information. We'll start with the * * TRANSACTION section, and then we'll find this:

---TRANSACTION 36E, ACTIVE 1256288 sec
MySQL thread ID, OS thread handle 0x7f8baaccc700, query ID 7900290 localhost Root show
engine InnoDB status
Trx read view would not? Trx with ID >= 36F, sees < 36F

This is one of the most common causes, a fairly old transaction created 14 days ago. This state is active, which means that InnoDB has created a snapshot of the data, so the old pages need to be maintained in the Undo log to secure a consistent view of the database until the transaction begins. If your database has a large number of write tasks, that means storing a large number of undo pages.

If you can't find any long-running transactions, you can also monitor other variables in the InnoDB STATUS, "History list Length" shows some pending cleanup operations. In this case, the problem often occurs because the purge thread (or the old version of the main thread) cannot handle the undo as fast as these records come in.

2. How do I check what's stored in the ibdata1?

Unfortunately, MySQL does not provide information to see what is stored in the Ibdata1 shared tablespace, but there are two tools that will be helpful. The first is a modified version of the Mark Karahan Innochecksum, which is published in this vulnerability report.

It's fairly easy to use:

#./innochecksum/var/lib/mysql/ibdata1
0 Bad checksum
fil_page_index
19272 fil_page_undo_log
230 Fil_page_inode
1 fil_page_ibuf_free_list
892 fil_page_type_allocated
2 Fil_page_ibuf_bitmap
195 Fil_page_type_sys
1 fil_page_type_trx_sys
1 fil_page_type_fsp_hdr
1 fil_page_type_xdes
0 FIL_ Page_type_blob
0 Fil_page_type_zblob
0 other
3 Max index_id

There are 19,272 undo log pages in all 20608. This takes up 93% of the table space.

The second way to check tablespace content is the InnoDB Ruby tool that Jeremy Core makes. It is a more advanced tool for checking the internal structure of InnoDB. For example, we can use the Space-summary parameter to come to a list of each page and its data type. We can use standard Unix tools to count the number of undo log pages:

# innodb_space-f/var/lib/mysql/ibdata1 space-summary | grep Undo_log | Wc-l
19272

Despite this particular situation, innochedcksum is quicker and easier to use, but I recommend that you use Jeremy's tools to learn more about the internal data distribution and the internal structure of InnoDB.

OK, now we know what the problem is.

3. Ibdata1 Slim Body Plan
Some of these can be configured on Percona servers to avoid growing too large. For example, you can set the maximum change buffer by innodb_ibuf_max_size or set the Innodb_doublewrite_file to store the double write buffer in a separate file.

MySQL version 5.6 You can also create external undo table space, so they can be placed in their own files to replace storage to ibdata1.

InnoDB data files are usually not removed. To reduce the size of the data file, you must use mysqldump to dump all of the data tables, re-establish a new database, and import the data into the new database. The specific steps are as follows:
(1) Backing Up the database
mysqldump -uroot -p123456 --default-character-set=utf8 --opt --extended-insert=true --triggers -R --hex-blob --single-transaction --no-autocommit  test > db_name.sql 
(2) Stop the database

Service Mysqld Stop 

(3) Delete related documents

Ibdata1 
ib_logfile* 
mysql-bin.index 

(4) Manually delete all database folders except MySQL, and then start the database

Service mysqld Start 

(5) Restore data

 /usr/local/mysql/bin/mysql-uroot-phigkoo </data/bkup/mysqldump.sql 

The main is to use some of the parameters of mysqldump, it is recommended to see a description before using the operation. In addition to the backup before you can look at the current database which tables occupy a large space, some unnecessary to truncate table off. So save some space and time