Original On the proxy of Sharding-sphere

Hello everyone, github.com/tuohai666 (the sea) today to share the Sharding-sphere launched by the blockbuster products: sharding-proxy! In the previous Sharding-sphere 3.0.0.M1, the first release of the Sharding-proxy, the new product in the end? This hope through a few optimization practice, let everybody glimpse, from a few details point can imagine sharding-proxy of the whole picture. More detailed MySQL protocol, IO model, Netty and other issues, the opportunity to share with you later.

Sharding-proxy Introduction Sharding-proxy Overview

Sharding-proxy is a second product of Sharding-sphere. It is positioned as a transparent database proxy, providing a server-side version that encapsulates the database binary protocol for the support of heterogeneous languages. Currently available in MySQL version, it can use any MySQL protocol-compatible access client (e.g. MySQL Command client, MySQL Workbench, etc.) to manipulate data and be more user-friendly to DBAs.

• Completely transparent to the application and can be used directly as MySQL.
• Applies to any client that is compatible with the MySQL protocol.

Compare to two other products (SHARDING-JDBC, Sharding-sidecar):

	Sharding-jdbc	Sharding-proxy	Sharding-sidecar
Database	Any	Mysql	Mysql
Number of connections consumed	High	Low	High
Heterogeneous languages	Java only	Any	Any
Performance	Low loss	Slightly higher loss	Low loss
Non-centralized	Is	Whether	Is
Static entry	No	Yes	No

They can be used independently, can also cooperate with each other, with different architectural models, different pointcuts to achieve the same functional objectives, and its core functions, such as data fragmentation, read and write separation, flexible transactions, etc., are the same set of implementation code. For example, for scenarios where Java is used only to develop a technology stack, SHARDING-JDBC has a very high degree of support for various Java ORM frameworks, and developers can easily introduce data fragmentation capabilities into existing systems and deploy them to the online environment, and DBAs can deploy a Sharding-proxy instances to query and manage the data.

Sharding-proxy Architecture

The entire architecture can be divided into the front end, the back end and the core components of the three parts. The front end (Frontend) is responsible for network communication with the client, using the NIO-based client/server framework, which uses the NIO model under the Windows and Mac operating systems, and the Linux system is automatically adapted to the Epoll model. During the communication process, the MySQL protocol is compiled and decoded. After the core component (Core-module) gets the decoded MySQL command, it begins to call Sharding-core to parse, rewrite, route, and merge the core functions of SQL. The back end (backend) interacts with the real database temporarily with the bio-based Hikari connection pool. Bio's approach can degrade performance when the database cluster is large, or if it's a master-slave scenario. So in the future we will also provide NIO way to connect to the real database.

In this way, the throughput of proxy will be greatly improved, and can effectively deal with large-scale database cluster.

Sharding-proxy function Details prepared statement function implementation

My first task in Sharding-sphere is to implement the PreparedStatement function of proxy, which is said to be a tall function that can precompile SQL to improve query speed and prevent SQL injection attacks. A service-side pre-compilation, multiple queries, reduce SQL compilation overhead, improve efficiency, it sounds fine. However, when it is done, it is found that the SQL execution efficiency has not improved, even with the naked eye can be seen more slowly than the original statement.

Leave the proxy aside, we'll look at how the MySQL protocol interacts when running PreparedStatement with the Wireshark grab bag.

The sample code is as follows:

1  for(inti = 0; I < 2; i++) {2String sql = "SELECT * from T_order WHERE user_id=?";3     Try (4Connection Connection =datasource.getconnection ();5PreparedStatement PreparedStatement =connection.preparestatement (SQL)) {6Preparedstatement.setint (1, 10);7ResultSet ResultSet =preparedstatement.executequery ();8          while(Resultset.next ()) {9             ...Ten         } One     } A}

The code is easy to understand, use PreparedStatement to perform two query operations, and set the parameter user_id to 10 each time. Analyzing the captured packets, the protocol message interactions between JDBC and MySQL are as follows:

JDBC Queries MySQL two times (query), MySQL returns to JDBC two results (Response), the first message is not what we expect preparedstatement,select there is no question mark, that prepare not take effect , at least not for the MySQL service. For this problem, I think we all know, because the JDBC URL does not set the parameter useserverprepstmts=true, the role of this parameter is to let the MySQL service prepare. Without this parameter, it doesn't make sense to have JDBC Prepare,mysql completely unaware. Next we add this parameter to the URL:

Jdbc:mysql://127.0.0.1:3306/demo_ds?useserverprepstmts=true

The interactive process becomes like this:

At first glance this is the right process, the 1th message is that Preparedstatement,select also has a question mark, notify MySQL to pre-compile SQL. 2nd message MySQL tells JDBC to be ready for success. 3rd message JDBC Sets the parameter to 10. 4th message MySQL returns query results. However, in the 5th article, how did JDBC send again preparedstatement? It is expected that each subsequent query will simply pass the value of the executestatement parameter to achieve the effect of a precompiled run multiple times. If you "precompile" each time, it is equivalent to no precompilation, and there are two more message delivery costs compared to normal queries: Response (prepare OK) and executestatement (parameter = 10). It seems that the problem with performance is here.

Like this use of preparedstatement is not as good as not, must be where the wrong, so the sea began to read the JDBC source code, finally found another need to set the parameters: Cacheprepstmts. Let's add this parameter to see if the miracle will happen:

Jdbc:mysql://127.0.0.1:3306/demo_ds?useserverprepstmts=true&cacheprepstmts=true

It sure did get the message flow we expected, and it was tested faster than normal queries:

Starting from the 5th message, each query can only pass parameter values, and finally achieve the effect of a compilation of multiple runs, MySQL efficiency has been improved. And because Executestatement only passes the value of the parameter, the message length is much shorter than the full SQL, and the efficiency of the network IO is improved. The original cacheprepstmts=true This parameter means to tell the JDBC cache to prepare SQL, such as "SELECT * from t_order WHERE user_id=?" , after running once, the next time you run Skip PreparedStatement, set the parameter value directly with Executestatement.

After understanding the principle, we know how to optimize the proxy. Proxy uses the Hikari database connection pool, which sets the above two parameters at initialization time:

1 config.adddatasourceproperty ("Useserverprepstmts", "true"); 2 config.adddatasourceproperty ("Cacheprepstmts", "true");

This guarantees the performance between the proxy and the MySQL service. So how is the performance between proxy and client guaranteed?

When the proxy receives the client's PreparedStatement, the message is not forwarded to MySQL because the partition key in SQL is a question mark, and Proxy does not know which real database to route to. After receiving this message, the proxy only caches SQL, stores it in a Statementid-to-SQL map, and then requests the database when it receives executestatement. This logic is fine before optimization, because each query is a new PreparedStatement process, and Executestatement tells the client the parameter type and parameter values.

Add two parameters, the message content has changed, executestatement the second time, the message body only the parameter value and no parameter type, the proxy does not know the type will not be able to correctly remove the value. So the optimization that the proxy needs to do is to cache the parameter type at the beginning of the PreparedStatement.

After completing the above optimization, the message interaction on both sides of the client-proxy and Proxy-mysql becomes the final flow of the diagram, starting from the 9th step to efficiently query.

Hikari Connection Pool Configuration optimization

When the proxy is initialized, a Hikari connection pool is configured for each real database. According to the Shard rule, SQL is routed to some real libraries, the result is obtained through Hikari connection, and the final proxy merges the results back to the client. So, how big is the database connection pool? For this controversial topic, there should be a verdict today. You will be pleasantly surprised to find that this problem is not set "how big", but should be set "how small"! Wouldn't it be a bit counterintuitive if I said it was faster to run a task than parallel?

Even a single-core CPU can support up to hundreds of threads "simultaneously". But we should all know that this is just a little trick that the operating system plays with "time slices". In fact, a CPU core can only execute one thread at a time, then the operating system switches context, and the CPU executes another thread, so back and forth. The basic rule of a CPU is that the sequential execution of task A and task B is always faster than "simultaneous" execution of A and B through the time slice. Once the number of threads exceeds the number of CPU cores, increasing the number of threads will only be slower, not faster. A test of Oracle (HTTP://WWW.DAILYMOTION.COM/VIDEO/X2S8UEC) validates this view. The tester gradually reduced the size of the connection pool from 2048 to 96,tps from 16163 to 20702, and the ping rang from 110ms to 3ms.

Of course, it's not that simple to make the number of connections equal to the number of CPUs, but also to consider the impact of network IO and disk IO. When IO occurs, the thread is blocked, and the operating system can use that idle CPU core to serve other threads. So, because threads are always blocking on I/O, we can make the number of threads (connections) More than the CPU core, so that we can do more work in the same time. How much more should it be? PostgreSQL performed a benchmark test:

The growth rate of TPS began to slow from 50 connections. Based on this result, PostgreSQL gives the following formula: connections = ((Core_count * 2) + Effective_spindle_count)

Number of connections = ((Number of cores * 2) + disks). Even with a 32-core machine, more than 60 connections are sufficient. So, when the small partners in the configuration proxy data source, do not write hundreds of connections, not only waste resources, but also slow down the speed.

Result merging optimization

Now proxy access to the real database using JDBC, soon Netty + MySQL protocol asynchronous Access will also be online, the two will coexist, by the user choose which method to access.

Using JDBC's resultset in a proxy can cause great pressure on memory. Proxy front-end corresponding to M-client, back-end corresponding to n real database, the back end of the data to the front-end client process, the data need to pass through the proxy memory. If the data in the proxy memory for a long time, then memory may be full, resulting in the consequences of service unavailability. Therefore, resultset memory efficiency can be optimized from two directions, one is to reduce the data in the proxy dwell time, the other is the current limit.

Let's look at the memory performance of the pre-optimized proxy. Using 5 Client Connection proxies, each client queries out 150,000 data. The results are as follows, for short, figure 1.

It can be seen that the inside of the proxy has been growing, instant GC can not be recovered. This is because ResultSet will block next () until all data that is queried is saved to memory. This is the way resultset defaults to extracting data, which consumes memory heavily. So, is there a way for resultset to receive a piece of data and consume it immediately? In the connector/j document (dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-implementation-notes.html), there is a sentence like this: If You is working with resultsets that has a large number of rows or large values and cannot allocate heap space in your JV M for the memory required, you can tell the driver to stream the results back one row at a time. If you use ResultSet to encounter too many query results, so that heap memory is not fit, you can instruct the driver to use a streaming result set and return one piece of data at a time. Activating this feature simply sets a parameter when creating an statement instance:

Stmt.setfetchsize (Integer.min_value);

So it's done. The proxy can consume data immediately after the command is queried, and the data can be cleared at the next GC. Of course, the proxy in the results of the merger, also need to optimize the instant merge, instead of all the data are taken out and then merged, Sharding-core provides the interface of the instant merge, here is not detailed introduction. Here's a look at the optimized effect, hereinafter referred to as Figure 2.

The data is shortened in memory, and every time the GC is reclaimed, memory efficiency increases dramatically. See here, seems to have done, but the water is very deep, please put on the diving suit continue to explore with me. Figure 2 is produced in the most ideal situation, that is, the client from the proxy consumption data speed, greater than equal to proxy from the speed of MySQL consumption data.

What happens if the client slows down for some reason, or simply doesn't spend it? Through the test found that memory usage straight up, more powerful than figure 1, and finally ran out of memory, Proxy was KO. Let's figure out why this happens, and then introduce the 2nd optimization for resultset: current limit. With a few major caches, SO_RCVBUF/SO_SNDBUF is the TCP cache, and Channeloutboundbuffer is the Netty write cache.

When the client block, its so_rcvbuf will be instantly filled, and then through the sliding window mechanism to notify the proxy not to send data, and the proxy so_sndbuf will be instantly netty full. Proxy's So_sndbuf full, Netty's channeloutboundbuffer will be like a bottomless pit, swallow all the data from MySQL, because by default channeloutboundbuffer is unbounded. Because there are users (Netty) in the consumption, so proxy so_rcvbuf has space, resulting in MySQL will always send data, and Netty will keep the data to Channeloutboundbuffer, until the memory exhaustion.

Knowing the principle, we know that the goal is that when the client blocks, the proxy no longer receives MySQL data. Netty through the water level parameter Write_buffer_water_mark to control the write buffer, when the buffer size exceeds the high watermark, we control the Netty not to write again, when the buffer size is lower than the low watermark, it is allowed to write. When the Channeloutboundbuffer full, proxy so_rcvbuf is full, notify MySQL to stop sending data. Therefore, in this case, the memory consumed by proxy is only the size of the Channeloutboundbuffer high watermark.

Two modes of proxy

In the forthcoming release of the Sharding-sphere 3.0.0.m2 version, Proxy joins the configuration of the two proxy modes:

memory_strictly: Proxy maintains a connection to all the tables that are routed to the database, and the benefit of this approach is to use streaming resultset to conserve memory.

connection_strictly: The agent releases the connection when all data in the resultset is removed, and the memory consumption increases.

Simple can be understood as, if you want to consume smaller memory, use memory_strictly mode, if you want to consume fewer connections, use connection_strictly mode.

The principle of memory_strictly is actually the content that we introduced in the last section, the merit already said. One side effect of this is that the streaming resultset needs to maintain a connection to the database and must successfully establish a connection with all the real tables routed to it before it can be immediately merged, returning the results to the client. Assuming that the database is set to max_user_connections=80, and that the library is routed to a table of 100, then it is not possible to establish 100 connections at the same time, and the results cannot be merged.

connection_strictly is to solve the above problems and exist. Memory consumption increases without the use of streaming resultset. However, this mode does not need to maintain a connection to the database, and the connection can be released every time the full amount of data in the resultset is removed. Or just the example max_user_connections=80, and the table that the library is routed to is 100. The proxy establishes 80 connection query data, and 20 connection requests are cached in the connection pool queue, and the 20 requests are successfully connected to the database in succession as the previous query completes.

If you are still confused about this configuration, remember that the connection_strictlyis used only if max_user_connections is smaller than the maximum number of tables that the library may be routed to.

Summary

Sharding-sphere since 2016 open source, continuous refinement, continuous development, by more and more enterprises and individuals recognized: on GitHub Harvest 5000+ star,1900+forks,60+ of the major companies to use it, Has provided important success stories for Sharding-sphere. In addition, a growing number of business partners and individuals have joined the Sharding-sphere Open source project, contributing to its growth and development of great power.

In the future, we will continue to optimize the current characteristics of excellence, at the same time, we are concerned about flexible transactions, data governance and other new features will be coming. Sharding-sidecar will also become a cloud-native database middleware!

May all people of insight join us and depict Sharding-sidecar's new future together!

May be reading you can help us, share articles, join us!

About Sharding-sphere

Sharding-sphere is an open-source, distributed database middleware solution consisting of 3 independent products, SHARDING-JDBC, Sharding-proxy and Sharding-sidecar. They provide standardized data fragmentation, read/write separations, flexible transactions, and data governance capabilities for a variety of applications such as Java isomorphism, heterogeneous languages, containers, cloud primitives, and more.

It's hard to keep up with the open source, and your biggest support is to leave a star on GitHub.

Project Address:

Github.com/sharding-sphere/sharding-sphere/

Gitee.com/sharding-sphere/sharding-sphere/

For more information, please visit our website:

/HTTP