How can MySQL improve response speed

MySQL's own limitations, many sites have adopted the classic architecture of MySQL Memcached, and some even abandoned MySQL and adopted NoSQL products. It is undeniable that when doing some simple queries (especially PK queries), many NoSQL products are much faster than MySQL.

1. Overview

2. Application scenarios

MySQL has its own limitations, many All sites use the classic architecture of MySQL Memcached, and some sites even abandon MySQL and use NoSQL products, such as Redis/MongoDB, etc. It is undeniable that when doing some simple queries (especially PK queries), many NoSQL products are much faster than MySQL, and more than 80% of the queries on the front-end website are simple query services.

MySQL provides API access interface through the HandlerSocket plug-in. In our benchmark test, the ordinary R510 server single instance Percona/XtraDB reached 72W QPS (pure reading). If a more powerful CPU is used, it will increase more A network card can theoretically achieve higher performance. Under the same conditions, Memcached only has 40W QPS (pure reading), and a single instance of Memcached on R510 cannot improve performance because Memcached has a large lock on memory that limits its concurrency capabilities.

Innodb engine, search by primary key, unique key or index (that is to say, its SQL where conditions must be these); supports limit statements, IN, INSERT/UPDATE/DELETE.

• Searching without primary key, unique key or index will not work!

• The table must be Innodb engine

The main usage scenarios of HandlerSocket and NoSQL are different. HandlerSocket is mainly used to improve MySQL, optimize operations such as table additions, deletions, modifications, and table structure modifications, and supports intensive CPU operations; while NoSQL, as a cache function, supports intensive I/O operations.

Therefore, when necessary, the two can be combined to work together.

3. Principle

HandlerSocket is a plug-in for MySQL, integrated in the mysqld process; complex queries and other operations that NoSQL cannot implement are still implemented using MySQL's own relational database. At the operation and maintenance level, the widely used experience of MySQL master-slave replication continues to play a role. Compared with other NoSQL products, data security is more guaranteed. The principle is as follows:

It can be seen that HandlerSocket bypasses MySQL's SQL parsing layer (SQL Layer) and directly accesses the MySQL storage layer. In addition, HandlerSocket uses epoll and worker thread/thread pooling network architecture to achieve higher performance.

The architecture of MySQL is the separation of "database management" and "data management", which is the mode of MySQL Server Storage Engine. MySQL Server is a layer that directly interacts with the Client. It is responsible for managing connection threads, parsing SQL to generate execution plans, managing and implementing views, triggers, stored procedures and other things that have nothing to do with specific data operation management. It allows storage by calling the Handler API. The engine operates on specific data. Storage Engine implements the functions of Handler API by inheriting, and is responsible for directly interacting with data, data access implementation (must be implemented), transaction implementation (optional), index implementation (optional), and data cache implementation (optional).

HandlerSocket is an internal component of MySQL that provides NoSQL-like network services in the form of MySQL Daemon Plugin. It does not directly process data, but only listens to a configured Through a port method, it receives the communication protocol using NoSQL/API, and then calls the storage engine (such as InnoDB) to process the data through the Handler API inside MySQL. Theoretically, HanderSocket can handle various MySQL storage engines, but when using MyISAM, the inserted data cannot be found. This is actually because the first byte is not initialized to 0xff when constructing the row. After initialization, there is no problem, and MyISAM also It can still be supported, but in order to better utilize memory, HandlerSocket will be used with the InnoDB storage engine.

As can be seen from the above figure, HandlerSocket, as the middle layer between the mysql client and mysql, replaces part of mysql's native data and table processing work, using a multi-threaded approach. Distinguish between DDL and DML for operations. The purpose is to ensure that complex processing can be processed efficiently.

Because HandlerSocket exists in the form of MySQL Daemon Plugin, MySQL can be used as NoSQL in applications. Its biggest function is to enable interaction with storage engines, such as InnoDB, without any SQL initialization overhead. When accessing MySQL's TABLE, of course you also need to open/close the table, but it does not open/close the table every time because it will save the previously accessed table cache for reuse, while opening/closing tables is It is the most resource-consuming and can easily cause contention for the mutex. This is very effective in improving performance. When the traffic becomes smaller, HandlerSocket will close tables, so it generally does not block DDL.

What is the difference between HandlerSocket and MySQL Memcached? Comparing Figure 1-2 and Figure 1-3, we can see the differences. Figure 1-3 shows the typical MySQL Memecached application architecture. Because Memcached's get operation is much faster than MySQL's primary key query in memory or on disk, Memcached is used to cache database records. If HandlerSocket's query speed and response time are comparable to Memcached, we can consider replacing the architectural layer of Memcached's cache records.

4. Advantages and disadvantages

Advantages and features of HandlerSocket

1) Supports multiple query modes

HandlerSocket Currently, it supports index queries (both primary key indexes and non-primary key ordinary indexes), index range scans, and LIMIT clauses, which means it supports full functions of adding, deleting, modifying, and querying, but it does not yet support operations that cannot use any index. In addition, execute_multi() is supported to transmit multiple Query requests over the network at one time, saving network transmission time.

2) Handle a large number of concurrent connections

HandlerSocket connections are lightweight because HandlerSocket uses epoll() and worker-thread/thread-pooling architecture, and the number of MySQL internal threads is Limited (can be controlled by the handlersocket_threads/handlersocket_threads_wr parameters in my.cnf), so even if tens of millions of network connections are established to HandlerSocket, it will not consume a lot of memory, and its stability will not be affected in any way (consuming too much memory, will cause huge mutual exclusion competition and other problems, such as bug#26590, bug#33948, bug#49169).

3) Excellent performance

The performance of HandlerSocket is described in the article HandlerSocket's performance test report. Compared with other NoSQL products, the performance is not inferior at all. Not only does it not call SQL-related function, and also optimizes network/concurrency-related issues:

• Smaller network packets: Compared with the traditional MySQL protocol, the HandlerSocket protocol is shorter, so the entire network traffic is smaller .

• Run a limited number of MySQL internal threads: refer to the above.

• Group client requests: When a large number of concurrent requests arrive at HandlerSocket, each worker thread aggregates as many requests as possible, and then executes the aggregated requests and returns the results at the same time. In this way, performance is greatly improved by sacrificing a little response time. For example, you can reduce the number of fsync() calls and reduce copy latency.

4) No duplicate caching

When using Memcached to cache MySQL/InnoDB records, these records are cached in both Memcached and InnoDB Buffer Pool, so the efficiency is very low (There are actually two pieces of data, and Memcached itself may need HA support). Using the HandlerSocket plug-in, it directly accesses the InnoDB storage engine and records are cached in the InnoDB Buffer Pool, so other SQL statements can reuse the cached data.

5) No data inconsistency

Since the data is only stored in one place (in the InnoDB storage engine cache area), unlike when using Memcached, the data needs to be maintained between Memcached and MySQL consistency.

6) Crash safety

The back-end storage is the InnoDB engine, which supports the ACID characteristics of transactions and ensures the security of transactions. Even if innodb_flush_log_at_trx_commit=2 is set, if the database server crashes, only Data of

7) SQL/NOSQL coexistence

In many cases, we still want to use SQL (such as complex report queries), and most NoSQL products do not support the SQL interface, HandlerSocket is just A MySQL plug-in, we can still send SQL statements through the MySQL client, but when high throughput and fast response are required, HandlerSocket is used.

8) Inherit MySQL functions

Because HandlerSocket runs on MySQL, all MySQL functions are still supported, such as: SQL, online backup, replication, HA, monitoring, etc.

9) No need to modify/rebuild MySQL

Because HandlerSocket is a plug-in and open source, it supports building from any MySQL source code, even third-party versions (such as Percona), without the need for MySQL makes any modifications.

10) Storage engine independent

Although we only tested the MySQL-EnterpriseInnoDB and Percona XtraDB plug-ins, HandlerSocket can theoretically interact with any storage engine. MyISAM can also be supported through simple modifications, but this is of little significance from the perspective of data caching and memory utilization.

Defects and precautions of HandlerSocket

1) Protocol incompatibility

The HandlerSocket API is not compatible with the Memcached API, and although it is easy to use, it still requires a bit of learning to learn how to interact with HandlerSocket. However, we can translate it to the HandlerSocket API by overloading the Memecached function.

2) No security features

Similar to other NoSQL databases, HandlerSocket does not support security features. The worker thread of HandlerSocket runs with system user permissions, so applications can access all tables through the HandlerSocket protocol. Object, but you can simply modify the protocol and add a configuration item as password in my.cnf. When connecting, you can verify the password through this configuration. Of course, you can also filter data packets through the network firewall.

3) There is no advantage for disk IO-intensive scenarios

For IO-intensive application scenarios, the database cannot execute thousands of queries per second, and usually only has 1-10% CPU utilization. In this case, SQL parsing will not become a performance bottleneck, so there is no advantage to using HandlerSocket. HandlerSocket should only be used on servers where the data is fully loaded into memory. However, for PCI-E SSD (such as Fusion-IO) devices, which can provide 4w IOPS per second, and the IO device itself consumes a large amount of CPU, using HandlerSocket still has advantages.

5. Installation

Note: The installation method in the book is outdated and the version is relatively low. It is not recommended to use it. It is recommended to use the official documentation for installation. Github address: https:// github.com/DeNA/HandlerSocket-Plugin-for-MySQL

Installation documentation: https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/installation. en.txt

Download source code: You can git clone directly through github or download it

Installation steps:

1. build Handlersocket

./autogen.sh
./configure --with-mysql-source=/work/mysql-5.1.50 --with-mysql-bindir=/work/mysql-5.1.50-linux-x86_64-glibc23/bin  --with-mysql-plugindir=/work/mysql-5.1.50-linux-x86_64-glibc23/lib/plugin

Note ：

• with-mysql-source：MySQL source code directory
• with-mysql-bindir：MySQL binary executable file directory (the directory where mysql_config is located)
• with -mysql-plugindir: MySQL plug-in directory

2. Compile

make && make install

3. Configuration

HandleSocket cannot be used after compilation, and you need to configure it in the MySQL configuration file ( my.cnf) add the following configuration:

[mysqld]
# 绑定读请求端口
loose_handlersocket_port = 9998
# 绑定写请求端口
loose_handlersocket_port_wr = 9999
# 读请求线程数
loose_handlersocket_threads = 16
# 写请求线程数
loose_handlersocket_threads_wr = 16
# 设置最大接收连接数
open_files_limit = 65535

The additions here are mainly for the configuration of HandleSocket. It has two ports, 9998 for reading data and 9999 for writing data, but reading through 9998 is more efficient. Here, the number of threads for reading and writing is set to 16. In addition, in order to handle more concurrent connections, the number of open file descriptors is set to 65535

In addition, the relationship between the configuration options of InnoDB's innodb_buffer_pool_size or MyISAM's key_buffy_size to the cache index, so set it as large as possible so that the potential of HandleSocket can be unleashed.

4. Activate HandleSocket

Log in to MySQL to execute

mysql> install plugin handlersocket soname 'handlersocket.so';

You can see HandleSocket through show processlist or show plugins

Finally, you need to install the PHP expansion pack PHP HandlerSocket

Installation documentation: https://github.com/tz-lom/HSPHP

PHP usage:

Select

<?php 
$c = new \HSPHP\ReadSocket();
$c->connect();
$id = $c->getIndexId(&#39;data_base_name&#39;, &#39;table_name&#39;, &#39;&#39;, &#39;id,name,some,thing,more&#39;);
$c->select($id, &#39;=&#39;, array(42)); // SELECT WITH PRIMARY KEY
$response = $c->readResponse();

//SELECT with IN statement
$c = new \HSPHP\ReadSocket();
$c->connect();
$id = $c->getIndexId(&#39;data_base_name&#39;, &#39;table_name&#39;, &#39;&#39;, &#39;id,name,some,thing,more&#39;);
$c->select($id, &#39;=&#39;, array(0), 0, 0, array(1,42,3));
$response = $c->readResponse();

Update

<?php
$c = new \HSPHP\WriteSocket();
$c->connect(&#39;localhost&#39;,9999);
$id = $c->getIndexId(&#39;data_base_name&#39;,&#39;table_name&#39;,&#39;&#39;,&#39;k,v&#39;);
$c->update($id,&#39;=&#39;,array(100500),array(100500,42)); // Update row(k,v) with id 100500 to  k = 100500, v = 42
$response = $c->readResponse(); // Has 1 if OK

$c = new \HSPHP\WriteSocket();
$c->connect(&#39;localhost&#39;,9999);
$id = $c->getIndexId(&#39;data_base_name&#39;,&#39;table_name&#39;,&#39;&#39;,&#39;k,v&#39;);
$c->update($id,&#39;=&#39;,array(100500),array(100500,42), 2, 0, array(100501, 100502)); // Update rows where k IN (100501, 100502)
$response = $c->readResponse(); // Has 1 if OK

Delete

<?php
$c = new \HSPHP\WriteSocket();
$c->connect(&#39;localhost&#39;,9999);
$id = $c->getIndexId(&#39;data_base_name&#39;,&#39;table_name&#39;,&#39;&#39;,&#39;k,v&#39;);
$c->delete($id,&#39;=&#39;,array(100500));
$response = $c->readResponse(); //return 1 if OK

Insert

<?php
$c = new \HSPHP\WriteSocket();
$c->connect(&#39;localhost&#39;,9999);
$id = $c->getIndexId(&#39;data_base_name&#39;,&#39;table_name&#39;,&#39;&#39;,&#39;k,v&#39;);
$c->insert($id,array(100500,&#39;test\nvalue&#39;));
$response = $c->readResponse(); //return array() if OK

Related recommendations: "mysql tutorial"

The above is the detailed content of How can MySQL improve response speed. For more information, please follow other related articles on the PHP Chinese website!