Slow Mysql storage write speed analysis, slow mysql write speed

Slow Mysql storage write speed analysis, slow mysql write speed
Problem background description: Execute SQL statements in MySQL, such as insert, which is slow and may be just a row of data insertion. The data volume is small, but it takes a lot of time. Why?
I. Storage Structure Analysis

MySQL storage structure:



Resolution:

1. Read operation: Memory read --> cache read --> Disk Physical read

The data read will be sent back in the above Order.

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

It can be seen from the above that the cache plays a key role in the Fast Reading and Writing speed of MySQL.

1. Cache features:

1. Fast

2. Power Loss Data

3. Limited Capacity

2. Measures to protect data security (to prevent accidental power loss:

In storage, add BBU (battery backup unit, that is, battery). After power loss, you can write the cached data to the disk to ensure that the data will not be lost.

If the BBU is not provided or the BBU is broken, the memory data will not be written to the cache and will be directly written to the disk, the speed of writing data to a disk is greatly reduced (10 thousand times worse ). At the same time, because the slow "write" occupies the vast majority of "read" bandwidth. Therefore, the BBU problem is a major factor affecting poor read/write performance.

3. Limited cache capacity

(4G, 8G, 16G, and 32G), the system periodically writes the cached data to the disk to ensure that the cache is useful.

Ii. Analysis of slow storage write speed

Database --> slow write speed --> system hang
Q: How can I determine whether the write speed is slow?

A:

1. Suspected BBU Problems

Monitoring BBU bug. Solution: restart BBU

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

1. Massive amounts of written data are full of cache. Judgment:

Shell> iostat-x

Mysql> show global status like 'handler _ write ';

2. the cache write speed to disk is slow (the drainage speed is much lower than the water injection speed)

Hard Disk I/O exception, high load: massive physical reads of the database (abnormal SQL), judgment:

Mysql> show status like 'innodb _ buffer_pool_reads ';

3. Poor storage performance

1. Poor storage devices, update Devices

2. Disaster Recovery synchronization risks: "no matter how good an engineer is, it cannot beat lanxiang's excavators or migrant workers ......"

Iii. About BBU

Abbreviation: BBU

Battery Backup Unit

Full name: battery backup unit, battery

1. role:

After power loss, write the cached data to the hard disk to prevent data loss;

It is a protection measure for accidental damage to dirty data;

It can provide backup power support when the system's external power supply fails to ensure the security of business data in the storage array.

2. Many storage devices are equipped with BBU

When the power supply is faulty, BBU provides power to the RAID Controller cache. When the power supply is down, BBU power enables the data cached in the Controller to be stored for a certain period of time (determined based on the BBU model ). The user only needs to recover normal power supply before the BBU Power Consumption (limited battery), the data in the cache can be completely written back to RAID, to avoid data loss caused by power failure.

It is a little different from the server battery. In the server, you can configure a battery when configuring the RAID card. After the system powers down, the data in the memory will not be lost, but the time is limited, about 12 hours, if it is 12 hours, if it is not restored within 12 hours, the data in the memory will be lost;

3. super capacitor

The server also has a protection method called super capacitor, which is also compatible with the RAID card. After the server powers down, it can write the cached data into the capacitor and save it permanently, writing data to a hard disk is actually a write capacitor, which means writing data to a USB flash disk. It is better than battery, even if the server does not recover power after 12 hours, it will not cause data loss.