Mastering Sharding-Proxy: Architecture, Performance Tweaks, and Best Practices

Sharding-Proxy Overview

Sharding-Proxy is a transparent MySQL‑compatible database proxy that is part of the Sharding‑Sphere ecosystem. It implements the MySQL binary protocol, so any client that speaks the protocol (e.g., MySQL command line, MySQL Workbench) can connect without code changes.

Fully drop‑in replacement for a MySQL server.

Works with any MySQL‑protocol client.

Architecture

The proxy consists of three logical layers:

Frontend (NIO) : Network I/O with clients, using a non‑blocking NIO framework (Epoll on Linux, NIO on Windows/Mac). Handles MySQL protocol encoding/decoding.

Core module : After decoding, delegates to Sharding‑Core for SQL parsing, rewriting, routing and result merging.

Backend (BIO) : Communicates with the real databases via a HikariCP connection pool built on blocking I/O. Future releases will add NIO support for backend connections.

PreparedStatement Implementation

To obtain true server‑side preparation, the JDBC URL must include the following parameters:

jdbc:mysql://127.0.0.1:3306/demo_ds?useServerPrepStmts=true&cachePrepStmts=true

Corresponding Hikari configuration:

config.addDataSourceProperty("useServerPrepStmts", "true");
config.addDataSourceProperty("cachePrepStmts", "true");

With useServerPrepStmts the driver sends a COM_STMT_PREPARE to MySQL, and cachePrepStmts caches the prepared SQL on the client side. After the first preparation, subsequent executions are sent as COM_STMT_EXECUTE containing only parameter values, reducing network traffic and latency.

Hikari Connection‑Pool Tuning

Benchmarks show that a smaller pool often yields higher TPS. A practical rule of thumb derived from tests is:

connections = (core_count * 2) + effective_spindle_count

For a 32‑core machine this results in roughly 60 connections. Over‑provisioning (hundreds of connections) wastes resources and can degrade performance.

Result‑Set Merging Optimization

Using the default JDBC ResultSet loads the entire result set into memory, causing high memory pressure. Enabling streaming returns rows one‑by‑one and allows immediate consumption: stmt.setFetchSize(Integer.MIN_VALUE); When streaming, Netty’s write‑buffer water‑mark provides back‑pressure: if the buffer exceeds the high water‑mark, further writes are paused, preventing the proxy from exhausting memory when the client reads slowly.

Proxy Operating Modes (Sharding‑Sphere 3.0.0.M2)

MEMORY_STRICTLY : Keeps a connection for each routed table and streams ResultSet rows, minimizing memory usage. Suitable when the deployment can afford many open connections.

CONNECTION_STRICTLY : Releases the connection after the full ResultSet is fetched, reducing the number of simultaneous connections at the cost of higher memory consumption.

Choose MEMORY_STRICTLY for low‑memory environments; choose CONNECTION_STRICTLY when the maximum allowed user connections is smaller than the number of tables that may be routed.

References

Source code and documentation are available at:

https://github.com/sharding-sphere/sharding-sphere/

https://gitee.com/sharding-sphere/sharding-sphere/