MySQL Storage write slow analysis

Problem background Description: Execute SQL statements in MySQL, such as insert, thief slow, obviously may be just a row of data inserted, the amount of data is very small, but the time spent is many, why?

First, storage structure analysis

MySQL storage structure diagram:

Analytical:

1. Read operation: memory read-->cache cache read-disk physical read

The data that is read is sent back in the order above.

2. Write operation: memory data is written directly to the cache cache (very fast)--write disk

Thecache plays a key role in MySQL's fast read and write speed.

1. Cache Caching Features:

1. Fast speed

2. Loss of lost power data

3. Limited capacity

2. measures to protect data (against accidental power loss):

In the storage, add the BBU (battery backup unit, is the battery), after power down, you can write the data in the cache to disk, to ensure that the data is not lost.

If the BBU is not provided or the BBU is broken, the memory data is not written to the cache, it is written directly to disk, and the speed of writing to the disks is reduced (the difference in multiples) compared to the write memory. At the same time, because the slow down "write" accounted for the vast majority of the "read" bandwidth. So the BBU problem is a big factor in poor read and write performance.

3, cache buffer capacity is limited

(4G, 8G, 16G, 32G), the system will periodically write cache cached data to disk in order to keep the cache useful, to prevent the cache from being fully occupied.

Second, slow memory write speed analysis

Database--Write slow--system hang

Q: How to determine the slow write speed?

A:

1. Suspected BBU Issues

Monitor Bbu bug, FIX: Restart Bbu

2. The cache is full (similar to the case where the BBU is bad)

1, the vast amount of write data fills the cache buffer, judge:

Shell> Iostat-x

Mysql> show global status like ' Handler_write ';

2, cache write disk slow speed (drainage speed is much less than the water injection speed)

Hard disk I/O exception, high load: Database massive physical read (exception sql), Judge:

Mysql> Show status like ' Innodb_buffer_pool_reads ';

3. Poor storage performance

1. Poor storage device, update equipment

2, disaster preparedness and synchronization risk, "a good engineer, enemy but Lanxiang excavator, peasant workers hoe ..."

Iii. about the Bbu

English abbreviation: BBU

English full name: Battery Backup Unit

Chinese full name: Battery backup unit, battery

1. Function:

After the power-down, the data in the cache is written to the hard disk to ensure that the data is not lost;

is to accidentally drop the brush dirty data of a protective measure;

Provides back-up power support to ensure the security of the business data in the storage array, with the ability to supply power to the outside of the system.

2. Many storage devices will be equipped with a BBU

The BBU supplies the RAID controller cache with power when there is a problem with the power supply. When power is powered off, the Bbu power allows the data in the controller's cache to be stored for a certain amount of time (depending on the model of the BBU). The user only needs to return to normal power supply before the BBU is exhausted (Battery limited), and the data in the cache can be fully written back into the raid to avoid loss of data due to power outages.

and server battery a bit different, the server, in the configuration of the raid card can be equipped with a battery, after the system power down, can maintain the memory of the data is not lost, but the time is limited, about 12 hours, if it is 12 hours, if there is no recovery within 12, the data in memory will be lost;

3. Super Capacitor

Server also has a protection, called super-capacitor, and RAID card matching, he can after the server power down the cache of data written to the capacitor, and will be permanently saved, similar to writing to the hard disk, is actually a write capacitor, you understand the write to the USB stick. He is better than the battery, even if the server does not recover power after 12 hours, it will not cause data loss.

MySQL Storage write slow analysis