Interview questions | What configuration files can be modified by MySQL to optimize?

This article is reproduced from itpub public number

What is specifically modified in the configuration file? If the interviewer asks you, what should you say? You want to, you sit in a room.

Server MySQL performance optimization, there are two general directions to consider, the first is the server hardware, and the other is the MySQL own my.cnf configuration file.

Server disk, CPU and memory, these are the factors to consider

1, the disk's I/O capability, that is, its seek ability, the current SCSI high-speed rotation is 7200 rpm, such a speed, once the user volume of access, the pressure on the disk will be too large, if the daily site PV in 150w, such a general configuration can not meet such a demand. At this time the limit is not small, will have disk array, but choose which option? Raid-0+1, do not choose RAID5, this option does not reach the result you want. For specific reasons, such as the following comparison of the disk array of similarities and differences.
2,CPU, the capabilities of the processor, are not specifically elaborated here.
3, physical memory, physical memory more than 2G bar, some of which is also 4 G. More high-end will not talk about.

MySQL modifies the configuration file.

MY.CNF Here's the Mysqld field, the other fields have little effect.

[Mysqld]

Port = 3306
ServerID = 1
Socket =/tmp/mysql.sock
Skip-locking
#避免MySQL的外部锁定, reducing the chance of error increases stability.

Skip-name-resolve

#禁止MySQL对外部连接进行DNS解析

Use this option to eliminate the time for DNS resolution for MySQL. However, it is important to note that if this option is turned on, all remote host connection authorizations will use IP address mode, otherwise MySQL will not be able to handle the connection request properly!

Back_log = 384

#back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中.

If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections. Different operating systems have its own limitations on this queue size. Attempting to set a limit of back_log above your operating system will be invalid. The default value is 50.

For Linux systems, the recommended setting is an integer less than 512.
Key_buffer_size = 256M

#key_buffer_size指定用于索引的缓冲区大小, increase it to get better index processing performance.

The parameter can be set to 256M or 384M for a server that has around 4GB. Note: This parameter value setting is too large to be the overall efficiency of the server down!
Max_allowed_packet = 4M
Thread_stack = 256K
Table_cache = 128K
Sort_buffer_size = 6M

#查询排序时所能使用的缓冲区大小.

Note: The allocated memory for this parameter is per-connection exclusive, and if there are 100 connections, then the actual allocated total sort buffer size is 100x6 = 600MB. Therefore, the recommended setting for a server that has around 4GB is 6-8m.
Read_buffer_size = 4M

#读查询操作所能使用的缓冲区大小.

As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.
Join_buffer_size = 8M

#联合查询操作所能使用的缓冲区大小, like Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.

Myisam_sort_buffer_size = 64M
Table_cache = 512
Thread_cache_size = 64
Query_cache_size = 64M

#指定MySQL查询缓冲区的大小.

It can be observed in the MySQL console that if the value of the qcache_lowmem_prunes is very large, there is often insufficient buffering, and if the value of qcache_hits is very large, the query buffer is used very frequently, and if the value is small it can affect efficiency. Then consider not querying the buffer; qcache_free_blocks, if the value is very large, it indicates that there is a lot of fragmentation in the buffer.
Tmp_table_size = 256M
Max_connections = 768

#指定MySQL允许的最大连接进程数.

If the too many connections error is frequently encountered when accessing the forum, you need to increase the parameter value.
Max_connect_errors = 10000000
Wait_timeout = 10

#指定一个请求的最大连接时间, the server with about 4GB of memory can be set to 5-10.
Thread_concurrency = 8

#该参数取值为服务器逻辑CPU数量

In this example, the server has 2 physical CPUs, and each physical CPU supports H.T Hyper-threading, so the actual value is 4*2=8
Skip-networking

#开启该选项可以彻底关闭MySQL的TCP/IP connection, do not turn on this option if the Web server accesses the MySQL database server as a remote connection! Otherwise it will not connect properly!
table_cache=1024

#物理内存越大, the larger the setting. Default is 2402, set to 512-1024 best
innodb_additional_mem_pool_size=4m

#默认为2M
Innodb_flush_log_at_trx_commit=1

#设置为0就是等到innodb_log_buffer_size列队满后再统一储存, default is 1
Innodb_log_buffer_size=2m

#默认为1M
Innodb_thread_concurrency=8

#你的服务器CPU有几个就设置为几, the recommended default is typically 8
key_buffer_size=256m
#默认为218, tune to 128 best
tmp_table_size=64m

#默认为16M, tune to 64-256 most hanging
read_buffer_size=4m

#默认为64K
read_rnd_buffer_size=16m

#默认为256K
sort_buffer_size=32m

#默认为256K
thread_cache_size=120

#默认为60
query_cache_size=32m
※ It is worth noting that:

Many situations require specific analysis of specific circumstances

First, if the key_reads is too large, it should be my.cnf in the key_buffer_size to become larger, keep key_reads/key_read_requests at least 1/100, the smaller the better.

Second, if the qcache_lowmem_prunes is large, it is necessary to increase the value of query_cache_size.

Interview questions | What configuration files can be modified by MySQL to optimize?