Mysql config file my.cnf parameter optimization and Chinese detailed _mysql

MySQL parameter optimization for beginners, is more difficult to understand things, in fact, this parameter optimization, is a very complex thing, for different sites, and its online volume, access, number of posts, network conditions, as well as the machine hardware configuration are related, optimization can not be completed one-time, the need for continuous observation and debugging, is likely to get the best results.

Copy Code code as follows:

[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, use this option to eliminate the time that MySQL does DNS resolution. Note, however, that if this option is turned on,
# All remote host connection authorizations will use IP address mode, otherwise MySQL won't handle connection requests properly

#skip-networking

Back_log = 600
# The number of connections MySQL can have. This works when the main MySQL thread gets a lot of connection requests in a very short 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 on the stack within a short time before MySQL temporarily stops answering the new request.
# If you expect to have a lot of connections in a short time, you need to increase it. In other words, if MySQL's connection data reaches Max_connections, the new request will be on the stack,
# to wait for a connection to free the resource, the number of that stack is back_log, and if the number of pending 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 limits on the size of this queue (you can check your OS documentation to find the maximum value for this variable), and trying to set Back_log above your operating system will be ineffective.

max_connections = 1000
# The maximum number of MySQL connections, if the server has a large number of concurrent connection requests, 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 more connections, between MySQL will provide a connection buffer for each connection, it will cost more memory, So to adjust the value properly, can not blindly improve the set value. You can view the number of connections in the current state by using the ' conn% ' wildcard to decide on 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. If the host is required to be lifted, execute: FLUSH host.

Open_files_limit = 65535
# MySQL Open file descriptor limit, default minimum 1024; When Open_files_limit is not configured, compare the value of max_connections*5 and Ulimit-n, which is the most used,
# when OPEN_FILE_LIMIT is configured, compare the values of Open_files_limit and max_connections*5, which is the most used.

Table_open_cache = 128
# MySQL each open a table, will read some data into the Table_open_cache cache, when the MySQL in this cache can not find the appropriate information, will go to disk read. Default Value 64
# Assuming that the system has 200 concurrent connections, 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 there will be client failure, not connected

Max_allowed_packet = 4M
# packet size accepted, increasing the value of this variable is safe because additional memory is allocated only when needed. For example, mysqld allocates more memory only if you issue a long query or mysqld must return a large result row.
# The smaller default value of this variable is a precaution to capture the error packets between the client and the server and to ensure that there is no memory overflow caused by accidental use of large packets.

Binlog_cache_size = 1M
# a transaction, in the absence of submission, the resulting log, recorded in the cache; When a transaction submission needs to be submitted, the log is persisted to disk. Default Binlog_cache_size size 32K

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

Tmp_table_size = 16M
# MySQL's heap (stacked) table buffer size. All unions are completed within a DML instruction, and most unions can be completed without a temporary table.
# Most temporary tables are memory based (HEAP) tables. Temporary tables with large record lengths (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 heap table in memory to a MyISAM table based on your needs. You can also increase the size of the temporary table by setting the Tmp_table_size option. That is, if you raise this value, MySQL will also increase the size of the heap table, to improve the query speed of the join effect

Read_buffer_size = 2M
# MySQL reads into buffer size. A request to sequentially scan a table allocates a read buffer, which is allocated a memory buffer by MySQL. The read_buffer_size variable controls the size of this buffer.
# If the sequential scan request for a table is very frequent, and you think frequent scans are too slow, you can improve performance by increasing the value of the variable and the size of the memory buffer

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

Sort_buffer_size = 8M
# MySQL performs sort 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 sorting stage.
# if not, you can try to increase the size of the sort_buffer_size variable

Join_buffer_size = 8M
# The size of the buffer that the union query operation can use, like sort_buffer_size, the allocated memory for this parameter is exclusive to each connection

Thread_cache_size = 8
# This value (default 8) indicates that the number of saved threads stored in the cache can be recycled, and if there is room in the cache when the connection is 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, and if the cache is empty or a new request, then the thread will be recreated, if there are many new threads,
# Adding this value can improve system performance. You can see the effect of this variable by comparing the variables of connections and threads_created states. (–> represents the value to be adjusted)
# Set the rules according to physical memory as follows:
# 1g-> 8
# 2g-> 16
# 3g-> 32
# Greater than 3g-> 64

Query_cache_size = 8M
#MySQL的查询缓冲大小 (from 4.0.1, MySQL provides query buffering) using query buffering, MySQL stores SELECT statements and query results in a buffer.
# Future for the same SELECT statement (case-sensitive), the result is read directly from the buffer. According to the MySQL user manual, you can use query buffering up to 238% efficiency.
# By checking the status value ' qcache_% ', you can know if 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 Qcache_hits is also very large, it means that the query buffer is used very frequently, at this time need to increase the buffer size, if the value of qcache_hits is small, your query repeat rate is very low,
# In this case, the use of query buffering will affect efficiency, so you can consider not query buffering. In addition, adding sql_no_cache to a SELECT statement makes it clear that query buffering is not used

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

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

Ft_min_word_len = 4
# Word segmentation Minimum Length, default 4

Transaction_isolation = Repeatable-read
# MySQL supports 4 transaction isolation levels, respectively:
# read-uncommitted, read-committed, Repeatable-read, SERIALIZABLE.
# if not specified, MySQL defaults to repeatable-read,oracle by 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 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 for standalone tablespace mode, each table in each database generates a data space
# Independent Table Space benefits:
# 1. Each table has its own table space.
# 2. The data and indexes for each table exist in the table space of their own.
# 3. You can implement a single table to move in a different database.
# 4. Space can be recycled (except for the drop table operation, table null cannot be retrieved)
Shortcomings
# Single table increases too large, such as over 100G
Conclusion
# Shared table spaces have few advantages in insert operations. Others do not perform well in their own table space. When standalone tablespace is enabled, adjust appropriately: innodb_open_files

Innodb_open_files = 500
# limit the data that InnoDB can open, if the table in the library is very much, please add this. This is the default value of 300.

Innodb_buffer_pool_size = 64M
# InnoDB uses a buffer pool to hold indexes and raw data, unlike MyISAM.
# The larger you set here, the less disk I/O you need to access the data in the table.
# on an independently used database server, you can set this variable to 80% of the size of the server's physical memory
# do not set too large, otherwise, due to the competition of physical memory may cause the operating system of page-changing.
# Note that each process on a 32-bit system 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 the read/write I/O (input/output) requests on the data page, depending on your CPU kernel number, the default is 4
# Note: These two parameters do not support dynamic change, you need to add this parameter to the MY.CNF, after the modification to restart the MySQL service, allow the range of values from 1-64

innodb_thread_concurrency = 0
# The default setting is 0, which means unlimited concurrency, the recommended setting is 0, better to play CPU multi-core processing capacity, increase the concurrent volume

Innodb_purge_threads = 1
# The purge operation in InnoDB is a class of operations that periodically recycle unwanted data. In previous versions, the purge operation was part of the main thread, which meant that it might block other database operations at run time.
# Starting with the mysql5.5.x version, the operation runs on a separate thread and supports more concurrent numbers. The user can select whether the purge action uses a single by setting the Innodb_purge_threads configuration parameters.
# single thread, by default the parameter is set to 0 (not using a separate thread), and setting to 1 indicates the use of a separate purge thread. Recommended for 1

Innodb_flush_log_at_trx_commit = 2
# 0: If the value of Innodb_flush_log_at_trx_commit is 0,log, the log file is brushed to disk every second, and no action is taken when committing the transaction (execution is performed by the master thread thread of MySQL).
# The Redo log buffer is written to the disk's redo log file (REDO log) per second in the main thread. The default log file, regardless of whether the transaction has been committed, is Ib_logfile0,ib_logfile1
# 1: When set to the default value of 1, every time the transaction is committed, log buffer brush is written to the log.
# 2: If set to 2, each commit transaction will write a log, but does not perform the operation of the brush. Each second is periodically brushed to the log file. Note that there is no guarantee that 100% will be brushed to disk per second, depending on the scheduling of the process.
# writes data to the transaction log each time the transaction is committed, while writing here only invokes a file system write operation, and the file system 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, the last 1 seconds of transactions are lost when the mysqld process crashes. Set to 2, the last 1 seconds of data will be lost only when the operating system crashes or loses power. InnoDB ignores this value when doing a restore.
# Summary
# set to 1 is of course the safest, but the performance page is the worst (compared 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 the most performance, such as high concurrent write log server, set to 0来 for higher performance

Innodb_log_buffer_size = 2M
# This parameter determines the amount of memory used by some log files, in M. A larger buffer can improve performance, but unexpected failures will lose data. MySQL developer recommends setting to 1-8m

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 The number of timeout seconds that a transaction can wait for a lock before being rolled back. InnoDB automatically detects a transaction deadlock in its own locking table and rolls back the transaction. InnoDB notes the lock setting with the lock tables statement. The default value is 50 seconds

Bulk_insert_buffer_size = 8M
# BULK INSERT cache size, this parameter is for the MyISAM storage engine. Applies to increase efficiency when inserting 100-1000+ records at one time. The default value is 8M. You can increase the amount of data by doubling it.

Myisam_sort_buffer_size = 8M
# MyISAM Sets the size of the buffer to use when restoring the table, when sorting MyISAM index-allocated buffers during repair table or when creating index or ALTER TABLE with CREATE INDEX

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

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

Interactive_timeout = 28800
# Number of seconds the server waits for activity before shutting down an interactive connection. An 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 the server waits for activity before it closes the non-interactive connection. When a thread starts, the session Wait_timeout value is initialized based on the global Wait_timeout value or the global interactive_timeout value.
# depends on the client type (defined by the Mysql_real_connect () connection option client_interactive). 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 to handle the appropriate operation,
# You should disconnect and free up memory consumption. If your MySQL server has a large number of idle connections, they will not only consume memory in vain, but if the connection keeps accumulating and driving,
# will eventually reach the MySQL server connection limit number, which will report ' Too many connections ' error. For the Wait_timeout value setting, it should be judged according to the operation 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, and if you find a connection process with a large number of sleep states, this parameter is set too large.
# you can make the appropriate adjustments smaller. To set both Interactive_timeout and wait_timeout 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