HandlerSocket extension for PHP

Introduction

This is the HandlerSocket PECL PHP extension. It provides an interface for accessing the HandlerSocket from PHP.

This repository is a mirror from the google code project php-handlersocket.

Documentation

There's not much documentation about HandlerSocket.

1. There are some slides from the slides from the MySQL plugin author.

2. A blog post from the database infrastructure architecht at DeNA, the largest japanese social gaming platform.

3. One more blog post about installing HandlerSocket on RedHat based systems and having it up and running.

4. Using this PHP extension detailed on another blog post.

Just the facts

HandlerSocket overrides all the CPU usage related with SQL parsing. Instead it speaks a text protocoldirectly to InnoDB to Create/Read/Update/Delete (CRUD) data. It can combine several operations on the server side.

Supported SQL commands:

SELECT, UPDATE, INSERT, DELETE

Supported SQL operators:

IN, WHERE, ORDER BY, LIMIT, FROM, =, >=, <=, >, <

For it to work the tables being accessed must have and index or/and a primary key since what HandlerSocket performs is either a primary key lookup or an index scan.

Debian, Percona Server and MariaDB support

The HandlerSocket MySQL plugin is included in Debian and comes also included in MariaDB 5.3 and Percona Server 5.1.52-12.3.

Configuring the MySQL server

HandlerSocket configuration

You must enable HandlerSocket in your instance of mysqld. Add the following in /etc/mysql/my.cnf:

## Number of reader threads. The recommended value is the number
## of logical CPUs. 
handlersocket_threads = 16

You can get this number with cat /proc/cpuinfo | grep 'processor' | wc -l.

## Number of writer threads
## Recommended value is 1.
handlersocket_thread_wr = 1

## Listening ports for reader requests.
handlersocket_port = 9998
## Listening port for writer requests.
handlersocket_port_wr = 9999

InnoDB Configuration

Since HandlerSocket talks directly to InnoDB the engine runtime parameters needs to be tweaked in order to extract the maximum possible performance of HandlerSocket as well to maintain data integrity when doing writes.

Performance Settings

## Set this as large as possible.
innodb_buffer_pool_size

## As large as possible.
innodb_log_file_size 
innodb_log_files_in_group

## No concurrent threads. Handler socket uses a single thread with
## an event approach using epoll().
innodb_thread_concurrency = 0

## HandlerSocket can handle up to 65000 concurrent
## connections. This needs to be supported at the OS level.
open_files_limit = 65535

Check your current maximum number of simultaneosuly open file descriptors value with ulimit.

## Enable adaptive hash index.
innodb_adaptive_hash_index = 1

Data Integrity Settings

HandlerSocket has no transaction support

## Flush the transaction log after each transaction.
innodb_flush_log_at_trx_commit = 1    

## Guarantees that at most we lost a transaction. This has
## performance penalties. We're asking for MySQL to flush to disk
## every transaction log instance after being done. This enables
## us to recover almost all data upon a crash.
sync_binlog = 1

## Enable two phase commit. First prepare then commit in XA
## transactions, i.e., 
innodb_support_xa = 1

More information regarding InnoDB tunning can be found here.

An explanation of innodb_support_xa.

TODO

1. Expand the documentation with more examples.

2. Debianize the extension and provide a package at debian.perusio.net.