Detailed explanation of MYSQL large-scale write problem optimization

Abstract: When people talk about MySQL performance optimization, they all focus on optimizing SQL and indexes to improve query performance. Most products or websites face more high-concurrency data reading problems. However, how to optimize the scenario of writing a large amount of data?

Today, I will mainly introduce to you the optimization solution for scenarios with a large number of writes.

In general, the write performance of MYSQL database is mainly limited by the configuration of the database itself, the performance of the operating system, and the performance of disk IO. The main optimization methods include the following:

1. Adjust database parameters

(1) innodb_flush_log_at_trx_commit

The default value is 1. This is the transaction commit setting parameter of the database. The optional values ​​are as follows:

0: The log buffer is written to the log file once per second, and the log file is flushed to disk, but no operation is performed on a transaction commit.

1: When each transaction is committed, the log buffer is written to the log file, and the log file is refreshed by disk operations.

2: At each commit, the log buffer is written to the file, but no disk operations are done to the log file. The log file is flushed every second.

Some people may say that it would be unsafe if the value is changed to something other than 1? The security comparison is as follows:

In the MySQL manual, in order to ensure the persistence and consistency of transactions, it is recommended to set this parameter to 1. The factory default is 1, which is also the safest setting.

When innodb_flush_log_at_trx_commit and sync_binlog are both 1, it is safest. In the event of a mysqld service crash or a server host crash, the binary log may only lose a maximum of one statement or one transaction.

However, in this case, frequent IO operations will occur, so this mode is also the slowest.

• When innodb_flush_log_at_trx_commit is set to 0, a crash of the mysqld process will result in the loss of all transaction data in the last second.
• When innodb_flush_log_at_trx_commit is set to 2, all transaction data in the last second may be lost only if the operating system crashes or the system loses power.

For the same table, batch insert is performed through C# code according to the system business process. 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 500,000 rows of data;
• (b. Under the same conditions: innodb_flush_log_at_trx_commit=1, it takes 17 minutes and 21.91 seconds to insert 500,000 rows of data;
• (c. Under the same conditions: innodb_flush_log_at_trx_commit=2, it takes 1 minute and 0.35 seconds to insert 500,000 rows of data.

Conclusion: When set to 0, data writing is the fastest, which can quickly improve the database writing performance, but there is a possibility of losing data from the last 1 second.

(2) temp_table_size, heap_table_size

These two parameters mainly affect the writing of temporary tables and memory engine tables. If they are set too small, an error message "table is full" may even appear.

The space occupied should be set larger than the amount of data to be written according to the actual business situation.

(3) max_allowed_packet=256M, net_buffer_length=16M, set autocommit=0

If you set these three parameters correctly when backing up and restoring, your backup and recovery speed will skyrocket!

(4) innodb_data_file_path=ibdata1:1G;ibdata2:64M:autoextend

Obviously, the autoextend after the tablespace is to make the tablespace automatically expand, but by default it is only 10M. In the scenario of large-scale data writing, you may wish to increase this parameter;

Allocate as much table space as possible at a time when the table space grows, avoiding frequent file expansion when writing in large batches

(5) innodb_log_file_size, innodb_log_files_in_group, innodb_log_buffer_size

Set the transaction log size, number of log groups, and log buffer. The default value is very small. The default value of innodb_log_file_size is only tens of MB, and the default value of innodb_log_files_in_group is 2.

However, in InnoDB, data is usually written to the cache first, then to the transaction log, and then to the data file. If the value is set too small, in scenarios where large amounts of data are written, it will inevitably lead to frequent triggering of database checkpoints to write data in the log to disk data files. Frequent buffer refreshes and log switches will result in reduced performance when writing large amounts of data.

Of course, it should not be set too large. If the size is too large, the database may crash abnormally. When the database restarts, it will read the dirty data in the log that has not been written to the data file, perform redo, and restore the database. If the size is too large, the recovery time will become longer. When the recovery time far exceeds the user's expected recovery time, it will inevitably cause user complaints.

For this setting, you can refer to the default settings of Huawei Cloud's database. In Huawei Cloud's 2-core 4G environment, the default configuration seems to be buffer: 16M, log_file_size: 1G, which is almost 25% of the total memory as recommended by MySQL; and the log group files_in_group is set to 4 groups.

With such a low hardware configuration of 2 cores and 4G, due to the reasonable parameter settings, it can withstand thousands of read and write requests per second and 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 if you want to change parameters at will, you can import large amounts of data and then adjust log_file_size to a larger value, which can reach 25%~100% of innodb_buffer_pool_size.

(6) innodb_buffer_pool_size sets the available cache size of MySQL Innodb. Theoretically, the maximum value that can be set is 80% of the total server memory.

Of course, setting a larger value will provide better write performance than setting a smaller value. For example, the above parameter innodb_log_file_size is set with reference to the size of innodb_buffer_pool_size.

(7) innodb_thread_concurrency=16

As the name suggests, it controls the number of concurrent threads. In theory, the more threads there are, the faster the writing will be. Of course, it cannot be set too large. The official recommendation is about twice the number of CPU cores.

(8) write_buffer_size

Controls the cache size for a single session write. The default value is about 4K and generally does not need to be adjusted. However, in scenarios where large batches of data are frequently written, you can try adjusting it to 2M, and you will find that the writing speed will be improved to a certain extent.

(9) innodb_buffer_pool_instance

The default value is 1, which mainly sets the number of memory buffer pools. To put it simply, it controls the number of concurrent reads and writes of innodb_buffer_pool.

In scenarios with large-scale writes, you can also increase this parameter, which will also bring significant performance improvements.

(10) bin_log

Binary logs usually record all addition, deletion, and modification operations of the database. However, when importing large amounts of data, such as restoring a database, you may want to temporarily close bin_log, turn off writing to the binary log, write data only to the data file, quickly complete data recovery, and then reopen it when you are done.

2. Reduce disk IO and improve disk read and write efficiency

The following methods are included:

(1): Database system architecture optimization

a: Do master-slave replication;

For example, deploy a dual master-slave mode. The dual master-slave mode is deployed to back up each other and ensure data security. Different business systems are connected to different database servers, and the automatic switching function of ngnix or keepalive is combined to achieve load balancing and automatic switching in case of failure.

Through this architectural optimization, the concurrent read and write IO of the distributed business system is moved from one server to multiple servers, which can also improve the write speed of a single database.

b: Do read-write separation

Similar to the issues to be considered in 1, the disk IO of a single server can be reduced, and the backup operations on the server can be moved to the standby server to reduce the IO pressure on the primary server, thereby improving write performance.

(2): Hardware optimization

a: When resources are limited, there should be multiple disks in the operating system during installation and deployment. Applications, database files, log files, etc. should be distributed to different disks for storage to reduce the IO of each disk, thereby improving the write performance of a single disk.

b: Use solid state drive SSD

If resources are sufficient, SSD storage can be used. SSD has the characteristics of high-speed writing and can also significantly improve all disk IO operations.

Of course, there are more hardware or software optimization methods, which are not listed here one by one.

This is the end of this article about the detailed optimization of MYSQL mass writing problems. For more relevant MYSQL mass writing content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!