MyISAM and InnoDB engine optimization analysis

  • 2020-05-14 05:06:31
  • OfStack

These days yu mingtang is learning the optimization of mysql database and setting it on his own server. Yu mingtang has mainly learned the optimization methods of MyISAM and InnoDB engines, which have their own advantages and disadvantages. Generally, it is better to combine the two engines in practical application. Hardware configuration and software environment of yu mingtang test are as follows:

Server model: IBM S226
CPU: super strong 4 core
Memory: 4 G
Hard drives: two 80G do RAID1
System: windows server 2003 SP1 32-bit enterprise edition
Mysql version: 5.5
According to the actual situation of the server, the optimized parameters are as follows:

1. Public options
skip-external-locking // avoid external locking of MySQL, reduce error probability and enhance stability.
skip-name-resolve // disables MySQL from DNS parsing of external connections. Use this option to eliminate MySQL parsing of DNS. However, it is important to note that if this option is enabled, all remote eion host connection authorizations will use the IP address mode, otherwise MySQL will not be able to properly process connection requests!
max_connections = 1024 // specifies the maximum number of connection processes allowed by MySQL. If you frequently get an Too Many Connections error when visiting the forum, you need to increase the parameter value.
query_cache_size = 16M // 0 by default, but not enabled. Specify the size of the MySQL query buffer. You can observe this by executing the following command from the MySQL console:
# > SHOW VARIABLES LIKE '%query_cache%';
# > SHOW STATUS LIKE 'Qcache%';
If the value of Qcache_lowmem_prunes is very large, it indicates that insufficient buffering often occurs;
If the value of Qcache_hits is very large, it indicates that the query buffer is used very frequently. If the value of Qcache_hits is small, it will affect the efficiency. Qcache_free_blocks, if this value is very large, it indicates that there is a lot of fragmentation in the buffer.
sort_buffer_size = 6M // sort cache size per thread, this option applies to sort order by, group by. Note: the allocated memory for this parameter is exclusive per connection! If there are 100 connections, the actual total sort buffer size allocated is 100 × 6 = 600MB. Therefore, the recommended setting for servers with memory of 4GB is 6-8M.
record_buffer=16M // each thread performing 1 sequential scan allocates 1 buffer of this size for each table it scans, which can be set to 2M or higher
table_cache = 512 // number of open tables for all threads. Increasing this value increases the number of file descriptors required by mysqld. Mysql requires two file descriptors for each table with only one open.

2. MyISAM option
key_buffer_size = 256M //key_buffer_size specifies the buffer size for the index, increasing it for better index processing performance. This parameter can be set to 256M or 384M for servers with 4GB or so.
Note: if the parameter value is set too high, the overall efficiency of the server will be reduced.
read_buffer_size = buffer size available for 4M // read query operations. Like sort_buffer_size1, the allocated memory corresponding to this parameter is also exclusive to each connection!
myisam_sort_buffer_size = 64M // default is 16M. The buffer size to use when setting, restoring, or modifying a table. Do not set the value too large.
join_buffer_size = 8M // the buffer size that can be used for joint query operations, like sort_buffer_size1, the allocated memory corresponding to this parameter is also exclusive to each connection!

3. InnoDB options
innodb_buffer_pool_size = 1G // the most important parameter of Innodb. This parameter is similar to MyISAM's key_buffer_size, but there are also some differences. This parameter mainly caches the index of innodb table, the data, and the buffer when inserting data. The larger the setting is, the less disk I/O is needed to access the data in the table. Generally, 1 is half of the memory, no more than 2G, otherwise the system will crash. Optimize the primary parameter for Innodb acceleration. Principle of memory allocation for this parameter: this parameter is allocated by default only 8M, which is a very small 1 value. If it is a dedicated DB server, it can take up 70-80% of the memory. This parameter cannot be changed dynamically, so allocation needs to be considered. If the allocation is too large, Swap will take up too much and the query of Mysql will be extremely slow. If your data is small, the allocable value for this parameter is your data size + 10% or so.

For example, if the data size is 50M, then assign innodb_buffer_pool_size = 64M
innodb_additional_mem_pool_size=16M // for the internal directory of Innodb this value does not need to be allocated too much, the system can adjust it automatically. You don't have to set it too high. Usually 16M is enough for big data setting. If there are more tables, it can be appropriately increased. If this value is automatically increased, it will be displayed in error log.

innodb_log_file_size=256M // the size of each log file in the log group, 1 is generally 25% of innodb_buffer_pool_size, and the official recommendation is innodb_buffer_pool_size. It is better to control the sum size of several LOG files within 2G. It also depends on your transaction size, based on the data size. Note: the size of this value allocation has a lot to do with the write speed of the database, the transaction size, and the recovery after an abnormal restart.

innodb_log_files_in_group=2 // specify how many groups you have. Allocation principle: 1 generally we can use 2-3 daily value groups. The default is two.

innodb_log_buffer_size = 3M // the transaction is buffered in memory. Rule of allocation: control at 2-8M. This value does not need to be too much. Its internal memory is written to disk 1 time per second. How you write depends on how you commit your transaction. Learn about this in databases such as oracle, where a 1 Max of 3M is appropriate.

innodb_flush_logs_at_trx_commit = 0 // this parameter has only 3 values, 0, 1, 2 please confirm the acceptable level of 1. The default is 1. Please do not change the main library. Higher performance ones can be set to 0 or 2, but will lose 1 second of the transaction. Note: the setting of this parameter has a great impact on the performance of Innodb, so here is one more note. When this value is 1: innodb's transaction LOG writes to the daily value file after each commit and does a flush to disk for the daily value. This does not lose any transaction. When this value is 2: at each commit, the log buffer is written to the file, but the log file is not flushed for disk operation, and the log file is flushed once per second at a value of 2. It is important to note, however, that there is no guarantee that this will happen 100% of the time per second due to process invocation issues. So it's the fastest in terms of performance. But it takes an operating system crash or power failure to delete the last second of the transaction. When this value is 0: the log buffer is written to the log file once per second, and the log file is flushed for disk operation, but nothing is done for 1 transaction commit. The crash of the mysqld process removes the last second of the transaction before the crash.

From the above analysis, when the value is not 1, you can achieve better performance, but there will be loss in the case of exceptions, so you need to measure according to your own situation.

Related articles: