Using HandlerSocket Plugin for MySQL with PHP

December 28, 2010

This document was updated and tested for CentOS 6.0

In my last two posts I installed the HandlerSocket plugin into MariaDB and showed how to use it with Perl.  That’s good, but if you are thinking of using HandlerSocket  I’m guessing you have a very high traffic website and it’s written in PHP.  In this post I’m going to connect HandlerSocket with PHP.  In the next post I’ll discuss using HandlerSocket on a production system.

There are a couple of HandlerSocket php modules projects.  I tried each of them and I found PHP-HandlerSocket was the best.  Both of them are still rough and neither of them have documentation beyond their source code.  Maybe this will move things forward.

Here are the applications you need to have installed that where not installed in my last two posts.  Run this to check your system.

 yum install php-devel re2c php php-mysql

Start in the a Downloads directory and wget the newest tarball.

 cd ~
 mkdir Downloads
 cd Downloads
 wget https://php-handlersocket.googlecode.com/files/php-handlersocket-0.3.0.tar.gz
 tar zxf  php-handlersocket-0.2.0.tar.gz

Compile and install the module.  You may need to install php and php-devel .

 cd php-handlersocket
 phpize
 ./configure
 make
 make test
 make install

In the ‘make test’ step you are looking for Tests passed    :    1 (100.0%) (100.0%)

With everything in place, you need to teach PHP to load the module my editing the PHP configuration file  and add the extension.

 vi /etc/php.ini

Search for the Dynamic Extensions section and add this line.

 extension=handlersocket.so

Run php to test that the module is loading correctly.  If nothing happens, your good.

I had some trouble at the start.   This is bad:

 # php
 PHP Warning:  PHP Startup: Unable to load dynamic library  '/usr/lib64/php/modules/hadlersocket.so'
 -  /usr/lib64/php/modules/hadlersocket.so: cannot open shared object file:
  No such file or directory in Unknown on line 0

 

To do more testing we need a database and some data.  Here is what I created. I’ve colored the sections of code as they correspond to the output.

CREATE TABLE `user` (
 `user_id` INT (10) UNSIGNED NOT NULL,
 `user_name` VARCHAR (50),
 `user_email` VARCHAR (255),
 `created` DATETIME DEFAULT NULL,
 PRIMARY KEY (`user_id`),
 KEY `NAME` (`user_name`)
 ) ENGINE = INNODB ;

INSERT INTO `user`  VALUES
 ( 1, 'mark', 'mark@www.mysqlfanboy.com', '0000-00-00 00:00:00' ),
 ( 2, 'linda', 'linda@www.mysqlfanboy.com', '0000-00-00 00:00:00' ),
 ( 3, 'mark', 'test@www.mysqlfanboy.com', NULL ) ;

I created this program to read this data:  (mytest.php)  HandlerSocket can write data to the database.  I’ve left writing as an exercise to the reader. (For now.)

openIndex(0, $dbname, $table, $index, $columns)))
 {
 echo $hs->getError(), PHP_EOL;
 die();
 }

 $retval = $hs->executeSingle(0, '=', array('mark'), 10, 0);
 echo "data > ",$retval[0][1], " \n";
 var_dump($retval);

 $retval = $hs->executeMulti(
 array(array(0, '=', array('mark'), 1, 0),
 array(0, '=', array('linda'), 1, 0)));
 var_dump($retval);

 unset($hs);

 ?>

Here is the output of running mytest.php.

# php mytest.php
data > mark 
array(2) { [0]=> array(4) { [0]=> string(1) "1" [1]=> string(4) "mark" [2]=> string(20) "mark@www.mysqlfanboy.com" [3]=> string(19) "0000-00-00 00:00:00" } [1]=> array(4) { [0]=> string(1) "3" [1]=> string(4) "mark" [2]=> string(20) "test@www.mysqlfanboy.com" [3]=> NULL } }
array(2) { [0]=> array(1) { [0]=> array(4) { [0]=> string(1) "1" [1]=> string(4) "mark" [2]=> string(20) "mark@www.mysqlfanboy.com" [3]=> string(19) "0000-00-00 00:00:00" } } [1]=> array(1) { [0]=> array(4) { [0]=> string(1) "2" [1]=> string(5) "linda" [2]=> string(21) "linda@www.mysqlfanboy.com" [3]=> string(19) "0000-00-00 00:00:00" } } }  

So it works, but do you really want to use it?  There are a lot more to questions to investigate.   Is it writing data any faster then SQL? Is writing faster then a bulk file load?  What if you mix SQL and HandlerSocket calls?  What does buffering do to these processes?

You you have any questions about about what I’ve written or that you would like be to investigate, email me – mark @ Grennan.com.

Tweet

tags: , , , ,
posted in CentOS, Code, HOW TO by mark

Follow comments via the RSS Feed | Leave a comment | Trackback URL

Leave Your Comment

 



Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org
Creative Commons License
MySQL Fan Boy by Mark Grennan is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.
HOME