MySQL configuration file my. cnf parameter optimization and Chinese explanation

MySQL configuration file my. cnf parameter optimization and Chinese explanation MySQL configuration file my. cnf parameter optimization and Chinese explanation, very detailed comment on the role of each parameter and the recommended value in Chinese. Mysql parameter optimization is hard to understand for new users. In fact, this parameter optimization is very complicated. For different websites, their online volume, and access volume

MySQL configuration file my. cnf parameter optimization and Chinese explanation MySQL configuration file my. cnf parameter optimization and Chinese explanation, very detailed comment on the role of each parameter and the recommended value in Chinese. Mysql parameter optimization is hard to understand for new users. In fact, this parameter optimization is very complicated. For different websites, their online volume, and access volume

MySQL configuration file my. cnf parameter optimization and Chinese explanation
MySQL configuration file my. cnf parameter optimization and detailed description of Chinese characters. The function and recommended values of each parameter are annotated in Chinese.

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.
Code:

[Client] port = 3306 socket =/tmp/mysql. sock [mysqld] port = 3306 socket =/tmp/mysql. sockbasedir =/usr/local/mysqldatadir =/data/mysqlpid-file =/data/mysql. piduser = mysqlbind-address = 0.0.0.0server-id = 1 # indicates that the serial number of the local machine is 1, generally, it means skip-name-resolve # disables MySQL from performing DNS resolution on external connections. This option can eliminate the time for MySQL to perform DNS resolution. However, if this option is enabled, # the IP address is used for all remote host connection authorizations, otherwise MySQL will not be able to process connection requests # skip-networkingback_log = 600 # the number of connections that MySQL can have. When the main MySQL thread receives a lot of connection requests in a short period of time, this works, # 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 exceeds back_log, connection resources are 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 # maximum number of MySQL connections. 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 if there are more connections, MySQL will provide a connection buffer for each connection, the more memory overhead, therefore, you must adjust the value appropriately and do not blindly increase the value. You can use the 'conn' % 'wildcard to view the number of connections in the current status to determine the value size. Max_connect_errors = 6000 # For the same host, if there is an interrupted error connection that exceeds the value of this parameter, the host will be disabled. To unban the HOST, run: flush host. Open_files_limit = 65535 # file descriptor limit opened by MySQL. The default value is 1024. When open_files_limit is not configured, max_connections * 5 and ulimit-n are compared, # 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 will read some data into the table_open_cache cache. when MySQL cannot find the relevant information in this cache, it will read it on the disk. The default value is 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 will become invalid and cannot connect to 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 # when a transaction is not committed, the generated logs are recorded in the Cache. when the transaction is committed, the logs are persisted to the disk. The default binlog_cache_size is 32Kmax_heap_table_size = 8 M # defines the size of memory tables (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 dynamically changing the buffer size of the tmp_table_size = 16 M # MySQL heap (stacked) table. 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 join query speed. read_buffer_size = 2 M # MySQL reads the buffer size. 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 read_rnd_buffer_size = 8 M # MySQL random read buffer by adding the variable value and the memory buffer size. 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 # the buffer size used by MySQL to perform sorting. To increase the order by speed, first check whether MySQL can use indexes instead of additional sorting stages. # If not, try to increase the size of the variable 'sort_buffer_size '. join_buffer_size = 8 M # the buffer size that can be used by the Joint query operation, which is the same as sort_buffer_size, the allocated memory corresponding to this parameter is also exclusive to thread_cache_size = 8 # This value (default value 8) indicates that the number of threads stored in the cache can be reused, when the connection is disconnected, if there is space in the cache, the client thread will be put into the cache. # If the thread is requested again, the request will be read from the cache, if the cache is empty or a new request, this thread will be re-created. If there are many new threads, # adding this value can improve system performance. by comparing variables in 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 3G-> 64query_cache_size = 8 M # MySQL query buffer size (starting from 4.0.1, mySQL provides a query buffer mechanism. MySQL stores SELECT statements and query results in the buffer. # For the same SELECT statement (case sensitive ), the results will be read directly from the buffer zone. 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. In addition, adding SQL _NO_CACHE to the SELECT statement explicitly indicates that Query Buffer query_cache_limit = 2 M # is not used to specify the buffer size that can be used by a single query, by default, 1Mkey_buffer_size = 4 M # specifies the buffer size used for the index, and adds the index that can be better processed (for all reads and writes), so that you can afford that much. If you make it too large, # The system will begin to change pages and it will really 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. # You can check whether the key_buffer_size setting is reasonable. The ratio of key_reads/key_read_requests should be as low as possible, # At least, is better (the above STATUS values can be obtained using show status like 'key _ read % ). Note: The value of this parameter is too large, but it will reduce the overall efficiency of the server ft_min_word_len = 4 # minimum length of word segmentation, the default 4transaction_isolation = REPEATABLE-READ # MySQL supports four transaction isolation levels, they are: # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE. # If it is not specified, MySQL uses the REPEATABLE-READ by default, ORACLE default is READ-COMMITTEDlog_bin = mysql-binbinlog_format = mixedexpire_logs_days = 30 # Over 30 days of binlog Delete log_error =/data/mysql/mysql-error.log # Error Log Path slow_query_log = 1long_query_ti Me = 1 # Slow query time exceeds 1 second is slow query slow_query_log_file =/data/mysql/mysql-slow.logperformance_schema = rows # lower_case_table_names = 1 # case-insensitive skip-external-locking # MySQL Option to avoid External Locking. 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 by itself) # disadvantages: # A large increase in a single table, for example, over 100 GB # conclusion: (script school www.jbxue.com) # 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_filesinnodb_open_files = 500 # Restrict the data of tables that can be opened by Innodb. If there are many tables in the database, add this. The default value is 300innodb_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 it to too large. Otherwise, the competition in physical memory 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 = 4innodb_read_io_threads = 4 # innodb uses background threads to process read/write I/O (input/output) requests on the data page. The default value is 4 # Note: these two parameters do not support dynamic changes and need to be added to my. in cnf, restart the MySQL service after modification. The value range is 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 it to 0 to better utilize the processing capabilities of multiple CPU cores, increase the concurrency innodb_purge_threads = 1 # The cleanup operation 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 single # single thread for the purge operation. By default, the parameter is set to 0 (no separate thread is used ), when it is set to 1, it means to use a separate thread to clear. The recommended value is 1innodb_flush_log_at_trx_commit = 2 #0. If the innodb_flush_log_at_trx_commit value is 0, log buffer writes log files 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 or not the transaction has been committed) the default log file is ib_logfile0, ib_logfile1 #1: When set to the default value of 1, the log buffer is written to the log each time the 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 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 (but not unacceptable for 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 writing, set it to 0 to get 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. MySQL developers are advised to set innodb_log_file_size = 32 M between 1 and 8 M # This parameter determines the size of the Data Log File. Larger settings can improve performance, however, it will also increase 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. It is recommended to set 3innodb_max_dirty_pages_pct = 90 # the innodb main thread refreshes the data in the cache pool, make the proportion of dirty data less than 90% innodb_lock_wait_timeout = 120 # InnoDB transactions can wait for a locked timeout period before being rolled back. 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 buffer size used for table restoration, 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 will exceed the INDEX, do not use the Quick Sort index method to create an index. Note: this parameter is provided in bytes. myisam_repair_threads = 1 # If the value is greater than 1, create the MyISAM Table index in parallel during the Repair by sorting process (each index is in its own thread) interactive_timeout = 28800 # 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 # number of seconds waiting for activity before the server closes 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 the global interactive_timeout value. # It 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, # The connection should be closed and the occupied memory should be released. 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 and continue to open, # It will eventually reach the maximum number of connections of the MySQL Server, this will report the 'too has connections' error. 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] quickmax_allowed_packet = 16 M # maximum package length sent and accepted by the server [myisamchk] key_buffer_size = 8Msort_buffer_size = 8Mread_buffer = 4Mwrite_buffer = 4 M