HandlerSocket, NoSQL solution under MySQL

HandlerSocket, NoSQL solution under MySQL

Currently, most websites using MySQL use Memcache as the key-value cache at the same time. Although such an architecture is extremely popular and there are many successful cases, it relies too much on Memcache to virtually make Memcache the root cause of failure:

Memcache data consistency problem: when MySQL DATA changes, if the expired data cannot be cleared in a timely and effective manner, it will cause data inconsistency. This is not desirable in the instant web era.

The avalanche effect after the Memcache crash: Once the Memcache as a cache crashes, MySQL may suffer high load and crash in a short time. It is said that Sina Weibo encountered such a problem some time ago.

Note: To clear expired data, you can find a solution in the program architecture. If there is a uniform DAO encapsulation for data operations, you can use the Observer mode to clear expired data, non-subject content, information self-check.

In the face of these problems, the HandlerSocket project is a good solution. It provides MySQL with a complete NoSQL function through plug-ins. In principle, it skips MySQL's most time-consuming Syntax Parsing, query plan and other steps to directly read data. If the memory is large enough, the query efficiency of MySQL can be improved several times if indexes can be loaded!

Performance test instance: Using MySQL as a NoSQL-A story for exceeding 750,000 qps (GFW)

Because HandlerSocket performance is good enough, there is no need to use Memcache, which can save a lot of hardware resources, rather low carbon! In addition, HandlerSocket operates the index of MySQL in the memory without any additional cache, so there is no data consistency problem.

Install

If you use MySQL of Percona Server version, it is simple because it has built-in HandlerSocket support. However, considering that its built-in version is not new enough, there are some bugs that have already been fixed, so it is best to use source code compilation.

The official website already has a simple installation document, but I encountered some other unspecified problems during actual installation, so I will write the corresponding installation process again here.

First, make sure that the version of MySQL5.1 and later has been installed. I use the Ubuntu operating system. I have installed MySQL5.1.37 with apt in advance, and also need the corresponding mysql_config. If it is Ubuntu, yes:

 

Shell> aptitude install libmysqld-dev

 

Note: If the MySQL you are using is a binary version compiled from the source code or officially provided, you can skip this step.

Download the MySQL source code and HandlerSocket source code that are consistent with the MySQL version of the system:

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 meanings are as follows: with-mysql-source indicates the MySQL source code directory, and with-mysql-bindir indicates the MySQL binary executable file directory (that is, the directory where mysql_config is located ), with-mysql-plugindir indicates the MySQL plug-in directory. If you do not know where the directory is, you can query it as follows:

 

Mysql> show variables like 'in in % '; + --------------- + ----------------------- + | Variable_name | Value | + --------------- + ----------------------- + | Plugin_dir |/usr/lib/mysql/plugin | + --------------- + ----------------------- +

 

After running the command, if MySQL5.1.37 is used, the following error message is displayed:

MySQL source version does not match MySQL binary version

It is clear that both the MySQL source code version and the binary version are 5.1.37. Why does this error still occur? By querying the HandlerSocket compilation script, we found that it would retrieve the VERSION file in the MySQL source code directory, but the file was not found in the source code directory of MySQL5.1.37, so an error was reported, now that we know the reason, let's create a VERSION file and put it in the MySQL source code directory. The content is as follows:

 

MYSQL_VERSION_MAJOR = 5 MYSQL_VERSION_MINOR = 1 MYSQL_VERSION_PATCH = 37 MYSQL_VERSION_EXTRA =

 

Run the configure script again, and the remaining steps should be completed:

 

Shell> make Shell> make install

 

Configure HandlerSocket, edit the MySQL configuration file, and add the following content:

 

[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 concurrent # Connections, make open_files_limit as large # Possible.

 

In addition, innodb_buffer_pool_size of InnoDB, key_buffy_size of MyISAM, and other options related to cache indexes should be set as much as possible, so as to realize the potential of HandlerSocket.

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

Finally, log on to MySQL and activate the HandlerSocket plug-in:

 

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 check whether the configured port has been occupied by MySQL to check whether the installation is successful:

 

Shell> lsof-I: 9998 Shell> lsof-I: 9999

 

Finished! Now your MySQL has the NoSQL capability!

Practice

First, create a test table:

 

Create table if not exists 'test'. 'T '( 'Id' int (10) unsigned not null AUTO_INCREMENT, 'A' varchar (10) not null, 'B' varchar (10) NOT NULL, Primary key ('id '), KEY 'A _ B '('A',' B ') ) ENGINE = InnoDB;

 

Note: In theory, HandlerSocket supports various engines such as MyISAM and InnoDB, but InnoDB is recommended.

The HandlerSocket protocol is very simple. commands are separated by tabs, and one row is a request.

Enable the index: P <index ID> <database> <Table> <index> <field>

Insert data: <index ID> '+' <number of parameters> <parameter 1>... <Parameter N>

Read data: <index ID> <operation> <number of parameters> <parameter 1>... <Parameter N> <number> <OFFSET>

SQL prototype: 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 HandlerSocket is used, because no SQL is actually run, the Binlog records the Row format.

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 misunderstanding of HandlerSocket is that only KV queries of the PRIMARY type can be executed. In fact, as long as indexes are supported, simple queries are generally competent and limited in length. I will not say much here, if you think it is difficult to directly operate telnet, you can also use a client that you are familiar with to test it. The official documentation provides an introduction.