MySQL Configuration Explained

MySQL Configuration explained 650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M00/98/D8/wKioL1lBAR3AFi9AAABlcDgDoCw255.jpg " Title= "11.jpg" alt= "Wkiol1lbar3afi9aaablcdgdocw255.jpg"/>

MySQL installed, we are from the installation package support-files inside a copy of the last class configuration file, the default MySQL configuration file is under/ETC/MY.CNF, in fact, this path or file name we can modify, in the startup script to modify.

Here are some common settings:

[Mysqld]

Socket =/tmp/mysql.sock

#为MySQL客户程序与服务器之间的本地通信制定一个套接字文件 (default is/var/lib/mysql/mysql.sock file under Linux)

Port = 3306

#指定MySQL侦听端口

Skip-name-resolve

#禁止MySQL对外部连接进行DNS解析, this option eliminates the time for DNS resolution for MySQL, but it is important to note that if you turn on this option, all remote host connection authorizations will use IP address, otherwise MySQL will not be able to handle connection requests properly.

Key_buffer = 256M

#key_buffer是利用索引块的缓冲区大小, increase the index (for all reads and multiple writes) that he can get better processing. The index is shared by all threads, and the size of the key_buffer depends on the size of the memory.

Table_open_cache = 256

#MySQL每打开一个表, some data is read into the Table_open_cache cache, and when MySQL does not find the appropriate information in this cache, it is read on the disk. The default value is 64, assuming that the system has 200 concurrent connections, you need to set this parameter to 200*n (N is the number of file descriptors required for each link), and when the Table_open_cache is set to very large, if the system cannot handle so many file descriptors, the client will fail. Connection not on.

Max_allowed_packet = 1M

#接受的数据包大小: It is safe to increase the value of this variable because additional memory is allocated only when needed. For example, more memory is allocated only if a long query is issued or if the mysqld must return a large result row. The fact that the variable goes to a smaller default value is a precaution that has captured the error packets between the client and the server and ensured that memory overflows are not caused by accidental use of packets.

Sort_buffer_size = 1M

#MySQL执行排序使用的缓冲大小. If you want to increase the speed of the order by, first see if you can let MySQL use the index instead of the extra sort stage. If you cannot try to increase the size of the sort_buffer_size variable.

Read_buffer_size = 1M

#读查询操作所能使用的缓冲区大小. As with Sort_buffer_size, the allocated memory for this parameter is also exclusive for each link. Requests for sequential scans of a table are assigned a read-in buffer, and MySQL allocates a memory cache flush for it. If the sequential scan request is frequent and the frequent scan is considered too slow, you can increase its performance by increasing the value of the variable and the size of the memory buffer.

Join_buffer_size =1m

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

Query_cache_size= 16M

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

Read_rnd_buffer_size = 4M

#随机读缓冲区大小. When rows are read in any order (for example, in sort order), a random read buffer is allocated. When sorting queries, MySQL scans the side buffer first to avoid disk searches, improve query speed, and, if you need to sort large amounts of data, raise the value appropriately. However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriately as possible to avoid excessive memory overhead.

myisam_sort_buffer_size=64m

#myisam_sort_buffer_size This buffer is primarily used to fix the memory used by the sort index during the table process or the memory size used by the sort index when indexing, typically 4G memory to 64M.

Thread_concurrency =8

# Thread_concurrency This value is set to twice times the number of CPU cores

Thread_cache_size =8

# thread_cache_size indicates that the number of threads saved in the cache can be re-used,

Refer to the following values: 1G memory, 8 2G memory, 16,4g above set to 64

Max_connections =1000

# max_connections Maximum number of connections, according to the amount of business request appropriate adjustment, set 500 enough

max_connect_errors=6000

# max_connect_errors is a security-related counter value in MySQL that is responsible for blocking excessive attempts by clients that fail to prevent brute-force password violations. is not much related to performance. In order to avoid some errors we generally set relatively large, for example, 10000, (for the same host, if there is more than the number of values of this parameter interrupt error connection, the host will be banned from the connection.) If you need to unblock the host, execute: FLUSH host.)

Open_files_limit = 65535

#mysql打开的文件描述符限制, default minimum 1024

Skip_locking

#避免MySQL的外部锁定, reducing the chance of error increases stability.

Wait_timeout =8

#表示空闲的连接超出时间, the default is 28800s, this parameter is used with Interactive_timeout, that is, you want wait_timeout to take effect, you must set the Interactive_timeout at the same time.

Interactive-timeout =8

Long_query_time =1

#慢查询日志的超出时间

Log_slow_queries =/path/to/slow_querics

#慢查询日志路径 must be used together with the above parameters

MySQL Tuning

MySQL tuning can be done in several ways:

1. Architecture Layer:

Do from the library, to achieve read and write separation;

2. System level:

Increased memory;

RAID0 or RAID5 the disk to increase the read and write speed of the disk;

You can re-mount the disk and add the Noatime parameter, which reduces the I/O to the disk;

3. mysql itself tuning:

(1) If master-slave synchronization is not configured, the Bin-log function can be turned off to reduce disk I/O

(2) Add skip-name-resolve to my.cnf to avoid slow MySQL execution due to delayed parsing of host names

(3) Adjust several key buffer and cache. The basis of adjustment, mainly according to the state of the database debugging. You can refer to 5 for tuning.

4. Application level:

View the slow query log, based on the SQL statements in the slow query log optimizer, such as increasing the index

5. Adjust several key buffer and cache

1) Key_buffer_size first can be based on the system's memory size set it, probably a reference value: 1G or less memory settings 128m;2g/256m; 4g/384m;8g/1024m;16g/2048m. This value can be checked by checking the status values key_read_requests and key_reads to see if the key_buffer_size settings are reasonable. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above status values can be obtained using the show status like ' key_read% '). Note: This parameter value setting is too large to be the overall efficiency of the server down!

2) Table_open_cache open a table, will temporarily put the data inside the table in this part of memory, generally set to 1024 is enough, its size we can measure by this way: if you find Open_tables equals Table_cache, And Opened_tables is growing, you need to increase the value of Table_cache (the above status values can be obtained using show status like ' Open%tables '). Note that you cannot blindly set the Table_cache to a very large value. If set too high, it may cause insufficient file descriptors, resulting in performance instability or connection failures.

3) Sort_buffer_size the size of the buffer that can be used when the query is sorted, the allocated memory for that parameter is per-connection exclusive! If there are 100 connections, the actual allocated total sort buffer size is 100x4 = 400MB. Therefore, the recommended setting for a server that has around 4GB is 4-8m.

4) read_buffer_size The buffer size that can be used by the read query operation. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!

5) join_buffer_size The buffer size that can be used by the Federated query operation, as with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!

6) Myisam_sort_buffer_size This buffer is primarily used to fix the memory used by the sort index during the table process or the memory size used to sort the index when indexing, generally 4G memory to 64M.

7) query_cache_size MySQL query operation buffer size, adjusted by the following practices: SHOW STATUS like ' qcache% '; If qcache_lowmem_prunes this parameter records how many queries are removed from the query cache because of insufficient memory. With this value, the user can adjust the cache size appropriately. If the value is very large, it indicates that buffering is often insufficient, and the cache size needs to be increased; Qcache_free_memory: Query cache memory size, through this parameter can be very clear know the current system query memory is sufficient, is more, or not enough, we can make adjustments according to the actual situation. In general, 4G memory setting 64M is sufficient.

8) thread_cache_size indicates that the number of threads stored in the cache can be re-used, referring to the following values:1g-> 8 2g-> 3g-> >3G-> 64

In addition, there are a few more key parameters:

9) Thread_concurrency This value is set to twice times the number of CPU cores

Wait_timeout indicates idle connection timeout time, default is 28800s, this parameter is used with Interactive_timeout, that is, for wait_timeout to take effect, you must set the Interactive_ Timeout, it is recommended that both of them are set to 10

One) max_connect_errors is a security-related counter value in MySQL that is responsible for blocking excessive attempts by clients that fail to prevent brute-force password violations. is not much related to performance. In order to avoid some errors, we generally set a larger, for example, 10000

Max_connections Maximum number of connections, according to the amount of business request to adjust appropriately, set 500 enough

Max_user_connections refers to the maximum number of connections that the same account can connect to the MySQL service at the same time. Set to 0 to indicate no limit. Usually we set it to 100 enough

This article from "12350027" blog, declined reprint!

MySQL Configuration Explained