NoSQL solution under MySQL handlersocket

Most of the Web sites currently using MySQL use Memcache as the key-value cache. Although such a structure is extremely popular, there are many successful cases, but too dependent on memcache, virtually let memcache become the root cause of failure:

Memcache Data Consistency problem: When the MySQL data changes, if not timely and effectively clean out the outdated data, will cause data inconsistency. This is not desirable in the Web2.0 era, which emphasizes immediacy.

The avalanche effect after the collapse of Memcache: As cached memcache crashes, MySQL is likely to withstand high load and downtime in a short time. Sina Weibo is said to have suffered such problems in the previous period.

Note: On the issue of cleaning up expired data, you can find a way to do it in the program architecture, if the data operation has unified DAO package, you can use observer mode to clean up outdated data, not subject content, data self-examination.

Faced with these problems, Handlersocket project is a good solution, it through the plug-in way to give MySQL complete NoSQL function, from the principle, it skips the most time-consuming in MySQL syntax parsing, query plan and other steps, directly read the data, if the memory is large enough to install the index, MySQL query efficiency can be increased several times!

Performance Test Example: Using MySQL as a nosql–a story for exceeding 750,000 QPS (GFW)

Because Handlersocket performance is good enough, so there is no need to use memcache, can save a lot of hardware resources, quite low carbon! And Handlersocket operation is the MySQL put in memory index, no additional cache, so there is no data consistency problem.

Installation

If you use Percona server version of MySQL is simple, because it has built-in handlersocket support, but in view of its built-in version is not new, there are some already fixed bugs, so it is best to use source code compilation.

The official already has a simple installation document, but when I actually installed, encountered some other unspecified problems, so here is the corresponding installation process to write again.

First make sure that you have installed more than MySQL5.1 version, I use the Ubuntu operating system, has been installed with apt MySQL5.1.37, and also need the corresponding mysql_config, if it is Ubuntu, you can:

Shell> Aptitude Install Libmysqld-dev

Note: You can skip this step if you are using MySQL as a binary version compiled from source code or officially provided.

Next, download a copy of MySQL source code and Handlersocket source code consistent with the system MySQL version:

Mysql-5.1.37.tar.gz

Ahiguti-handlersocket-plugin-for-mysql-1.0.6-76-gf5f7443.tar.gz

Shell> Tar zxf mysql-5.1.37.tar.gz Shell> Tar zxf ahiguti-handlersocket-plugin-for-mysql-1.0.6-76-gf5f7443.tar.gz Shell> CD ahiguti-handlersocket-plugin-for-mysql-f5f7443 Shell>./autogen.sh shell>./configure--with-mysql-source=. /mysql-5.1.37 --with-mysql-bindir=/usr/bin --with-mysql-plugindir=/usr/lib/mysql/plugin

The parameter meaning is as follows: With-mysql-source represents the MySQL source code directory, With-mysql-bindir represents the MySQL binary executable directory (that is, Mysql_config directory), With-mysql-plugindir represents the MySQL plugin directory, if you do not know where the directory, you can query as follows:

Mysql> show VARIABLES like ' plugin% '; +---------------+-----------------------+ | variable_name | Value | +---------------+-----------------------+ | Plugin_dir | /usr/lib/mysql/plugin | +---------------+-----------------------+

After you run the command, if you are using the MySQL5.1.37 version, you may experience the following error message:

MySQL source version does not match MySQL binary version

Obviously our MySQL source code version and binary version are 5.1.37, why will this error occur? By querying Handlersocket's compiled script, it turns out that it will retrieve the version file in the MySQL source directory. Can MySQL5.1.37 source code directory Somehow unexpectedly did not have this file, so the error, since know the reason, then we suit do a version file to the MySQL source directory, the contents are as follows:

Mysql_version_major=5 Mysql_version_minor=1 mysql_version_patch=37 Mysql_version_extra=

Run the Configure script again, it should be OK, the rest of the steps are completed:

Shell> make shell> make Install

Then you need to configure the Handlersocket, edit the MySQL configuration file, add the following:

[Mysqld] Loose_handlersocket_port = 9998 # The port number to bind to (for read requests) LOOSE_HANDLERSOCKET_PORT_WR = 9999 # The port number to bind to (for write requests) Loose_handlersocket_threads = 16 # The number of worker threads (for read requests) LOOSE_HANDLERSOCKET_THREADS_WR = 1 # The number of worker threads (for write requests) Open_files_limit = 65535 # to allow Handlersocket accept many concurrent # connections, make Open_files_limit as large as # possible.

In addition, InnoDB's innodb_buffer_pool_size, or MyISAM's key_buffy_size and other related caching indexes are as large as possible, so that the potential of the handlersocket can be played.

Note: APT package management under the configuration file is generally/etc/mysql/my.cnf, otherwise it is generally/etc/my.cnf

Finally login MySQL and activate the Handlersocket plugin:

mysql> INSTALL PLUGIN handlersocket soname ' handlersocket.so ';

If there is no problem, you can see the handlersocket thread in MySQL:

Mysql> show Processlist;

You can also confirm that the installation was successful by querying whether the port that you just configured is already occupied by MySQL:

Shell> lsof-i: 9998 Shell> Lsof-i: 9999

Finish the job! Now your MySQL has the ability to NoSQL!

Actual combat

Start by creating a test table:

CREATE TABLE IF not EXISTS ' test '. ' t ' ( ' ID ' int (a) unsigned not NULL auto_increment, ' A ' varchar (a) not NULL, ' B ' varchar (a) not NULL, PRIMARY KEY (' id '), KEY ' A_b ' (' A ', ' B ') ) Engine=innodb;

Note: Theoretically handlersocket supports various engines such as MYISAM,INNODB, but it is recommended to use InnoDB.

The Handlersocket protocol is very simple, the instruction is separated by tab, and a line is a request.

Open index: P < index ID > < database > < table > < index > < field >

Insert data:< index ID > ' + ' < parameter number > < parameter 1> ... < parameters n>

Read Data:< index ID > < operation > < parameter number > < parameter 1> ... < parameters n> < number > < offset >

SQL prototypes: INSERT into test.t (ID, a, b) VALUES (1, ' a1′, ' b1′), (2, ' a2′, ' b2′)

shell> telnet localhost 9999 Trying 127.0.0.1 ... Connected to localhost. Escape character is ' ^] '. P 1 Test T PRIMARY id,a,b 0 1 1 + 3 1 A1 B1 0 1 0 1 + 3 2 A2 B2 0 1 0

Note: When using Handlersocket, the Binlog record is in row format because the SQL is not actually running.

SQL prototype: SELECT ID, a, b from test.t WHERE id = 1 LIMIT 1

shell> telnet localhost 9999 Trying 127.0.0.1 ... Connected to localhost. Escape character is ' ^] '. P 1 Test T PRIMARY id,a,b 0 1 1 = 1 1 1 0 0 3 1 A1 b1

SQL prototype: SELECT ID, a, b from test.t WHERE ID >=1 LIMIT 2

shell> telnet localhost 9999 Trying 127.0.0.1 ... Connected to localhost. Escape character is ' ^] '. P 1 Test T PRIMARY id,a,b 0 1 1 >= 1 1 2 0 0 3 1 A1 B1 2 A2 B2

SQL prototype: SELECT ID, a, b from test.t WHERE a = ' a1′and b = ' B1′limit 1

shell> telnet localhost 9999 Trying 127.0.0.1 ... Connected to localhost. Escape character is ' ^] '. P 1 Test T A_b id,a,b 0 1 1 = 2 A1 B1 1 0 0 3 1 A1 b1

A common misconception about handlersocket is that only the primary type of KV query can be executed, in fact, as long as the index, the general simple query it can be competent, space limit, here is not much to say, if you think direct operation Telnet some labored, You can also use your familiar client to test, as described in the official documentation.