Shulou(Shulou.com)05/31 Report--

How to improve MySQL response speed, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

I. Overview

Second, application scenarios

Due to the limitations of MySQL, many sites adopt the classic architecture of MySQL+Memcached, and even some websites abandon MySQL and adopt NoSQL products, such as Redis/MongoDB and so on. There is no denying that when doing some simple queries (especially competitive queries), many NoSQL products are much faster than MySQL, and more than 80% of the queries on the front site are simple query business.

MySQL provides API access interface through the HandlerSocket plug-in. In our benchmark test, the ordinary R510 server single instance Percona/XtraDB achieves 72W+QPS (pure read). If you use a stronger CPU and add more network cards, you can theoretically achieve higher performance. Under the same conditions, Memcached only has 40W+QPS (read only), and a single instance of Memcached on R510 can no longer improve performance, because a large lock on memory by Memcached limits its concurrency ability.

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

Not without primary key, unique key, or index search!

Table must be an Innodb engine

The main usage scenarios of HandlerSocket and NoSQL are different. HandlerSocket is mainly used to improve MySQL, optimize table addition, deletion and query, and table structure modification and other operations to support intensive CPU operations, while NoSQL, as a cache function, supports intensive Imax O operations.

Therefore, when necessary, we can combine the two to work together.

Third, principle

HandlerSocket is a plug-in of MySQL, integrated in the mysqld process, and operations such as complex queries that cannot be realized by NoSQL 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, and data security is more secure than other NoSQL products, as shown in the figure:

As you can see, HandlerSocket bypasses MySQL's SQL parsing layer (SQL Layer) and accesses the MySQL storage layer directly. In addition, HandlerSocket uses epoll and worker thread/thread pooling network architecture for higher performance.

The architecture of MySQL is the separation of "database management" and "data management", that is, the mode of MySQL Server+Storage Engine. MySQL Server is a layer that interacts directly with 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, and let the storage engine operate specific data by calling Handler API. Storage Engine implements Handler API functions through inheritance, and is responsible for direct interaction 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, which provides network services similar to NoSQL in the form of MySQL Daemon Plugin. It does not deal with data directly, but listens to a configured port, receives communication protocols using NoSQL/API, and then invokes storage engines (such as InnoDB) to process data through Handler API within MySQL. In theory, HanderSocket can handle a variety of MySQL storage engines, but when using MyISAM, the inserted data can not be found. In fact, the first byte of the row is not initialized to 0xff, so there is no problem after initialization, and MyISAM can also be supported, but in order to make better use of memory, HandlerSocket will be used with InnoDB storage engine.

As can be seen from the above figure, HandlerSocket, as the middle layer between mysql client and mysql, replaces part of the original data and table processing work of mysql, and uses multithreading to distinguish between DDL and DML for operation. This purpose is to ensure that in the case of complex processing, the processing can be carried out efficiently.

Because HandlerSocket exists in the form of MySQL Daemon Plugin, MySQL can be used as NoSQL in applications. Its biggest function is to interact with storage engines, such as InnoDB, which does not require any initialization overhead in SQL. Of course, you also need open/close table to access MySQL's TABLE, but it doesn't always go to open/close table because it saves previously visited table cache for reuse, while opening/closing tables is the most resource-intensive and easily leads to competition for mutexes, which is very effective in improving performance. When the traffic becomes small, HandlerSocket will close tables, so it generally does not block DDL.

What is the difference between HandlerSocket and MySQL+Memcached? You can see the difference between figures 1-2 and figure 1-3, which shows a typical MySQL+Memecached application architecture. Because Memcached's get operations are much faster than MySQL's in-memory or on-disk primary key queries, Memcached is used to cache database records. If the query speed and corresponding time of HandlerSocket is comparable to that of Memcached, we can consider replacing the architectural layer of Memcached cache records.

IV. Advantages and characteristics of HandlerSocket

1) multiple query modes are supported

HandlerSocket currently supports index query (primary key index and non-primary key ordinary index), index range scan, LIMIT clause, that is, add, delete, modify, query complete functions, but does not support the operation that can not use any index. In addition, execute_multi () supports network transmission of multiple Query requests at a time, saving network transmission time.

2) handle a large number of concurrent connections

The connection of HandlerSocket is lightweight, because HandlerSocket uses epoll () and worker-thread/thread-pooling architecture, and the number of internal threads in MySQL is limited (which can be controlled by the handlersocket_threads/handlersocket_threads_wr parameter 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, which will cause huge mutex competition and other problems. Such as bug#26590,bug#33948,bug#49169).

3) excellent performance

The performance of HandlerSocket is described in the performance test report of HandlerSocket. Compared with other NoSQL products, its performance is not inferior at all. It not only does not call SQL-related functions, but also optimizes network / concurrency related problems:

Smaller network packets: the HandlerSocket protocol is shorter than the traditional MySQL protocol, so there is less traffic across the network.

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

Grouping client requests: when a large number of concurrent requests reach the 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 replication latency.

4) No duplicate cache

When using Memcached to cache MySQL/InnoDB records, these records are cached in both Memcached and InnoDB Buffer Pool, so it is very inefficient (in fact, there are two pieces of data, Memcached itself may also need to do HA support), while the HandlerSocket plug-in is used, which directly accesses the InnoDB storage engine, records are cached in InnoDB Buffer Pool, so other SQL statements can reuse the cached data.

5) numerous data inconsistencies

Because the data is stored in only one place (in the InnoDB storage engine cache), unlike with Memcached, you need to maintain data consistency between Memcached and MySQL.

6) crash security

Back-end storage is an InnoDB engine, which supports the ACID feature 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 install plugin handlersocket soname 'handlersocket.so' will be lost.

You can see the HandleSocket through show processlist or show plugins

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

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

PHP usage:

Select

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.