Cache Optimization Design of MyISAM engine and InnoDB Engine in MySQL

In the MyISAM engine, index data frequently read from disks is loaded into memory to improve io efficiency and read efficiency.

MyISAM designs an index buffer pool Key Cache in the memory. Key Cache only caches index data and uses the LRU algorithm to load frequently read indexes to the Key Cache.

Using System Variableskey_buffer_sizeTo control the size of the Key Cache. This variable is related to the Cache performance.

 

The buffer pool of the cache pool is also set in the InnoDB engine. What is different from MyISAM is that the buffer pool not only caches index data, but also caches table data.

This buffer pool also brings about many problems: how to keep the cached data consistent with the data on the disk, and the data support in the cache does not support modification and update operations, and synchronization with the logging module.

It is complicated to solve these problems, but it is also worthwhile to improve the overall performance: the hard disk access speed and memory speed are not an order of magnitude, reading data through memory can greatly improve the overall performance of the database.

In addition to the memory used for system running, we recommend that you set the buffer pool as large as possible for the remaining memory. In this way, there is a large buffer pool, and the performance in practical applications is more like an in-memory database, compared to the read/write speed on the disk, the read/write performance is simply a huge improvement.

All of this is of course based on the read data hit rate in the buffer pool. Modifying updates and other operations will change some data in the buffer pool and update it through the LRU algorithm, maintain the buffer pool hit rate to a relatively high level.

 

Another problem is how to synchronize data in the buffer pool to the disk. Think about the efficiency and direct read/write of a disk if a buffer pool is updated and the disk is written once. here we need to design a synchronization policy to solve this problem.

InnoDB is transaction-safe. After modifying the data in the buffer pool, you must also record this operation in the transaction log. After modifying the buffer pool, the data is not directly synchronized to the disk. Instead, the operation is recorded in the transaction log file. Here is another question: why do I not write data to the table data file on the disk, but to the transaction log file on the disk? What is the difference between disk write operations?

This involves disk searching and reading and writing. I have learned the computer composition principle. Disk reading and writing can be divided into two types: sequential reading and writing and random reading and writing. If it is random reading and writing, it takes a certain amount of time for head addressing. If sequential read/write is performed, data is continuously written to the magnetic surface, with few head addressing operations. The efficiency of the two read/write modes varies greatly.

The transaction log file is a fixed size file requested by the InnoDB Engine for continuous physical space. The read/write operations on log files are basically sequential read/write, with few addressing operations.

The table data in the buffer pool is complex: the data files in multiple tables are different in the disk storage space and random. If the data in the buffer pool is updated to the disk each time, the tablespace in each operation is random, and the read/write operations on the disk are also random. Therefore, frequent addressing read/write operations will put the disk in a busy random read/write state.

Therefore, the buffer pool policy also improves the overall io performance.

 

Please indicate the source of reprint: http://www.cnblogs.com/iamsupercp/, thank you for your cooperation