MyISAM and InnoDB Engine Optimization

MyISAM and InnoDB engine optimization 1. Public options www.2cto.com skip-external-locking // avoid MySQL external locks, reduce the chance of errors and enhance stability. 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, IP addresses are required for all remote host connection authorizations. Otherwise, MySQL cannot process connection requests normally! Max_connections = 1024 // specify the maximum number of connection processes allowed by MySQL. If the Too connector Connections error message appears frequently during access, you need to increase the value of this parameter. Query_cache_size = 16 M // The default value is 0, that is, it is not enabled. Specify the size of the MySQL Query Buffer. You can run the following command on the MySQL Console: #> show variables like '% query_cache %'; #> show status like 'qcache % '; # If the Qcache_lowmem_prunes value is very large, this indicates that the buffer is insufficient frequently. If the Qcache_hits value is very large, it indicates that the query buffer is frequently used. If this value is small, it will affect the efficiency, you can consider not to query the buffer; Qcache_free_blocks. If this value is very large, it indicates that there are many fragments in the buffer. Sort_buffer_size = 6 M // The cache size for sorting of each thread. This option applies to sorting order by and group. Note: The allocated memory corresponding to this parameter is exclusive to each connection! If there are 100 connections, the total size of the actually allocated sort buffer is 100 × 6 = 600 MB. Therefore, we recommend that you set the size of a server with around 4 GB to 6-8 Mb. Record_buffer = 16 M // each thread conducting a Sequential Scan allocates a buffer of this size to each table it scans, you can set it to 2 MB or more for www.2cto.com table_cache = 512 // The number of tables opened by all threads. Increase this value to increase the number of file descriptors required by mysqld. Mysql requires two file descriptors for each unique opened table. 2. MyISAM option key_buffer_size = 256 M // key_buffer_size specifies the buffer size used for the index. Increasing the size can improve the index processing performance. This parameter can be set to 384 M or M for servers with around 4 GB of memory. Note: If this parameter value is set too large, the overall efficiency of the server will be reduced! Read_buffer_size = 4 M // the buffer size that can be used by the read query operation. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection! Myisam_sort_buffer_size = 64 M // The default value is 16 M. The buffer size used to set, restore, and modify a table. The value must not be too large. 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! 3. InnoDB option innodb_buffer_pool_size = 1G // The Most Important Innodb parameter. this parameter is similar to MyISAM's key_buffer_size, but it is also different. This parameter is mainly used to cache indexes, data, and buffer when data is inserted in the innodb table. The larger the value is set, the less disk I/O required to access data in the table, generally, it is half of the memory and cannot exceed 2 GB. Otherwise, the system will crash. It is the primary parameter for Innodb acceleration and optimization. This parameter is used to allocate memory. The default value of this parameter is 8 MB, which is a very small value. If it is a dedicated DB server, it can account for 70%-80% of the memory. This parameter cannot be changed dynamically. Therefore, you need to consider the allocation. If the allocation is too large, Swap will take up too much, resulting in slow Mysql query. If your data is small, you can allocate about 10% of your data size as the value of this parameter. For example, if the data size is 50 M, innodb_buffer_pool_size = 64 M innodb_additional_mem_pool_size = 16 M is allocated to the Innodb internal directory. This value does not need to be too large and can be automatically adjusted. No need to set too high. Generally, it is enough to set 16 MB for big data. If there are more tables, you can increase the size as appropriate. If this value is automatically increased, it will be displayed in the error log. Innodb_log_file_size = 256 M // the size of each log file in the log group. Generally, it is 25% of innodb_buffer_pool_size. It is officially recommended to be 40-50% of innodb_buffer_pool_size. Generally, it is better to add up to 2 GB of LOG files. The specific situation also depends on the transaction size and data size. Note: the size of the value allocation has a great relationship with the database write speed, transaction size, and recovery after an exception or restart. Innodb_log_files_in_group = 2 // specify several log groups. Allocation principle: Generally we can use 2-3 daily value groups. The default value is two. Innodb_log_buffer_size = 3 M // buffer the transaction in the memory. Allocation principle: the limit is 2-8 m. This value does not need to be too large. The memory in it is generally written to the disk once a second. The specific write method is related to your transaction commit method. It is generally appropriate to specify a maximum of 3 m for oracle and other databases. Innodb_flush_logs_at_trx_commit = 0 // The allocation principle for controlling the transaction commit mode: this parameter has only three values, 0, 1, and 2. Check the acceptable level. The default value is 1. Do not change the master database. A transaction with higher performance can be set to 0 or 2, but will be lost for one second. Note: the setting of this parameter has a great impact on the performance of Innodb, so I would like to explain it here. When the value is 1: The transaction LOG of innodb writes the Daily Value file after each commit, and refreshes the daily value to the disk. This can avoid losing any transaction. When the value is 2: log buffering is written to a file for each commit, but disk operations are not refreshed for log files, the log file is refreshed once per second when the value is 2. However, it should be noted that, due to process calling problems, it cannot be guaranteed that 100% occurs every second. Thus, the performance is the fastest. However, the transaction in the last second will be deleted only when the operating system crashes or powers down. When the value is 0, the log buffer is written to the log file once per second, and the disk operation is refreshed for the log file, but no operation is performed on the commit of a transaction. The crash of the mysqld process will delete the last second of the transaction before the crash.