The parameter configuration of my. cnf optimizes the performance of the InnoDB engine, and my. cnfinnodb

The parameter configuration of my. cnf optimizes the performance of the InnoDB engine, and my. cnfinnodb

I have read numerous my. cnf configurations on the Internet. Most of the configurations mentioned are similar to the following:

1. innodb_buffer_pool_size
2. innodb_log_file_size
3. innodb_log_buffer_size
4. innodb_flush_log_at_trx_commit

Then I wrote two examples, one single thread and one multi-thread, to test whether the performance is improved by changing the configuration parameters. The result is that only innodb_flush_log_at_trx_commit can improve the performance. For parameters 1, 2, and 3, either when one or three parameters are enabled, the test performance is not affected. I thought about it. It may be because my test data volume is not large enough. I will test the three parameters with more data in the future.

Here we will detail innodb_flush_log_at_trx_commit:

If innodb_flush_log_at_trx_commit is set to 0, log buffer writes data to log file once per second, and flush (flush to disk) of log file is performed simultaneously. In this mode, when a transaction is committed, the write operation to the disk is not triggered.
If innodb_flush_log_at_trx_commit is set to 1, MySQL writes the data in log buffer to log file and flush (flush to disk) each time a transaction is committed.
If innodb_flush_log_at_trx_commit is set to 2, MySQL writes the data in log buffer to log file every time a transaction is committed. However, flush (flush to disk) operations are not performed at the same time. In this mode, MySQL performs the flush (flush to disk) operation once per second.

Result:

If it is set to 0, this mode is the fastest but not secure. The crash of the mysqld process will cause the loss of all transaction data in the last second.
When set to 1, this mode is the safest, but the slowest. When the mysqld service crashes or the server host crashes, binary log may only lose up to one statement or transaction.
When set to 2, this mode is faster and safer than 0. Only when the operating system crashes or the system is powered off can all transaction data be lost in the last second.

Note:: Due to a process scheduling policy issue, this "flush (flush to disk) operation per second" does not guarantee 100% "per second ".

Conclusion: When innodb_flush_log_at_trx_commit is set to 0 or 2, the speed is almost the same, and the two are much faster than when set to 1.

This reminds me of the difference between InnoDB and MyISAM. The advantage of InnoDB is that it is faster than MyISAM in concurrent processing. The number of thread pools is set based on the number of cpu threads. Then, I set the number of thread pools to be larger and larger than the number of cpu threads, as a result, the performance of my testing program has been improved. I was drunk. It turns out that my understanding of the thread pool is too simple. Optimum thread pool size