Shulou(Shulou.com)06/01 Report--

MySQL's NoSQL extension-HandlerSocket plug-in 1.HandlerSocket introduction: in November 2010, Japanese social game development company DeNA released the MySQL HandlerSocket plug-in. The Handlersocket plug-in was developed by Japanese Yoshinori Matsunobu. When dealing with each request, the traditional relational database needs to do a series of overhead operations, such as SQL parsing, query optimization, execution, transaction management, lock management and so on. Non-relational databases (NoSQL) remove these operations. Through the handlersocket plug-in, you can directly do key-value-style interaction with the storage engine layer of MySQL, omitting the SQL level processing of MySQL, and greatly reduce the cost of resources. II. Handlersocket architecture: HandlerSocket implements a NoSQL network service within MySQL, exists as the daemon of the mysqld process, listens on specific ports (9998 and 9999) and client receives NoSQL protocols and API through TCP/IP, and then directly carries out CRUD-related operations through MySQL storage engine API. At the same time, it can also be operated through the traditional MySQL way. Simple and fast operation is realized by handlersocket. Complex operations are realized by the traditional MySQL method. The HandlerSocket structure diagram is as follows: 1.MySQL Client à MySQL Upper Layer à Storage Engine Layer uses the traditional MySQL method, with the client interacting with the Upper layer through port 3306, performing SQL parsing at the Upper layer, opening tables, query optimization, closing tables, and so on, before submitting them to the Storage Engine layer. 2.HandlerSocket Client à HandlerSocket daemon plugin à Storage Engine Layer uses the HandlerSocket approach, which reduces a lot of SQL layer operations in the traditional way. Use port 9998 for read operations and port 9999 for write operations. Advantages and disadvantages of HandlerSocket: 1. The operation related to the SQL layer of MySQL is omitted, and the consumption of CPU is greatly reduced. two。 In the way of merge operation, multiple requests are executed at the same time, which reduces the CPU overhead and the number of IO operations. 3. Based on simple text protocol, it can save network traffic and improve network throughput. 4. Can use traditional MySQL and HandlerSocket to access MySQL database without conflict. 5. Large concurrent connections are supported. 6. You can continue to use mature mechanisms such as MySQL's Replication. 7. Avoid double caching. 8. It has high read and write performance. In CPU Bound scenarios, the read performance is generally 3-7.5 times higher than that of MySQL in the same environment. At the same time, the write performance can also reach 3-5 times. Inadequacies: 1. Because of the merge operation, the response time is larger than that of MySQL. two。 Without security-related guarantees, most NoSQL products have this problem. 3. In the IO Bound scenario, the performance improvement may not be significant. 4. Currently, only MySQL5.1 and MySQL5.5 versions of the Innodb storage engine are supported. 4. Installation of HandlerSocket: 1. Download and install # wget https://download.github.com/ahiguti-HandlerSocket-Plugin-for-MySQL-1.0.6-71-g159ea6d.tar.gz # tar zxvf ahiguti-HandlerSocket-Plugin-for-MySQL-1.0.6-71-g159ea6d.tar.gz-C.. / software/ # cd.. / software/ahiguti-HandlerSocket-Plugin-for-MySQL-159ea6d/ #. / autogen.sh #. / configure-- prefix=/usr/local/handlersocket-- with-mysql -source=/usr/local/src/software/mysql-5.1.36 / / mysql source directory-- with-mysql-bindir=/usr/local/mysql-5.1.36/bin / / mysql installation directory executable directory-- with-mysql-plugindir=/usr/local/mysql-5.1.36/lib/mysql/plugin / / mysql plug-in directory # make # make install 2. Modify the configuration file to add the following configuration under the my.cnf file [mysqld]: loose_handlersocket_port = 9998 / / specify read request port number loose_handlersocket_port_wr = 9999 / / specify write request port number loose_handlersocket_threads = 16 / / specify the number of read threads loose_handlersocket_threads_wr = 1 / / specify the number of write threads 3. Install handlersocket.so: > INSTALL PLUGIN handlersocket SONAME 'handlersocket.so'; 4. Check the process status # netstat-ntpl tcp 0 0 0.0 0 LISTEN 31402/mysqld tcp 3306 0 0 0. 0 LISTEN 31402/mysqld tcp 0 0 0 9 99 0 0 0. 0 LISTEN 31402/mysqld tcp 0 0 0. 0. 0. 0. FAQ configure: error: MySQL source version does not match MySQL binary version solution: add a line [MySQL Server] 5.1.36 on the first line of the configure.in file. 5. Handlersocket performance test: first, let's take a look at the test results of the software author: environment: 2.53GHz CPU 8-core socket Results of Nehalem server with 32GB memory: VI. Install PHP expansion pack php-handlersocket # wget http://php-handlersocket.googlecode.com/files/php-handlersocket-0.0.7.tar.gz # tar zxvf php-handlersocket-0.0.7.tar.gz-C.. / software/ # / usr/local/php5.2.13/bin/phpize #. / configure-- prefix=/usr/local/php-handlersocket\-- with-php-config=/usr / local/php5.2.13/bin/php-config\-- with-handlersocket\-- with-handlersocket-includedir=/usr/local/handlersocket/include/handlersocket # make # make install # vim / usr/local/Zend/etc/php.ini add the following line extension=/usr/local/php5.2.13/lib/php/extensions/no-debug-zts-20060613/handlersocket.so to check whether the load is successful: FAQ: cc-shared .libs / handlersocket.o-lstdc++ -L/usr/local/handlersocket/include/handlersocket/lib-lhsclient-Wl -rpath-Wl,/usr/local/handlersocket/include/handlersocket/lib-Wl,-soname-Wl,handlersocket.so-o. Libs / handlersocket.so / usr/bin/ld: cannot find-lhsclient collect2: ld returned 1 exit status make: * [handlersocket.la] Error 1 solution: # cd / usr/local/handlersocket # cp-rf lib include/handlersocket/ VII. Test: 1. Set up a test table mysql > create table `key hs` (- > `id` int (10) auto_increment not null,-> `k` varchar (50) default null,-> `v` varchar (50) default null,-> primary key (`id`),-> key `index_ k` (`k`)-> engine=innodb; mysql > insert into test_hs values ('','K1'); mysql > insert into test_hs values ('','k2') Mysql > insert into test_hs values ('','k3'); examples of 2.PHP usage

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.