MySQL parameter optimization

MySQL parameter optimization

Innodb_buffer_pool_size:

Write Innodb_log_bufferbuffer write full or transaction commit, refresh data large transaction frequently, increase innodb_log_buffer_size size, for a separate MySQL database server recommended set to 75% of physical memory

-------------------------------------------

Innodb_buffer_pool_instances:

Divide the innodb_buffer_pool into different instance each instance independent LRU, FLUSH, Free independent Mutex Control--------------------------------------------

Innodb_log_file_size:

Before MySQL 5.5 and 5.5 innodb logfile Max set to 4GB, in 5.6 later version logfile the largest can be set to 512GB.
The logfile of InnoDB is the transaction log, which is used to recover after MySQL crash. So setting a reasonable size is very important for MySQL performance.
In version 5.5, default is set to 5M. In the new MySQL server, you need to modify this parameter as soon as possible.

--------------------------------------------

Innodb_log_buffer_size:

Write Innodb_log_bufferbuffer or transaction commits first, refresh data large transactions frequently, increase innodb_log_buffer_size size

--------------------------------------------

Innodb_thread_concurrency (Concurrent threads):

innodb_thread_concurrency = 0,innodb Internal Control –kernel_mutex Competition –CPU Context Switch innodb_thread_concurrency set to CPU core number

--------------------------------------------

Innodb_io_capacity:

InnoDB per second The upper limit of the data page of the background process processing IO operation innodb_buffer_pool_size total IO processing capacity innodb_buffer_pool_instances divided into multiple memory blocks, The IO processing capacity of each memory block is: innodb_io_capacity/innodb_buffer_pool_instances--------------------------------------------

innodb_max_dirty_pages_pct:

InnoDB dirty pages refreshed from InnoDB buffer, resulting in Checkpoint dirty page refresh innodb_max_dirty_pages_pct * Innodb_io_ Capacity---------------------------------------------

Innodb_flush_method:

O_dsync: Use O_sync to open and refresh the log file, and use Fsync () to refresh the data file. O_direct: Use O_direct to open the data file and use Fsync () to refresh the log files and data files. On RAID devices, to avoid data being innodb_buffer and RAID multiple caches, set to O_direct mode.

---------------------------------------------

Innodb_file_per_table:

Different table spaces allow you to flexibly set the address of the data directory to avoid the IO competition generated by shared table spaces

------------------------------------------

Innodb_flush_log_at_trx_commit:

0: Log buffer contents and transaction log and data brush disk per second;-----------------------fastest data is the least secure

1: After each transaction commits, writes the Log_buffer content to the transaction log and the data brush disk;-----------Slowest and safest

2: After each transaction commits, the content of the Log_buffer is written to the transaction log, but no data brush disk;---compromise

------------------------------------------

Sync_binlog:

Note that if in autocommit mode, each execution of a statement is written to the binary log once, otherwise each transaction is written once. The default value is 0 and is not synchronized with the hard drive. A value of 1 is the safest option because when you crash, you lose at most one statement/transaction in the binary log, but this is the slowest option.

Dual 1 mode, that is, innodb_flush_log_at_trx_commit=1,sync_binlog=1, the data of the master repository is consistent, and no data is lost. (Would you consider the IO load?) ）

When Sync_binlog=n:

N>0 after each binary log file writes n SQL or n transactions, the cached data of the binary log file is flushed to disk;

N=0 does not actively flush the data of the binary log files to disk, but is determined by the operating system;

Recommended configuration combinations:

n=1,1 suitable for data security requirements are very high, and disk IO write ability to support the business, such as recharge consumption system;

n=1,0 suitable for high data security requirements, disk IO write ability to support the business is not redundant, allowing the repository behind or no replication;

n=2,0 or 2,m (0<m<100) is suitable for data security requirements, allowing the loss of a bit of transaction log, and the delay of replication schema can be accepted;

n=0,0 Disk IO Write capability is limited, no replication or allow replication delay is slightly longer acceptable, for example: Log registration business;

----------------------------------------

Key_buffer_size:

Key_buffer_size can only cache index data for MyISAM or class MyISAM engines, and innodb_buffer_pool_size not only caches index data, but also caches metadata, but for a database system where we only use the InnoDB engine, This parameter value also cannot be set too small, because the temporary table may be used to this key buffer space, the index buffer recommended: 64M

----------------------------------------

Query_cache_type and Query_cache_size:

Query_cache_type=n:

n=0--Disable the function of query caching;

(Some people think that MySQL's query cache in most cases is only a chicken, and the proposal is completely disabled; In a word, if more than 99% of the online environment is read-only, there are few updates, then consider turning on QC, otherwise, do not open. See http://www.wtoutiao.com/p/r9aGUI.html)

n=1--enables the ability to cache all of the required query result sets, except for select Sql_no_cache., and the result set that does not conform to the query cache settings;

n=2--only caches Select Sql_cache ... The query result set of the clause, in addition to the result set that does not conform to the query cache settings;

Query_cache_size:

How big is the query cache setting? You need to consider at least four dimensions:

① the performance impact of query buffers on DDL and DML statements;

② Query the internal maintenance cost of the buffer;

③ Query cache Hit rate and memory usage and so on comprehensive consideration

④ Business Type

----------------------------------------

Max_connections:

The maximum number of MySQL connections, increasing this value increases the number of file descriptors required by the mysqld. If the server has a large number of concurrent connection requests, it is recommended that this value be increased to increase the amount of concurrent connections, of course, this is based on the machine can support the case, because if the number of connections between MySQL will provide a connection buffer for each connection, it will cost more memory, so to adjust the value appropriately, can not blindly increase the value.

If the value is too small, error 1040:too Many connections errors, you can check the number of connections in the current state through the ' conn% ' wildcard to decide the size of the values.

Show variables like ' max_connections '; View the current maximum number of connections setting value

Show status like ' Max_used_connections '; View the maximum number of connections for a response

As follows:

Mysql> Show variables like ' max_connections ';

+ ——————— –+ ——-+

| variable_name | Value |

+ ——————— –+ ——-+

| max_connections | 256 |

+ ——————— –+ ——-+

Mysql> Show status like ' Max%connections ';

+ ——————— –+ ——-+

| variable_name | Value |

+ —————————-+ ——-+

| max_used_connections | 256|

+ —————————-+ ——-+

Max_used_connections/max_connections * 100% (ideal value ≈85%)

If the max_used_connections is the same as max_connections, then it is max_connections set too low or exceed the server load limit, less than 10% is set too large.

Modify the method: Vim/etc/my.cnf (permanent) or directly modify the session global variables (temporary immediate effect, restart MySQL after the failure, restore the original). Therefore, it is recommended to modify the specified parameters so that the my.cnf remains consistent.

[Mysqld]

max_connections=1000

----------------------------------------

MySQL parameter optimization