Jitter in MySQL 5.5 massive data insertion and Solutions

Source: Internet
Author: User
Tags mysql commands

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.