MySQL news in a spot – June 5

Simple issue that Mark Callaghan mentioned on DDL operation. I found the back porting interesting, not always a possible option, and the out-coming results.

Nothing to comment on top of what was already stated there, but mentioning it because interesting to read.

http://www.facebook.com/note.php?note_id=10150783146150933

http://www.facebook.com/note.php?note_id=10150826999790933

Why a Killed query do not disappear, after it was killed?
====================================

It happens to me, and I think to all MySQL DBAs, that a customer asks us, “WHY did that query remain in a KILL state, and does not go away?”.

As we know KILLed queries remain in MySQL until a clean up takes place, given that the action is not done synchronously.

I found this article a good description of such event, and a good reference to pass by to customers when they asks WHY, the query is still there.

http://www.dbasquare.com/2012/05/15/why-do-threads-sometimes-stay-in-killed-state-in-mysql/

MySQL Internals
============================

http://www.mysqlperformanceblog.com/2012/05/31/a-case-for-mariadbs-hash-joins/

MariaDB Hash Join, test done in Percona. I am not fully convinced about the test results, specifically I am not convinced on the indexes used.

But what is important is that we have an initial review of the feature, and then evaluate how and if it makes sense.

Another relevant post on Join optimization and comparison between MariaDB and MySQL

http://www.mysqlperformanceblog.com/2012/04/04/join-optimizations-in-mysql-5-6-and-mariadb-5-5/

“BKA improves the query time by a huge margin for IO bound workload but does not make much of a difference to in-memory workload. Also BKA relies on both the join_buffer_size and the read_rnd_buffer_size/mrr_buffer_size and both of these buffers should be increased appropriately for the best possible performance gain. This is not entirely visible in the manual either for MariaDB or MySQL, but you need to appropriately increase read_rnd_buffer_size/mrr_buffer_size because these have an impact on MRR performance, and BKA uses the MRR interface, so these buffers indirectly impact BKA performance. I did not find much of a performance improvement from using Hash Join in MariaDB 5.5 or from Key-ordered Scan for TPCH query #3, in fact disabling both of these provided the best result for MariaDB 5.5 for in-memory workload.”

MySQL Cluster
====================

Another astonishing announcement about MySQL Cluster performance, but you must read it, and be careful…

https://blogs.oracle.com/MySQL/entry/performance_testing_of_mysql_cluster

“most recently delivering 1.2 Billion UPDATE operations per Minute across a cluster of 30 x commodity Intel Xeon E5-based servers.”

Cluster is known for his high speed and the scaling in write, so I am not impress by the numbers, but I want to do a consideration.

NDBCluster is already a product that requires, quite an in depth knowledge if you want to use it correctly, it is also not the common MySQL solution, but it is “the” solution only for very special cases.

That’s it, NDBCLuster, is not usable as a simple replacement of another storage engine, it requires good analysis and review, it needs to be correctly placed and configured, and also in those cases, it is mainly associated to MySQL.

This means that MySQL works as the data interface against the applications, there are very few limited cases where NDBCLuster is directly connected to an application using C++ API.

In my many years of work with NDBCLuster I have seen only the 10% of total customer using C++.

At the same time the benchmark reporting the numbers, are normally executed against the NDBCluster directly (C++ API), not involving the MySQL layer.

Those working with NDBCLuster know very well that skipping the MySQL layer makes it faster, a lot faster, so at the end the benchmarks are true and good, but hide a small secret.

The results are not applicable to MySQL, only to NDBCluster.

The difference is significant given in MySQL you are going to use the SQL to query the cluster, while you will not do it using the C++ interface.

Also the usability and the effort of developing a good interrogation layer is quite significant, comparing it to SQL.

What I am saying is that the customer could not fully understand what those numbers means and how they got there.

So it would be nice to have comparative results, every time, MySQL -> NDBCLuster and NDBCluster + C++ API.

All this would bring better understanding and will define correct expectations from the customer side.

Said that, I think Oracle is doing a great job on MySQL NDBCLuster. Awesome!