Mysql innodb Learning (1)

Mysql innodb Learning (1) the two most common storage engines in mysql are Myisam and innodb. There are many differences and comparisons between the two engines on the Internet, I will not explain it here. Here I will focus on the role of each innodb parameter and its impact on performance. The innodbbufferpoolsize and innodblogfilesize parameters have the most significant impact on performance. The parameter www.2cto.com innodbbufferpool_size plays a decisive role in innodb performance. The buffer size determines the performance of innodb. The buffer not only caches innodb indexes, but also stores data, adaptive hash indexes, insert cache, locks, and other internal structures. How can I set the size of this value? How big is the proper value? The advice in the mysql configuration file is to set it to 50%-80% of the server memory, but on a 32-bit machine, the mysql manual has a warning: www.2cto.com is on 32-bit GNU/Linux x86, so be careful not to set high memory usage. Glibc may allow processes to accumulate on the thread stack, which may cause your server to crash. If the value of the following expression is close to or greater than 2 GB, the system will face a crisis: innodbbufferpool_size + keybuffersize (buffer used by myisam) + maxconnections * (sortbuffersize + readbuffersize + binlogcachesize) + maxconnections * 2 MB innodb uses this buffer zone to delay write. Therefore, you can merge multiple writes and write them to the disk in sequence. In this way, the original random writes can be improved to sequential writes, improve data writing efficiency. The innodb write operation is first written to the buffer pool, and then the operation log is recorded to log file (the configuration parameter of the log file is innodblogfile_size, then return the result to the client, which improves the response speed of innodb (the operation in memory is faster than the operation on the disk without waiting to be written to the disk ), the modified record in the buffer pool is "dirty data (dirty page) dirty page ". Then the question comes: How does innodb write the "dirty pages" in the buffer pool to the disk? How many dirty pages can be stored in the buffer zone of innodb? For the first problem, innodb uses a background thread to refresh (flush) data to the disk (multiple write operations can be combined and executed, and random writes can be changed to sequential writes to improve efficiency) for the second problem, innodb uses the innodbmaxdirtypagespct parameter to control the maximum proportion of dirty page in the buffer pool. When this value is reached, innodb will use flush thread to write data to the disk (how to write it will be discussed later). When this value is not reached, the flush thread of innodb does not have enough space in the buffer pool to execute the flush operation when writing new data. This behavior is called "lazy )", innodb can delay the flush operation. Delayed execution does not mean that execution will not be executed, because if there is a large number of concurrent writes on the server, there will be a lot of dirty pages in the buffer pool, it will easily reach innodbmaxdirtypagespct, so flush thread will try to refresh the dirty page in the buffer pool to the disk as soon as possible to reduce the proportion of dirty pages in the buffer. Although the buffer pool has a great impact on the performance of innodb, the larger the buffer pool, the longer it takes to shut down and start the mysql service. For example, there are many "Dirty pages" in the buffer, and innodb takes a relatively long time to refresh the dirty pages to the disk, only after all the dirty pages are refreshed, mysql is disabled. Of course, you can also use Quick Close, but it takes a relatively long time for mysql to be restored during startup, so for the whole process of closing and starting, the large buffer zone is still relatively long for the entire process. We can dynamically adjust the innodbmaxdirtypagespct value during mysql running. The dynamic setting is relatively small. It can be that the flush thread refreshes the dirty page to the disk as soon as possible, and then closes the page, in this way, the shutdown time can be shortened in one aspect. You can run the show innodb status Command to view innodbbufferpoolpagesdirty data on the current dirty page. If we have a large buffer zone and a slow disk, it will take a relatively long time to warm up when the server is restarted. This will also affect the overall performance of mysql.