Percona server UDF fast hashing with fnv1a_64

Written by Peter Davies on .

Percona Server is distributed with several useful UDF (User Defined Function) from Percona Toolkit. Three of the Percona Toolkit UDFs that provide faster checksums are:

  • fnv_udf
  • fnv1a_udf
  • murmur_udf

Initially you will need the Percona Toolkit installing: apt-get install percona-toolkit

You can then choose to run one of the following commands to create these functions:

mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

Based on some *loose* research we chose the fnv1a_64 function so make sure the ".so" exists like so:

[email protected]:~# locate fnv1a_udf.so
/usr/lib/mysql/plugin/libfnv1a_udf.so
/usr/lib/mysql/plugin/libfnv1a_udf.so.0
/usr/lib/mysql/plugin/libfnv1a_udf.so.0.0.0

Then run the command:

mysql -u root -ppass -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'fnv1a_udf.so'"

This will show the file does not exist in that name so update the command to match the file name:

mysql -u root -ppass -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"

See notes here for more details:

  • Percona Toolkit - http://www.percona.com/doc/percona-server/5.5/management/udf_percona_toolkit.html
  • FNV1a64 - http://www.getopt.org/utils/build/api/org/getopt/util/hash/FNV1a64.html
  • MurmurHash - http://en.wikipedia.org/wiki/MurmurHash