MySQL configuration file

Objective:

The volume of the database is growing, as a good programmer can not rely too much on the DBA, while available, comb the MySQL configuration knowledge.

Pit: MYSQL.CNF files are generally placed in the/etc/mysql/mysql.cnf or/ETC/MYSQL.CNF, found that the modification is invalid the first time to consider whether to change the wrong path.

Pit: Careful modification of each profile, not knowing clearly the principle of optimization may be causing side effects. do not understand the configuration do not blind a few changes.

First, MySQL basic configuration information

To view the native installed MySQL path

# which mysqld/usr/sbin/1'Default options'

VI mysql.cnf # The following configuration node description, is a personal understanding, reference can be.

[mysqld]# General (Basic configuration information)DataDir=/var/lib/Mysqlsocket=/var/lib/mysql/Mysql.sockpid_file=/var/lib/mysql/Mysql.piduser=Mysqlport=3306
 # Default storage engine Default_storage_engine=Innodb#innodb
# InnoDB buffer pool size innodb_buffer_pool_size=<value>
# InnoDB log file sizeinnodb_log_file_size=<value>innodb_file_per_table=1
 # InnoDB How to write Logs (O_dsync/o_direct/o_sync)Innodb_flush_method=0_direct#myisam
# MyISAM Key Cache size (MyISAM engine only supports key [index] cache, not cache data)key_buffer_size=<value>#Logging
# error log directory Log_error=/var/lib/mysql/mysql-Error.log
# Slow query log directory Slow_query_log=/var/lib/mysql/mysql-Slow.log #Other
# temp Table maximum value tmp_table_size=32mmax_heap_table_size=32M
# Whether to turn on InnoDB query cache Query_cache_type=0query_cache_size=0
 # MySQL Maximum number of connections max_connections=<value>
 # Connection thread buffer pool size Thread_cache=<value>
 # Number of tables open on all connections Table_cache=<value>Open_files_limit=65535[Client]
# Connection type (TCP/IP, socket)socket=/var/lib/mysql/Mysql.sockport=3306

Configuration file configuration options, not many to appear you professional. Some configurations do not have to be set according to business requirements, it is superfluous.

The general system above configuration is sufficient.

Second, let MySQL a little faster

1. Configuring Memory usage

At a critical point, MySQL's correct use of memory has a significant impact on performance.

Where memory is generally consumed we can be divided into two categories: controllable memory and uncontrolled memory, and uncontrolled memory, such as MySQL server running, parsing query and MySQL program management consumption;

How to configure Memory:

1, determine the memory limit that can be used;

2. Determine how much memory to use for each connection to MySQL, such as sort buffers and temporary tables;

3, the operating system needs memory;

4, the remaining memory all left to the MySQL cache;

2, InnoDB cache how to match?

The InnoDB engine is chosen to use the most popular or most stable and best performance, so the configuration of the InnoDB node is particularly important in the configuration file.

There is a popular experience to say that the InnoDB buffer pool size should be set to the current server memory 75%~80% this interval,

In fact, this is an accidental rate, but not always correct.

There is a better way to set the size of the buffer pool:

1, starting from the total amount of server memory;

2, minus the server on the other services may occupy the amount of memory space, and operating system;

3. Subtract the memory required by the MySQL service itself, such as allocating some buffers for each query;

4, minus enough to let the operating system cache InnoDB log files of memory;

5, minus the other configuration of the MySQL buffer and cache required memory, such as MyISAM abnormality, check the storage;

　　

3. Some misunderstandings about buffer pool

Do not think that the buffer pool is only used to store the data you have queried, two times the situation is small, so do not take this configuration seriously.

Data cached by the buffer pool:

At the core of the index, when you read a larger table, the size of the buffer can not be supported, it may be used to index the hard disk query calculation;

The second is to cache some row data, Adaptive hash Index, insert buffer (deferred write), lock, and internal data structure;

MySQL configuration file