Special note://The annotation representing #
//* * * Application customization options***
// MySQL Server SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
[mysqld]
//General configuration options
port = @MYSQL_TCP_PORT@
socket = @MYSQL_UNIX_ADDR@ SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//Back_log is the number of connections that the operating system can keep in the listening queue. The queue stores the connections before the MySQL connection manager thread processes them. If you have a very high connection rate and a "connection refused" error occurs, You should increase the value here. Check your operating system documentation to get the maximum value of this variable. If back_log is set to a value higher than your operating system limit, it will have no effect on how many requests can be stored in the stack in a short time before MYSQL temporarily stops responding to new requests. If the system has many connections in a short time, you need to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections. The default value is 50.
back_log = 300 SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//Do not listen on the TCP/IP port. If all processes connect to the local mysqld on the same server, this setting will be a way to enhance security. All mysqld connections are made through Unix sockets or named pipes. Note that in Windows, if the named pipe option is not opened, only this option is used (through the "enable named pipe" option) This will lead to no effect of MySQL service!
// skip-networking SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//The maximum number of simultaneous sessions allowed by the MySQL service. One of the connections will be reserved with the SUPER permission to log in as an administrator Even if the maximum number of connections has been reached MySQL allows the maximum number of process connections. If the error prompt of Too Many Connections often appears, you need to increase this value.
max_connections = 3000 SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//The maximum number of errors allowed for each client connection. If this limit is reached, the client will be blocked by the MySQL service until "FLUSH HOSTS" is executed, or illegal passwords for service restart and other errors during the link will increase this value View the "Aborted_connects" status to obtain global counters. Set the maximum number of connection requests that are interrupted abnormally for each host. When this number is exceeded, the MySQL server will disable the host's connection requests until the MySQL server restarts or clear the host's related information through the flush hosts command.
max_connect_errors = 30 SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//The number of tables opened by all threads. Increasing this value will increase the number of file descriptors required by mysqld. You need to confirm that the "open files limit" variable in [mysqld_safe] allows at least 4096 open files to indicate that the table adjusts the buffer size.
//The table_cache parameter sets the number of table caches. Each connection will open at least one table cache# Therefore, the size of table_cache should be related to the setting of max_connections. For example, for 200 # connections running in parallel, the table cache should be at least 200 × N, where N is the maximum number of tables in a join of query # that can be executed by the application. In addition, 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, you can directly access the cache; If # has not been cached yet, but there is still space in the MySQL table buffer, the table will be opened and placed in the table buffer # buffer; If the table cache is full, the currently unused tables will be released according to certain rules, or the table cache will be temporarily expanded for storage. The advantage of using table cache is that you can access the contents of the table more quickly. Executing flush tables will # clear the cache contents. In general, you can check the status values Open_tables # and Opened_tables of the database running peak time to determine whether you need to increase the table_cache value (where open_tables is the number of tables opened before #, and Opened_tables is the number of tables already opened). That is, if open_tables is close to table_cache, and the Opened_tables value is gradually increasing, you should consider increasing the size of the # value. Also, when Table_locks_waited is high, table_cache needs to be added.
table_cache = 4096 SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//Allow external file level locks Opening the file lock will have a negative impact on the performance, so this option is only used when you run multiple database instances on the same file (note that there are still other constraints!) or you use some other software dependencies at the file level to lock MyISAM tables Use the – skip external locking MySQL option to avoid external locking. This option is enabled by default
//external-locking SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//The maximum size of the request packet that the service can process and the maximum size of the request that the service can process (quite necessary when working with large BLOB fields). The independent size of each connection. The size is dynamically increased to set the maximum value of a message transmission volume in network transmission. The system default value is 1MB, and the maximum value is 1GB. The multiple of 1024 must be set.
max_allowed_packet = 32M SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//In a transaction, the cache size held by binlog to record the SQL status. If you often use large, multi declaration transactions, you can increase this value to obtain greater performance All the statuses from transactions will be buffered in the binlog buffer and written to the binlog once after the commit. If the transaction is larger than this value, the temporary file on the disk will be used to replace it. This buffer is created when each connected transaction updates the status for the first time
binlog_cache_size = 4M SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//The maximum capacity allowed by an independent memory table This option is used to prevent the accidental creation of a large memory table, which will lead to the permanent exhaustion of all memory resources
max_heap_table_size = 128M SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//The sort buffer is used to process the sort caused by the ORDER BY and GROUP BY queues. If the sorted data cannot be put into the sort buffer, a disk based merge classification used to replace it will be used to view the "Sort_merge_passes" status variable Allocated by each thread when sorting occurs
//Sort_Buffer_Size is a connection level parameter. When each connection (session) needs to use this buffer for the first time, the memory set by one-time allocation Sort_Buffer_Size is not as large as possible. Because it is a connection level parameter, too large a setting and high concurrency may exhaust the system memory resources. For example, 500 connections will consume 500 * sort_buffer_size (8M)=4G memory. When Sort_Buffer_Size exceeds 2KB, mmap() will be used instead of malloc() for memory allocation, resulting in lower efficiency.
//Technical Guide http://blog.webshuo.com/2011/02/16/mysql-sort_buffer_size/
//dev-doc: http://dev.mysql.com/doc/refman/5.5/en/server-parameters.html
//explain select*from table where order limit； Filesort appears
//Key optimization parameters
sort_buffer_size = 16M SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//This buffer is used to optimize full federations (full JOINS without indexes). Similar federations have very poor performance in most cases, but setting this value higher can reduce the performance impact. View the number of full federations through the "Select_full_join" status variable. When full federations occur, allocate the size used for inter table association cache in each thread, Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection.
join_buffer_size = 16M SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//How many threads do we reserve in the cache for reuse? When a client disconnects, if the number of threads in the cache is less than thread_cache_size, the client threads are put into the cache. This can greatly reduce the cost of thread creation when you need a large number of new connections (generally speaking, if you have a good thread model, this will not significantly improve the performance.)
//The server thread cache value indicates that the number of threads stored in the cache can be reused. If there is still space in the cache when the connection is disconnected, the client thread will be put into 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, increasing this value can improve the system performance. You can see the effect of this variable by comparing the variables in the Connections and Threads_created states. The setting rules are as follows: 1GB memory is configured as 8, 2GB memory is configured as 16, 3GB memory is configured as 32, 4GB memory or higher, and larger memory can be configured.
thread_cache_size = 16 SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//This allows the application to give a hint to the thread system about the number of threads that are eager to be run at the same time. This value is only meaningful for systems that support the thread_concurrency() function (such as Sun Solaris). You can try to use [CPU number] * (2.. 4) as the value of thread_concurrency
//Whether the thread_concurrency value is set correctly or not has a great impact on the performance of MySQL. If the thread_concurrency value is set incorrectly in the case of multiple CPUs (or multi-core), MySQL cannot make full use of multiple CPUs (or multi-core), and only one CPU (or core) can work at a time. Thread_concurrency should be set to twice the number of CPU cores For example, if there is a dual core CPU, the thread_concurrency should be 4; For two dual core CPUs, the thread_concurrency value should be 8
//Key optimization parameters
thread_concurrency = 8 SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//Query buffering is often used to buffer the results of SELECT, and the next time the same query is executed, the results will not be returned directly. Opening query buffering can greatly improve the server speed. If you have a large number of the same queries and rarely modify the table, see "QCache_lowmem_prunes" State variable to check whether the current value is high enough for your load. Note: when your table changes frequently or if your original query text is different each time, query buffering may cause performance degradation rather than performance improvement. This variable must be familiar to users using MySQL. This parameter is also an important optimization parameter in MyISAM engine optimization several years ago. However, with the development, some problems have emerged in this parameter. The memory of the machine is getting larger and larger, and people are accustomed to allocating larger and larger values of previously useful parameters. The increase of this parameter also caused a series of problems. Let's first analyze the working principle of query_cache_size: after a SELECT query works in the DB, the DB caches the statement. When the same SQL is called again in the DB, the DB returns the result from the cache to the client without any change in the table. There is a key point here, that is, when DB uses Query_cache to work, it requires that the table involved in the statement does not change during this period of time. What about the data in Query_cache when the table changes? First, set all statements related to Query_cache and the table to invalid, and then write updates. If Query_cache is very large, the table has many query structures, and the query statement fails slowly, an update or Insert will be very slow. This shows how the update or Insert is so slow. Therefore, this parameter is not suitable for systems with large database writes or updates. In addition, it is recommended to disable this function in systems with high concurrency and large amount of writes.
//Key optimization parameters (addition, deletion and modification of main database - MyISAM)
query_cache_size = 128M SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//Only results smaller than this setting value will be buffered. This setting is used to protect the query buffer and prevent a large result set from overwriting all other query results. Specify the buffer size that can be used by a single query. The default is 1M
query_cache_limit = 4M SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//The minimum word length indexed by full-text search You may want to reduce it if you need to search for shorter words. Note that after you modify this value, you need to rebuild your FULLTEXT index
ft_min_word_len = 8 SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//If your system supports the memlock() function, you may want to turn on this option to keep data locked in memory and prevent it from being swapped out when running MySQL under high memory stress
//This option is good for performance
//memlock SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//This is the default table type when creating a new table. If there is no special execution table type when creating a new table, this value will be used
default_table_type = MYISAM SourceByrd's Weblog- https://note.t4x.org/database/mysql-my-cnf/
//The size of the heap used by the thread This amount of memory is reserved each time you connect MySQL itself usually does not need more than 64K of memory. If you use your own UDF functions that require a lot of heaps or your operating system needs more heaps for some operations, you may need to set it higher
thread_stack = 512K
//Set the default transaction isolation level. The available levels are as follows: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_isolation = REPEATABLE-READ
//The maximum size of an internal (in memory) temporary table will be automatically converted to a disk based table if a table grows to a larger size than this value. This limit is for a single table, not a total
tmp_table_size = 128M
//Turn on the binary log function In replication configuration, as the master server, this option must be enabled. If you need to do point in time recovery from your last backup, you also need binary logs
log-bin=mysql-bin
//If you are using the chain slave server structure replication mode (A ->B ->C), you need to open this option on server B. This option opens the updated log that has been redone on the slave thread and writes it to the binary log of the slave server
//log_slave_updates
//Open the full query log All queries received by the server (even for a query with incorrect syntax) will be recorded This is very useful for debugging, and is often closed in production environments
//log
//Prints out warnings to the error log file If you have any problems with MySQL, you should open the warning log and carefully review the error log to find out the possible causes
//log_warnings
//Record slow queries Slow queries are queries that consume more time than the "long_query_time" definition If log_long_format is opened, queries that do not use indexes will also be recorded. If you often add new queries to existing systems Generally speaking, this is a good idea,
log_slow_queries
//All queries that use more than this time (in seconds) will be considered as slow queries. Do not use "1" here, or all queries, even very fast query pages, will be recorded (because MySQL's current time accuracy can only reach the second level)
long_query_time = 6
//Record more information in the slow log Generally, it is better to open this item If this option is enabled, the records will be added to the slow log as a slow query for queries that do not use indexes
log_long_format
//This directory is used by MySQL to save temporary files. For example, it is used to handle large disk based sorting, just like internal sorting. It also has simple temporary tables. If you do not create very large temporary files, it may be better to place them on the swapfs/tmpfs file system. Another option is that you can also place them on a separate disk. You can use ";" To place multiple paths, they will be polled according to the route robin method
//tmpdir = /tmp
//The size of the keyword buffer is generally used to buffer the index block of the MyISAM table. Do not set it to be greater than 30% of your available memory, because some memory is also used by the OS to buffer row data. Even if you do not use the MyISAM table, you need to still set 8-64M memory, because it will also be used by the internal temporary disk table
key_buffer_size = 128M
//The buffer size used for full table scanning of MyISAM tables When full table scanning is required, it is allocated in the corresponding thread
read_buffer_size = 8M
//When rows are read from a sorted sequence after sorting, row data will be read from this buffer to prevent disk seek If you increase this value, you can improve the performance of many ORDER BY. It is allocated by each thread when necessary
read_rnd_buffer_size = 64M
//MyISAM uses special tree like caches to make burst inserts (these inserts are INSERT... SELECT, INSERT... VALUES (...), (...),..., and LOAD DATA INFILE) faster This variable limits the number of bytes of the buffer tree in each process Setting to 0 turns off this optimization For optimization, do not set this value to be greater than "key_buffer_size". When burst insertion is detected, this buffer will be allocated
bulk_insert_buffer_size = 256M
//This buffer is allocated when MySQL needs to re index an empty table in REPAIR, OPTIMIZE, ALTER and LOAD DATA INFILE This is allocated in each thread, so be careful when setting large values
myisam_sort_buffer_size = 256M
//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 through the key value buffer (slower)
myisam_max_sort_file_size = 10G
//If the temporary file used for faster index creation is larger than the specified value, the key value buffer method is used. This is mainly used to force long string keys in large tables to use a slow key value buffer method to create indexes
myisam_max_extra_sort_file_size = 10G
//If a table has more than one index, MyISAM can use more than one thread to repair them 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 are not properly closed
myisam_recover
//Federated is off by default
skip-federated
//If your MySQL service includes InnoDB support but you do not intend to use it, using this option will save memory and disk space, and speed up some parts
//skip-innodb
//The additional memory pool is used by InnoDB to store metadata information. If InnoDB needs more memory for this purpose, it will start to request memory from the OS. Since this operation is fast enough on most modern operating systems, you generally do not need to modify this value The SHOW INNODB STATUS command displays the quantity used first
innodb_additional_mem_pool_size = 64M
//InnoDB uses a buffer pool to store indexes and raw data, unlike MyISAM. The larger you set here, the less disk I/O you need to access data in the table. On an independent database server, you can set this variable to 80% of the server's physical memory size. Otherwise, Due to the competition of physical memory, the page change of the operating system may be bumpy Note that on 32-bit systems, each process may be limited to the 2-3.5G user level memory limit, so do not set it too high
innodb_buffer_pool_size = 6G
//InnoDB stores data in one or more data files as tablespaces If you only have a single logical drive to save your data, a single auto increment file is good enough. In other cases, one file for each device is generally a good choice. You can also configure InnoDB to use raw disk partitions - please refer to the manual for more information
innodb_data_file_path = ibdata1:10M:autoextend
//Set this option if you want InnoDB tablespace files to be saved in other partitions It is saved in the datadir of MySQL by default
//innodb_data_home_dir =
//The number of IO threads used to synchronize IO operations This value is hard coded to 4 under Unix, but disk I/O in Windows may perform better at a large value
innodb_file_io_threads = 4
//If you find that the InnoDB tablespace is corrupted, setting this value to a non-zero value may help you export your table. Start with 1 and increase this value until you can successfully export the table
//#innodb_force_recovery=1
//The number of threads allowed in the InnoDb core. The optimal value depends on the scheduling method of the application, hardware, and operating system. Too high a value may lead to the mutual exclusion of threads
innodb_thread_concurrency = 16
//If set to 1, InnoDB will refresh the (fsync) transaction log to the disk after each commit, which provides complete ACID behavior If you are willing to compromise transaction security, and you are running a small food, you can set this value to 0 or 2 to reduce the disk I/O caused by the transaction log. 0 means that the log is only written to the log file every second and the log file is flushed to the disk 2 means that the log file written to the log file is submitted every time, but the log file will be refreshed to the disk only about every second
innodb_flush_log_at_trx_commit = 2
(Note: If it is a game server, it is recommended to set this value to 2; if it is an application with high data security requirements, it is recommended to set it to 1; if it is set to 0, the performance is the highest, but if there is a failure, the data may be lost! The default value of 1 means that every transaction commit or command outside the transaction needs to write the log to the hard disk, which is time-consuming. Especially when battery backed up cache is used. Set it to 2 for many applications, especially those transferred from MyISAM table. It means not writing to the hard disk but to the system cache. The log will still flush to the hard disk every second, so you generally won't lose more than 1-2 seconds of updates. Setting it to 0 is faster, but the security is poor. Even if MySQL fails, the transaction data may be lost. A value of 2 can only lose data when the entire operating system hangs.)
//Accelerate the shutdown of InnoDB This will prevent InnoDB from performing full clearing and inserting buffer merging when it is closed This may greatly increase the shutdown time, but instead, InnoDB may do these operations at the next startup
//#innodb_fast_shutdown
//The size of the buffer used to buffer log data When this value is almost full, InnoDB will have to refresh the data to the disk. Since it will refresh once every second, it is unnecessary to set this value too high (even for long transactions)
innodb_log_buffer_size = 16M
//The size of each log file in the log group. You should set the total log file size to 25%~100% of your buffer pool size to avoid unnecessary buffer pool flushing behavior on log file overwriting However, please note that a large log file size will increase the time required for the recovery process
innodb_log_file_size = 512M
//Total number of files in the log group Generally speaking, 2~3 is better
innodb_log_files_in_group = 3
//Location of InnoDB log files The default is MySQL datadir You can assign it to a separate hard disk or a RAID1 volume to improve its performance
//#innodb_log_group_home_dir
//The maximum allowed proportion of dirty pages in the InnoDB buffer pool. If the quota is reached, InnoDB will start to refresh them to prevent them from interfering with clean data pages This is a soft limit and cannot be guaranteed to be implemented absolutely
innodb_max_dirty_pages_pct = 90
//The method InnoDB uses to refresh logs The default value of the double write refresh method is "fdatasync" and the other is "O_DSYNC"
//#innodb_flush_method=O_DSYNC
//Before being rolled back, how long should an InnoDB transaction wait for a lock to be approved InnoDB automatically detects transaction deadlocks in its own lock table and rolls back the transaction. If you use the LOCK TABLES command or use a transaction safe storage engine other than InnoDB in the same transaction, a deadlock may occur that InnoDB cannot notice. In this case, the timeout value is very helpful for solving this problem
innodb_lock_wait_timeout = 120
