MySQL configuration file parameters in a detailed

A

[Client]
Port = 3306
Socket =/tmp/mysql.sock

[Mysqld]
Port = 3306
Socket =/tmp/mysql.sock

Basedir =/usr/local/mysql
DataDir =/data/mysql
Pid-file =/data/mysql/mysql.pid
user = MySQL
Bind-address = 0.0.0.0
Server-id = 1 #表示是本机的序号为1, which is generally the meaning of master

Skip-name-resolve
# Disable MySQL for DNS resolution of external connections and use this option to eliminate the time for DNS resolution for MySQL. But be aware that if you turn on this option,
# All remote host connections are authorized to use IP address, otherwise MySQL will not be able to handle connection requests properly

#skip-networking

Back_log = 600
# Number of connections MySQL can have. This works when the primary MySQL thread gets very many connection requests in a very short period of time,
# then the main thread takes some time (albeit very short) to check the connection and start a new thread. The Back_log value indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops answering a new request.
# If you expect to have a lot of connections in a short period of time, you need to add it. That is, if MySQL's connection data reaches Max_connections, the new request will be present in the stack,
# to wait for a connection to release resources, the number of the stack is Back_log, and if the number of waiting connections exceeds back_log, the connection resource will not be granted.
In addition, this value (Back_log) is limited to the size of your operating system's listening queue for incoming TCP/IP connections.
# your operating system has its own limit on this queue size (you can check your OS documentation to find out the maximum value of this variable), trying to set the limit of Back_log above your operating system will be invalid.

max_connections = 1000
# mysql Maximum number of connections, if the server's concurrent connection request volume is larger, it is recommended to increase the number of concurrent connections, of course, this is based on the machine can support the case, because if the number of connections between MySQL will provide a connection buffer for each connection, it will cost more memory, Therefore, to adjust the value appropriately, you can not blindly increase the value set. The ' conn% ' wildcard character can be used to view the number of connections in the current state to decide the size of the value.

Max_connect_errors = 6000
# for the same host, if there is an interrupt error connection that exceeds the number of values for this parameter, the host will be blocked from connecting. To unblock the host, execute: FLUSH host.

Open_files_limit = 65535
# MySQL Open file descriptor limit, default minimum 1024, when Open_files_limit is not configured, compare the values of max_connections*5 and ulimit-n, which big use which,
# when OPEN_FILE_LIMIT is configured, compare the values of open_files_limit and max_connections*5, whichever is larger.

Table_open_cache = 128
# MySQL every time you open a table, it will read some data into the Table_open_cache cache, and when MySQL cannot find the appropriate information in this cache, it will go to the disk to read. Default Value 64
# Assuming 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 connection);
# when the Table_open_cache is set to very large, if the system can not handle so many file descriptors, then the client will fail, the connection is not

Max_allowed_packet = 4M
# The size of the packet accepted; It is safe to increase the value of the variable because additional memory is allocated only when needed. For example, MYSQLD will allocate more memory only if you issue a long query or if mysqld must return a large result row.
# This variable takes a smaller default value as a precaution to capture error packets between the client and server, and to ensure that memory overflows are not caused by accidental use of large packets.

Binlog_cache_size = 1M
# A transaction that, when not committed, logs the log to the cache, and persists the log to disk until the commit of the transaction is required. Default Binlog_cache_size size 32K

Max_heap_table_size = 8M
# defines the size of memory table that the user can create. This value is used to calculate the maximum row value for the memory table. This variable supports dynamic change

Tmp_table_size = 16M
# MySQL heap (heap) table buffer size. All unions are completed within a DML instruction, and most unions can be done without a temporary table.
# Most temporary tables are memory-based (HEAP) tables. Temporary tables with large record lengths (the and the length of all columns) or tables containing BLOB columns are stored on the hard disk.
# If an internal heap (stacked) table is larger than Tmp_table_size,mysql, you can automatically change the in-memory heap table to the hard disk-based MyISAM table as needed. You can also increase the size of the staging table by setting the Tmp_table_size option. In other words, if the value is raised, MySQL will increase the size of the heap, which can improve the speed of the join query.

Read_buffer_size = 2M
# MySQL read into buffer size. A request to sequentially scan a table allocates a read-in buffer, and MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of this buffer.
# If the sequential scan requests to the table are very frequent and you think that frequent scans are going too slowly, you can improve their performance by increasing the value of the variable and the size of the memory buffer

Read_rnd_buffer_size = 8M
# mysql Random read buffer size. When rows are read in any order (for example, in sort order), a random read buffer is allocated. When you sort a query,
# MySQL will first scan the buffer to avoid disk search, improve query speed, and if you need to sort large amounts of data, you can adjust the value appropriately. However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriate to avoid excessive memory overhead

Sort_buffer_size = 8M
# MySQL performs sorting using the buffer size. 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 can't, try increasing the size of the sort_buffer_size variable

Join_buffer_size = 8M
# The buffer size that can be used by the union query operation, as with Sort_buffer_size, the allocated memory for this parameter is also per connection exclusive

Thread_cache_size = 8
# This value (default 8) means that the number of threads stored in the cache can be re-used, and if there is room in the cache when disconnected, the client's thread will be placed in the cache.
# If the thread is requested again, then the request will be read from the cache, if the cache is empty or a new request, then the thread will be recreated, if there are many new threads,
# Increasing this value can improve system performance. You can see the effect of this variable by comparing the variables of the connections and threads_created states. (–> indicates the value to adjust)
# According to the physical memory setting rules are as follows:
# 1g-> 8
# 2g-> 16
# 3g-> 32
# Greater than 3g-> 64

Query_cache_size = 8M
#MySQL的查询缓冲大小 (starting with 4.0.1, MySQL provides a query buffering mechanism) using query buffering, MySQL stores the SELECT statement and query results in a buffer,
# Future for the same SELECT statement (case sensitive), the result will be read directly from the buffer. Depending on the MySQL user manual, the use of query buffering can be up to 238% efficient.
# By checking the status value ' qcache_% ', you can know whether the query_cache_size setting is reasonable: If the value of Qcache_lowmem_prunes is very large, it indicates that there is often insufficient buffering,
# If the value of the qcache_hits is also very large, it indicates that the query buffer is used very frequently, the buffer size needs to be increased, and if the value of qcache_hits is small, your query repetition rate is very low.
# In this case, the use of query buffering can affect efficiency, so consider not querying the buffers. Additionally, adding Sql_no_cache in the SELECT statement can make it clear that the query buffer is not used

Query_cache_limit = 2M
#指定单个查询能够使用的缓冲区大小, default 1M

Key_buffer_size = 4M
#指定用于索引的缓冲区大小, increase it to get better processing index (for all read and multiple writes), to the extent that you can afford it. If you make it too big,
# The system will start changing pages and really slows down. The parameter can be set to 384M or 512M for a server that has around 4GB. By checking the status values key_read_requests and Key_reads,
# you can tell 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 is better (the above status values can be obtained using show status like ' key_read% '). Note: This parameter value setting is too large to be the overall efficiency of the server is reduced

Ft_min_word_len = 4
# lexical Minimum length, default 4

Transaction_isolation = Repeatable-read
# MySQL supports 4 types of transaction isolation levels, namely:
# read-uncommitted, read-committed, Repeatable-read, SERIALIZABLE.
# if not specified, MySQL defaults to repeatable-read,oracle default is read-committed

Log_bin = Mysql-bin
Binlog_format = Mixed
Expire_logs_days = #超过30天的binlog删除

Log_error =/data/mysql/mysql-error.log #错误日志路径
Slow_query_log = 1
Long_query_time = 1 #慢查询时间 more than 1 seconds for slow query
Slow_query_log_file =/data/mysql/mysql-slow.log

Performance_schema = 0
Explicit_defaults_for_timestamp

#lower_case_table_names = 1 #不区分大小写

Skip-external-locking #MySQL选项以避免外部锁定. This option is turned on by default

Default-storage-engine = InnoDB #默认存储引擎

innodb_file_per_table = 1
# InnoDB is a standalone tablespace pattern, and each table in each database generates a data space
# Standalone Table Space benefits:
# 1. Each table has a self-contained table space.
# 2. The data and indexes for each table will exist in the table space themselves.
# 3. You can implement a single table to move through different databases.
# 4. Space can be recycled (except for the drop table operation, the meter is not able to recycle)
Disadvantages
# single table increased too large, such as more than 100G
Conclusion
# shared tablespace has few advantages over insert operations. Others do not have a separate table space to perform well. When enabling a stand-alone tablespace, make reasonable adjustments: Innodb_open_files

Innodb_open_files = 500
# Limit the data of the table that InnoDB can open, if the table in the library is particularly many cases, please add this. This value is 300 by default.

Innodb_buffer_pool_size = 64M
# InnoDB uses a buffer pool to hold indexes and raw data, unlike MyISAM.
# The larger you set up, the less disk I/O you need to access the data in the table.
# on a standalone database server, you can set this variable to 80% of the server's physical memory size
# do not set too large, otherwise, due to the competition of physical memory may cause the operating system to break the page.
# Note on 32-bit systems you each process may be limited to 2-3.5g user-level memory limits,
# so don't set it too high.

Innodb_write_io_threads = 4
Innodb_read_io_threads = 4
# InnoDB uses a background thread to process read/write I/O (input and output) requests on the data page, changing according to the number of CPU cores, default is 4
# Note: These two parameters do not support dynamic changes, you need to add this parameter to the MY.CNF, after modification, restart the MySQL service, the allowable value range from 1-64

innodb_thread_concurrency = 0
# The default setting is 0, which means no limit on concurrency, the recommended setting is 0, better to play CPU multicore processing capacity, increase concurrency

Innodb_purge_threads = 1
# Cleanup in InnoDB is a class of operations that periodically reclaim useless data. In previous releases, the purge operation was part of the main thread, which meant that it could clog other database operations at runtime.
# Starting with the mysql5.5.x version, the operation runs on a separate thread and supports more concurrent numbers. The user can set the innodb_purge_threads configuration parameter to select whether the purge operation uses a single
# single-threaded, by default the parameter is set to 0 (not using a separate thread), when set to 1 means a separate purge thread is used. Recommended for 1

Innodb_flush_log_at_trx_commit = 2
# 0: If the value of Innodb_flush_log_at_trx_commit is 0,log buffer, the log file will be written to disk every second, and no action is taken when committing the transaction (execution is performed by the MySQL master thread thread.)
# Redo log buffers are written to the disk's redo log file (REDO log) every second in the main thread. Whether the transaction has been committed or not, the default log file is Ib_logfile0,ib_logfile1
# 1: When set to the default value of 1, log buffer will be written to the log every time the transaction is committed.
# 2: If set to 2, each commit transaction will write the log, but will not perform the brush operation. The log file is brushed on a per-second schedule. Note that there is no guarantee that 100% per second will be brushed to disk, depending on the scheduling of the process.
# each time a transaction commits, the data is written to the transaction log, where the write is only called the file system, and the filesystem is cached, so this write does not guarantee that the data has been written to the physical disk
# The default value of 1 is to ensure complete acid. Of course, you can set this configuration to a value other than 1 in exchange for higher performance, but you will lose 1 seconds of data when the system crashes.
# set to 0, when the mysqld process crashes, it loses the last 1 seconds of the transaction. Set to 2, the last 1 seconds of data will be lost only if the operating system crashes or loses power. InnoDB will ignore this value when doing a recovery.
# Summary
# set to 1 is certainly the safest, but the performance page is the worst (as opposed to the other two parameters, but not unacceptable). If the data consistency and integrity requirements are not high, can be set to 2, if only for performance, such as high concurrent write log server, set to zero for higher performance

Innodb_log_buffer_size = 2M
# This parameter determines the amount of memory used by the log files, in M. Larger buffers can improve performance, but unexpected failures will cause data loss. MySQL developer recommends setting the 1-8m between

Innodb_log_file_size = 32M
# This parameter determines the size of the data log file, larger settings can improve performance, but also increases the time required to recover the failed database

Innodb_log_files_in_group = 3
# to improve performance, MySQL can write log files to multiple files in a circular fashion. Recommended setting is 3

innodb_max_dirty_pages_pct = 90
# InnoDB The main thread flushes the data in the cache pool so that the dirty data scale is less than 90%

Innodb_lock_wait_timeout = 120
# InnoDB transactions can wait a locked timeout number of seconds before being rolled back. InnoDB automatically detects the transaction deadlock in its own locking table and rolls back the transaction. InnoDB Use the Lock tables statement to notice the locking settings. The default value is 50 seconds

Bulk_insert_buffer_size = 8M
# BULK INSERT cache size, this parameter is for the MyISAM storage engine. For increased efficiency when inserting 100-1000+ records at once. The default value is 8M. Can be doubled to the size of the data volume.

Myisam_sort_buffer_size = 8M
# MyISAM Sets the size of the buffer to be used when restoring the table, when the buffer is allocated in repair table or in the order of the MyISAM index when the index is created or the ALTER TABLE is indexed with CREATE index

Myisam_max_sort_file_size = 10G
# if the temporary file becomes more than the index, do not use the Quick Sort index method to create an index. Note: This parameter is given as a byte

Myisam_repair_threads = 1
# If the value is greater than 1, the MyISAM table index (each index in its own line range) is created in parallel in the repair by sorting procedure

Interactive_timeout = 28800
# Number of seconds that the server waits for activity before closing the interactive connection. The interactive client is defined as a client that uses the Client_interactive option in Mysql_real_connect (). Default value: 28,800 seconds (8 hours)

Wait_timeout = 28800
# Number of seconds that the server waits for activity before closing a non-interactive connection. Initializes the session Wait_timeout value based on the global Wait_timeout value or global interactive_timeout value when the thread starts,
# depends on the client type (defined by the connection option Client_interactive Mysql_real_connect ()). Parameter default value: 28,800 seconds (8 hours)
# The maximum number of connections supported by the MySQL server is capped, because each connection is built to consume memory, so we want the client to be connected to MySQL server after the corresponding operation is processed,
# You should disconnect and release the memory that is occupied. If your MySQL server has a lot of idle connections, they will not only consume memory in vain, but if the connection keeps accumulating,
# Eventually the number of connections to MySQL server will be reached, which will be reported as ' too many connections ' error. For the value setting of wait_timeout, it should be judged according to the operation condition of the system.
# After the system has been running for a period of time, you can view the current system's connection status through the show Processlist command, if you find a large number of sleep status of the connection process, the parameter setting is too large,
# you can make the appropriate adjustments smaller. To set Interactive_timeout and wait_timeout at the same time will not take effect.

[Mysqldump]
Quick
Max_allowed_packet = 16M #服务器发送和接受的最大包长度

[Myisamchk]
Key_buffer_size = 8M
Sort_buffer_size = 8M
Read_buffer = 4M
Write_buffer = 4M

Two

[Mysqld]
Port = 3306
ServerID = 1
Socket =/tmp/mysql.sock
Skip-name-resolve
#禁止MySQL对外部连接进行DNS解析, this option can be used 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! Note: If you use WinForm to connect to MySQL, the speed of joining this sentence will be greatly improved.

Skip-locking
# avoid MySQL external lock, reduce the chance of error increase stability.

Back_log = 384
Specifies the number of possible connections for MySQL. When the MySQL main thread receives very many connection requests in a very short time, this parameter takes effect and the main thread takes a short time to check the connection and start a new thread. The value of the Back_log parameter indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops responding to a new request. 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 = 32M
# Key_buffer_size This is very important for the MyISAM table. If you use only the MyISAM table, you can set it to the 30-40% of available memory. A reasonable value depends on the size of the index, the amount of data, and the load-remember, the MyISAM table caches the data using the operating system's cache, so you need to set aside some memory for them, and in many cases the data peso is mostly. Nonetheless, it is always necessary to check that all key_buffer are exploited-. MYI files are only 1GB, while Key_buffer is set to 4GB is very rare. This is a waste of time. If you rarely use the MyISAM table, you also keep the key_buffer_size below 16-32MB to accommodate the temporary table index that is given to the disk.

Innodb_buffer_pool_size = 2.4G
#这对Innodb表来说非常重要. InnoDB is more sensitive to buffering than the MyISAM table. MyISAM can be run under the default Key_buffer_size settings, but InnoDB is snail-like under the default Innodb_buffer_pool_size settings. Since InnoDB caches both data and indexes, there is no need to leave too much memory on the operating system, so if you only need to use InnoDB, you can set it up to 70-80% of usable memory. – 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_pool_size = 20M
#这个选项对性能影响并不太多, at least on an operating system that has almost enough memory to allocate. But if you still want to set it to 20MB (or larger), you need to look at how much memory InnoDB other needs to allocate.

Innodb_log_file_size = 512M
#在高写入负载尤其是大数据集的情况下很重要. The larger the value, the higher the performance, but note that the recovery time may be increased. I often set it to 64-512MB, depending on the size of the server.

Innodb_log_buffer_size =16m
#默认的设置在中等强度写入负载以及较短事务的情况下, server performance is also possible. If there is a peak update operation or a large load, you should consider increasing its value. If its value is set too high, memory may be wasted-it refreshes every second, so there is no need to set the required memory space for more than 1 seconds. Usually the 8-16MB is enough. The smaller the system, the less its value.

Innodb_flush_logs_at_trx_commit = 2
#是否为Innodb比MyISAM慢1000倍而头大? Maybe you forgot to modify this parameter. The default value is 1, which means that each committed update transaction (or statements outside of each transaction) is flushed to disk, which is quite resource intensive, especially when there is no battery backup cache. Many applications, especially those transformed from MyISAM, set its value to 2, which is to not flush the log to disk, but only to the operating system's cache. Logs are still flushed to disk every second, so there is usually no loss of 1-2 updates per second. If you set it to 0, it's a lot faster, but it's also relatively insecure. When the-mysql server crashes, some transactions are lost. Set to 2 command to lose the part of the transaction that was flushed to the operating system cache.

Max_allowed_packet = 4M
Thread_stack = 256K
Table_cache = 128K
Sort_buffer_size = 6M
#查询排序时所能使用的缓冲区大小. Note: This parameter corresponds to the allocated memory that is per connection exclusive! If there are 100 connections, 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
#联合查询操作所能使用的缓冲区大小, as with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!

Myisam_sort_buffer_size = 64M
Table_cache = 512
#打开一个表的开销可能很大. For example MyISAM the Myi file header flag that the table is in use. You certainly don't want this to happen too often, so it's often necessary to increase the number of caches to maximize the cache of open tables. It needs to use the operating system's resources and memory, which is certainly not a problem for the current hardware configuration. If you have more than 200 tables, it might be appropriate to set it to 1024 (each thread needs to open the table) and increase its value if the number of connections is larger. I've seen cases set to 100,000.

Thread_cache_size = 64
#线程的创建和销毁的开销可能很大, because each thread is required to connect/disconnect. I usually set it at least to 16. If there is a large number of hops concurrent connections in the application and the value of threads_created is larger, then I will increase its value. It is intended to create no new threads in the usual operation.

Query_cache_size = 64M
#指定MySQL查询缓冲区的大小. You can observe this by executing the following commands in the MySQL console:

# > SHOW VARIABLES like '%query_cache% ';
# > SHOW STATUS like ' qcache% ';
# If the value of Qcache_lowmem_prunes is very large, it indicates that buffering is not enough, and if the value of qcache_hits is very large, it indicates that the query buffer is used very frequently and if the value is smaller it will affect the efficiency, then you can 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数量 x2, in this case, the server has 2 physical CPUs, and each physical CPU supports H.T Hyper-threading, so the actual value is 4x2 = 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!

Show Status Command
The meaning is as follows:
Aborted_clients Client Illegal interrupt connection count
Aborted_connects number of MySQL failures connected
com_xxx xxx Command execution times, there are many bars
Connections number of connections to MySQL
Created_tmp_disk_tables temporary tables created on disk
Created_tmp_tables temporary tables created in memory
Created_tmp_files number of temporary files
Key_read_requests the number of requests to read a key block from the cache
Key_reads the number of physical reads of a key block from disk
Max_used_connections the number of simultaneous connections used
Open_tables Open Table
Open_files Open files
Opened_tables Open a table
Questions the number of queries submitted to the server
Sort_merge_passes If this value is large, you should increase the Sort_buffer value in the MY.CNF
Number of seconds that the Uptime server has been working

Recommendations for improving performance:
1. If the opened_tables is too large, you should make the Table_cache in the my.cnf bigger.
2. If the key_reads is too large, the my.cnf should be key_buffer_size larger. The cache failure rate can be calculated with key_reads/key_read_requests
3. If the handler_read_rnd is too large, then you write a lot of SQL statement query is to scan the entire table, and do not play the role of the index key
4. If the threads_created is too large, increase the value of thread_cache_size in the my.cnf. Cache hit rate can be calculated with Threads_created/connections
5. If the created_tmp_disk_tables is too large, increase the value of tmp_table_size in MY.CNF and replace the disk-based temporary table with a memory-based

MySQL configuration file parameters in a detailed