Performance Optimization series eight: MySQL configuration optimization

One, the key configuration 1. Location of the configuration file

MySQL configuration file

/ETC/MY.CNF or/ETC/MY.CNF.D/SERVER.CNF

A few key documents:
. pid file, which records the process ID
. sock file, which is the socket interface used for internal communications, is faster than 3306
. log files, log files
. cnf or. conf files, configuration files
Installation directory: Basedir
Data Catalog: DataDir

2. Where to save your data

Basic configuration, specifying data directory, MY.CNF or SERVER.CNF

[Mysqld]
user = MySQL
Port = 3306
Socket =/data/3306/mysql.sock, #这里指定了一个特别的连接
Basedir =/usr/local/mysql
DataDir =/data/3306/data
[Client]
Port = 3306
Socket =/data/3306/mysql.sock, also declare it on the client, command line to use

3. Query the cache to open write frequently to the database, do not open the query cache

Query_cache_size
How do you deal with the data in Query_cache? The first thing to do is to invalidate the Query_cache and the table-related statements, and then write the update. So if the query_cache is very large, the query structure of the table is more, the query statement invalidation is slow, an update or insert will be very slow, so see is update or insert how slow. Therefore, in the database write volume or update volume is also relatively large system, this parameter is not suitable for allocation too large. And in high concurrency, write a large-scale system, it is recommended to disable this function.
Query_cache_limit
Specifies the buffer size that can be used by a single query, which defaults to 1M
Query_cache_min_res_unit
The default is 4KB, setting a large value for big data query is good, but if your query is small data query, it is easy to create memory fragmentation and waste

Description: The way to disable query caching is to directly comment out the configuration of the query cache, such as #query_cache_size=1m, so that you can

4. Other caches that need to be open read cache, thread cache, sort cache

Sort_buffer_size = 2M
Connection-level parameters. Too general causes insufficient memory when the number of connections increases.

Max_allowed_packet = 32M
The maximum number of message transfers in a network transmission. The system defaults to 1MB and the maximum value is 1GB, which must be set to a multiple of 1024.

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

Tmp_table_size = 256M
The default size is 32M. GROUP by not much problem

Max_heap_table_size = 256M
Key_buffer_size = 2048M
The buffer size of the index, which can be set to 256MB or 384MB for a server that has about 4GB in it.

Read_buffer_size = 1M
Read_rnd_buffer_size = 16M
When you sort a query, MySQL first scans the buffer again to avoid disk searches

Bulk_insert_buffer_size = 64M
Bulk INSERT data cache size, can effectively improve the efficiency of insertion, the default is 8M

InnoDB Cache

Innodb_buffer_pool_size = 2048M
With InnoDB, you can set it up to 70-80% of available memory. Some of the rules that apply to Key_buffer are--if you have a small amount of data and do not burst, you do not need to set the innodb_buffer_pool_size too large.

Innodb_additional_mem_pool_size = 16M
The maximum number of message transfers in a network transmission. The system defaults to 1MB and the maximum value is 1GB, which must be set to a multiple of 1024.

Innodb_log_files_in_group = 3
Loop to write log files to multiple files. Recommended setting is 3

Innodb_lock_wait_timeout = 120
The InnoDB has its built-in deadlock detection mechanism that can cause incomplete transactions to be rolled back. innodb_file_per_table = 0 Exclusive table space, off

5. Number of connections

Open_files_limit = 10240
Number of files allowed to open

Back_log = 600
How many requests within a short period of time can be present in the stack

Max_connections = 3000
MySQL default maximum number of connections is the maximum number of connections allowed for the 100,MYSQL server 16384

Max_connect_errors = 6000
Sets the maximum number of times each host's connection request will be interrupted, and when this number is exceeded, the MySQL server disables the host's connection request

Thread_cache_size = 300
Re-use the number of threads saved in the cache

Thread_concurrency = 8
Thread_concurrency should be set to twice times the total number of CPU cores

Thread_stack = 192K
The stack size of each thread, the default value is large enough to satisfy normal operations. Can be set to a range of 128K to 4GB, the default is 192KB.

6. Thread Pool Related parameters

Thread Pools rarely match

Thread_handling
Represents a thread pool model.

Thread_pool_size
Represents the number of group of thread pools, typically set to the current CPU cores. Ideally, a group is an active worker thread that is designed to take full advantage of the CPU.

Thread_pool_stall_limit
Used by the timer thread to periodically check whether the group is "stuck" and the parameter represents the interval of detection.

Thread_pool_idle_timeout
When a worker is idle for a period of time, it automatically exits, ensuring that the worker threads in the thread pool remain at a lower level when the request is satisfied. 60 seconds

Thread_pool_oversubscribe
This parameter is used to control the number of "overclocking" threads on the CPU core. This parameter setting value does not include the listen thread count.

Threadpool_high_prio_mode
Represents the mode of the priority queue.

Thread_pool_max_threads
Limit the maximum number of threads in the thread pool, and more threads will no longer be created, with a default of 100000.

Thread_pool_high_prio_tickets
The maximum number of word orders is placed in the high priority queue, which defaults to 4294967295. It only works when Thread_pool_high_prio_mode is transactions.

Description

The minimum unit of thread processing is statement (statement)
thread pool implementation on the server side, by creating a certain number of thread service DB requests, the minimum unit of the thread pool service is a statement, that is, a thread can correspond to multiple active connections, relative to the one-conection-per-thread of a thread serving a connection.

7. Slow query Log

Slow_query_log
Whether to turn on the slow query log, 1 means on, 0 is off.

Log-slow-queries
Old version (version 5.6 below) MySQL database slow query log storage path. You can not set this parameter, the system will default to a default file Host_name-slow.log

Slow-query-log-file
New version (5.6 and later) MySQL database slow query log storage path. You can not set this parameter, the system will default to a default file Host_name-slow.log

Long_query_time
A slow query threshold that logs when the query time exceeds the set threshold value.

Log_queries_not_using_indexes
Queries that do not use indexes are also logged in the slow query log (optional).

Log_output
How the log is stored. log_output= ' file ' means that the log is stored in a file and the default value is ' file '. log_output= ' table ' means that the log is stored in the database so that the log information is written to the Mysql.slow_log table. MySQL database supports the same two types of log storage, the configuration is separated by commas, such as: log_output= ' file,table '. Logging to the system's dedicated log table is more resource-intensive than logging to a file, so it is recommended to log to a file if you need to enable slow query logging and you need to be able to achieve higher system performance.

Second, the Monitoring tool 1. Innotop Tools

Installation: Yum Install Innotop
Start: Innotop-u root-p ' 123 '
Help:?

Description

Innotop is a real-time tool that only looks at the current situation and cannot record the historical situation

When viewing Help, type one?

Help the ABCD inside ..... You can view the corresponding indicator, such as query list input q, query buffer input b, command statistics input C

2. Lepus tools

Http://www.lepus.cc/page/product

Description

Lepus is an open-source database monitoring tool that can monitor a variety of databases, monitor multiple databases, and view historical situations

Performance Optimization series eight: MySQL configuration optimization