Innodb_buffer_pool_pages_dirty [a story @mysql Dba]mysql

http://www.orczhou.com/index.php/2010/12/more-about-mysql-innodb-shutdown/
http://www.orczhou.com/index.php/2014/03/some-tricky-about-mysqladmin-extended-status/
https://dbarobin.com/2015/08/29/mysql-optimization-under-ssd/
https://www.percona.com/blog/2016/05/05/percona-server-5-7-multi-threaded-lru-flushing/
Https://yq.aliyun.com/articles/40903?spm=5176.8091938.0.0.b6sdPr
Https://yq.aliyun.com/groups/25
http://mysql.taobao.org/index.php?title=MySQL%E5%86%85%E6%A0%B8%E6%9C%88%E6%8A%A5_2015.02

Quick Close mysql/InnoDBif the engine is InnoDB, every time you knock the Mysqladmin-uroot-p shutdown When the database is closed, it is always difficult to predict how long this command will take, and the actual experience shows that the short is five seconds and the length is 30 minutes an hour is possible. Share my experience, too. 1Why is InnoDB shutdown slow? In fact, not every time you close the InnoDB is slow. Why? An important feature of InnoDB compared to MyISAM is that InnoDB will open a buffer pool in memory to store the most recently accessed chunks/index block so that the next time you access the block again, the speed will be fast. When the InnoDB needs to modify the data block, it will record the modification log and then direct the operation of the data block in the Buffer_pool. Logging is sequential, and operations on chunks of data are memory operations, which gives InnoDB a good speed advantage in many scenarios. After the memory block modification is complete, the InnoDB is returned to the client. But then the actual disk data block, and has not been updated, we call such a page dirty page. In the background of InnoDB there is a dedicated thread to do the work of flush the memory data blocks to the disk. This flush operation is the main factor that affects the InnoDB shutdown time. After you close MySQL/InnoDB, all dirty_page need to flush, so the more dirty_page, the more data blocks to flush, which means the longer the InnoDB is closed. We can observe the number of dirty page by the following command: Mysqladmin-uroot Ext-i1|grep"Innodb_buffer_pool_pages_dirty"2The number of dirty_page in the parameter Innodb_max_dirty_pages_pctbuffer_pool directly affects the closing time of the InnoDB. The parameter innodb_max_dirty_pages_pct can directly control the ratio of dirty_page in Buffer_pool, and fortunately innodb_max_dirty_pages_pct can be changed dynamically. Therefore, the innodb_max_dirty_pages_pct is reduced before closing the InnoDB, forcing the data block to flush for a period of time, which can greatly shorten the time of MySQL shutdown. Set Globalinnodb_max_dirty_pages_pct=0generally, after the above command is executed, the Dirty_page flush still takes a while, so wait a little while and then close MySQL for the effect. 3what to do before shutting down sometimes, even if you change innodb_max_dirty_pages_pct=0, the InnoDB is still not guaranteed to close quickly. There are a few things to note. Set the database to read-only: If the database is always active and there is a connection to write data to it, then the dirty page may continue to be generated. In the case of a standby, it is a good idea to stop slave at the same time that innodb_max_dirty_pages_pct is set to 0: This is critical and will have a significant impact on the closing time. First, after the active stop slave, when MySQL shuts down, the thread that needs to stop is actually less. Second, if slave's SQL thread is still executing, Buffer pool is still active, and Dirty page is likely to continue to grow. Generally, if you notice the above three points:Set Globalinnodb_max_dirty_pages_pct=0Set Globalread_only=1Stop slave shut down the database and it will be fast. If you do the above three steps, and then observe the number of dirty page, when the number is very small, then execute the command close the library, which will always guarantee a faster speed to complete the closing of the Library command. 4. A note it is important to note that you do not have to wait for the number of dirty page to zero before you start to turn off MySQL. Because sometimes, even if there are no active sessions, the merge of the InnoDB insert buffer will still produce some dirty page, so you may find that you wait a long, long time dirty The number of page is still greater than 0. In fact, you can close the database quickly. How can I judge this situation? At this point we can judge by InnoDB LSN, below is the information obtained in show InnoDB status: Log sequence number814 3121743145Log flushed up to814 3121092043Last checkpoint at814 2826361389As you can see here, the current LSN is 8143121743145, the last checkpoint is at 814.2826361389, which means the difference between the two is 3121743145-2826361389=295381756, it means that InnoDB still has a lot of dirty page to flush. The following is the LSN information for another library: Log sequence number0 1519256161Log flushed up to0 1519256161Last checkpoint at0 1519256161As you can see, the dirty page here has already been flush, so closing the InnoDB is fast. Generally, it is not necessary to wait until the last checkpoint and the current LSN are equal before closing, as long as there is not much difference (< +shut up quickly. 5. Finally, I'm going to be a little bit more verbose, you crossing. In fact, like the above toss, the whole Guanqu process may not be directly executed than you mysqladmin-uroot shutdown fast, but performing the above steps will give you a clear idea of how long Guanqu is going to take to get your Guanqu command to complete in a predictable time period. Simply put, will let the Guanqu time Heart has a bottom. When you want everything to be in your heart, you need to pay attention to some of the details mentioned above. 

Innodb_buffer_pool_pages_dirty [a story @mysql Dba]mysql