Mysql Service can optimize-my.cnf_my.ini configuration instructions (16G memory) _mysql

This configuration is used on the old boy production line in the configuration, in the training time he gave, I'm here, add a Chinese description to each parameter
This configuration has been optimized, if your MySQL is not a special case, you can directly use the configuration parameters

MySQL server MY.CNF configuration document detailed
Hardware: Memory 16G

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

[MySQL]
No-auto-rehash

[Mysqld]
user = MySQL
Port = 3306
Socket =/data/3306/mysql.sock
Basedir =/usr/local/mysql
DataDir =/data/3306/data
Open_files_limit = 10240

Back_log = 600
#在MYSQL暂时停止响应新请求之前, the number of requests within a short time can be on the stack. If the system has many connections in a short period of time, you need to increase the value of the parameter, which specifies the size of the listener queue for the incoming TCP/IP connection. The default value is 50.

Max_connections = 3000
#MySQL允许最大的进程连接数, you need to increase this value if you frequently receive error prompts for too Many connections.

Max_connect_errors = 6000
#设置每个主机的连接请求异常中断的最大次数, when this number is exceeded, the MySQL server disables the host's connection request until the MySQL server restarts or clears the information about the host through the flush hosts command.

Table_cache = 614
#指示表调整缓冲区大小. # Table_cache parameter sets the number of table caches. Each connection comes in with at least one table cache open. #因此, the size of the Table_cache should be related to the Max_connections setting. For example, for 200 # parallel connections, the table should have a cache of at least 200xN, where N is the maximum number of tables in a join in which query # can be executed. In addition, you need to keep some extra file descriptors for temporary tables and files.
# when Mysql accesses a table, if the table is already open in the cache, you can access the cache directly, if # is not yet cached, but there is room in the Mysql table buffer, the table is opened and placed in the slow # flush area, and if the table is full, the currently unused table is released according to certain rules , or temporarily enlarge the table cache, the advantage of using table caching is that you can access the contents of the table more quickly. Execute flush tables to empty the cached contents. In general, you can determine whether you need to increase table_cache values (where Open_tables is the number of tables opened before #, by looking at the status value of the peak time of the database Open_tables #和 opened_tables, Opened_ Tables are the number of tables that are already open). That is, if Open_tables is approaching Table_cache, and opened_tables this value is gradually increasing, consider increasing the size of this # value. There is also the time when table_locks_waited is higher, also need to increase table_cache.

external-locking = FALSE
#使用 –skip-external-locking mysql option to avoid external locking. This option is turned on by default

Max_allowed_packet = 32M
#设置在网络传输中一次消息传输量的最大值. The system defaults to 1MB, the maximum is 1GB, and you must set a multiple of 1024.

Sort_buffer_size = 2M
# sort_buffer_size is a connection-level parameter that allocates the set of memory at a time when each connection (session) needs to use this Buffer for the first time.
#Sort_Buffer_Size is not the larger the better, because it is connection-level parameters, too large settings + high concurrency may deplete the system memory resources. For example: 500 connections will consume 500*sort_buffer_size (8M) =4g memory
When the #Sort_Buffer_Size exceeds 2KB, mmap () instead of malloc () is used to allocate memory, resulting in lower efficiency.
#技术导读 http://blog.webshuo.com/2011/02/16/mysql-sort_buffer_size/
#dev-doc:http://dev.mysql.com/doc/refman/5.5/en/server-parameters.html
#explain select*from table where order limit; filesort
#属重点优化参数

Join_buffer_size = 2M
#用于表间关联缓存的大小, as with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.

Thread_cache_size = 300
# Server thread caching This value means that the number of stored threads in the cache can be recycled. When disconnected, if there is room in the cache, the client's thread is placed in the cache, and if the thread is requested again, the request is read from the cache, if the cache is empty or a new request, Then this thread will be recreated, and if there are many new threads, adding this value can improve system performance. By comparing the variables of connections and threads_created states, you can see the effect of this variable. The setting rules are as follows: The 1GB memory configuration is configured for 8,2GB to 16,3GB configured for 32,4GB or higher memory and can be configured larger.

Thread_concurrency = 8
# Set the correct value of the thread_concurrency, the performance of MySQL is very large, in multiple CPUs (or multi-core), the error set the Thread_concurrency value, will cause MySQL can not take full advantage of multiple CPUs ( or multicore), occurs at the same time only one CPU (or core) is working in the case. Thread_concurrency should be set to twice times the number of CPU cores. For example, there is a dual-core CPU, then the thread_concurrency should be 4; 2 Dual-core CPUs, the value of thread_concurrency should be 8
#属重点优化参数

Query_cache_size = 64M
# # for users who use MySQL, we must not be unfamiliar with this variable. In previous years of MyISAM engine optimization, this parameter is also an important optimization parameter. But with the development, this parameter also burst to expose some problems. The memory of the machine is getting bigger and larger, and people are accustomed to allocating the previously useful parameters more and more. The increase in this parameter also causes a series of problems. Let's start with an analysis of how Query_cache_size works: When a select query is working in DB, DB caches the statement, and when the same SQL comes in DB again, DB returns the result from the cache to the client without changing the table. There is a closing point where DB requires that the table involved in the statement not be changed during that time when it is working with Query_cache. What about the data in the Query_cache if the table is changing? First, the Query_cache and the table-related statements are all invalidated and then written to the update. So if the query_cache is very large, the query structure of the table is more, the query statement is slow to fail, an update or insert will be slow, so see is update or insert why so slow. Therefore, in the database writes the quantity or the update quantity is also relatively large system, this parameter is not suitable allocates too big. And in the high concurrency, write a large system, it is recommended to ban the function.
#重点优化参数 (Main library additions and deletions change-myisam)

query_cache_limit = 4M
#指定单个查询能够使用的缓冲区大小, the default is 1M

Query_cache_min_res_unit = 2k
#默认是4KB, setting a value is good for large data queries, but if your queries are small data queries, it can easily lead to memory fragmentation and waste.
#查询缓存碎片率 = Qcache_free_blocks/qcache_total_blocks * 100%
#如果查询缓存碎片率超过20%, you can use flush query cache to defragment, or try to reduce query_cache_min_res_unit, if your query is a small amount of data.
#查询缓存利用率 = (query_cache_size–qcache_free_memory)/query_cache_size * 100%
#查询缓存利用率在25% below the query_cache_size set is too large, can be appropriately reduced; query cache utilization is above 80% and Qcache_lowmem_prunes > 50 query_cache_ Size may be a bit small, or too much fragmentation.
#查询缓存命中率 = (qcache_hits–qcache_inserts)/qcache_hits * 100%

Default-storage-engine = MyISAM
#default_table_type = InnoDB

Thread_stack = 192K
#设置MYSQL每个线程的堆栈大小, the default value is large enough to accommodate normal operations. The range can be set to 128K to 4GB, and the default is 192KB.

Transaction_isolation = read-committed
# Set the default transaction isolation level. The available levels are as follows:
# read-uncommitted, read-committed, Repeatable-read, SERIALIZABLE
# 1.READ uncommitted-READ UNCOMMITTED 2.READ committe-Read submitted 3.REPEATABLE read-repeatable read 4. SERIALIZABLE-Serial

Tmp_table_size = 256M
# The default size for Tmp_table_size is 32M. If a temporary table exceeds that size, MySQL produces a full form of the table Tbl_name error, and if you do many advanced GROUP by queries, increase the tmp_table_size value. If this value is exceeded, the temporary table is written to disk.
max_heap_table_size = 256M
Long_query_time = 2
Log_long_format
Log-slow-queries=/data/3306/slow-log.log
#log-bin =/data/3306/mysql-bin
Log-bin
Binlog_cache_size = 4M
Max_binlog_cache_size = 8M
Max_binlog_size = 512M

Expire_logs_days = 7
Key_buffer_size = 2048M
#批定用于索引的缓冲区大小, adding it can get better index processing performance, which can be set to 256MB or 384MB for servers with around 4GB.

Read_buffer_size = 1M
# 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 that frequent scans are too slow, you can improve performance by increasing the value of the variable and the size of the memory buffer. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.

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

Bulk_insert_buffer_size = 64M
#批量插入数据缓存大小, can effectively improve the insertion efficiency, the default is 8M

Myisam_sort_buffer_size = 128M
# MyISAM The buffer needed to reorder when the table is changed

Myisam_max_sort_file_size = 10G
# The maximum amount of temporary files allowed when MySQL rebuilds the index (when REPAIR, ALTER TABLE, or LOAD DATA INFILE).
# If the file size is larger than this, the index is created through a key-value buffer (slower)

Myisam_max_extra_sort_file_size = 10G
Myisam_repair_threads = 1
# If a table has more than one index, MyISAM can fix them by using more than one thread in parallel sorting.
# This is a good choice for users with multiple CPUs and large amounts of memory.

Myisam_recover
#自动检查和修复没有适当关闭的 MyISAM Table
Skip-name-resolve
lower_case_table_names = 1

Server-id = 1

Innodb_additional_mem_pool_size = 16M
#这个参数用来设置 InnoDB stored data directory information and other internal data structure of the memory pool size, similar to Oracle's library cache. This is not a mandatory parameter that can be breached.

Innodb_buffer_pool_size = 2048M
# This is very important for the InnoDB table. InnoDB is more sensitive to buffering than MyISAM tables. MyISAM can be run under the default Key_buffer_size settings, however InnoDB is like a snail in the default innodb_buffer_pool_size settings. Because InnoDB caches data and indexes without leaving the operating system with too much memory, you can set it up to 70-80% usable memory if you only need to use InnoDB. Some of the rules applied to Key_buffer are-if your data is small and not exploding, then you don't have to set the innodb_buffer_pool_size too big.

Innodb_data_file_path = Ibdata1:1024m:autoextend
#表空间文件 Important data

Innodb_file_io_threads = 4
#文件IO的线程数, typically 4, but can be set larger under Windows.

Innodb_thread_concurrency = 8
#服务器有几个CPU就设置为几, it is recommended to use the default setting, typically 8.

Innodb_flush_log_at_trx_commit = 2
# If this parameter is set to 1, the log will be written to disk after each commit transaction. To provide performance, you can set to 0 or 2, but assume the risk of losing data in the event of a failure. A setting of 0 indicates that the transaction log is written to the log file, and the log files are flushed to disk once per second. A set of 2 indicates that the transaction log will be written to the log at the time of submission, but the log file is flushed to disk once.

Innodb_log_buffer_size = 16M
#此参数确定些日志文件所用的内存大小, in M as a unit. A larger buffer can improve performance, but unexpected failures will lose data. MySQL developer recommends setting to 1-8m

Innodb_log_file_size = 128M
#此参数确定数据日志文件的大小, in M, larger settings can improve performance, but also increase the time required to recover a failed database

Innodb_log_files_in_group = 3
#为提高性能, MySQL can write log files to multiple files in a circular fashion. Recommended set to 3M

innodb_max_dirty_pages_pct = 90
#推荐阅读 http://www.taobaodba.com/html/221_innodb_max_dirty_pages_pct_checkpoint.html
# The number of dirty_page in Buffer_pool directly affects the closing time of InnoDB. Parameter innodb_max_dirty_pages_pct can directly control the ratio of dirty_page in Buffer_pool, and fortunately innodb_max_dirty_pages_pct can be changed dynamically. Therefore, before closing the InnoDB, the innodb_max_dirty_pages_pct is reduced, forcing the data block flush a period of time, can greatly shorten the time of MySQL shutdown.

Innodb_lock_wait_timeout = 120
# InnoDB has its built-in deadlock detection mechanism that can cause unfinished transactions to roll back. However, if you combine InnoDB with a MYISAM lock tables statement or a Third-party transaction engine, InnoDB cannot recognize the deadlock. To eliminate this possibility, you can set Innodb_lock_wait_timeout to an integer value indicating how long the MySQL waits (in seconds) before allowing other transactions to modify the data that is eventually rolled back by the transaction.

innodb_file_per_table = 0
#独享表空间 (OFF)

[Mysqldump]
Quick
Max_allowed_packet = 32M

[Mysqld_safe]
Log-error=/data/3306/mysql_oldboy.err
Pid-file=/data/3306/mysqld.pid

#补充
#wait_timeout = 10
#指定一个请求的最大连接时间, it can be set to 5-10 for memory servers around 4GB.
#skip_networking
#开启该选可以彻底关闭MYSQL的TCP/IP connection, do not open this option if the Web server accesses the MySQL database server as a remote connection, otherwise the connection will not work properly.

#log-queries-not-using-indexes
Record queries that do not use indexes