In my previous article, Comparisons of Proxies for MySQL, I showed how MySQL Router was the lesser performing Proxy in the comparison. From that time to now, we had several MySQL releases and, of course, also some new MySQL Router ones.

Most importantly, we also had MySQL Router going back to being a level 7 proxy capable of redirecting traffic in case of R/W operations (see this).

All these bring me hope that we will also have some good improvements in what is a basic functionality in a router: routing.

So with these great expectations, I had to repeat the exact same tests I did in my previous tests, plus I tested for MySQL Router only the cost of encapsulating the select inside a transaction.

Just keep in mind that for all the tests, MySQL Router was configured to use the read/write split option.

The results

Given this is the continuation of the previous blog, all the explanations about the tests and commands used are in the first article. If you did not read that, do it now, or it will be difficult for you to follow what is explained later.

As indicated, I was looking to identify when the first proxy would reach a dimension that would not be manageable. The load is all in creating and serving the connections, while the number of operations is capped at 100.

As you can see, MySQL Router was reaching the saturation level and was unable to serve traffic at exactly the same time as the previous test.

Test two

When the going gets tough, the tough get going reprise ;) 

Let’s remove the –rate limitation and see what will happen. First, let us compare MySQL router versions only:

As we can see the MySQL Router version 8.2 is doing better up to 64 concurrent threads.

Latency follows the same trend in old and new cases, and we can see that the new version is acting better, up to 1024 threads.

Is this enough to cover the gap with the other proxies? Which, in the end, is what we would like to see. 

Well, I would say not really; we see a bit of better performance with low concurrent threads, but still not scaling and definitely lower than the other two.

Now let us take a look at the CPU saturation:

Here, we can see how MySQL Router hits the top as soon as the rate option is lifted and gets worse with the increase of the running threads.

Test three

This simple test was meant to identify the cost of a transaction, or better, what it will cost to include selects inside a transaction.

As we can clearly see, MySQL Router, when handling selects inside a transaction, will drop its performance drastically going back to version 8.0 performance.

Conclusions

To the initial question — Is MySQL Router 8.2 any better? — we can answer a small (very small) yes.

However, it is still far, far away from being competitive with ProxySQL (same proxy level) or with HAProxy. The fact it is not able to serve efficiently the requests also inside the lower set of concurrent threads, is disappointing.

Even more disappointing because MySQL Router is presented as a critical component in the MySQL InnoDB Cluster solution. How can we use it in the architectures if the product has such limitations?

I know that Oracle suggests to scale out, and I agree with them. When in need to scale with MySQL Router, the only option is to build a forest. However, we must keep in mind that each MySQL Router connects and queries the data nodes constantly and intensively. Given that it requires adding a forest of router nodes to scale, it is not without performance impact, given the increasing noise generated on the data nodes.

Anyhow also, if there is a theoretical option to scale, that is not a good reason to use a poor performing component.

I would prefer to use ProxySQL with Group Replication and add whatever script is needed in mysqlshell to manage it as Oracle is doing for the MySQL InnoDB cluster solution.

What also left me very unhappy is that MySQL InnoDB Cluster is one of the important components of the OCI offer for MySQL. Is Oracle using MySQL Router there as well? I assume so. Can we trust it? I am not feeling like I can.

Finally, what has been done for MySQL Router so far leads me to think that there is no real interest in making it the more robust and performing product that MySQL InnoDB Cluster deserves.

I hope I am wrong and that we will soon see a fully refactored version of MySQL Router. I really hope Oracle will prove me wrong.

Great MySQL to everyone.