MySQL 5.6.24 on-line version configuration file parsing

On-line MySQL server configuration file parsing

Innodb_buffer_pool_size

A very important parameter for configuring the InnoDB buffer pool, if the database has only the OH InnoDB table, the recommended configuration amount is 75% of total memory

Select Engine,round (SUM (data_length + index_length)/1024/1024,1) as ' total MB '

From Information_schema.tables where Table_schema (' information_schema ', ' Performance_schema ') group by engine;

Innodb_buffer_pool_instances

You can control the size of the buffer pool into several, by default there is only one buffer pool, the new parameters introduced after version 5.5, if it is a buffer, it is possible to increase the frequency of blocking

Innodb_log_buffer_size

InnoDB log cache size, because the log will be refreshed every second, so generally not too big

Innodb_flush_log_at_trx_commit

This is a very critical parameter, the InnoDB IO efficiency is very large, the default value is 1, you can take 0,1,2 three values, the general recommendation is 2, but if the data security requirements are relatively high, use the default value of 1.

Decide how long MySQL is going to flush changes to disk. If it is 0, it is not refreshed each time it is committed, and the transaction is flushed to disk every second. If 2, the change is flushed to the buffer each time the transaction commits, and then after 1 seconds, the log buffer changes are flushed to disk.

Innodb_read_io_threads

Innodb_write_io_threads

The above two parameters determine the number of IO threads InnoDB Read and write, the default is 4, after 5.5, you can adjust the values of the two parameters according to the core number of the CPU

Innodb_file_per_table

Key parameters, control innodb each table uses a separate table space, which is off by default, which means that all tables are built into the shared table space.

There are two main problems: IO read-write conflict, cannot shrink the table space, to export the entire table space table, and then delete to shrink the space.

Optimize configuration parameters with third-party tools Percona Company Online Configuration Wizard.

Key_buffer_size

Specifies the buffer size to use for the index, increasing the index to which it can be better processed

Query-cache-type = 1

Query-cache-size = 256M

Turn on the query caching feature

Set the query cache size to 256M, can you check the value of the device through the status variable is it reasonable?

Mysql> Show status like ' qcache% ';

+-------------------------+-----------+

| variable_name | Value |

+-------------------------+-----------+

| Qcache_free_blocks | 75 |

| Qcache_free_memory | 268195744 |

| Qcache_hits | 1531 |

| Qcache_inserts | 377996 |

| Qcache_lowmem_prunes | 0 |

| qcache_not_cached | 63820 |

| Qcache_queries_in_cache | 95 |

| Qcache_total_blocks | 283 |

+-------------------------+-----------+

Query caching is divided into two types:

1, can be put into the query buffer, but also can be divided into the hit, now miss, ready for the next query hit.

2, can not be put into the query buffer;

Explain in detail the meaning of each state variable:

Qcache_free_blocks the number of memory blocks in the query cache, which is currently idle, indicates a large number of possible fragments.

Qcache_free_memory the number of idle memory in the query buffer

Qcache_hits number of query caches hit

The number of qcache_inserts misses in the query cache, ready to be added to the hit buffer.

Qcache_lowmem_prunes the number of buffers that need to be removed from the buffer because of insufficient memory for the query hit buffer.

The number of qcache_not_cached that can not be placed in the query buffer.

Qcache_queries_in_cache the number of SELECT statements that are currently ' registered ' in Query_cache

Qcache_total_blocks total number of blocks in the buffer

Key_buffer_size

This parameter is used to set the size of the index block cache, it is shared by all the threads, strictly speaking, it determines the speed of database indexing processing, especially the speed of index reading, then how do we know key_buffer_size is set reasonable, can generally check the status Key_read_ Request and key_reads, such as key_reads/key_read_request should be as low as possible, such as 1:100,1:1000,1:10000

[MySQL]

# CLIENT #

Port = 3306

Socket =/tmp/mysql.sock

[Mysqld]

# General #

user = MySQL

Default-storage-engine = InnoDB

Socket =/tmp/mysql.sock

Pid-file =/data/mysql_data/mysql.pid

# MyISAM #

Myisam-recover = Force,backup

# SAFETY #

Max-connect-errors = 1000000

Skip-name-resolve

Sysdate-is-now = 1

InnoDB = Force

Innodb-strict-mode = 1

# DATA STORAGE #

DataDir =/data/mysql_data/

# BINARY LOGGING #

Log-bin =/data/mysql_data/mysql-bin

Expire-logs-days = 14

Sync-binlog = 1

# CACHES and LIMITS #

Tmp-table-size = 512M

Max-heap-table-size = 512M

Query-cache-type = 1

Query-cache-size = 256M

Max-connections = 5000

Thread-cache-size = 50

Open-files-limit = 65535

Table-definition-cache = 4096

Table-open-cache = 1024

Key_buffer_size = 512M

Max_allowed_packet = 128M

Sort_buffer_size = 128M

Read_buffer_size = 128M

Read_rnd_buffer_size = 128M

Myisam_sort_buffer_size = 512M

Thread_cache_size = 16

wait_timeout=172800

interactive_timeout=172800

# INNODB #

Innodb-flush-method = O_direct

Innodb-log-files-in-group = 2

Innodb-log-file-size = 512M

Innodb-flush-log-at-trx-commit = 2

innodb-file-per-table = 1

Innodb-buffer-pool-size = 8G

innodb_log_buffer_size=1m

Innodb_lock_wait_timeout =50

Innodb_read_io_threads = 4

Innodb_write_io_threads = 4

# LOGGING #

Log-error =/data/mysql_data/mysql-error.log

Log-queries-not-using-indexes = 1

Slow-query-log = 1

Slow-query-log-file =/data/mysql_data/mysql-slow.log

#Rep

Binlog-format=row

Log-slave-updates=true

server_id = 13

#Other

Sql_mode= "No_engine_substitution"

MySQL 5.6.24 on-line version configuration file parsing