MySQL memory and IO optimizations some important parameters

1, Innodb_flush_log_at_trx_commit

0: The log buffer is written once per second to the log file, and the log file is refreshed with disk operations, but no action is taken on a transactional commit.

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

2: In each commit, the log buffers are written to the file, but the log files are not refreshed for disk operations. Refreshes the log file once per second.

The default value is 1, which is the safest setting, where each transaction commits a log file (buffer log) from log buffer, and the disk is actually flushed, but there is some loss of performance.

If you can tolerate a loss of some data when a database crashes, setting it to 0 or 2 will improve.

Set to 0, it is the least secure and most efficient to lose transactions that are not written to the log file when the database crashes, with a maximum loss of 1 seconds of transactions.

When set to 2, because it is not flushed to disk, but has been written to the log file, so as long as the operating system does not crash, then there is no loss of data, than set to 0 more secure.

In the MySQL manual, this parameter is recommended to be set to 1 in order to ensure the persistence of transactions and the tolerance and consistency of replication settings.

2, Innodb_log_file_size

The size (in megabytes) of each log file in the log group. If n is the number of log files in the log group, the ideal value is 1M to the 1/n of the buffer pool (bufferpool) size set below.

A larger value reduces disk I/O by reducing the number of times the buffer pool is flushed. But a large log file means that it takes longer to recover the data when it crashes. Generally use about 512M, depending on the space of the server.

3, Innodb_log_buffer_size

InnoDB the buffer size before the log is written to the log disk file. The ideal value is around 8M. A large log buffer allows the transaction to run without having to save the log to disk and only to the transaction being committed (commit).

Therefore, if you have large transactions, setting a large log buffer can reduce disk I/O.

4, Innodb_buffer_pool_size

The InnoDB is used to cache data and index memory buffer sizes. Larger settings enable you to reduce disk I/O when accessing data. It can be set to 80 of physical memory on a dedicated database (INNDB dedicated) server.

As with Key_buffer, if the amount of data is smaller it does not increase, so do not set this value too high to increase memory usage. Because of the use of physical memory, contention may affect the operating system's page invocation.

5, Innodb_additional_mem_pool_size

The InnoDB is used to store the memory pool size of the data dictionary information and other internal structures (internal data structures). The ideal value is 2M,

If you have more tables, you need to redistribute them here. If InnoDB runs out of all the memory in this pool, it allocates memory from the operating system and writes the error information to the MySQL error log.

6, Innodb_file_io_threads

The file I/O thread in InnoDB. typically set to 4.

7, Sync_binlog

The frequency at which binary logs (binary log) are synchronized to disk. Binary log writes to disk after each write to Sync_binlog. If Autocommit is turned on, each statement is written once by binary log, otherwise each transaction is written once.

The default value is 0, does not actively synchronize, and relies on the operating system itself to flush the contents of the file to disk

Set to 1 The safest, synchronize a binary log once per statement or transaction, even if it crashes, it loses at most one statement or transaction log, but is therefore also the slowest.

In most cases, the consistency of the data is not strictly required, so the Sync_binlog will not be configured to 1, in order to pursue high concurrency, improve performance, can be set to 100 or directly with 0.

8. Write/read Thread

Number of asynchronous IO threads

Innodb_write_io_threads=16

Innodb_read_io_threads=16

(This parameter needs to be added in the configuration file, restart the MySQL instance to be effective) the number of dirty page write threads, increase this parameter can improve the write performance

9, innodb_max_dirty_pages_pct

The maximum dirty page percentage, when the percentage of dirty pages in the system exceeds this value, InnoDB writes to write the updated data in the page to the disk file. By default 75, it is hard to reach this ratio for SSD drives that are generally popular today. Can be adjusted according to the actual situation between 75-80

10, innodb_io_capacity=5000

The number of dirty pages refreshed one time when dirty pages are flushed from the buffer. The following are generally recommended settings based on the capacity of disk IOPS:

SAS 200

SSD 5000

Pci-e 10000-50000

11, Innodb_flush_method=o_direct (this parameter needs to restart the MySQL instance to be effective)

Controls the open, brush-write mode of the InnoDB data file and redo log. There are three values: Fdatasync (Default), O_dsync,o_direct.

Fdatasync mode: When writing data, write this step does not need to really write to the disk is completed (may write to the operating system buffer will return to completion), the real completion is the flush operation, buffer to the operating system to flush, Also, the metadata information for the file needs to be updated to disk.

O_dsync mode: The Write log operation is done in write, and the data file is written in flush this step through fsync.

O_direct mode: The data file write operation is directly from the MySQL InnoDB buffer to the disk, and not through the operating system buffer, and the real completion is also in the flush this step, the log will go through the OS buffer, in a large number of random write environment O_ Direct is more efficient than fdatasync, with more sequential writes, or the default Fdatasync more efficient.

12, Innodb_adaptive_flushing

Set to ON (makes flushing dirty pages smarter)

13, innodb_max_dirty_pages_pct

This parameter data control the proportion of dirty pages if it is innodb_plugin or more than MySQL5.5 version, it is recommended that the parameters can be set to 75%-90% line. If it is a large number of writes and the data being written is not too active, consider setting the value lower. If the data being written or updated is hot data, consider setting this value to: 95%

This article is from the users and stored procedures blog, so be sure to keep this source http://9548010.blog.51cto.com/9538010/1744203

MySQL memory and IO optimizations some important parameters