Optimization Analysis of MyISAM and InnoDB Engines

In the past few days, I have learned how to optimize the mysql database and set it on my own server. I have learned how to optimize the MyISAM and InnoDB engines. They have their own advantages and disadvantages, generally, it is better to combine the two engines in practical applications. The hardware configuration and software environment of the test are as follows:

Server Model: IBM S226
CPU: Xeon quad-core
Memory: 4 GB
Hard Disk: Two 80 GB for RAID 1
System: windows server 2003 SP1 32-bit Enterprise Edition
Mysql version: 5.5
Based on the actual situation of your server, the optimization and parameters are as follows:

I. Public options
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 connections to the Yun technology host. 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 Forum 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, it indicates that there is often insufficient buffer;
If the Qcache_hits value is very large, it indicates that the query buffer is used very frequently. If the value is small, it will affect the efficiency, you can consider not to use the Query Buffer; Qcache_free_blocks, if the 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 that performs an ordered scan allocates a buffer of this size to each table it scans. It can be set to 2 M or more.
Table_cache = 512 // number of tables opened for 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.

Ii. MyISAM options
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!

Iii. InnoDB options
Innodb_buffer_pool_size = 1G // The Most Important Innodb parameter. this parameter is similar to the key_buffer_size of MyISAM, 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 is assigned to this value.
Innodb_additional_mem_pool_size = 16 M // The value of the internal directory used to store Innodb does not need to be allocated too large, and the system can automatically tune it. 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.

From the above analysis, when this value is not 1, it can achieve better performance, but there will be a loss in the case of exceptions, so it should be measured according to your own situation.