An unforgettable online big table structure modification

Background introduction:

Server 252GMem 40CPU SSD disk

The MySQL version used by the gadget is 5.6.25

Mysql> Select COUNT (*) from cisxx_data_xxx;
+-----------+
| COUNT (*) |
+-----------+
| 110908162 |
+-----------+
1 row in Set (1 min 27.38 sec)

mysql> desc cisxx_data_xxx;

...

0.01 in Set (SEC)

first attempt to index directly

Mysql> ALTER TABLE cisxx_data_xxx add column ' RESERVED1 ' varchar (a) Not NULL DEFAULT ' COMMENT ' reserved field 1 ',
Add column ' RESERVED2 ' varchar (a) Not NULL DEFAULT ' COMMENT ' reserved field 2 ',
Add column ' RESERVED3 ' varchar (a) Not NULL DEFAULT ' COMMENT ' reserved field 3 ';
ERROR 1878 (HY000): temporary file write failure.

Because the table is too large, the resulting temporary table has exceeded the disk limit in/directory.

Second attempt to create a new table

Modify the new table structure, and then import the data by insert....select .... Mode  

CREATE TABLE tmp_cisxx_data_xxx_20160620 (

......

);

INSERT INTO tmp_cisxx_data_xxx_20160620 (

....

) Select

...

From Cisxx_data_xxx;

In the process of data import, a new session is opened. Test what locks are added to the Cis_data_tag table during this build. Test results such as:

In this way, it is a shared lock that is added to Cis_data_tag.

In the process of data import

The server load is as follows:

Disk read/write pressure:

The discovery/directory of disk space in the derivative process is still an alarm.

Then kill the derivative process, after performing the kill operation, found that the memory and disk usage on the server is still rising. Then try kill again. Try again or fail. The small series realizes that the problem may be grim. If the system resources have been so consumed, will not cause the server to hang up the machine. Oh no, this is the main library, if you hang up, that means it will affect the production business. The main thing now is to stop this horrible derivative operation. The memory and disk space resources begin to release when you are trying to find a way to do it. It is estimated to be the same as the first attempt. However, when disk space reaches 100%, the derivative process is terminated by the server.

Third attempt to modify:

On the basis of the second attempt, the small part will no longer perform this operation on the main library, and realize that the table to be manipulated is too large. and/The disk space is too small, you must change the MySQL temporary space settings. So the following solutions have been sorted out

Check the pre-modification

Memory usage has been on the rise, increasing by 72G

The disk directory where the temp directory is located has been rising, increasing by 72G

The read-write rate of the disk reaches 589836.00 wsec/s 172812.00 rsec/s

The physical file size of the table is also 48G

In the process of indexing, the small series found that MySQL actually supports online DDL operations, which means that no other requests are blocked. Please see:

It was really a very, very great discovery, and MySQL did progress. But it is a pity to hear that only some of MySQL's structural modifications are supported online.

Read through the official documentation for more details:

Reference Link: http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

Modify a 20 million table to add fields and index the time reference required:

Modify a 100 million table to add fields and index the time reference required:

An unforgettable online big table structure modification