Solution to the problem of MySQL deleting and inserting data very slowly

When a company developer executes an insert statement in a test environment, it takes more than 10 seconds to execute successfully. We checked the test environment database performance, data volume, deadlock and other information and found no anomalies. Finally, this problem was solved by modifying the log writing method.

1. Modification Method

Modify the /etc/my.cnf file and change innodb_flush_log_at_trx_commit = 1 to 0. However, this will incur the risk of losing data that is not stored in the database within 1 second after the database crashes. The MySQL documentation describes this parameter as follows:

If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

2. Parameter Description

• 0: The log buffer will be written to the log file once per second, and the log file will be flushed (flush to disk) at the same time. In this mode, when a transaction is committed, the operation of writing to disk will not be triggered actively.
• 1: Each time a transaction is committed, MySQL writes the data in the log buffer to the log file and flushes it to disk. This mode is the system default.
• 2: Every time a transaction is committed, MySQL writes the data in the log buffer to the log file, but the flush operation is not performed at the same time. In this mode, MySQL will perform a flush operation once per second.
  

3. Notes

When set to 0, this mode is the fastest, but less secure. A crash of the mysqld process will result in the loss of all transaction data in the last second.

When set to 1, this mode is the safest, but also the slowest. In the event of a mysqld service crash or a server host crash, the binary log may lose at most one statement or one transaction.

When set to 2, this mode is faster and safer than 0. All transaction data in the last second may be lost only if the operating system crashes or the system loses power.

The two parameters innodb_flush_log_at_trx_commit and sync_binlog are key parameters for controlling MySQL disk write strategy and data security. When both parameters are set to 1, the write performance is the worst. The recommended practice is innodb_flush_log_at_trx_commit=2, sync_binlog=500 or 1000.

This is the end of this article about how to solve the problem of MySQL deleting and inserting data very slowly. For more related content about MySQL deleting and inserting data very slowly, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!