MySQL Cluster Performance Tuning
In this session, we will look at different tuning aspects of MySQL Cluster. As well as going through performance-tuning basics in MySQL Cluster. We will look closely at the new parameters and status variables of MySQL Cluster 7.2 to determine issues with e.g disk data performance and query (join) performance.
This was the last session I attended, and it’s always a great pleasure to see Johan Andersson’s presentations. Two of the many reasons are:
- He is probably the best cluster expert (in service delivery).
- He knows a lot of distinctiveness and insight that no one else knows.
MySQL/Oracle has released a new version of MySQL cluster recently, and I had the opportunity to test a little bit, but not really in depth as I would like. But one of the aspect that I was looking for was the way the “condition pushdown” was modified.
Johan confirmed how it is now working by tests (empiric is always more trustable then commercial announcements). NDBcluster returns the full result set and no sub set to MySQL and additional requests. Therefore, we have less roundtrip, and the relations by ID are resolved at the local data node level.
What is interesting is that it’s also possible to partition data by Key, associating that on the Data node. It would increase the locality of reference as well, if not implementing a real partition pruning.
The only negative note is that the annoying problem with the MySQL SQL node/connection pool is still there. It is still taking 1 slot out of the 253 available for each connection in the pool. This means that if I have 4 MySQL SQL nodes, each allocating 10 connection in the pool, I will take out 40 connections from the 253 instead of 4.
This cannot be a problem, but given that I have done several MySQL cluster implementations, I can say that when MySQL is really used it needs to heave a huge number of Data nodes and MySQL nodes, resulting in a very high number of connections slot used.