Senior Expert Xaprb comment on MySQL 5.5

Recently, I have rarely discussed MySQL functional changes in my blog posts. However, MySQL and InnoDB engineers have been doing a lot of work in recent years. Now, with the release of MySQL 5.5, their work results can be presented to people, therefore, it is necessary for us to congratulate and be grateful to them, and to share our views on this version.

At the recent MySQL conference, I was very excited to wait for MySQL 5.5 to arrive, but it turns out that I have overestimated it. Of course, there are many things worth talking about in MySQL 5.5, And the InnoDB and MySQL teams have published many related blog articles. My personal opinions on MySQL 5.5 are as follows.

Select InnoDB as the default storage engine

When I first saw this, I didn't care too much about it. Senior users can achieve this in various ways. But MySQL expert Morgan Tocker said this is actually a major change. It will lead to great changes in MySQL usage. In the past, users used the MyISAM storage engine initially, and then learned to turn to the more powerful InnoDB for data management. Now they have used more advanced and complex storage engines from the very beginning. We will see that more people begin to learn about InnoDB, while those who know the MyISAM engine will be much less. People are no longer talking about "why should I switch from MyISAM to InnoDB ?" But "I heard there is another MyISAM engine. Under what circumstances should I try it out ?"

This is a wise move for MySQL.

Complete InnoDB

This is a complex topic. Some changes are very good. Some changes seem like improvements to the XtraDB function. Of course, XtraDB also has an excellent storage engine.

In my previous articles on XtraDB, I mentioned improvements to the restoration time, independent cleanup threads, and support for changes to multiple rollback regions. These concepts have been proven by XtraDB users in the real world, and I hope InnoDB will do a lot of work to implement these concepts. The restoration function of InnoDB looks very good, although it is not clear whether it is faster than the corresponding function of XtraDB. Through these improvements, InnoDB has made a huge leap.

I am not very excited about the multiple buffer pool (pools) function. This feature is helpless, because there is no best solution to this problem. The buffer pool itself is already very difficult to manage (the size cannot be changed during running and cannot be indexed) ". The new MySQL database does not seem to have much improvement in this regard. As for the so-called true enhancement of the internal architecture, just like a zero-sum game, it only improves performance. I think this is not a future-proof game). I think this solution will change in the future. Unless other problems of the buffer pool are solved, any enhancement to the buffer pool in the future may lead to problems such as storage fragments. Of course, it is not helpful to criticize without making any constructive comments, but I know that all my suggestions lack sufficient evidence.

It is a very complicated task to strip the mutex lock. I have no idea about this currently. The benchmark is good, but there are often unexpected things in the real world. Therefore, let's take a look at the real performance changes. I know that InnoDB has done a lot of work in this area, but I don't think Percona needs to imitate InnoDB. However, the former can still look at the performance of the latter and learn from it to make better improvements.

I am very worried about synchronizing I/o; I/O is not easy to control, which is a complicated change. It is another difficult or even incomprehensible thing.

I suspect that the buffer deletion function may have completely deviated from the track and it adopts the same method as inserting the buffer. The buffer mechanism is poorly controlled during write buffering. The buffer size cannot be controlled and cannot be detached from the backend (XtraDB can achieve this ). However, if InnoDB solves this defect, it is not surprising. I don't think it is difficult to implement it. The insert buffer operation is sometimes very strange and lacks necessary control, which will cause performance problems. From this point, even the latest InnoDB version is different from XtraDB.