MyISAM and InnoDB Difference and optimization method

Brief introduction from the official website.

Brief introduction: MyIsam

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

Each myisam is stored on disk as three files. The first file begins with the name of the table, and the extension indicates the file type.. frm file storage table definition. The data file has an extension of. MyD (MYData). The name of the index file extension is. Myi (Myindex).

Brief introduction: InnoDB

INNODB provides MySQL with transaction security (acid-compatible) storage engine with Commit, rollback, and crash recovery capabilities. InnoDB locks the row level and also provides an oracle-consistent, unlocked read in the SELECT statement. These features add to multi-user deployment and performance. There is no need to enlarge the lock in InnoDB because the InnoDB row-level locking fits very small spaces. InnoDB also supports foreign key coercion. In SQL queries, you can freely mix innodb types of tables with other types of MySQL tables, even in the same query.

InnoDB is the maximum performance design for processing large amounts of data. Its CPU efficiency may be unmatched by any other disk-based relational database engine.

The InnoDB storage engine is fully consolidated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its table & index in a tablespace, a tablespace can contain several files (or a raw disk partition). This is different from the MyISAM table, for example in the MyISAM table where each table is separated from the file. The InnoDB table can be any size, even on an operating system that has a file size limited to 2GB.

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

InnoDB is used in many large database sites that require high performance. The famous Internet news site slashdot.org runs on the InnoDB. Mytrix, Inc. stores more than 1TB of data on InnoDB, and some other sites handle an average of 800 inserts/updates per second on the InnoDB.

Now that we know the features of MyISAM and InnoDB, now we're going to optimize them.

key_buffer_size– This is very important for the MyISAM table. If you are using only the MyISAM table, you can set it to the 30-40% of available memory. A reasonable value depends on the size of the index, the amount of data, and the load.
Remember, the MyISAM table uses the operating system's cache to cache the data, so you need to set aside some memory for them, and in many cases the data peso is much more. Still, it is necessary to always check whether all the key_buffer are being exploited.
. Myi files are only 1GB, and Key_buffer is set to 4GB is very rare. It's too wasteful to do so. If you rarely use MyISAM tables, then keep key_buffer_size below 16-32MB to accommodate the temporary table indexes that are given to the disk.

innodb_buffer_pool_size– This is very important for the InnoDB table. InnoDB is more sensitive to buffering than MyISAM tables. MyISAM can be run under the default Key_buffer_size settings, however InnoDB is like a snail in the default innodb_buffer_pool_size settings. Because InnoDB caches data and indexes without leaving the operating system with too much memory, you can set it up to 70-80% usable memory if you only need to use InnoDB. If you have a small amount of data and will not surge, then you don't have to set the innodb_buffer_pool_size too large.

innodb_additional_pool_size– This option does not have much impact on performance, at least on an operating system with almost enough memory to allocate. But if you still want to set it to 20MB (or more), you need to look at the amount of memory other InnoDB need to allocate.

Innodb_log_file_size is important in the case of high write loads, especially large data sets. The larger the value, the higher the performance, but note that recovery time may be increased. I often set it to 64-512MB, which varies according to the size of the server. Innodb_log_buffer_size default settings for medium-intensity write load and shorter transactions, server performance is also available. If there is a peak update operation or a large load, you should consider increasing its value. If its value is set too high, memory may be wasted. It refreshes every second, so there is no need to set the required memory space for more than 1 seconds. Usually the 8-16MB is enough. The smaller the system, the smaller its value.

Innodb_flush_logs_at_trx_commit is InnoDB 1000 times times slower than MyISAM and head is big? It seems that you have forgotten to revise this parameter. The default value is 1, which means that each submitted update transaction (or statements outside of each transaction) is flushed to disk, which is quite resource-intensive, especially when there is no battery-standby cache. Many applications, especially those from MyISAM, set the value to 2, which means that the log is not flushed to disk, but only to the operating system's cache. Logs are still flushed to disk per second, so there is usually no loss of 1-2 updates per second. If set to 0 is much faster, but also relatively insecure. Some transactions are lost when the MySQL server crashes. Set to 2 command to lose that part of the transaction that is flushed to the operating system cache.

table_cache– the cost of opening a table can be significant. For example MyISAM the Myi file header flag that the table is in use. You certainly don't want this to be too frequent, so it's usually a lot more cache, so it's enough to cache open tables. It needs the operating system's resources and memory, which is certainly not a problem for the current hardware configuration. If you have more than 200 tables, it might be appropriate to set it to 1024 (each thread needs to open the table), and if the number of connections is larger then increase its value. I have seen the case set to 100,000.

The cost of creating and destroying thread_cache– threads can be significant because each thread needs to be connected/disconnected. I'm usually at least set to 16. If there is a large number of jumps and concurrent connections in the application and the value of threads_created is larger, then I will increase its value. It is designed to not create a new thread in the usual operation.

Query_cache– This is useful if your application has a large number of reads, and there is no application-level caching. Don't set it too large, because it costs a lot to maintain it, which can cause MySQL to slow down. usually set to 32-512MB. After setting up, it's best to track for a while to see if it's working well. Under certain load pressure, if the cache hit rate is too low, enable it.