Friday, December 25, 2009

Gearman with php and mysql

In the earlier post we learned how to push jobs from the client to the worker. What we will try to do here is to use php script - the same worker we developed last time to process queries in the mysql server. That is to use gearman to process requests posted inside mysql client.

We would need the mysql UDFs for gearman which could be downloaded from the gearman.org

http://gearman.org/index.php?id=download#databases

Once you have downloaded the gearman mysql UDFs, untar it and compile it. I am using gearman-mysql-udf version 0.4 and mysql version 5.1.30 installed in path /usr/local/mysql here.

tar -xvzf gearman-mysql-udf-0.4.tar.gz
cd gearman-mysql-udf-0.4/
./configure --with-mysql=/usr/local/mysql/bin/mysql_config --libdir=/usr/local/mysql/lib/plugin/
make
sudo make install


check if the so files have been installed properly

jayant@gamegeek:~$ ls /usr/local/mysql/lib/plugin/
libdaemon_example.a libdaemon_example.so.0 libgearman_mysql_udf.la libgearman_mysql_udf.so.0.0.0 mypluglib.so
libdaemon_example.la libdaemon_example.so.0.0.0 libgearman_mysql_udf.so mypluglib.a mypluglib.so.0
libdaemon_example.so libgearman_mysql_udf.a libgearman_mysql_udf.so.0 mypluglib.la mypluglib.so.0.0.0

You wll see libgearman_mysql_udf.* files here.

Now load the gearman-mysql-udfs into mysql using the following queries

CREATE FUNCTION gman_do RETURNS STRING
SONAME "libgearman_mysql_udf.so";
CREATE FUNCTION gman_do_high RETURNS STRING
SONAME "libgearman_mysql_udf.so";
CREATE FUNCTION gman_do_low RETURNS STRING
SONAME "libgearman_mysql_udf.so";
CREATE FUNCTION gman_do_background RETURNS STRING
SONAME "libgearman_mysql_udf.so";
CREATE FUNCTION gman_do_high_background RETURNS STRING
SONAME "libgearman_mysql_udf.so";
CREATE FUNCTION gman_do_low_background RETURNS STRING
SONAME "libgearman_mysql_udf.so";
CREATE AGGREGATE FUNCTION gman_sum RETURNS INTEGER
SONAME "libgearman_mysql_udf.so";
CREATE FUNCTION gman_servers_set RETURNS STRING
SONAME "libgearman_mysql_udf.so";

check whether they have been properly loaded. log into mysql and run.


mysql> select * from mysql.func;
+-------------------------+-----+-------------------------+-----------+
| name | ret | dl | type |
+-------------------------+-----+-------------------------+-----------+
| gman_do | 0 | libgearman_mysql_udf.so | function |
| gman_do_high | 0 | libgearman_mysql_udf.so | function |
| gman_do_low | 0 | libgearman_mysql_udf.so | function |
| gman_do_background | 0 | libgearman_mysql_udf.so | function |
| gman_do_high_background | 0 | libgearman_mysql_udf.so | function |
| gman_do_low_background | 0 | libgearman_mysql_udf.so | function |
| gman_sum | 2 | libgearman_mysql_udf.so | aggregate |
| gman_servers_set | 0 | libgearman_mysql_udf.so | function |
+-------------------------+-----+-------------------------+-----------+
8 rows in set (0.00 sec)


Now lets try calling the reverse function we developed in php to process some work from mysql client. For this.

start gearmand if it is not running
gearmand

start the worker
php -q gearmanWorker.php

log into mysql and run the following queries.

select gman_servers_set('127.0.0.1');
SELECT gman_do("reverse", Host) AS test FROM mysql.user;

The output is as below....


Here we have simply said that all functions would be processed by worker running on 127.0.0.1.
We could also set function wise servers - different servers for different functions
SELECT gman_servers_set("192.168.1.1", "sortme");
SELECT gman_servers_set("192.168.1.2", "reverseme");

And multiple servers for the same function.
SELECT gman_servers_set("192.168.1.3:4730,192.168.1.4:4730", "factorialme");

Interesting, right??
Enjoy!!!

No comments: