HandlerSocket is a magic horse

HandlerSocket is a MySql plug-in written by akira higuchi, a Japanese. With this plug-in, you can directly interact with the MySQL back-end storage engine in the key-value manner, saving the CPU overhead such as SQL interpretation at the MySQL upper layer, opening and closing tables, and creating query plans. Based on the data given by the author, the QPS of the data can be up to when all the data is in the memory.

Applicable scenarios:

Innodb engine, by primary key, unique key or index search, that is, the where condition of its SQL must be); supports limit statements, IN, INSERT/UPDATE/DELETE.

Install and test the results directly.

Installation:

Install plug-ins for MySQL and extensions for PHP. Here I will not go into details, you can refer to this article http://blog.1984fox.com/read.php? 30 # entrymore, very detailed.

API:

Google codehttp: // code.google.com/p/php-handlersocket/) provides the PHP extension author's API. Here I will describe the parameters of each method and you can refer to them ):

Instantiation:

 
 

  
  
/*
  
  
* String $ host: MySQL ip address;
  
  
* String $ port: the listening port of the handlersocket plug-in. It has two optional ports: one for reading and the other for writing.
  
  
*/
  
  
$ Hs = new HandlerSocket ($ host, $ port );
 
 

Open a data table:

 
 

  
  
/*
  
  
* Int $ index: This number is equivalent to the handle in the file operation. All other HandlerSocket Methods Operate the table opened by this openIndex based on this number,
  
  
* String $ dbname: Database Name
  
  
* String $ table: table Name
  
  
* String $ key: the "PRIMARY key" HandlerSocket: PRIMARY) or "index name" of the table as the search keyword segment. This means that the table must have a PRIMARY key or index.
  
  
* Personal understanding: the key field to be used as the where condition. In this way, handlersocket has only one where condition.
  
  
* String $ column: 'column1, column2 'the fields in the opened table are separated by commas (,). That is to say, other fields in the $ table will not be operated.
  
  
*/
  
  
$ Hs-> openIndex ($ index, $ dbname, $ table, $ key, $ column );
 
 

Query:

 
 

  
  
/*
  
  
* Int $ index: $ index used by openIndex ()
  
  
* String $ operation: Operator Used for the $ key field specified in the openIndex method. Currently, '=', '> =', '<=', '> ', and '<'; can be understood as the where Condition
  
  
* Array $ value
  
  
* Int $ number: 1 by default): the maximum number of results obtained. It is equivalent to the second parameter of limit in SQL.
  
  
* Int $ skip is 0 by default): The number of hops in the past. It is equivalent to the first parameter of limit in SQL.
  
  
*/
  
  
$ Retval = $ hs-> executeSingle ($ index, $ operation, $ value, $ number, $ skip );
 
 

Note: $ port_wr (read/write port) is used for openIndex ):

 
 

  
  
/*
  
  
* Int $ index: $ index used by openIndex ()
  
  
* Array $ arr: the number of numeric elements is the same as that of $ column in openIndex.
  
  
*/
  
  
$ Retval = $ hs-> executeInsert ($ index, $ arr );
 
 

Delete Note: $ port_wr is used for the openIndex here, that is, the read/write port ):

 
 

  
  
/*
  
  
* Int $ index: $ index used by openIndex ()
  
  
* String $ operation: Operator Used for the $ key field specified in the openIndex method. Currently, '=', '> =', '<=', '> ', and '<'; can be understood as the where Condition
  
  
* Array $ value
  
  
* Int $ number: 1 by default): the maximum number of results obtained. It is equivalent to the second parameter of limit in SQL.
  
  
* Int $ skip is 0 by default): The number of hops in the past. It is equivalent to the first parameter of limit in SQL.
  
  
*/
  
  
$ Retval = $ hs-> executeDelete ($ index, $ operation, $ value, $ number, $ skip );
 
 

Update note: $ port_wr (read/write port) is used for openIndex ):

 
 

  
  
/*
  
  
* Int $ index: $ index used by openIndex ()
  
  
* String $ operation: Operator Used for the $ key field specified in the openIndex method. Currently, '=', '> =', '<=', '> ', and '<'; can be understood as the where Condition
  
  
* Array $ value
  
  
* Int $ number: 1 by default): the maximum number of results obtained. It is equivalent to the second parameter of limit in SQL.
  
  
* Int $ skip is 0 by default): The number of hops in the past. It is equivalent to the first parameter of limit in SQL.
  
  
*/
  
  
$ Retval = $ hs-> executeUpdate ($ index, $ operation, $ value, $ number, $ skip );
 
 

Test:

Create a user table with million data records. The id is the primary key, including the uname, email, and add_time fields. use two different machines for the AB stress test:

Read test:

Concurrent stress tests:

MySQL: min: 0.504740953445 max: 13.1727859974 average: 1.05 CPU: 0.7% us, 0.3% sy use: 111 s

HandlerSocket: min: 0.302443981171 max: 9.37712621689 average: 0.736 CPU: 0.4% us, 0.3% sy use: 77 s

Concurrent stress tests:

MySQL: min: 0.504750013351 max: 10.4482009411 average: 1.094 CPU: 0.9% us, 0.4% sy use: 85 s

HandlerSocket: min: 0.302488803864 max: 10.3345310688 average: 0.788 CPU: 0.5% us, 0.4% sy use: 62 s

Concurrent stress tests:

MySQL: min: 0.505280017853 max: 21.3242678642 average: 1.095 CPU: 1.5% us, 0.7% sy use: 55 s

HandlerSocket: min: 0.30281996727 max: 10.6022770405 average: 0.786 CPU: 1.1% us, 0.7% sy use: 39 s

Concurrent or stress tests:

MySQL: min: 0.505041122437 max: 28.8087069988 average: 1.073 CPU: 1.8% us, 0.9% sy use: 61 s

HandlerSocket: min: 0.302739143372 max: 12.878344059 average: 0.774 CPU: 1.0% us, 0.9% sy use: 30 s

Summary:

In common: the higher the concurrency, the better the performance.

The hs system occupies and executes less time than MySQL performance by about 30% ~ 40%

Write test:

Concurrent stress tests:

MySQL: min: 0.507106781006 max: 4.95259904861 average: 0.594 CPU: 0.76% us, 0.49% sy use: 62 s

HandlerSocket: min: 0.303457021713 max: 7.0854101181 average: 0.383 CPU: 0.4% us, 0.2% sy use: 43 s

Concurrent stress tests:

MySQL: min: 0.508066892624 max: 12.8451189995 average: 0.659 CPU: 1.0% us, 0.6% sy use: 51 s

HandlerSocket: min: 0.30427312851 max: 12.4244120121 average: 0.417 CPU: 0.53% us, 0.29% sy use: 32 s

Concurrent stress tests:

MySQL: min: 0.507676839828 max: 12.8466610909 average: 0.689 CPU: 1.3% us, 0.72% sy use: 45 s

HandlerSocket: min: 0.304312229156 max: 12.4680581093 average: 0.465 CPU: 0.66% us, 0.38% sy use: 29 s

Concurrent stress tests:

MySQL: min: 0.507092952728 max: 11.7785778046 average: 0.775 CPU: 1.34% us, 0.82% sy use: 45 s 13 unwritten Records)

HandlerSocket: min: 0.219769954681 max: 12.6269509792 average: 0.556 CPU: 0.63% us, 0.37% sy use: 32 s 15 records not written)

Concurrent or stress tests:

MySQL: min: 0.507570981979 max: 13.4538660049 average: 0.75 CPU: 1.9% us, 1.1% sy use: 29 s write more than one entry)

HandlerSocket: min: 0.304651975632 max: 16.3402500153 average: 0.555 CPU: 0.7% us, 0.43% sy use: 26 s 8 unwritten Records)

Summary:

In common: the higher the concurrency, the better the performance.

The hs system occupies and executes less time than MySQL performance by about 50% ~ 60%

The test results are indeed obvious. HandlerSocket can replace MySQL in a highly concurrent and simple table operation environment.