Mysql database server-side core parameters detailed and recommended configuration _mysql

The MySQL manual also has the server-side parameter explanation, as well as the parameter value correlation explanation information, now is for us everybody important attention, needs to modify or affects the performance the server side parameter, makes its usefulness explanation and how configures the parameter value the recommendation, this matter has dragged a lot of time, For the convenience of everyone to help error correction (note: Always on and off, there may be a clerical error), the way to publish the article to the website, the follow-up to determine that no problem, will be through Sina Weibo micro-disk to provide PPT download, easy to learn and reference.
L Lower_case_table_names
Linux or Unix-like platform, the file name is sensitive to the case, the database, tables, stored procedures and other object names are case-sensitive, in order to reduce the development costs of developers, to recommend that you set this parameter to automatically convert the object name to lowercase;
L Max_connect_errors
Max_connect_errors default value is 10, that is, the MYSQLD thread has not restarted, a physical server as long as the connection to the abnormal interruption of more than 10 times, can no longer connect to the MYSQLD service, we recommend that you set this value is at least greater than or equal to 10W; If the abnormal interrupt accumulation exceeds the value of the parameter setting, there are two solutions to execute the command: FLUSH HOSTS; or restart mysqld service;

L Interactive_timeout and Wait_timeout
U interactive_timeout
An activity that is connected in an interactive state is forcibly closed by the server, while the waiting time, in seconds;
U wait_timeout
A connection that has no interaction with the server end until it is forced to shut down by the server, which is valid only for connections based on TCP/IP or socket-based communication protocols, in seconds;
U recommended settings
Interactive_timeout = 172800
Wait_timeout = 172800
L Transaction-isolation and Binlog-format
U transaction-isolation
Values available for setting: Read-uncommitted, read-committed, Repeatable-read,
SERIALIZABLE, the default value is: Repeatable-read, the transaction isolation level setting is different, to the binary log registration grid
The impact of the type is very large, detailed information can be seen in the article interpretation of MySQL transaction isolation level and log registration mode selection skills;
U binlog-format
The mode of replication, the value to set: STATEMENT, Row, MIXED (Note: 5.0.* only command line copy),
5.1.* version default setting: MIXED;
U Recommended Configuration
①-oriented business application scenario
Transaction-isolation = read-committed
Binlog-format = mixed #5.1.* version, 5.0.* can only be set to statement
① Business application scenario based on non-read-only
Transaction-isolation = Repeatabled-read
Binlog-format = mixed #5.1.* version, 5.0.* can only be set to statement
L Event_scheduler
Transaction scheduling defaults to the shutdown state, also recommended that the source code compiled version does not compile, and the actual production environment to keep the default disabled state, when the real need to use, you can temporarily open, command: SET GLOBAL event_scheduler=1;
L skip_external_locking
External lock, also is the operating system of the lock, only for the MyISAM engine effective, and easy to cause deadlock, we are all disabled;
L Innodb_adaptive_hash_index
The InnoDB engine uses a hash index (note: Hash index) to be more efficient, depending on how frequently the data is accessed, slowly slowing down the data in the table to memory, and if a table's data is heavily cached in memory. InnoDB has a hash index mechanism, monitoring data access, you can automatically create and maintain a hash index to provide access efficiency, reduce the use of memory;
L innodb_max_dirty_pages_pct
The InnoDB main thread updates the data that exists in the innodb_buffer_pool_size and does not brush back to the disk in real time, but waits for the relevant penalty event to occur, allowing the maximum percentage of the amount of data cached to be brushed back to disk in real time. Proportional settings are small, to reduce the MYSQLD service problems when recovery time, the disadvantage is the need for more physical I/O, for this reason we must be based on business characteristics and affordability of a compromise, the general scope of recommendations set for 5%~90%, like our SNS game industry write very powerful, comprehensive All aspects of the factor, set to 20%;
L Innodb_commit_concurrency
Meaning: At the same time, how many threads are allowed to commit INNODB transactions at the same time, the default value is 0, range 0-1000.
0-Allow any number of transactions to be submitted at the same point in time;
n>0-allows n transactions to be committed at the same point in time;
Precautions:
①mysqld to provide services, the value of innodb_commit_concurrency should not be changed from 0 to 0, or 0 to 0;

When ②mysqld is provided, the Innodb_commit_concurrency value n>0 is allowed to be changed to M and m>0;
L Innodb_concurrency_tickets
Meaning:
At the same time, the number of threads that can access the InnoDB engine data, the default value is 500, range 1-4294967295.
Supplemental Note: When the number of threads accessing the InnoDB engine data reaches the set line, the thread will be placed in the queue waiting for other threads to release the ticket.
Suggestions:
MySQL database service maximum thread connection number parameter max_connections, in general, will be set in the range of 128-1024, and then the actual business can be the maximum transaction concurrency degree, innodb_concurrency_ Tickets keeping the default value is generally sufficient.
L Innodb_fast_shutdown and Innodb_force_recovery
Innodb_fast_shutdown:
Meaning: Set the InnoDB engine shutdown mode, the default value is: 1, the normal shutdown state;
Before the 0-mysqld service is closed, the data is fully cleaned and the merge of the buffer is inserted, if the dirty data
Many or more factors such as server performance can cause this process to take several minutes or longer;
1-Normal shutdown mysqld service, for the InnoDB engine does not do any other operation;
2-If the mysqld appears to crash, immediately brush the transaction log to disk and cool off the MYSQLD service; no submission
Transactions will be lost, but transaction rollback recovery will occur when the MYSQLD service is started;
Innodb_force_recovery:
Meaning:
After the Mysqld service crashes, the InnoDB engine rolls back the mode, the default value is 0, the value can be set 0~6;
Tips:
It is recommended that you set the Innodb_force_recovery value greater than 0 only when you need to make a data backup from a database that is in the wrong state. If you use this parameter as a security option, you can also set the value of the parameter to be greater than 0, prevent the InnoDB engine from changing the data, and set the function of different values:
0-Normal shutdown and startup, do not do any forced recovery operations;
1-Skip the error page and let the MYSQLD service continue to run. Skipping error index records and storage pages to try
SELECT * inot outfile ' ... /filename ' from tablename; mode, complete data backup;
2-prevents InnoDB from running the main thread. A mysqld service crash occurs while a cleanup operation prevents data recovery operations;
3-rollback of transaction is not done when resuming;
4-Block Insert buffer merge operation. Do not perform a merge operation to prevent the MYSQLD service from crashing. does not calculate
Statistical information for tables
The 5-mysqld service does not check the rollback log when it is started: The InnoDB engine treats every indeterminate transaction like a commit
's Affairs;
6-Do not do transaction log roll forward recovery operation;
Recommended parameter combination configuration:
Innodb_fast_shutdown = 1
#若是机房条件较好可设置为0 (Dual power supply, UPS, RAID card batteries and power supply stability)
Innodb_force_recovery =0
#至于出问题的时候, set the value of why, depending on the cause and extent of the error, the follow-up action on the data
L Innodb_additional_mem_pool_size
Meaning: Open a piece of memory for caching InnoDB engine data dictionary information and internal data structure (for example: Adaptive hash index structure);
Default value: Build-in version default is: 1m;plugin-innodb version default is: 8M;
Tip: If there are more table objects on the Mysqld service, InnoDB engine data is large and innodb_buffer_pool_size values are set larger, the Innodb_additional_mem_pool_size value should be appropriately adjusted. If there is not enough memory in the buffer, it will request the memory allocation directly to the operating system, and will write the warning message to the MySQL error log file;
L Innodb_buffer_pool_size
Meaning: Open a piece of memory for caching InnoDB engine table data and indexes;
Default: The default value is: 8M, now the default is: 128M;
Parameter maximum: CPU-limited architecture, 32-bit support or 64-bit, and also limited to 32-bit or 64-bit operating system;
Tips:
The Innodb_buffer_pool_size value is set appropriately, saving the physical IO that accesses the data in the Table object. The Official Handbook recommends a dedicated database server that can be set to 80% of the total physical memory, but it is recommended that you look at the total physical memory of the physical server and consider whether you are using only the InnoDB engine, the MYSQLD internal management footprint, the maximum number of thread connections, and temporary tables, The official 80% value as a reference, cite an example to facilitate the decision (prerequisite: Physical server is dedicated to the MYSQLD service, and only with the InnoDB engine, assuming that the amount of data is much larger than physical memory):
1). Memory Configuration: 24G innodb_buffer_pool_size=18g
1). Memory Configuration: 32G innodb_buffer_pool_size=24g
Consider reducing the value of innodb_buffer_pool_size if any of the following conditions are true:
1. The emergence of competition in physical memory may result in paging of the operating system;
2). InnoDB allocates additional memory to buffer and structure management, when the total amount of memory allocated exceeds 10% of the innodb_buffer_pool_size value;
3. Address space requirements must be continuous, there is a serious problem in the Windows system, DLLs need to be loaded in a specific address space;
4). The time consumption of the initialization buffer is proportional to the size of the buffer. The official data Linux X86 64-bit system initialization innodb_buffer_pool_size=10g takes about 6 seconds;
L Innodb_flush_log_at_trx_commit and Sync_binlog
Innodb_flush_log_at_trx_commit = N:
n=0– every second, the data in the transaction log buffer is written to the log file, and the log file data is flushed to disk;
n=1– each transaction, the transaction log is written from the buffer to the log file, and the log file data is refreshed to disk;

n=2– The transaction log data is written to the log file from the buffer, and every second, the log file is refreshed, but it is not necessarily flushed to disk, but depends on the operation system scheduling;
Sync_binlog = N:
n>0-the binary log files are flushed to disk after each write n SQL or n transactions to the binary log file;
N=0-does not actively refresh the data of the binary log files to disk, but is determined by the operating system;
Recommended configuration combination:
n=1,1-suitable for data security requirements is very high, and disk IO write capacity enough to support the business, such as recharge the consumer system;
n=1,0-suitable for data security requirements, disk IO write ability to support the business is not redundant, allowing the database to lag behind or no replication;
n=2,0 or 2,m (0<m<100)-Suitable for data security requirements, allow the loss of a bit of transaction log, replication architecture delay is also acceptable;
n=0,0-Disk IO Write ability is limited, no replication or allow replication delay slightly longer acceptable, such as: Log registration business;
L innodb_file_per_table
Enable single table space, reduce the cost of shared table space maintenance, and reduce the pressure of free disk space release. In addition, the performance of large amount of data can also improve performance, so we suggest that we use independent table space instead of shared table space;
L Key_buffer_size
Key_buffer_size can only cache index data for MyISAM or class MyISAM engines, and innodb_buffer_pool_size not only can cache index data but also cache metadata, but for database systems that we use only the InnoDB engine, this Parameter values can not be set too small, because temporary tables may use this key buffer space, index buffer recommended: 64M;
L Query_cache_type and Query_cache_size
N query_cache_type=n
n=0--the ability to disable query caching;
n=1--enables feature caching, caching all of the required query result sets, except for select Sql_no_cache ... and a result set that does not conform to query cache settings;
n=2--only Cache Select Sql_cache ... clause, in addition to the result set that does not conform to the query cache settings;
N query_cache_size
How large is the query cache setting? You need to consider at least four dimensions:
① query buffer to the performance effect of DDL and DML statements;
② Query the internal maintenance cost of the buffer;
③ Query cache Hit ratio and memory usage, and other comprehensive considerations
④ Business Type