InnoDB Insert (insert) operation (bottom)--mysql Technology Insider

After an article, the final experiment, I was trying to prove the MySQL InnoDB storage engine, the relationship between the commit operation and the flush data to disk, and when communicating with a colleague, he said, you should take the size of innodb_buffer_size into account, In fact, I have to consider, in the beginning part has made assumptions, if the buffer size is 1G, the data size is 2G, then in the transaction insert operation, it is bound to be in the commit operation, the cache data will be written to disk.

In addition, after the experiment, I carried out two groups, one group is carried out rollback operation, the other group is a commit, found that the rollback operation time is much greater than the commit operation. Whether the illusion is that the data has been dropped, but on the data page, there is a transaction identifier, the implementation of the transaction isolation mechanism, for the other session is not visible. And at this time rollback operation will be a piece of data from the disk to clear, this speed will certainly be relatively slow, of course, this removal mechanism, I am still not very clear, is to go back to the buffer, or directly disappear; and, after the rollback operation, The size of the data file does not change, whether it can be false, the physical space has been allocated, and did not recover in time for the next reuse. The commit operation, which is done in a very short time, is possible because the operation simply removes the transaction ID information from the data page on the disk and is OK. Well, take a rigorous experimental attitude, and then do an experiment. (I'll do it when I get back from work)

Innodb_buffer_size=, ensure that you have enough buffers for the newly inserted data.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

InnoDB Insert (insert) operation (bottom)--mysql Technology Insider