Detailed explanation of optimization of MySQL configuration file my.cnf

MySQL 5.5.13
Parameter description:
[client]
character-set- server = utf8
port = 3306
socket = /data/mysql/3306/mysql.sock
[mysqld]
character-set-server = utf8
user = mysql
port = 3306
socket = / data/mysql/3306/mysql.sock
basedir = /usr/local/webserver/mysql
datadir = /data/mysql/3306/data
log-error = /data/mysql/3306/mysql_error.log
pid-file = /data/mysql/3306/mysql.pid
# The table_cache parameter sets the number of table caches. Every time a connection comes in, at least one table cache will be opened. #Therefore, the size of table_cache should be related to the setting of max_connections. For example, for 200 # connections running in parallel, you should have a cache of tables of at least 200 × N, where N is the maximum number of tables in a join that the application can execute for a query #. Additionally, some additional file descriptors need to be reserved for temporary tables and files.
# When Mysql accesses a table, if the table has been opened in the cache, the cache can be accessed directly; if # it has not been cached, but there is still space in the Mysql table buffer , then the table is opened and put into the table buffer; if the table cache is full, the currently unused table will be released according to certain rules, or the table cache will be temporarily expanded for storage. The benefits of using the table cache are Provides faster access to table contents. Executing flush tables will #clear the contents of the cache. Generally speaking, you can judge whether you need to increase the value of table_cache by looking at the status values ​​of Open_tables # and Opened_tables during the peak running time of the database (where open_tables is the number of tables currently open, and Opened_tables is the number of tables that have been opened). That is, if open_tables is close to table_cache, and the value of Opened_tables is gradually increasing, then you should consider increasing the size of this # value. Also, when Table_locks_waited is relatively high, table_cache also needs to be increased.
open_files_limit = 10240
table_cache = 512
#Non-dynamic variable, the service needs to be restarted
# Specify the number of possible connections to MySQL. When the MySQL main thread receives a lot of connection requests in a short period of time, this parameter takes effect, and the main thread spends a short time checking the connection and starting a new thread. The value of the back_log parameter indicates how many requests can be stored in the stack in a short period of time before MySQL temporarily stops responding to new requests. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections. Different operating systems have their own limits on this queue size. Attempting to set back_log higher than your operating system's limit will have no effect. The default value is 50. For Linux systems, it is recommended to set it to an integer less than 512.
back_log = 600
#MySQL allows the maximum number of connections
max_connections = 5000
#Yes How many error connections are allowed
max_connect_errors = 6000
#Use the --skip-external-locking MySQL option to avoid external locking. This option is enabled by default
external-locking = FALSE
# Set the maximum packet size, limit the size of data packets accepted by the server, and avoid problems with the execution of over-long SQL. Default value is 16M. When the MySQL client or mysqld server receives a packet larger than max_allowed_packet bytes, a "packet too large" error will be issued and the connection will be closed. For some clients, you may encounter a "Lost connection to MySQL server" error during query execution if the communication packets are too large. The default value is 16M.
#dev-doc: http://www.php.cn/
max_allowed_packet = 32M
# Sort_Buffer_Size is a connection Level parameter, when each connection (session) needs to use this buffer for the first time, the set memory is allocated once.
#Sort_Buffer_Size is not bigger, the better. Since it is a connection-level parameter, too large settings + high concurrency may exhaust system memory resources. For example: 500 connections will consume 500*sort_buffer_size(8M)=4G memory
#Sort_Buffer_Size. When it exceeds 2KB, mmap() will be used instead of malloc() for memory allocation, resulting in Efficiency is reduced.
#Technical Introduction http://www.php.cn/
#dev-doc: http://www.php.cn/
#explain select*from table where order limit; filesort appears
# is a key optimization parameter
sort_buffer_size = 8M
#The size used for inter-table association cache
join_buffer_size = 1M
# Server thread cache This value indicates the number of threads saved in the cache that can be reused. If there is still space in the cache when the connection is disconnected, the client's thread will be placed in the cache. If the thread is requested again, Then the request will be read from the cache. If the cache is empty or it is a new request, then the thread will be re-created. If there are many new threads, increasing this value can improve system performance. By comparing the Connections and Threads_created status Variable, you can see the role of this variable
thread_cache_size = 300
# Whether the value of thread_concurrency is set correctly or not has a great impact on the performance of mysql. In the case of multiple CPUs (or multiple cores), incorrectly setting the value of thread_concurrency will cause MySQL to be unable to fully utilize multiple CPUs (or multiple cores), and only one CPU (or core) can be working at the same time. thread_concurrency should be set to 2 times the number of CPU cores. For example, if there is a dual-core CPU, then the thread_concurrency value should be 4; for 2 dual-core CPUs, the thread_concurrency value should be 8
# is a key optimization Parameter
thread_concurrency = 8
# For users who use MySQL, everyone will be familiar with this variable. In the MyISAM engine optimization in previous years, this parameter was also an important optimization parameter. But with development, this parameter has also exposed some problems. The memory of machines is getting larger and larger, and people are accustomed to assign larger and larger values ​​​​to previously useful parameters. Increasing this parameter also caused a series of problems. Let's first analyze the working principle of query_cache_size: After a SELECT query is worked in DB, DB will cache the statement. When the same SQL comes to DB again and is called, DB will cache it without changing the table. The results are returned to the Client from the cache. A key point here is that when DB uses Query_cache to work, it requires that the table involved in the statement has not changed during this period of time. So if the table changes, how will the data in Query_cache be processed? First, invalidate all Query_cache and statements related to the table, and then write updates. Then if the Query_cache is very large, the table has many query structures, and the query statement is invalid slowly, an update or Insert will be very slow, so what you see is why the Update or Insert is so slow. Therefore, in systems where the amount of database writes or updates is relatively large, this parameter is not suitable for allocation that is too large. Moreover, in systems with high concurrency and large write volume, this function is recommended to be disabled.
#Key optimization parameters (main database addition, deletion and modification-MyISAM)
query_cache_size = 512M
#Specify the buffer that can be used by a single query Area size, the default is 1M
query_cache_limit = 2M
#The default is 4KB, setting a large value is good for big data queries, but if your queries are Small data queries can easily cause memory fragmentation and waste
#Query cache fragmentation rate = Qcache_free_blocks / Qcache_total_blocks * 100%
#If the query cache fragmentation rate exceeds 20% , you can use FLUSH QUERY CACHE to defragment the cache, or try reducing query_cache_min_res_unit, if your queries are all small data volumes.
#Query cache utilization = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
#If the query cache utilization is below 25%, it means that query_cache_size is set too large and can be appropriately reduced; if the query cache utilization is above 80% and Qcache_lowmem_prunes > 50, it means that query_cache_size may be a bit small, or it is too fragmented. many.
#Query cache hit rate = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
query_cache_min_res_unit = 2k
default-storage- engine = MyISAM
#Limits the stack size used for each database thread. The default settings are sufficient for most applications
thread_stack = 192K
# Sets the default transaction isolation level. The available levels are as follows:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
# 1.READ UNCOMMITTED-read uncommitted2.READ COMMITTE-read committed3.REPEATABLE READ-repeatable read4 .SERIALIZABLE -SERIAL
transaction_isolation = READ-COMMITTED
# The default size of tmp_table_size is 32M. If a temporary table exceeds this size, MySQL generates an error of the form The table tbl_name is full. If you do a lot of advanced GROUP BY queries, increase the tmp_table_size value.
tmp_table_size = 246M
max_heap_table_size = 246M
#Index cache size: It determines the speed of database index processing, especially the index Reading speed
key_buffer_size = 512M
# MySql read buffer size. A request for a sequential scan of the table will allocate a read buffer, and MySql will allocate a memory buffer for it. The read_buffer_size variable controls the size of this buffer. If sequential scan requests for a table are very frequent and you feel that the frequent scans are running too slowly, you can improve its performance by increasing the value of this variable and the memory buffer size.
read_buffer_size = 4M
# The random read (query operation) buffer size of MySql. When rows are read in any order (for example, in sorted order), a random read buffer is allocated. When performing a sorting query, MySql will first scan the buffer to avoid disk searches and improve query speed. If a large amount of data needs to be sorted, this value can be increased appropriately. However, MySql will allocate this buffer space for each customer connection, so you should try to set this value appropriately to avoid excessive memory overhead.
read_rnd_buffer_size = 16M
#Batch insert data cache size, which can effectively improve the insertion efficiency, the default is 8M
bulk_insert_buffer_size = 64M
# Buffering required for reordering when MyISAM tables change
myisam_sort_buffer_size = 128M
# The maximum temporary file size allowed when MySQL rebuilds the index (when REPAIR, ALTER TABLE or LOAD DATA INFILE).
# If the file size is larger than this value, the index will be created via key-value buffering (slower)
myisam_max_sort_file_size = 10G
# If a table has more than one index, MyISAM can fix them using more than one thread through parallel sorting.
# This is a good choice for users with multiple CPUs and large amounts of memory.
myisam_repair_threads = 1
#Automatically check and repair MyISAM tables that were not closed properly
myisam_recover
interactive_timeout = 120
wait_timeout = 120
innodb_data_home_dir = /data/mysql/3306/data
#Important data of table space files
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#This parameter is used to set the memory pool size of the data directory information and other internal data structures stored in InnoDB, similar to Oracle's library cache . This is not a mandatory parameter and can be exceeded.
innodb_additional_mem_pool_size = 16M
# This is very important for Innodb tables. Innodb tables are more sensitive to buffering than MyISAM tables. MyISAM can run fine under the default key_buffer_size setting, but Innodb runs like a snail under the default innodb_buffer_pool_size setting. Since Innodb caches both data and indexes, there is no need to leave too much memory to the operating system, so if you only need to use Innodb, you can set it up to 70-80% of the available memory. Some rules that apply to key_buffer are - If your data volume is not large and will not grow explosively, then there is no need to set innodb_buffer_pool_size too large
innodb_buffer_pool_size = 512M
#The number of threads for file IO is generally 4, but under Windows, it can be set larger.
innodb_file_io_threads = 4
# The number of threads allowed within the InnoDb core.
# The optimal value depends on the application, hardware And the scheduling method of the operating system.
# Too high a value may cause thread mutual exclusion thrashing.
innodb_thread_concurrency = 8
# If this parameter is set to 1, the log will be written to disk after each transaction is committed. To provide performance, it can be set to 0 or 2, but at the risk of losing data in the event of a failure. Setting to 0 means that the transaction log is written to the log file, and the log file is flushed to disk once per second. Setting to 2 means that the transaction log will be written to the log on commit, but the log file will be flushed to disk once at a time.
innodb_flush_log_at_trx_commit = 2
#This parameter determines the memory size used by some log files, in M. A larger buffer can improve performance, but unexpected failures will result in data loss. MySQL developers recommend setting it to between 1-8M
innodb_log_buffer_size = 16M
# This parameter determines the size of the data log file, in M. Larger settings can improve performance, but will also increase the time required to recover a failed database
innodb_log_file_size = 128M
#To improve performance, MySQL can write log files to multiple files in a circular manner. The recommended setting is 3M
innodb_log_files_in_group = 3
# Recommended reading http://www.php.cn/
# Buffer_Pool The number of Dirty_Pages directly affects the shutdown time of InnoDB. The 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 InnoDB, reduce innodb_max_dirty_pages_pct and force the data block to flush for a period of time, which can greatly shorten the shutdown time of MySQL.
innodb_max_dirty_pages_pct = 90
# InnoDB has its built-in deadlock detection mechanism, which can cause unfinished transactions to be rolled back. However, if you use MyISAM's lock tables statement or a third-party transaction engine in conjunction with InnoDB, InnoDB cannot recognize deadlocks. To eliminate this possibility, you can set innodb_lock_wait_timeout to an integer value that instructs MySQL how long (in seconds) to wait before allowing other transactions to modify data that is ultimately subject to transaction rollback
innodb_lock_wait_timeout = 120
#Exclusive table space (closed)
innodb_file_per_table = 0
#start mysqld with –slow-query- log-file=/data/mysql/3306/slow.log
slow_query_log
long_query_time = 1
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
#Configure updates from the database Whether to write a binary file for the operation. If this slave library is to be the master library of other slave libraries, then this parameter needs to be set so that the slave library can perform log synchronization. This parameter must be used together with -logs-bin
log-slave-updates
log-bin = /data/mysql/3306/binlog/binlog
binlog_cache_size = 4M
#STATEMENT,ROW,MIXED
# Statement-based replication (SBR), row-based replication (RBR), mixed mode replication (mixed -based replication, MBR). Correspondingly, there are three formats of binlog: STATEMENT, ROW, and MIXED.
binlog_format = MIXED
max_binlog_cache_size = 64M
max_binlog_size = 1G
relay-log-index = /data/mysql/3306/relaylog/relaylog
relay-log-info-file = /data/mysql/3306/relaylog/relaylog
relay-log = /data/mysql/3306/relaylog/relaylog
expire_logs_days = 30
skip-name-resolve
#master-connect -retry = 10
slave-skip-errors = 1032,1062,126,1114,1146,1048,1396
server-id = 1
[mysqldump]
quick
max_allowed_packet = 32M
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[ mysqlhotcopy]
interactive-timeout

The above is the detailed explanation of the optimization of the MySQL configuration file my.cnf. For more related content, please pay attention to PHP Chinese Net (www.php.cn)!