Mysql Data is not big, but the backup is very slow

Source: Internet
Author: User

Environment
Hardware: DELL 1950,146g sas 15 k rpms * 2, 8G Ram
Software: 2.6.9-55. ELsmp x86_64, mysql 5.1.x

Symptom
Two databases, one of which has more than 20 tables and the total table file size is less than 2 GB.
The other is the logstore, where more than 400 tables are generated every day. One of the tables is large, about 400 MB, with a total of more than 40 GB.
Each backup takes a long time, and the most serious one takes more than five hours to complete.
The business database is the current active database, and the logstore is mainly used for backup. logs are archived every day, and few read/write requests are sent to expired data tables.
The InnoDB Buffer Pool is allocated a total of 2 GB. From the top result of the system command, mysqld only allocates 1.7 GB of memory, and the buffer pool is not all exhausted.
The results of show engine innodb status also SHOW that the buffer pool is useless and there are still a lot of idle resources.
During backup, observe the vmstat results and find that bi and bo are large, and the two values are basically the same. The backup of a table is about 500 MB, which takes 46 seconds.
According to this time-consuming calculation, it does not take more than five hours to back up all data. Why?

Analysis
Let's analyze the cause and give the answer later.

The cause is actually very simple, but it is not easy to think. That is, because those historical log tables are not read for a long time, most of the data is not in innodb buffer. Therefore, during each backup, most of the data requires a large amount of physical reads and then physical writes. However, this server only has two hard disks and has limited I/O performance, therefore, backup is very slow.

At this time, we can have several solutions:
1. Delete the expired log table or put it on the offline archive database.
2. If innodb buffer has a large amount of free space, you can run select * from table on a regular basis to load this part of data into the buffer, reducing the physical I/O during Backup and improving the speed.

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.