Abstract: We mentioned that Mysql performance optimization is focused on optimizing SQL and index to improve query performance, most products or websites are faced with more high concurrency data read problems. But how do you optimize in a scenario where a lot of data is written?
Today, I’m going to show you how to optimize a scenario with a lot of writing.
Generally speaking, MYSQL database write performance is mainly limited by the configuration of the database itself, as well as the performance of the operating system, disk IO performance. The main optimization means include the following:
1. Adjust database parameters
(1) innodb_flush_log_at_trx_commit
The default value is 1, which is the transaction commit setting parameter of the database. Possible values are as follows:
0: The log buffer is written to the log file once per second, and the log file is flushed for disk operations, but nothing is done during a transaction commit.
1: At each transaction commit, the log buffer is written to the log file, and the log file is flushed for disk operations.
2: On each commit, the log buffer is written to the file, but the log file is not flushed for disk operations. Log files are refreshed every second.
One might say is it not safe to change it to something other than 1? The security comparison is as follows:
In the mysql manual, it is recommended to set this parameter to 1 in order to ensure transaction persistence and consistency. The factory default is 1, which is also the safest setting.
When innodb_flush_log_at_trx_commit and sync_binlog are both 1, the binary log can only lose at most one statement or transaction in the case of mysqld service crash or server host crash.
In this case, however, it results in frequent I/O operations, so this mode is also the slowest.
- When innodb_flush_LOG_at_trx_COMMIT is set to 0, the mysqld process crashes causing all transaction data to be lost for the last second.
- When innodb_flush_LOG_at_trx_COMMIT is set to 2, all transaction data in the last second can only be lost in case of an operating system crash or power failure.
For the same table through c# code according to the system business process for batch insertion, the performance comparison is as follows:
- (a. Under the same conditions: Innodb_flush_LOG_at_trx_commit =0, it takes 25.08 seconds to insert 50W rows of data;
- (b. Under the same conditions: Innodb_flush_log_at_trx_commit =1, it takes 17 minutes 21.91 seconds to insert 50W rows of data;
- (c. Under the same conditions: Innodb_flush_log_at_trx_commit =2, it takes 1 minute 0.35 seconds to insert 50W rows.
Conclusion: When the value is set to 0, data write is the fastest and improves the database write performance rapidly, but the data may be lost for one second.
(2) temp_table_size heap_table_size
The two parameters mainly affect the writing of temporary table and memory engine tables. If the setting is too small, the table is full error message may be displayed.
The space occupied by the data to be written must be larger than the space occupied by the data to be written.
Max_allowed_packet =256M, net_BUFFer_length =16M, set autoCOMMIT =0
If you set these three parameters during backup and restore, you can make your backup and restore speed fly!
(4) innodb_data_file_path = ibdata1:1 g; ibdata2:64M:autoextend
Autoextend allows the tablespace to be automatically extended. The default value is 10M, but for high-volume data writes, this parameter can be increased.
Allocate as many tablespaces as possible at a time as tablespaces grow, avoiding frequent file expansion when large volumes of data are written
(5) innodb_log_file_size innodb_log_files_in_group, innodb_log_buffer_size
Set transaction log size, number of log groups, and log cache. The default value is small, innodb_log_file_size default value is only tens of meters, innodb_log_files_in_group default value is 2.
In InnoDB, however, data is usually written to the cache first, then to the transaction log, then to the data file. Setting it too small will inevitably result in frequent database checkpoints being triggered to write data from the log to disk data files in the case of large volumes of data writes. Frequent buffer flushing and log switching can degrade the performance of bulk writes.
Of course, it should not be set too large. When the database is restarted, dirty data that has not been written to the log file is read, and redo data is performed to restore the database. If the data is too large, the recovery time becomes longer. When the recovery time is much longer than the expected recovery time, users will complain.
In the huawei CLOUD 2 core 4G environment, it seems that the default configuration of buffer:16M,log_file_size:1G—- approximately 25% of the total memory according to the official recommendation of mysql; Files_in_group is set to four groups.
2 core 4G so low hardware configuration, due to the rationality of parameter Settings, has been able to withstand thousands of times per second, more than 80,000 read and write requests per minute.
If the amount of data written is much larger than the amount of data read, or it is convenient to change the parameters of the scenario, you can adjust the log_file_size for a large number of data import, and then adjust the log_file_size larger, can reach 25% to 100% of the innodb_buffer_pool_size.
(6) Innodb_buffer_pool_size Specifies the available cache size for MySQL Innodb. Theoretically, the maximum can be set to 80% of the server’s total memory.
Setting a larger value certainly gives better write performance than setting a smaller value. For example, innodb_log_file_size is set to the size of innodb_buffer_pool_size.
(7) innodb_thread_concurrency = 16
So the name means, control the number of concurrent threads, in theory, the more threads will of course write faster. Of course, it should not be too large. The official recommendation is about twice the number of CPU cores.
Write_buffer_size (8)
This parameter controls the cache size of a single CDR write. The default value is about 4K. However, in frequent mass write scenarios, you can try to adjust to 2M, and you will find that the write speed will be improved to some extent.
(9) innodb_buffer_pool_instance
The default value is 1, which sets the number of innodb_buffer_pools and, more simply, the number of concurrent reads and writes to the innodb_buffer_pool.
This parameter can also be increased in mass write scenarios, which also results in significant performance improvement.
Bin_log (10)
Binary logs usually record all the add, delete, and change operations of a database. However, in the case of a large amount of data, such as database restore, you can temporarily turn off bin_log, turn off the binary log write, let the data only write to the data file, quickly complete the data recovery, then open it.
2. Reduce disk I/O and improve disk read and write efficiency
Including the following methods:
(1) optimization of database system architecture
A: Master slave replication;
For example, a dual-master/slave mode is deployed for mutual backup to ensure data security. Different service systems connect to different database servers. Load balancing and automatic failover in case of faults are realized by combining the function of Ngnix or Keepalive automatic switchover.
Through this architecture optimization, the concurrent read and write IO of distributed business systems can be transferred from one server to multiple servers, and the write speed of a single database can also be improved.
B: Do read-write separation
The same problems to be considered in 1 can reduce the DISK I/O load on a single server and transfer the backup operation on the server to the secondary server, reducing the I/O load on the primary server and improving write performance.
(2) hardware optimization
A: When resources are limited, the operating system must have multiple disks to store applications, database files, and log files on different disks. This reduces THE I/O load on each disk and improves the write performance of each disk.
B: SSDS are used
If the resources are sufficient, SSDS can be used for storage. SSDS have high write speed and can also significantly improve all disk I/O operations.
Of course, there are more hardware or software optimization methods, which are not listed here.
This article is shared by Huawei cloud community “MYSQL Mass write Performance optimization”, originally written by: Fu Chen.
Click to follow, the first time to learn about Huawei cloud fresh technology ~