MySQL configuration file my. cnf parameter optimization and Chinese explanation

Mysql parameter optimization is difficult for new users. In fact, this parameter optimization is very complicated. For different websites, their online volume, access volume, and number of posts, network Conditions and machine hardware configurations are related. Optimization cannot be completed at one time. You need to constantly observe and debug the optimization to obtain the best results.
Copy codeThe Code is 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. pid
User = mysql
Bind-address = 0.0.0.0
Server-id = 1 # indicates that the serial number of the local machine is 1, which is generally the meaning of the master.

Skip-name-resolve
# Prohibit MySQL from performing DNS resolution on external connections. Using this option can eliminate the time for MySQL to perform DNS resolution. However, if this option is enabled,
# The IP address must be used for all remote host connection authorization; otherwise, MySQL cannot process connection requests normally.

# Skip-networking

Back_log = 600
# Number of connections available for MySQL. When the main MySQL thread receives a lot of connection requests in a short time, this takes effect,
# Then the main thread takes some time (although very short) to check the connection and start a new thread. The back_log value indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops answering new requests.
# If you want to have many connections in a short period of time, you need to add it. That is to say, if the connection data of MySQL reaches max_connections, the new request will be stored in the stack,
# Wait for a connection to release the resource. The number of stacks is back_log. If the number of connections waiting for exceeds back_log, the connection resource is not granted.
# In addition, this value (back_log) is limited to the size of the listening queue of your operating system for incoming TCP/IP connections.
# Your operating system has its own limits on the queue size (check your OS documentation to find the maximum value of this variable ), trying to set back_log to be higher than your operating system limit will be invalid.

Max_connections = 1000
# The maximum number of connections of MySQL. If the number of concurrent connection requests on the server is large, we recommend that you increase this value to increase the number of parallel connections. Of course, this is based on the support of the machine, because the higher the number of connections, MySQL will provide a connection buffer for each connection, the higher the memory overhead will be. Therefore, you must adjust this value appropriately and do not blindly increase the setting value. You can use the 'conn' % 'wildcard to view the number of connections in the current status to determine the value size.

Max_connect_errorrs = 6000
# If an error connection is interrupted for the same host that exceeds the value of this parameter, the host will not be connected. To unban the HOST, run: flush host.

Open_files_limit = 65535
# Limit on file descriptors opened by MySQL. The default value is at least 1024. When open_files_limit is not configured, compare the values of max_connections * 5 and ulimit-n,
# When open_file_limit is configured, compare the values of open_files_limit and max_connections * 5 with the values of open_files_limit and max_connections * 5.

Table_open_cache = 128
# Each time MySQL opens a table, it reads some data into the table_open_cache cache. when MySQL cannot find the corresponding information in the cache, it reads data from the disk. Default Value 64
# If the system has 200 concurrent connections, set this parameter to 200 * N (N is the number of file descriptors required for each connection );
# When table_open_cache is set to a large value, if the system cannot process so many file descriptors, the client becomes invalid and the connection fails.

Max_allowed_packet = 4 M
# Accept the packet size. It is safe to increase the value of this variable because additional memory is allocated only when necessary. For example, if you issue a long query or MySQLd must return a large result, MySQLd will allocate more memory.
# Taking a small default value for this variable is a preventive measure to capture the error message package between the client and the server, and ensure that the memory overflow is not caused by accidental use of large information packets.

Binlog_cache_size = 1 M
# Logs generated when a transaction is not committed are recorded in the Cache. logs are persisted to the disk when the transaction is committed. The default binlog_cache_size is 32 K.

Max_heap_table_size = 8 M
# Defines the size of memory tables that can be created by users. This value is used to calculate the maximum row value of the memory table. This variable supports dynamic changes

Tmp_table_size = 16 M
# Cache size of the heap (stacked) Table of MySQL. All joins are completed in a DML command, and most joins can be completed even without a temporary table.
# Most temporary tables are memory-based (HEAP) tables. Temporary tables with a large record length (the sum of the lengths of all columns) or tables containing BLOB columns are stored on the hard disk.
# If the size of an internal heap (stacked) Table exceeds tmp_table_size, MySQL can automatically change the heap table in memory to a hard disk-based MyISAM table as needed. You can also increase the size of the temporary table by setting the tmp_table_size option. That is to say, if you increase this value, MySQL will also increase the size of the heap table, which can improve the speed of the join query.

Read_buffer_size = 2 M
# Size of the buffer read from MySQL. Requests that perform sequential scans on the table will be allocated with a read buffer, and MySQL will allocate it with a memory buffer. The read_buffer_size variable controls the size of the buffer.
# If the table's Sequential Scan requests are very frequent and you think frequent scans are too slow, you can increase the performance by increasing the variable value and memory buffer size.

Read_rnd_buffer_size = 8 M
# The random read buffer size of MySQL. When a row is read in any order (for example, in the sorting order), a random read cache is allocated. During sorting query,
# MySQL first scans the buffer to avoid disk search and increase the query speed. If you need to sort a large amount of data, you can increase the value accordingly. However, MySQL will issue this buffer space for each client connection. Therefore, set this value as much as possible to avoid excessive memory overhead.

Sort_buffer_size = 8 M
# Cache size used for sorting by MySQL. To increase the order by speed, first check whether MySQL can use indexes instead of additional sorting stages.
# If not, increase the variable sort_buffer_size.

Join_buffer_size = 8 M
# The buffer size that can be used by the Joint query operation. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection.

Thread_cache_size = 8
# This value (8 by default) indicates that the number of threads stored in the cache can be reused. If there is space in the cache when the connection is disconnected, the client thread will be placed in the cache,
# If the thread is re-requested, the request will be read from the cache. If the cache is empty or a new request, the thread will be re-created, if there are many new threads,
# Adding this value can improve the system performance. By comparing variables in the Connections and Threads_created states, you can see the role of this variable. (-> Indicates the value to be adjusted)
# Set the following rules based on the physical memory:
#1G-> 8
#2G-> 16
#3G-> 32
# Greater than 3 GB-> 64

Query_cache_size = 8 M
# MySQL query buffer size (from 4.0.1, MySQL provides the Query Buffer mechanism). MySQL stores SELECT statements and query results in the buffer,
# In the future, the results of the same SELECT statement (case sensitive) will be directly read from the buffer. According to the MySQL user manual, query buffering can achieve a maximum efficiency of 238%.
# Check the status value 'qcache _ % 'to check whether the query_cache_size setting is reasonable. If the value of Qcache_lowmem_prunes is very large, it indicates that the buffer is insufficient frequently,
# If the Qcache_hits value is also very large, it indicates that the query buffer is used very frequently and the buffer size needs to be increased. If the Qcache_hits value is not large, it indicates that your query repetition rate is very low,
# In this case, using the Query Buffer will affect the efficiency, so you can consider not to use the query buffer. Additionally, adding SQL _NO_CACHE to the SELECT statement explicitly indicates that no Query Buffer is used.

Query_cache_limit = 2 M
# Specify the buffer size that a single query can use. The default value is 1 MB.

Key_buffer_size = 4 M
# Specify the buffer size used for the index and increase the size of the index that can be better processed (for all reads and multiple writes) so that you can afford that much. If you make it too large,
# The system will start to change pages and it will actually slow down. This parameter can be set to 512 MB or MB for servers with around 4 GB of memory. Check the status values Key_read_requests and Key_reads,
# Check whether the key_buffer_size setting is reasonable. Ratio key_reads/key_read_requests should be as low as possible,
# At least and are better (the above STATUS values can be obtained using show status like 'key _ read % ). Note: If this parameter value is set too large, the overall efficiency of the server is reduced.

Ft_min_word_len = 4
# Minimum length of word segmentation. The default value is 4.

Transaction_isolation = REPEATABLE-READ
# MySQL supports four transaction isolation levels:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
# If not specified, MySQL uses REPEATABLE-READ by default, ORACLE is READ-COMMITTED by default

Log_bin = mysql-bin
Binlog_format = mixed
Expire_logs_days = 30 # delete a binlog that exceeds 30 days

Log_error =/data/mysql/mysql-error.log # Error Log Path
Slow_query_log = 1
Long_query_time = 1 # Slow query if the query time exceeds 1 second
Slow_query_log_file =/data/mysql/mysql-slow.log.

Performance_schema = 0
Explicit_defaults_for_timestamp

# Lower_case_table_names = 1 # case insensitive

Skip-external-locking # MySQL option to avoid external locking. This option is enabled by default.

Default-storage-engine = InnoDB # default storage engine

Innodb_file_per_table = 1
# InnoDB is an independent tablespace mode. Each table in each database generates a data space.
# Advantages of independent tablespace:
#1. Each table has its own independent tablespace.
#2. Data and indexes of each table are stored in its own tablespace.
#3. You can move a single table in different databases.
#4. space can be recycled (except for the drop table operation, the table cannot be recycled if it is empty)
# Disadvantages:
# The increase in a single table is too large, for example, more than 100 GB.
# Conclusion:
# Shared tablespace has few advantages in Insert operations. Other independent tablespaces do a good job. When the independent tablespace is enabled, make proper adjustments: innodb_open_files

Innodb_open_files = 500
# Restrict the data of tables that Innodb can open. If there are many tables in the database, add this parameter. The default value is 300.

Innodb_buffer_pool_size = 64 M
# InnoDB uses a buffer pool to store indexes and raw data, unlike MyISAM.
# The larger you set, 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 server's physical memory size
# Do not set too large. Otherwise, the physical memory competition may lead to page changes in the operating system.
# Note that on a 32-bit system, each process may be limited to 2-3.5 GB of user memory,
# Do not set it too high.

Innodb_write_io_threads = 4
Innodb_read_io_threads = 4
# Innodb uses background threads to process read/write I/O (input/output) requests on the data page, which is changed according to the number of your CPU cores. The default value is 4.
# Note: These two parameters do not support dynamic changes. You need to add them to my. cnf. After modification, restart the MySQL service. The allowed value ranges from 1 to 64.

Innodb_thread_concurrency = 0
# The default value is 0, indicating no limit on the number of concurrent threads. We recommend that you set this value to 0 to improve the processing capability of multiple CPU cores and increase the concurrency.

Innodb_purge_threads = 1
# Cleanup in InnoDB is a type of operation that regularly recycles useless data. In earlier versions, the cleanup operation is part of the main thread, which means it may block other database operations during the runtime.
# Starting from MySQL 5.5.x, this operation runs in an independent thread and supports more concurrent threads. You can set the innodb_purge_threads configuration parameter to determine whether to use a ticket
# Single thread. By default, the parameter is set to 0 (no separate thread is used). If it is set to 1, a separate thread is used to clear the thread. Recommended Value: 1

Innodb_flush_log_at_trx_commit = 2
#0: If the innodb_flush_log_at_trx_commit value is 0, the log buffer will be written to the disk every second, no operation is performed when the transaction is committed (the execution is performed by the mysql master thread.
# The redo log buffer is written to the redo log file of the disk every second in the main thread. Whether the transaction has been committed or not) the default log file is ib_logfile0 and ib_logfile1.
#1: when it is set to the default value of 1, the log buffer will be flushed to the log each time a transaction is committed.
#2: if it is set to 2, logs are written every time a transaction is committed, but the fl operation is not performed. The logs are flushed to every second. Note that it is not guaranteed that the disk will be flushed every 100% seconds, which depends on the process scheduling.
# Data is written to the transaction log every time a transaction is committed, and the write here only calls the write operation of the file system, and the file system has a cache, therefore, this write operation does not guarantee that the data has been written to the physical disk.
# The default value 1 is used to ensure the complete ACID. Of course, you can set this configuration item to a value other than 1 in exchange for higher performance, but when the system crashes, you will lose 1 second of data.
# If it is set to 0, the transaction in the last second will be lost when the mysqld process crashes. If this parameter is set to 2, data of the last second will be lost only when the operating system crashes or the power is down. InnoDB ignores this value during restoration.
# Conclusion
# Setting 1 is of course the safest, but the performance page is the worst (not acceptable to the other two parameters ). If you do not have high requirements on data consistency and integrity, you can set it to 2. If you only want performance, such as a log server with high concurrent writes, set it to 0 to achieve higher performance.

Innodb_log_buffer_size = 2 M
# This parameter determines the memory size used by some log files, in MB. A larger buffer zone can improve performance, but unexpected faults will cause data loss. It is recommended that MySQL developers set it to 1-8 m

Innodb_log_file_size = 32 M
# This parameter determines the size of the data log file. A larger setting can improve performance, but it also increases the time required to recover the faulty database.

Innodb_log_files_in_group = 3
# To improve performance, MySQL can write log files to multiple files cyclically. Recommended to 3

Innodb_max_dirty_pages_pct = 90
# The innodb main thread refreshes the data in the cache pool so that the proportion of dirty data is less than 90%

Innodb_lock_wait_timeout = 120
# Before the InnoDB Transaction is rolled back, you can wait for a lock timeout time in seconds. InnoDB automatically detects the transaction deadlock and rolls back the transaction in its own locked table. InnoDB uses the lock tables statement to notice the LOCK settings. The default value is 50 seconds.

Bulk_insert_buffer_size = 8 M
# Batch insert cache size. This parameter is for the MyISAM storage engine. This method improves the efficiency when more than 100-1000 records are inserted at a time. The default value is 8 Mb. You can double the data size.

Myisam_sort_buffer_size = 8 M
# MyISAM sets the size of the buffer used to restore the TABLE. During the repair table or create index creation or alter table process, sort the buffer allocated by the MyISAM INDEX.

Myisam_max_sort_file_size = 10G
# If the temporary file size exceeds the index size, do not use the Quick Sort index method to create an index. Note: this parameter is given in bytes.

Myisam_repair_threads = 1
# If the value is greater than 1, create an index for the MyISAM table in parallel during the Repair by sorting process (each index is in its own thread)

Interactive_timeout = 28800
# The number of seconds before the server closes the interactive connection. Interactive clients are defined as clients that use the CLIENT_INTERACTIVE option in mysql_real_connect. Default Value: 28800 seconds (8 hours)

Wait_timeout = 28800
# The number of seconds the server waits for activity before closing the non-interactive connection. When a thread starts, the wait_timeout value of the session is initialized based on the global wait_timeout value or global interactive_timeout value,
# Depends on the client type (defined by CLIENT_INTERACTIVE, the connection option of mysql_real_connect ). Default Value of the parameter: 28800 seconds (8 hours)
# The maximum number of connections supported by the MySQL Server is limited, because the creation of each connection consumes memory. Therefore, we hope that after the client connects to the MySQL Server to process the corresponding operations,
# Disconnect and release the occupied memory. 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 keep accumulating and opening,
# The maximum number of connections to the MySQL Server will be reached, which will result in an error of 'too has connections. The value of wait_timeout should be determined based on the system running condition.
# After the system runs for a period of time, you can run the show processlist command to view the connection status of the current system. If a large number of connection processes in sleep status are found, this parameter is set too large,
# You can make some adjustments. Interactive_timeout and wait_timeout must be set at the same time.

[Mysqldump]
Quick
Max_allowed_packet = 16 M # Maximum length of packets sent and received by the server

[Myisamchk]
Key_buffer_size = 8 M
Sort_buffer_size = 8 M
Read_buffer = 4 M
Write_buffer = 4 M