Jitter in MySQL 5.5 massive data insertion and Solutions

Background

A typical operation of a log-type application involves a large number of insert Operations periodically. This type of operation requires extended tablespace.

Analysis

In InnoDB, the extended tablespace operation is called directly by the execution thread during statement execution.

Especially for some tables with a large row, the tablespace needs to be extended every few records are inserted.

Although the insert buffer and write ahead logging policies ensure that the table data files are not directly operated in the execution thread, the extended tablespace operation will lead to an instantaneous low point of the updated tps. Such. In fact, the overall TPS is also affected.

Improvement Plan and Feasibility

You can pre-allocate tablespaces before such large operations to optimize this problem. Before our business went online, we had a capacity estimate and daily monitoring. Therefore, we can estimate the amount of time space that will increase in the next section.

Before the actual update starts, the tablespace is pre-allocated before the off-peak period or even before the service is provided, which can avoid this jitter and increase TPS.

Tool Verification

In the table space structure of InnoDB, the number N in the space header contains four bytes to indicate the size of the table space (number of pages). the actual size of the file is N * Page_SIZE.

Implements a tool extend_space, modifies four bytes, and appends the file to the specified size. The test showed that the insert performance was improved by 10%. This causes the jitter to be eliminated.

Follow-up

Currently, it is only implemented using tools. The tool is troublesome to use, mainly because the lock table operation during the update process requires an external script, and page_size will be variable in later versions, therefore, the elegant solution is to add this function to the commands supported by MySQL.

Supplemental update

New MySQL commands support pre-Extension

Http://bugs.mysql.com/bug.php? Id = 63858

Effect