Configuration of MySQL server core parameters in the production environment

(1) lower_case_table_names

● Recommendation reason

The GNU/Linux platform is case sensitive to names of databases, tables, stored procedures, and other objects.
To reduce developer development costs, we recommend that you set this parameter so that all object names are automatically converted to lowercase letters.

● Parameter Introduction

Value range:
0: Case Sensitive, default value for Linux
1: case insensitive

In the configuration file (/etc/My. CNF) of MySQL installed in Linux, there is no lower_case_table_names = 1 line.
In the MySQL configuration file (My. INI) installed in windows, there is a line of lower_case_table_names = 1.

So, please note that, in the replication configuration, the master and slave parameters should be consistent !!



(2) max_connect_errors

● Recommendation reason

A physical server can no longer connect to the mysqld service as long as it connects to the MySQL database server for more than 10 abnormal interruptions
Therefore, we recommend that you set this value to at least 10.
Solution 2:
Either restart mysqld or mysqladmin flush-hosts

● Parameter Introduction

However, this parameter is related to security,
Some hackers may attempt to crack the password brute force. If this value has been set before the conference



(3) interactive_timeout and wait_timeout

● Recommendation reason

If your MySQL server has a large number of idle connections, they will not only consume the memory in vain, but also if the connections continue to accumulate
In the end, the maximum number of connections to the MySQL server will be reached, which will result in an error of 'too has connections '.

Recommended value:
Interactive_timeout = 172800
Wait_timeout = 172800

● Parameter Introduction

Interactive_timeout
Parameter description: The number of seconds the server will wait to close the interactive connection.
Wait_timeout
Parameter description: the number of seconds that the server waits for to close a non-interactive connection.



(4) Transaction-isolation and BINLOG-format

Recommended Configuration

Read-only business application scenarios

Transaction-isolation = read-commited
BINLOG-format = mixed

Use Cases where the primary node is not read-only

Transaction-isolation = repeatabled-read
BINLOG-format = mixed



⑸ Innodb_adaptive_hash_index

● Recommendation reason

The InnoDB engine will gradually slow down the table data to the Memory Based on the Data Access frequency. If a table has a large amount of data cached in the memory
The hash index is more efficient.
InnoDB has a hash index mechanism to monitor data access information. A hash index can be created and maintained automatically.



⑹ Innodb_max_dirty_pages_pct

● Recommendation reason

Compared with MyISAM, InnoDB has an important feature: InnoDB will open a buffer pool in the memory to store recently accessed data blocks/index blocks.
This makes the next time you access this block faster. When InnoDB needs to modify the data block
The system records the modification logs and then directly performs operations on the data blocks in buffer_pool.
Logs are written in sequence, and data block operations are performed in memory, which gives InnoDB a good speed advantage in many scenarios.
After the memory block is modified, InnoDB returns the result to the client.
The data block on the actual disk is not updated yet. We call this page dirty page.
There is a dedicated thread in the background of InnoDB to flush the memory data block to the disk.
The innodb_max_dirty_pages_pct parameter can directly control the proportion of dirty_page in buffer_pool.
The general range is recommended to be 5% ~ 90%
The small scale setting helps reduce the recovery time when the mysqld service encounters a problem. The disadvantage is that more physical I/O is required.

● Parameter Introduction

Relationship between innodb_max_dirty_pages_pct and checkpoint
Show InnoDB status \ G; check the checkpoint position
Reduce innodb_max_dirty_pages_pct, increase the frequency of checkpoint events, and reduce the number of dirty pages.
In the production environment, we often find that:
After the database runs for a period of time, it often causes a large number of swap servers
There may be too much dirty data in InnoDB, because there is no free space, MySQL notifies the OS to swap some dirty pages.
So we can try to reduce innodb_max_dirty_pages_pct



Define innodb_commit_concurrency

● Recommendation reason

Parameter description: The number of threads allowed to submit InnoDB transactions at the same time. The default value is 0 and the value range is 0-1000.
0: any number of transactions can be committed at the same time point.
N> 0: N transactions can be committed at the same time point.
However, when mysqld provides services, it is not allowed to change non-0 to 0 or change 0 to non-0, but it can be changed between two non-0 values.



⑻ Innodb_fast_shutdown and innodb_force_recovery

● Recommendation reason

Innodb_fast_shutdown
Parameter description: Set the InnoDB Engine shutdown mode. The default value is 1. the InnoDB engine is normally disabled.
0: Before the mysqld service is disabled, perform the operations to completely clean up data and insert a buffer.
If there are many dirty data or server performance factors, this process may take several minutes or longer.
1: The mysqld service is disabled normally, and no other operations are performed on the InnoDB engine.
2: If mysqld crashes, immediately fl transaction logs to the disk and kill the mysqld service
Transactions that are not committed will be lost, but will be rolled back and restored when the mysqld service is started again.

Innodb_force_recovery
Parameter description: the InnoDB Engine rollback mode after the mysqld service crashes. The default value is 0, and the value range is 0 ~ 6
0: Close and start normally, and no forced recovery operation will be performed.
1: Skip the error page to continue running the mysqld service. Skip the error index record and storage page and try to use:
Select * inot OUTFILE '../filename' from tablename;
2: Stop the InnoDB main thread from running. If the mysqld service crashes during the cleaning operation, data recovery will be blocked.
3: No transaction rollback is performed during recovery.
4: prevents the merge operation of the insert buffer. No merge operation is performed to prevent the mysqld service from crashing. Do not calculate table statistics
5: When the mysqld service is started, the rollback log is not checked: the InnoDB Engine treats every uncertain transaction as the committed transaction.
6: do not perform transaction log rollback and recovery operations

Recommended parameter combination Configuration:
Innodb_fast_shutdown = 1
# If the machine room has good conditions, you can set it to 0 (dual power supply, UPS, RAID card battery and power supply system stability)
Innodb_force_recovery = 0
# When a problem occurs, you must set the value based on the cause and severity of the error and perform subsequent operations on the data.




⑼ Innodb_additional_mem_pool_size

● Recommendation reason

Parameter description: A piece of memory is used to cache data dictionary information and internal data structures of the InnoDB Engine (for example, adaptive hash index structure)
Default Value: build-in; default value: 1 m; default value: 8 m;

If the number of table objects on the mysqld service is large, the InnoDB engine has a large amount of data and the value of innodb_buffer_pool_size is large.
Adjust the value of innodb_additional_mem_pool_size appropriately.
If there is insufficient memory in the cache area, the system will apply for memory allocation directly to the OS and write warning information to the MySQL error log file.



⑽ Innodb_buffer_pool_size

● Recommendation reason

Parameter description: memory is used to cache data and indexes of InnoDB Engine tables.
Maximum parameter value: limited by the CPU architecture, 32-bit or 64-bit are supported, and 32-bit or 64-bit OS is also limited.

The value of innodb_buffer_pool_size is set appropriately, which saves the physical Io for accessing data in table objects.

In addition to innodb_buffer_pool_size, InnoDB memory is used to store page cache data. In addition, there is a 8% overhead normally.
It is mainly used for the description of each cached page frame, adaptive hash, and other data structures. If it is not safely disabled, it must be restored at startup.
We also need to allocate about 12% of the memory for recovery, and the sum of the two will have about 21% of the overhead.
Therefore, pay more attention to innodb_buffer_pool_size allocation.

For a dedicated dB, theoretically we can allocate 60%-80% of the memory to the DB. If we want to allocate 60%-80% of the memory, it will be okay. No, of course not.
Is it appropriate? You can use show engine InnoDB status \ G; to check the hit rate. When the hit rate does not reach more than 97%, you can consider adding memory.

To ensure that the system does not go down, memory overflow (OOM) does not occur, and serious memory SWAp does not occur, You can reserve a certain share for MyISAM, other applications, and systems.
The larger the buffer allocated to InnoDB, the better. It is a waste of resources and can be used sooner or later. Who makes the memory cheaper?




Export innodb_flush_log_at_trx_commit and sync_binlog

● Recommendation reason

Innodb_flush_log_at_trx_commit = N
N = 0-every second, write the data in the transaction log cache area to the log file, and refresh the data in the log file to the disk.
N = 1-when each transaction is committed, the transaction log is written from the cache to the log file, and the data in the log file is refreshed to the disk.
N = 2-when a transaction is committed, the transaction log data is written from the cache to the log file. The log file is refreshed every second.
But not necessarily refresh to the disk, it depends on the scheduling of the operating system.

Sync_binlog = N
N> 0-after n SQL statements or N transactions are written to the binary log file, the data in the binary log file is refreshed to the disk.
N = 0-the operating system determines not to actively refresh the data in the binary log file to the disk.


Recommended Configuration combination:
N =-suitable for high data security requirements, and the disk I/O write capability is sufficient to support businesses, such as the recharge consumption system
N =-suitable for high data security requirements. The disk I/O write capability supports no surplus of business, and the slave database is allowed to lag behind or not replicate
N = 100 or 2, m (0 <m <)-suitable for data security requirements, allow loss of transaction logs, replication architecture latency is acceptable
N =-limited disk I/O write capability, no replication or slightly longer replication latency is allowed, for example, log Registration



⑿ Innodb_file_per_table

● Recommendation reason

Parameter description: One tablespace for each table
If this parameter is set, you can clearly understand how much space the InnoDB table occupies.
Table backup is convenient, and space can be deleted and recycled

If it is a shared tablespace, when there are many tables, the tablespace will be very large; of course, with the deletion of the table, there will be a lot of gaps
If it is an independent tablespace, it will be very clear during data maintenance, such as alter table, the temporary space will be released after the end;
If the table space is shared, the temporary extended space will not be scaled in time, and a large number of fragments may exist.

Shared tablespace has a slight advantage in the insert operation. Other independent tablespaces do a good job.



⒀ Key_buffer_size

● Recommendation reason

This parameter value cannot be too small for database systems that only use the InnoDB engine.
Because temporary tables may use this key cache space, the index cache is recommended to be 64 MB.



⒁ Query_cache_type and query_cache_size

● Recommendation reason

Query_cache_type = N

N = 0 -- disable the query cache Function
N = 1 -- enable the message sending cache function to cache all query result sets that meet the requirements, except select SQL _no_cache ..,
And out-of-the-box results that do not meet the query cache settings
N = 2 -- only cache select SQL _cache... The query result set of the clause, except for the result set that does not meet the query cache settings.


Query_cache_size

What makes the query cache settings reasonable? It must be considered from at least four dimensions:
① Query the performance impact of the cache area on DDL and DML statements;
② Query the internal maintenance cost of the cache area;
③ Comprehensively consider the hit rate and memory usage of the query cache area
④ Business type

How query_cache_size works:
After a SELECT query works in a dB, the DB caches the statement. When the same SQL statement is called again in the DB,
The DB returns the result from the cache to the client without changing the table.

Here is a reference point, that is, when dB uses query_cache to work, it requires that the table involved in this statement not be changed during this period of time.
What if the data in query_cache is processed when the table is changed?
First, set all the query_cache statements related to the table to invalid, and then write the update
If query_cache is very large, the query structure of the table is large, and the query statement becomes invalid, the update or insert operation will be slow.

This parameter is not suitable for systems with a large number of database writes or updates. This function is disabled for systems with high concurrency and large write volumes.