How is the writing speed of Mysql storage formed?

1. Storage structure analysis

MySQL storage structure diagram:



Analysis:

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

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

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

From the above, it can be seen that cache is one of the reasons why MySQL has fast reading and writing speed. played a key role.

1. Cache features:

1. Fast speed

2. Data loss during power failure

3. Limited capacity

2. Protect data security (prevent data loss due to unexpected power outage) Countermeasures:

In storage, add BBU (battery backup unit, which is the battery). After power outage, the data in the cache can be written to the disk to ensure that 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 cache, but will be written directly to the disk; compared to writing to the memory, the speed of writing to the disk will be greatly reduced ( ten thousand times difference). At the same time, the slower "writing" takes up most of the "reading" bandwidth. Therefore, the BBU problem is a major influencing factor of poor read and write performance.

3. The cache cache capacity is limited

(4G, 8G, 16G, 32G). In order to keep the cache useful, the system will periodically write the cached data to the disk to avoid cache occupied.

2. Analysis of slow storage writing speed

Database-->Slow writing speed-->System hangs
Q: How to judge the slow writing speed?

A:

1. Suspected BBU problem

Monitor the BBU bug and solve it by restarting the BBU

2. The cache is full (similar to BBU is broken)

1. A large amount of written data fills up the cache cache. Judgment:

shell> iostat -x

mysql> show global status like 'handler_write ';

2. The speed of cache writing to disk is slow (the drainage speed is much slower than the water filling speed)

Hard disk I/O is abnormal and the load is too high: massive physical reads of the database (abnormal SQL ), judgment:

mysql> show status like 'Innodb_buffer_pool_reads';

3. Problem of poor storage performance

1. Poor storage device, update the device

2. Risks of disaster recovery synchronization, "No matter how good the engineer is, he is no match for Lan Xiang's excavator and migrant workers' hoes..."

3. About BBU

English abbreviation: BBU

English full name: Battery Backup Unit

Chinese full name: Battery Backup Unit, which is the battery

1. Function:

After power failure, the cache The data is written to the hard disk to ensure that the data will not be lost;

is a protection measure for brushing dirty data due to accidental power outage;

can provide data protection when the external power supply of the system fails. Backup power supply support to ensure the security of business data in the storage array.

2. Many storage devices will be equipped with BBU

BBU provides power for the RAID controller cache when there is a problem with the power supply. When the power is off, the BBU power can save the data in the cache in the controller for a certain period of time (depending on the BBU model). Users only need to restore normal power supply before the BBU power is exhausted (battery is limited), and the data in the cache can be completely written back to the RAID to avoid data loss caused by power outage.

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 is powered off, the data in the memory can be maintained without losing it, but the time is limited, about 12 hours. About 12 hours, if it is not restored within 12 hours, the data in the memory will be lost;

3. Supercapacitor

The server also has a protection method called supercapacitor, which is also Matched with the RAID card, it can write the data in the cache to the capacitor after the server is powered off, and it will be saved permanently. It is similar to writing to a hard disk, but actually writing to the capacitor. You can understand it as writing to a USB flash drive. . It is better than batteries. Even if the server does not restore power after 12 hours, there will be no data loss.

The above is the detailed content of How is the writing speed of Mysql storage formed?. For more information, please follow other related articles on the PHP Chinese website!