MYSQL write problems optimization
Everyone listed that Mysql’s performance optimization focuses on sql and index optimization to improve the performance of queries. More high-concurrency data reading issues are faced by most goods or websites. How to optimize, however, in a large-volume scenario of writing data?
Today, I will mainly introduce you in scenes with a lot of writing to the streamlined solution.
Generally speaking, MYSQL database write output is largely restricted by the configuration of the database itself, the performance of the operating system, and the performance of the IO disk. The key strategies for optimization include the following:
1. Adjust parameters for databases
(1) innodb flush log at trx commit”
The default is 1, which is the database’s transaction commit setting parameter. The following are the optional values:
0: The log buffer is written once a second to the log file and the log file is refreshed by disk operations, but no operation is performed during the commit transaction.
1: The log buffer is written to the log file when each transaction is committed, and the log file is refreshed for disk operations.
2: The log buffer is written to the file after each commit, but the disk process is not refreshed for the log file. Every second, the log file is refreshed.
If it is modified to a value other than 1, would any people say it will be unsafe? The comparison with defense is as follows:
In the MySQL manual, it is recommended that this parameter be set to 1. to ensure the durability and accuracy of the transaction. The default value of the factory is 1, which is the most stable environment as well.
It is the most stable if both innodb flush log at trx commit and sync binlog are 1. In the event of a crash of the mysqld service or a server host crash, the binary log can lose only one statement or transaction at most.
But it will cause regular io operations in this case, so this mode is the slowest method, too.
In the last second, when innodb flush log at trx commit is set to 0, the mysqld process crash will cause the loss of all transaction data.
When innodb flush log at trx commit is set to 2, only when the operating system crashes or the system is switched off will all transaction data be lost in the last second.
Use the c# code for the same table to perform batch insertion according to the business method of the device. The contrast of performances is as follows:
(A. Under the same conditions: innodb flush log at trx commit=0, inserting 50W lines of data takes 25.08 seconds;
(B. Under the same conditions: innodb flush log at trx commit=1, inserting 50W rows of data takes 17 minutes and 21.91 seconds;
(C. Under the same conditions: innodb flush log at trx commit=2, inserting 50W rows of data takes 1 minute and 0.35 seconds.
Conclusion: Data writing is the fastest when set to 0, which can quickly increase the database’s writing efficiency, but it can lose the last 1 second of data.
(2) temp-table-size, heap-table-size
These two parameters primarily influence the writing of the in-memory database engine’s temporary table and memory engine table. The setting is too thin, and an error message can also appear if the table is complete.
It should be set larger than the amount of data that needs to be written to fill the room according to the actual business situation.
(3) max allowed packet=256M, length=16M net buffer, set autocommit=0
You will make your backup and restore speed fly when you set these three parameters when you backup and restore!
(4) innodb data file path=ibdata1:1G;ibdata2:64M:autoextend;ibdata2:64M:autoextend;
Obviously, allowing the table space to be extended automatically is the auto-extension behind the table space. It is not enough, by necessity, to have just 10M. You may wish to increase this parameter in the scenario of writing large amounts of data;
Allocate as much table space as possible at a time when the table space expands, preventing periodic file expansion when writing big batches.
(5) innodb-log-file-size, innodb-log-files-in-group, innodb-log-buffer
Set the transaction log size, the number of log classes, and the cache of logs. The default value is very small, just tens of M is the default value of innodb log file size, and the default value is 2 for innodb log files in group.
In InnoDB, however, data is normally written first to the cache, then to the transaction log, and then to the data register. If the environment is too limited, in the scenario of writing large amounts of data, it will eventually cause periodic database checkpoints to be triggered to write the data to the disk data file in the log. In large amounts, repeated flushing of buffers and swapping logs can result in a decrease in writing data efficiency.
It should not be set too high, of course. If the database goes down abnormally due to a big meeting, it will read the dirty data in the log that is not written in the data file, redo it, and restore the database when the database is restarted. If it is too high, it will extend the recovery period. It will eventually cause customer concerns when the recovery time significantly exceeds the estimated recovery time of the user.
You may link to the Huawei Cloud database’s default settings for this configuration. The default configuration buffer: 16M, log file size: 1G-almost 25 percent of the total memory according to the official MySQL recommendation in the Huawei Cloud 2 core 4G environment; The log group files in group is set to 4 groups.
Given the reasonableness of the parameter settings, with such a low hardware configuration as 2 core 4G, it can withstand thousands of read and write requests per second and more than 80,000 read and write requests per minute.
You can import large amounts of data and make changes to modify log file size to a greater value, which can exceed 25 percent ~100 percent of innodb buffer pool size, if the amount of data written is much larger than that of read or if it is convenient to alter the parameters.
(6) innodb buffer pool size sets MySQL Innodb’s usable memory size. Theoretically, the limit can be set at 80 percent of the total memory of the server.
It is undoubtedly better to set a larger value than to set a small value for writing results. The above innodb log file size parameter, for example, is set with reference to the innodb buffer pool size size.
Innodb thread concurrency=16 (7)
It regulates the number of concurrent threads, as the name suggests. The more threads there are, in principle, the quicker the writing would be. It should not be set too high, of course. About twice the number of CPU cores is the official recommendation.
Write buffer size (8)
Monitor the size of a single-write cache in a single session. Approximately 4K is the default value. Generally, it does not need to be changed. However, in regular and large-scale writing situations, you may attempt to adapt to 2M, and you will find that the writing pace will increase to a certain degree.
(9) Instance of innodb buffer pool
The default is 1, which primarily sets the number of pools of memory buffers. To put it plainly, it regulates the sum of innodb buffer pool read and write concurrently.
This parameter can also be increased in large-volume writing situations, which will also bring major improvements in efficiency.
(10) bin log file
Typically, the binary log documents all database additions, deletions, and changes. However, you may want to briefly close bin log and turn off writing to the binary log while importing a large amount of data, such as database restore, so that data is only written to the data file, and data recovery is done easily, and then turn it on again.
2. Reduce disk IO and improve the read and write performance of the disk
Including the methods below:
(1): Optimization of Database System Architecture
A: Do replication for master-slave;
For instance, to ensure data protection, deploying a dual-master-slave, dual-master-slave mode deployment is for mutual backup. Different business systems, together with the automatic switching feature of ngnix or keepalive, are linked to various database servers to achieve load balancing and automatic switching in the event of failure.
The concurrent read and write IO of the distributed business system from one server to multiple servers can also increase the writing speed of a single database through this design optimization.
B: Do separate reading and writing:
It can reduce the disk IO of a single server, as well as the problem to be considered in 1, and can also transfer the backup activity on the server to the standby server to decrease the IO pressure of the primary server, thereby improving the write performance.
(2): Optimization of Hardware
A: In the case of restricted resources, several disks should be present in the operating system during installation and deployment. The programs, database files, log files, etc. should be spread to different disk storage to minimize the IO of each disk, thus increasing the single The write output of the disk.
B: Using SSD Solid State Drive
SSD storage can be used if the resources are appropriate. The SSD has high-speed writing characteristics, which can also greatly enhance all disk IO operations.
There are, of course, more methods of hardware or software optimization, which are not mentioned here.
I like to learn new and better ways of doing things when working on a scale, and feel free to ask questions and make suggestions.
Also, check out another story on this.
Thanks for reading this.
