MySQL database server side core parameter details and recommended configuration

MySQL manual also has the interpretation of the parameters on the server, and the parameter values of relevant information, for all of us need to pay attention to, now need to modify or affect the performance of the server parameter, explain its use and how to configure the parameters value of recommendation, this things for some time, in order to facilitate everybody help correction (note: always write off and on, there may be a slip of the pen), the first mode in the article is published to the site, the follow-up to determine no problem, will through sina weibo micro plate to provide PPT download, easy learning and reference.
l lower_case_table_names
Linux or Unix platform, is case-sensitive to file names, that is, to the database, tables, stored procedures and other object names case-sensitive, in order to reduce the developer's development costs, it is recommended to set this parameter so that the object names are automatically converted to lowercase;
l max_connect_errors
The default value of max_connect_errors is 10, that is, the mysqld thread has not been restarted. As long as a physical server has an abnormal connection interruption of more than 10 times, it will no longer be able to connect to mysqld service. Therefore, it is recommended to set this value at least to be greater than or equal to 10W. If the total number of abnormal interrupts exceeds the value of parameter setting, there are two solutions. Execute the command: FLUSH HOSTS; Or restart the mysqld service;

l interactive_timeout and wait_timeout
u interactive_timeout
The activities connected in the interactive state are forced to be shut down by the server side, and the waiting time is measured in seconds.
u wait_timeout
This parameter is only valid for the connection based on TCP/IP or Socket communication protocol. Unit: second;
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 is set differently on the binary log register grid
The formula has a great impact. The detailed information can be seen from the article's interpretation of the isolation level of MySQL transactions and the log registration mode selection techniques.
u binlog-format
Copy mode, available values: STATEMENT, ROW, MIXED (note: 5.0.* only command line copy),
5.1.* version default setting: MIXED;
u recommended configuration
Read only the main business application scenarios
transaction-isolation = read-committed
binlog-format = mixed #5.1.* version, 5.0.* can only be set to statement
Non-read-only business application scenarios
transaction-isolation = repeatabled-read
binlog-format = mixed #5.1.* version, 5.0.* can only be set to statement
l event_scheduler
Transaction scheduling is turned off by default, it is also recommended that the source code compiled version may not be compiled in, and the actual production environment remains in the default disabled state, when really needed, can be temporarily opened, command: SET GLOBAL event_scheduler=1;
l skip_external_locking
External locks, also known as locks implemented by the operating system, are only available to the MyISAM engine and are prone to deadlock. For this reason, we disable them.
l innodb_adaptive_hash_index
The InnoDB engine will gradually slow the table data into memory according to the data access frequency. If a large amount of data of a table is cached in memory, it will be more efficient to use the hash index (note: Hash Index). Hash Index has the mechanism of Hash Index, which can monitor the data access and automatically create and maintain an Hash Index, so as to provide access efficiency and reduce the use of memory.
l innodb_max_dirty_pages_pct
The InnoDB main thread directly updates the data existing in Innodb_buffer_pool_size, and does not brush back to disk in real time, but waits for the relevant penalty event to occur, allowing the maximum percentage of the data volume of the cache space not to brush back to disk in real time. Proportion is set smaller, to reduce recovery time when mysqld service problems, defect is need more physical I/O, therefore we must according to the business characteristics and range of affordable for a compromise, 1 range suggested set to 5% ~ 90%, as we write SNS game industry is very severe, the comprehensive factors, set to 20%;
l innodb_commit_concurrency
How many threads are allowed to commit an InnoDB transaction at the same time. The default value is 0, ranging from 0 to 1000.
0 - allows any number of transactions to be committed at the same point in time;
N > 0 - allows N transactions to commit at the same point in time;
Matters needing attention:
When providing mysqld services, do not change the value of innodb_commit_concurrency from 0 to non-0, or the value of non-0 to 0;

When mysqld is provided, innodb_commit_concurrency value N is allowed > 0 is M, and M > 0;
l innodb_concurrency_tickets
Meaning:
At the same time, the number of threads that can access InnoDB engine data, the default value is 500, ranges 1-4294967295.
Note: when the number of threads accessing InnoDB engine data reaches the set line, the thread will be placed in the queue, waiting for other threads to release ticket.
Advice:
The maximum number of thread connections for MySQL database service parameter max_connections is generally set in the range of 128-1024. In combination with the maximum transaction concurrency possible for real business, innodb_concurrency_tickets is sufficient if the default value of innodb_concurrency_tickets is kept as 1.
l innodb_fast_shutdown and innodb_force_recovery
innodb_fast_shutdown:
Meaning: set the mode of shutdown of innodb engine, the default value is: 1, the normal shutdown state;
Before the 0 -- mysqld service is shut down, complete data cleaning and buffer insertion merge are performed, if the data is dirty
More or more factors such as server performance can cause this process to take several minutes or longer;
1 -- normal shutdown of mysqld service, no other operation for innodb engine;
2 - if mysqld crashes, immediately brush the transaction log to disk and cold shut down the mysqld service; Not submit
The transaction will be lost when the mysqld service is started again, but the transaction will be rolled back and restored.
innodb_force_recovery:
Meaning:
After the crash of mysqld service, the InnoDB engine rolls back the mode. The default value is 0, and the value can be set from 0 to 6.
Tip:
It is recommended to set innodb_force_recovery to be greater than 0 only if data backup is required from a database in the wrong state. If this parameter is taken as a security option, the value of the parameter can also be set to be greater than 0 to prevent data change of InnoDB engine. The function of setting different values is as follows:
0 - normal shutdown and startup without any forced recovery operation;
1 -- skip the error page and let the mysqld service continue. Skip error index records and store pages, try using
SELECT * INOT OUTFILE '.. / filename 'FROM tablename; Way to complete data backup;
2 - prevents the main InnoDB thread from running. When the mysqld service crashes during the cleaning operation, the data recovery operation will be prevented.
3 - no transaction rollback at recovery time;
4 - prevents merging of the INSERT buffer. Do not do the merge operation to prevent the mysqld service from crashing. Don't calculate
Table statistics
5 -- the mysqld service does not check the rollback log when it starts: the InnoDB engine treats every uncertain transaction like a commit
Transaction 1;
6 -- do not do transaction log rollforward recovery operation;
Recommended parameter combination configuration:
innodb_fast_shutdown = 1
If the machine room is in good condition, it can be set to 0 (dual-circuit power supply, UPS, RAID card battery and power supply system stability)
innodb_force_recovery =0
When there is a problem, what value should be set according to the reason and extent of the error and the subsequent operation on the data
l innodb_additional_mem_pool_size
Meaning: set up a piece of memory to cache the data dictionary information and internal data structure of the InnoDB engine (e.g., adaptive HASH index structure);
Default: build-in default: 1M; The default value of Plugin-innodb version is: 8M;
Tip: if there are a large number of table objects on the mysqld service, the InnoDB engine has a large amount of data, and the value of innodb_buffer_pool_size is set to a large value, then the value of innodb_additional_mem_pool_size should be adjusted appropriately. If there is insufficient memory in the cache area, it will directly apply for memory allocation from the operating system and write warning information to the MySQL error log file.
l innodb_buffer_pool_size
Meaning: open up a piece of memory to cache the data and indexes of the InnoDB engine table;
Default: historical default: 8M, current default: 128M;
Parameter maximum: limited by the architecture of CPU, whether it supports 32-bit or 64-bit, and whether the operating system is 32-bit or 64-bit;
Tip:
The appropriate value setting for innodb_buffer_pool_size saves physical IO for accessing the data in the table object. Official manual suggest a dedicated database server, can consider to set to 80% of the total physical memory, but personal advice depends on the amount of physical memory physical servers, and consider: whether to use only InnoDB engine, mysqld internal memory management, thread biggest factors such as the number of connections and a temporary table, the official to provide 80% of the value as a reference, for example convenience and everybody to make decisions (the premise: physical server to serve mysqld is special, and only use InnoDB engines, assuming that the data quantity is greater than the 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 in any of the following situations:
1). Physical memory competition may result in paging of the operating system;
2).InnoDB pre-allocates extra memory for buffer and structure management when the total amount of memory allocated exceeds 10% of the value of innodb_buffer_pool_size;
3) the address space requirement must be continuous. There is a serious problem in the windows system. DLL needs to be loaded in a specific address space;
4). The time consumption of initializing the buffer is proportional to the size of the buffer. Official data: Linux X86 64-bit system initialization innodb_buffer_pool_size=10G takes approximately 6 seconds;
l innodb_flush_log_at_trx_commit AND sync_binlog
innodb_flush_log_at_trx_commit = N:
Every 1 second, write the data from the transaction log cache to the log file and flush the data from the log file to disk.
When each transaction is committed, the transaction log is written from the cache to the log file and the data of the log file is flushed to disk.

When each transaction is committed, the transaction log data is written from the cache to the log file. Every 1 second, refresh the log file once, but not always to disk, but depends on the operating system schedule;
sync_binlog = N:
N > 0 -- after writing N SQL or N transactions to the binary log file, the data of the binary log file is flushed to disk;
N=0 - does not actively flush data from binary log files to disk, but is determined by the operating system;
Recommended configuration mix:
N=1,1 - suitable for data security requirements are very high, and disk IO write capacity is sufficient to support business, such as top-up consumption system;
N=1,0 -- suitable for high data security requirements, disk IO write capacity support business is not redundant, allow backup backward or no replication;
N = 2, 0 or 2, m (0 < m < 100) - suitable for data security requirements, allow 1 point loss of transaction log, replication architecture delay is acceptable;
N=0,0 -- disk IO has limited write capacity, so it is acceptable to have no replication or allow replication with a slightly longer delay.
l innodb_file_per_table
Enable single table Spaces to reduce Shared table space maintenance costs and free disk space release pressure. In addition, the performance under the condition of large data volume will also have the performance improvement. Therefore, it is recommended to use the independent table space instead of the Shared table space.
l key_buffer_size
key_buffer_size only cache MyISAM or class MyISAM engine's index data, and innodb_buffer_pool_size can cache the index data, not only can also cache metadata, but for we only use InnoDB engine database system, also cannot set this parameter value is too small, because the temporary table buffer space may use this key, the index buffer recommended: 64 M;
l query_cache_type and query_cache_size
n query_cache_type=N
N=0 -- disable query caching;
N=1 -- enables the output cache to cache all the required query result sets except SELECT SQL_NO_CACHE.. , and the result set that does not conform to the query cache Settings;
N=2 -- just cache SELECT SQL_CACHE... The query result set of the clause, except the result set that does not conform to the query cache setting;
n query_cache_size
How big is a reasonable query cache setting? Consider at least four dimensions:
Query cache on the performance of DDL and DML statements;
(2) query the cache area internal maintenance costs;
Query cache area hit ratio and memory utilization and other comprehensive consideration
Business type