Differences between MyISAM and InnoDB and Optimization Methods

This article briefly introduces the differences between MyISAM and InnoDB. Based on their features, we can further optimize MyISAM and InnoDB as much as possible.

Brief introduction from the official website.

Brief Introduction: MyIsam

MyISAM is the default storage engine. It is based on older ISAM code, but there are many useful extensions. (Note that MySQL 5.1 does not support ISAM ).

Each MyISAM is stored as three files on the disk. The name of the first file starts with the name of the table. The extension indicates the file type .. Frm file storage table definition. The data file extension is. MYD (MYData ). The extension of the index file is. MYI (MYIndex ).

Brief Introduction: InnoDB

InnoDB provides MySQL with a transaction security (ACID-compatible) storage engine with the capabilities of commit, rollback, and crash recovery. InnoDB locks row-level and also provides an Oracle-style non-locked read in the SELECT statement. These features increase the deployment and performance of multiple users. There is no need to expand locking in InnoDB, because row-level locking in InnoDB is suitable for very small space. InnoDB also supports foreign key forcing. In SQL queries, You can freely mix InnoDB tables with other MySQL tables, or even in the same query.

InnoDB is designed for maximum performance when processing massive data volumes. Its CPU efficiency may be unmatched by any other disk-based relational database engine.

The InnoDB Storage engine is fully integrated with the MySQL server. the InnoDB Storage engine maintains its own buffer pool to cache data and indexes in the main memory. InnoDB stores its tables and indexes in a tablespace. The tablespace can contain several files (or original disk partitions ). This is different from the MyISAM table. For example, in the MyISAM table, each table is in a separate file. InnoDB tables can be of any size, even on an operating system with a file size limited to 2 GB.

InnoDB is included in MySQL binary distribution by default. Windows Essentials installer makes InnoDB the default MySQL table on Windows.

InnoDB is used to generate large databases that require high performance. The famous Internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. Stores more than 1 TB of data on InnoDB, and some other sites process an average of 800 inserts/updates per second on InnoDB.

Now that we know the features of MyISAM and InnoDB, We can optimize them.

Key_buffer_size-this is very important for the MyISAM table. If you only use the MyISAM table, you can set it to 30-40% of the available memory. A reasonable value depends on the index size, data volume, and load.
Remember, MyISAM tables use the operating system cache to cache data, so you need to leave some memory for them. In many cases, the data is much larger than the index. However, you need to always check whether all key_buffer is used.
The. MYI file only has 1 GB, but the key_buffer is set to 4 GB. This is a waste. If you seldom use the MyISAM table, keep the key_buffer_size smaller than 16-32 MB to meet the requirements for temporary table indexes on the disk.

Innodb_buffer_pool_size-this is very important for Innodb tables. Compared with MyISAM tables, Innodb is more sensitive to buffering. MyISAM can run in the default key_buffer_size setting. However, Innodb is similar to snail bait in the default innodb_buffer_pool_size setting. Because Innodb caches data and indexes, there is no need to leave too much memory for the operating system. Therefore, if you only need Innodb, you can set it to up to 70-80% of available memory. If your data volume is small and does not increase rapidly, you do not need to set innodb_buffer_pool_size too large.

 

Innodb_additional_pool_size-this option has little impact on performance, at least on an operating system with almost enough memory to allocate. However, if you still want to set it to 20 MB (or larger), you need to check the other Innodb memory to be allocated.

Innodb_log_file_size is very important for high write loads, especially for large datasets. The larger the value, the higher the performance, but note that the recovery time may increase. I often set it to 64-512 MB, which is different from the server size. Innodb_log_buffer_size is set by default to enable server performance when the write load is moderate and the transaction is short. If there is a peak update operation or a large load, you should consider increasing the value. If its value is too high, memory may be wasted. It refreshes every second, so you do not need to set the memory space that exceeds 1 second. Usually 8-16 MB is enough. The smaller the system, the smaller its value.

Is innodb_flush_logs_at_trx_commit 1000 times slower than MyISAM than Innodb? Maybe you forgot to modify this parameter. The default value is 1, which means that each commit of the update transaction (or a statement other than each transaction) will be refreshed to the disk, which is quite resource-consuming, especially when there is no battery backup cache. Many applications, especially those transformed from MyISAM, set the value to 2, that is, do not refresh the log to the disk, instead, it is only refreshed to the operating system cache. Logs are still refreshed to the disk every second, so the consumption of 1-2 updates per second is usually not lost. If it is set to 0, it will be much faster, but it is relatively insecure. Some transactions are lost when the MySQL server crashes. Set to 2 to direct the part of the transaction that is lost and refreshed to the operating system cache.

Table_cache-the overhead of opening a table may be high. For example, MyISAM marks the MYI file header that the table is in use. You certainly do not want this operation to be too frequent. Therefore, you usually need to increase the number of caches so that the opened tables can be cached to the maximum extent. It requires the resources and memory of the operating system, which is of course not a problem for the current hardware configuration. If you have more than 200 tables, it may be appropriate to set it to 1024 (each thread needs to open the Table). If the number of connections is large, it will increase its value. I have seen a 100,000 error.

Thread_cache-the overhead of thread creation and destruction may be large because connection/disconnection of each thread is required. I usually set at least 16. If the application has a large number of skip concurrent connections and the value of Threads_Created is large, I will increase the value. It does not need to create a new thread in common operations.

Query_cache-this is useful if your application has a large number of reads without application-level caches. Don't set it too large, because it also requires a lot of overhead to maintain it, which causes MySQL to slow down. Usually set to 32-512 Mb. After setting, it is best to track for a period of time to check whether the operation is good. If the cache hit rate is too low under a certain load, enable it.