Book: "Pentaho Kettle Solutions", Matt Casters, Roland Bouman, & Jos van Dongen, Wiley 2010 Book: "Pentaho Solutions", Roland Bouman & Jos van Dongen, Wiley 2009

Tuesday, July 29, 2008

MySQL aggregate UDF Performance

At the MySQL User's conference 2008 I did a tutorial on MySQL User-defined functions. At the same conference, Brian Miezejewski and Mark Atwood did a talk on aggregate UDFs.

In relation their upcoming talk, Mark wrote a blog post reporting that MySQL Aggregate UDFs are three times slower than equivalent native functions:
I just proved to myself via testing and benchmarking that user defined aggregate functions are about three times slower than the call interface to the built-in functions.
Later on he posted an update, explaining that it looks like this result is probably "a MacOS thing".
It turns out to be a MacOS thing. On Linux, the performance penalty is less than 10%. Further research will surely be done.

I just tested it on Linux and Windows. For a simple aggregate UDF that is equivalent to the built-in COUNT() function, I found that the UDF is about 5% slower than the built-in function. Personally I think that's not too bad.

If you are interested in trying this on your own system, download these instructions. Would be cool if you could leave a comment here of your results, TIA


Sergei Golubchik said...

Brian Miezejewski also found a similar slowdown for non-aggregate UDF's. On his MacOS X laptop they were much slower (as compared to a query plan without UDF) than on my Linux laptop.

Roland Bouman said...

Hi Sergei,

thanks for this info!

What do you think might be causing the difference?