« Optimizing MySQL Performance Using Direct Access to Storage Engines (faster timelines) | Main | Q4M 0.7 released »

Performance of MySQL UDFs vs. Native Functions

Hearing from Brian that UDFs might be slower than native functions in MySQL, I did a small benchmark.

mysql> select benchmark(1e9,abs(1));
+-----------------------+
| benchmark(1e9,abs(1)) |
+-----------------------+
|                     0 | 
+-----------------------+
1 row in set (27.15 sec)

mysql> select benchmark(1e9,udf_abs(1));
+---------------------------+
| benchmark(1e9,udf_abs(1)) |
+---------------------------+
|                         0 | 
+---------------------------+
1 row in set (43.04 sec)

The numbers were taken on my MacBook (Core 2 Duo @ 2GHz, OS X 10.4.11) running the official binary version of MySQL 5.1.25-rc for 32-bit arch. So the overhead of UDFs compared to native functions seems to be about 30 clocks per each call.

So the question is whether it would matter on an actual application. I created a 100k row heap table and performed sequential scan. For each row, either abs() or udf_abs() is called.

$ mysqlslap -S /tmp/mysql-dev.sock -u root -i 1000 -q 'select abs(v) as v1 from test.heap_t having v1=-1'
Benchmark
        Average number of seconds to run all queries: 0.014 seconds
        Minimum number of seconds to run all queries: 0.014 seconds
        Maximum number of seconds to run all queries: 0.018 seconds
        Number of clients running queries: 1
        Average number of queries per client: 1

$ mysqlslap -S /tmp/mysql-dev.sock -u root -i 1000 -q 'select udf_abs(v) as v1 from test.heap_t having v1=-1'
Benchmark
        Average number of seconds to run all queries: 0.015 seconds
        Minimum number of seconds to run all queries: 0.015 seconds
        Maximum number of seconds to run all queries: 0.019 seconds
        Number of clients running queries: 1
        Average number of queries per client: 1

There does seem to be some difference, and it might be worth remembering. But IMHO, in general, it would not be a problem since most UDFs perform much more complex operation than a simple abs calculation, and that accessing a single row in most cases would be much heavier than reading a four-byte fixed width heap table.

After I go to my office, I would like to take the same benchmark on a linux server running 64-bit version of MySQL.

Below are the benchmarks on Opteron 2218 running CentOS 5.1 (x86_64). The overhead of calling UDF exists, about 10 clocks per each call. However, when running a sequential scan, the UDF version performed faster than the native version. I am not sure why such a thing happens (I tried multiple times and got the same result), but it might be due to the memory access patterns and behaviour of prefetchers within the CPU.

mysql> select benchmark(1e9,abs(1));
+-----------------------+
| benchmark(1e9,abs(1)) |
+-----------------------+
|                     0 | 
+-----------------------+
1 row in set (20.69 sec)

mysql> select benchmark(1e9,udf_abs(1));
+---------------------------+
| benchmark(1e9,udf_abs(1)) |
+---------------------------+
|                         0 | 
+---------------------------+
1 row in set (30.65 sec)
$ /usr/local/mysql51/bin/mysqlslap -S /tmp/mysql51.sock -u root -i 1000 -q 'select abs(v) as v1 from test.heap_t having v1=-1'
Benchmark
        Average number of seconds to run all queries: 0.014 seconds
        Minimum number of seconds to run all queries: 0.011 seconds
        Maximum number of seconds to run all queries: 0.018 seconds
        Number of clients running queries: 1
        Average number of queries per client: 1

$ /usr/local/mysql51/bin/mysqlslap -S /tmp/mysql51.sock -u root -i 1000 -q 'select udf_abs(v) as v1 from test.heap_t having v1=-1'
Benchmark
        Average number of seconds to run all queries: 0.011 seconds
        Minimum number of seconds to run all queries: 0.011 seconds
        Maximum number of seconds to run all queries: 0.013 seconds
        Number of clients running queries: 1
        Average number of queries per client: 1

Attached: source code of the udf_abs function.

#include 
#include 
#include 

extern my_bool udf_abs_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
  if (args->arg_count != 1) {
    strcpy(message, "usage: udf_abs(int)");
    return 1;
  }
  args->arg_type[0] = INT_RESULT;
  args->maybe_null[0] = 0;
  initid->maybe_null = 0;
  return 0;
}

extern void udf_abs_deinit(UDF_INIT *initid)
{
}

extern long long udf_abs(UDF_INIT *initid, UDF_ARGS *args, char *is_null,
			 char *error)
{
  long long v = *(long long*)args->args[0];
  return v >= 0 ? v : -v;
}

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)