Stripping Digits – The benefits of Benchmarking and Profiling

Carsten Pedersen (Certification Manager of at MySQL AB) read my entry about stripping digits, and has done a good write up here on how this function could be improved, a great deal.

Have a read of Carstens link, and my previous entry if you didn’t catch it, before we progress.

As you see, I mentioned that “it performed fairly well”, but I admit given the nature of the request (a quick IRC question) I didn’t run a benchmark on it compared to something like Carsten’s REPLACE solution. I actually went with the REGEXP as the original question was actually something along the lines of “If I have a string such as “joe123” how I can I strip the digits from the end to return just “joe”. I wrote the quick function as an example of what you can do in 5.0, as the user was still 4.x anyway, really the answer would have been a little more along the lines of using REVERSE on the string and walking along the digits until you hit the first alpha (or [^0-9]), and then doing a substring up until that location.

However, Carsten was spot on when he picked up on this! This only goes to reinforce the fact that things should not be assumed on first glance, and should be benchmarked and tested rigorously. Carsten provides a great example of how to run a quick benchmark of function calls with the BENCHMARK function, which is the first logical step when testing any new stored functions that you may create. Thanks Carsten for the write up!

To take this one step further – I also read a great write up on “Benchmarking and Profiling” from “Pro MySQL” written by Mike Kruckenberg and Jay Pipes, which has generously been made available on the MySQL AB website here.

This gives some great information on using readily available open source benchmarking tools that allow would allow you to benchmark new functions within a number of different situations, with different data sets etc. It also gives some good information on profiling and diagnostics techniques, all of which is fantastic information.

This seems as good a time as any to introduce what I do for a living. I work for a company that sells and supports database management tools, primarily in the Oracle and SQLServer market place. If you read the section on profiling in the chapter above, a lot of it is exactly the kind of thing that we are putting together at the moment at Cool-Tools for the monitoring tool that we currently distribute – NORAD Surveillance. We’ve seen a huge upsurge of MySQL being picked up in the enterprise space, and we would quite frankly be foolish to sit back and ignore this. So we’re some way down the line of an early beta version of a plugin that Cool-Tools will provide that will monitor MySQL, as well as the underlying OS.

Here’s a quick sneak pic of the “Global Health Window“.. Although it’s a little boring on my little work desktop machine with no activity (that could really do with upgrading!), and still needs a few more variables to fill it out – it’s good enough to give an idea of what we are trying to do.. I ran a couple of the sys-bench scripts (test-insert and test-select) from a remote machine (the host) to give it at least a little bit to look at..

I’ll post a full overview as and when – if there’s anybody out there that would like to try a beta copy of it as well, when it’s ready, feel free to fire me an email – mark at cool hyphen tools dot co dot uk – and I’ll see about getting it out to a few of you. We won’t consider people sending from email addresses such as hotmail 😉

5 thoughts on “Stripping Digits – The benefits of Benchmarking and Profiling”

  1. Mark

    You are not getting a new PC yet or until the MYSQL community or users tell and pressure me enough! Good posting, though!

    Managing Director
    Cool-Tools UK Limited

  2. Hey Tim!

    I turned in to a MySQL champion instead! 🙂

    I’m now working for MySQL AB as a Support Engineer, looking after supported customers and working on some tools (nothing changes there eh? ;)). Still working with Oracle when I get the chance though – customer questions on compatibility, inter-communnication etc.

    How are you doing? Still over at SpeechMachines?



  3. Mark – did not realise this was for chatting on hence I have not replied for over a month
    (sort of expected to get an email in response).

    Yes, still here. Saw Gerry the other day and he has copied me your MySQL for Oracle DBAs – good stuff.

    Always on the look out for good DBAs (sorry great DBAs) so if you ever wanted a change (and Gerry does not snap you back up) give us a call (and if you can recommend anybody let me know).


Comments are closed.