Shulou(Shulou.com)06/01 Report--

1) .innodb_buffer_pool_size

To improve performance, the data to be written can be merged in the buffer buffer and then sent to the next level of storage. Doing so can improve the efficiency of reading and writing.

InnoDB Buffer Pool is the memory buffer that InnoDB uses to cache its data and indexes, which can be set by innodb_buffer_pool_size.

In theory, the higher you set this value, the less disk Imando O is required to access the data. A common practice is to make this value larger than the hotspot data, so that you can achieve better performance. It is recommended that you set its value to between 70% and 80% of the machine's physical memory size.

2). Innodb_log_file_size

The size of each log file in the log group. The combined size of log files on 32-bit computers must be less than 4GB. The default size is 5MB. In a production environment, this value needs to be adjusted. The value recommended by the official document is the buffer pool size from 1MB to 1 pound N, where N is the number of log files in the log group (determined by the innodb_log_files_in_group variable, generally

The default is 2). The higher the value, the less the behavior that requires checkpoint refresh in the buffer pool, so the more disk I mango O is saved, but larger log files also mean slower recovery in the event of a crash.

It is recommended that you set the size of the log file to 256MB or larger to meet the general needs.

3) .innodb_flush_log_at_trx_commit, which is recommended to be set to 2

The default value for this option is 1, and when set to 2, the log buffer is written to the file when each transaction is committed, but the log file is not flushed to disk. Refresh of log files occurs only once per second. So, in theory, an operating system crash or power loss will only lose the last second of the transaction.

The following is a detailed explanation of the three values set to 0prime1 and 2:

If innodb_flush_log_at_trx_commit is set to 0 innodb_flush_log_at_trx_commit log buffer will be written to log file once a second, and the flush (brush to disk) operation of log file will occur at the same time. In this mode, writing to disk is not actively triggered when the transaction is committed.

If innodb_flush_log_at_trx_commit is set to 1, MySQL will write log buffer data to log file and flush (flush to disk) each time the transaction commits.

If innodb_flush_log_at_trx_commit is set to 2, MySQL writes log buffer data to log file. But the flush (flush to disk) operation does not happen at the same time.

In this mode, MySQL performs a flush (flush to disk) operation once a second.

4) .sync_binlog, it is recommended to set it to 0

Sync_binlog=0, when a transaction is committed, MySQL does not do disk synchronization instructions such as fsync to refresh the information in binlog_cache to disk, but let Filesystem decide when to synchronize, or synchronize to disk after the cache is full.

Sync_binlog=n, after every n transaction commits, MySQL will issue a disk synchronization instruction such as fsync to force the data in binlog_cache to be written to disk.

In autocommit mode, each statement executed will be written to the binlog, otherwise each transaction will be written once. If the value of sync_binlog is positive, every time the statement or the number of transactions set by the sync_binlog parameter is written to binlog, the MySQL server synchronizes its binary logs to the hard disk. The default value is 0 and is not synchronized with the hard drive. A value of 1 is the safest option, because when crashing

You can lose at most one statement or transaction in binlog. But it's also the slowest option, and it's expensive.

The difference between innodb_flush_log_at_trx_commit and sync_binlog parameters:

Innodb_flush_log_at_trx_commit controls how the table stored by innodb is written, and how the file ib_logfile is refreshed.

Sync_binlog controls the writing of binlog and the refresh mode of mysql-bin.000.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.