Archive for the 'C' Category

A very fast FNV hash function for MySQL

Published at March 9, 2008 in Maatkit, C, SQL and Coding. 6 Comments

I wrote a User-Defined Function that implements the FNV (Fowler-Voll-No) hash function for MySQL. I’m not the first person to do this — in fact, I was inspired by the Google patches for MySQL. But my implementation is a little bit different from most, in a very important way that leads directly to much higher performance, especially suited for the Maatkit tools.

A bit of background: FNV hashing is a very fast hash algorithm that operates in fixed memory. It is widely used in lots of important areas in computer science. My implementation requires absolutely no malloc() calls, which is a darn good thing because I am not to be trusted with malloc(), having spent too many years programming in managed languages. I made it return a 64-bit integer, which matches the size MySQL uses internally for most integer arithmetic.

The most important thing I did was make my UDF accept 1 to infinity arguments. That means you can hash entire rows with a single function call. And that is very useful for the Maatkit table-checksumming tools, which tend to run about 8-10 times faster when they don’t have to make MySQL do a bunch of string concatenation. That translates directly to less impact on the server, and less slave lag (if that is a problem for you).

Here’s how my implementation works:

SELECT FNV_64(col1, col2, col3, .... colN) FROM ...

Compare this to MD5() hashing that accomplishes the same thing:

SELECT MD5(CONCAT_WS('#', col1, col2, col3, .... colN)) FROM ...

The UDF’s code is distributed with Maatkit, and I plan to eventually build it as a binary that can be installed without requiring you to compile it. However, compiling is very easy; there are instructions in the source code comments. Installing is also easy: just a simple SQL statement.

If you’re using Maatkit to make sure your slaves have the same data as their master, you should install the UDF on all your servers for a significant performance boost. You’ll save your servers a lot of work. You don’t need to do anything extra for Maatkit to take advantage of it. Maatkit will auto-detect it if you have it installed.

I’ve been running it in production for a couple of months now with nothing but good results. And the code is drop-dead simple, so I think the chance of bugs is very slim. But if you have questions, problems, bug reports etc, please use the Maatkit project page to report them.

Technorati Tags:, , , ,

You might also like:

  1. Speed up your MySQL replication slaves

How I patched InnoDB to show locks held

Published at December 21, 2007 in C, Innotop and SQL. 5 Comments

I’ve written before about how to figure out which connection is holding the InnoDB locks for which other connections are waiting. In other words, how to figure out who’s blocking you from getting work done when you get InnoDB lock timeouts or other InnoDB lock contention. The short and sweet: turn on the InnoDB lock monitor and use innotop to look at the locks held and waited-for.

The InnoDB lock monitor has a few major disadvantages, though:

  • It spews InnoDB status dumps into your log files
  • It prints tons of lock information you don’t want to see (it prints three lines of text for every row that’s locked, each of them in several formats just in case you need it!)
  • It can be so verbose that it crowds out the rest of the InnoDB status output, or even causes you not to see anything but a single lock

Basically, it’s written for the InnoDB developers, not for a MySQL DBA.

Fixing this requires changing the server and/or storage engine; there’s no configuration you can change. The easiest solution, in my view, is to a) turn off the verbose output b) print the locks held by default. Sure there are better ways, such as using INFORMATION_SCHEMA tables, but this is by far the lowest-hanging fruit.

By the way, I think the InnoDB developers are working on exporting some status to pluggable INFORMATION_SCHEMA tables in future releases.

Since MySQL is Free Software, I was able to patch InnoDB and MySQL the way I want them. The patch is attached to my feature request for fixing InnoDB lock output. It’s unlikely to be included in the MySQL server itself, but perhaps it’ll help someone else too.

With this patch, you get two new server variables, which you can set in either your my.cnf file, or dynamically via SET GLOBAL. By default, they are as follows:

mysql> show variables like 'innodb_show%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_show_locks_held    | 10    | 
| innodb_show_verbose_locks | 0     | 
+---------------------------+-------+

The first is the number of locks to print for each transaction. The second is whether to print the verbose record dumps for each lock. (My advice is to leave the second variable at 0).

Now when you run SHOW INNODB STATUS, you’ll see something like the following in the TRANSACTIONS section:

---TRANSACTION 0 268216580, ACTIVE 27 sec, process no 16382, OS thread id 2424191888
2 lock struct(s), heap size 320
MySQL thread id 8, query id 949 localhost 192.168.0.5 xaprb
TABLE LOCK table `test/t1` trx id 0 268216580 lock mode IX
RECORD LOCKS space id 0 page no 2670602 n bits 72 index `PRIMARY` of table `test/t1` trx id 0 268216580 lock_mode X locks rec but not gap

I caused that output by doing a SELECT FOR UPDATE query on an InnoDB table in a transaction.

If this were all I did, it would still be a big help in figuring out who’s blocking whom. But I also made innotop smarter to take advantage of the new output. Now it aggregates locks held and waited-for in L mode, so you can see very quickly “something is waiting for a lock on this table, and something else has a lock on the same table.” This works fine even when you haven’t applied my patch, but my patch lets you debug lock waits much more cleanly.

And as a bonus, it’ll prevent your SHOW INNODB STATUS from being completely overwritten when you have a big deadlock.

All in all, it’s been a huge relief to have this applied to the servers I manage. Sometimes InnoDB’s status output used to drive me nuts. I stopped complaining and did something about it!

Technorati Tags:, , , ,

You might also like:

  1. How to debug InnoDB lock waits
  2. How to monitor InnoDB lock waits
  3. How to find out who is locking a table in MySQL
  4. How to give locking hints in MySQL
  5. A little-known way to cause a database deadlock

How I built the NOW_USEC() UDF for MySQL

Published at October 30, 2007 in C, High Performance MySQL, SQL, GNU/Linux and Coding. 6 Comments

Last week I wrote about my efforts to measure MySQL’s replication speed precisely. The most important ingredient in that recipe was the user-defined function to get the system time with microsecond precision. This post is about that function, which turned out to be surprisingly easy to write.

The manual section on user-defined functions provides very good instructions on how they work and how to build them. But just for the record, on Ubuntu 7.04 on an AMD64 machine, all I had to do was install the libmysqlclient15-dev package, and I was then able to compile the UDF with no further ado. Also for the record, MySQL header files have some dependencies they shouldn’t that break building against a downloaded tarball. So don’t be surprised if you have troubles building against anything but Ubuntu’s provided header files.

Here’s the source, which I basically cribbed from a NOW_MSEC() function I saw in a bug report somewhere. Really, there’s not much to it besides the basic skeleton of a UDF, with a few lines to actually get the system time. And I actually believe if I took another ten minutes to learn about strftime(), there’s probably no need to do it in two steps; I could probably do the whole thing with one strftime() call and save a little memory and time. But that’s what I get for copying and pasting code of unknown quality:

#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>

#include <stdio.h>
#include <sys/time.h>
#include <time.h>
#include <unistd.h>

extern "C" {
   my_bool now_usec_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
   char *now_usec(
               UDF_INIT *initid,
               UDF_ARGS *args,
               char *result,
               unsigned long *length, char *is_null, char *error);
}

my_bool now_usec_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
   return 0;
}

char *now_usec(UDF_INIT *initid, UDF_ARGS *args, char *result,
               unsigned long *length, char *is_null, char *error) {

  struct timeval tv;
  struct tm* ptm;
  char time_string[20]; /* e.g. "2006-04-27 17:10:52" */
  char *usec_time_string = result;
  time_t t;

  /* Obtain the time of day, and convert it to a tm struct. */
  gettimeofday (&tv, NULL);
  t = (time_t)tv.tv_sec;
  ptm = localtime (&t);   

  /* Format the date and time, down to a single second.  */
  strftime (time_string, sizeof (time_string), "%Y-%m-%d %H:%M:%S", ptm);

  /* Print the formatted time, in seconds, followed by a decimal point
 *      and the microseconds.  */
  sprintf(usec_time_string, "%s.%06ld\n", time_string, tv.tv_usec);

  *length = 26;

  return(usec_time_string);
}

The installation looks like this:

baron@tigger now_usec $ make
gcc -fPIC -Wall -I/usr/include/mysql -shared -o now_usec.so now_usec.cc
baron@tigger now_usec $ sudo cp now_usec.so /lib
baron@tigger now_usec $ mysql test
mysql> create function now_usec returns string soname 'now_usec.so';
Query OK, 0 rows affected (0.00 sec)

mysql> select now_usec();
+----------------------------+
| now_usec()                 |
+----------------------------+
| 2007-10-23 10:28:13.862116 | 
+----------------------------+

For those who have reached this page via Google searches and are looking for more information, you should check out the MySQL User Defined Function Library project. Lots of good UDFs there.

Technorati Tags:, , , , , ,

You might also like:

  1. How fast is MySQL replication?
  2. More alternatives to openxml