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!!!

Using gearman to distribute your work...

Gearman is a system to farm out work to other machines, dispatching function calls to machines that are better suited to do work, to do work in parallel, to load balance lots of function calls, or to call functions between languages.

How does gearman work? Well, a gearman powered app consists of a client, a worker and a job server. The client creats a job and sends it to the job server. The job server finds a suitable worker and sends the job to the worker. Once the job is done, the worker sends the response back to the client via the job server. There are client and worker APIs available for gearman for different languages which allow the app to communicate with the job server.

Too heavy is it... Lets check how this thing actually runs.

Download gearman daemon from http://gearman.org/index.php?id=download

You would need the "Gearman server and library" - the one written in c. And a php extension which we will use to create and communicate with our workers.

I am using gearmand version 0.11, gearman extension for php version 0.60 and php version 5.3 here.

extract the gearmand server and install it using
./configure
make
sudo make install

extract the php extension for gearman and install it using
phpize
./configure
make
sudo make install

Enable the extension in php.ini. Add the following line in php.ini

extension=gearman.so

To check if the extension has been enabled run

php -i | grep -i gearman

And you will see something like

gearman
gearman support => enabled
libgearman version => 0.11

Now lets write some scripts and check how this works

Create a php client :

                 
<?php                 
# Create our client object.
$client= new GearmanClient();

# Add default server (localhost).
$client->addServer();            

echo "Sending job\n";

# Send reverse job
$result = $client->do("reverse", "Hello World");
if ($result)                                    
   echo "Success: $result\n";                   
?>                                              

Create a php worker :

<?php
# Create our worker object.
$worker= new GearmanWorker();

# Add default server (localhost).
$worker->addServer();

# Register function "reverse" with the server.
$worker->addFunction("reverse", "reverse_fn");

while (1)
{
  print "Waiting for job...\n";

  $ret= $worker->work();
  if ($worker->returnCode() != GEARMAN_SUCCESS)
          break;
}

# A much simple reverse function
function reverse_fn($job)
{
  $workload= $job->workload();
  echo "Received job: " . $job->handle() . "\n";
  echo "Workload: $workload\n";
  $result= strrev($workload);
  echo "Result: $result\n";
  return $result;
}
?>

To test the process

start the gearmand server
gearmand

start the worker
php -q gearmanWorker.php

And send jobs to the worker
php -q gearmanClient.php

The output is as below

Sql Antipatterns Strike Back

SQL Antipatterns strike back by Bill Karwin

Sql Antipatterns Strike Back

View more documents from Karwin Software Solutions LLC.

Using ssh tunneling to tunnel your browser requests.

You will need :

• A ssh server - a machine with access to the net which has ssh server installed on it. We will use this server to forward our requests. You should have access to the server.


• A ssh client - installed on your machine.


• A web browser - preferably firefox or internet explorer. Most basic browsers do not support socks proxy.

creating the tunnel :

The way to create the tunnel is

ssh -D 8888 <your_username>@<your_machine_ip_or_host>

What this command does is it hands over requests to localhost port 8888 to the server that you have specified. Ofcourse, you will have to login and authenticate yourself for the requests to reach the server. You can do this on whichever server you have access to. Using localhost would be the best.

Configure your browser :

To set socks proxy on firefox go to
Edit->Preferences->Advanced->Network->Settings

Enter socks host as localhost and socks port as 8888. And bingo now your requests are being tunneled from your browser to the ssh server through a ssh tunnel. And from there to the web server.

You can use www.whatismyip.com to check the external ip address of your machine.

You can use foxy proxy extension in firefox to have different proxy settings for different sites.