How UPSQL Proxy Implements MySQL Streaming to Boost Performance

MySQL Result Set Structure

When a MySQL query such as SELECT * FROM test is executed, the server returns a ResultSet composed of logical protocol packets. The ResultSet consists of two parts: metadata (field count, field descriptions, and an EOF marker that is removed in newer versions) and row data (individual rows, error packets, or an OK packet that replaces EOF in newer protocols).

Client Library Interfaces

MySQL’s C API provides two sets of functions for retrieving results: mysql_store_result / mysql_stmt_store_result: store the entire result set in client memory. mysql_use_result: keep data in the TCP buffer or on the server, fetching rows incrementally.

From a communication‑process perspective, mysql_store_result must wait for all data to be transferred and parsed before returning, whereas mysql_use_result can return each row packet to the upper‑level API as soon as it arrives.

Advantages of Streaming (mysql_use_result)

Faster application‑layer response because the client does not wait for the full result set.

More controllable memory usage, avoiding client‑side out‑of‑memory errors.

Command‑Line and JDBC Options for Streaming

Both the MySQL client and mysqldump support a --quick (or -q) flag that enables mysql_use_result, preventing OOM on large dumps.

In JDBC, streaming can be achieved by:

Setting ResultSet.TYPE_FORWARD_ONLY and ResultSet.CONCUR_READ_ONLY on prepareStatement.

Adding useCursorFetch=true&defaultFetchSize=-2147483648 to the JDBC URL (behavior varies across driver versions and is not generally recommended).

UPSQL Proxy Design Evolution

Earlier versions of UPSQL Proxy used a blocking model: the proxy collected complete result sets from multiple back‑ends before responding to the client. This caused increased response latency and limited the proxy’s ability to handle more than 100 k rows per shard due to memory constraints.

Starting with version 2.4.0, UPSQL Proxy implements streaming: as soon as metadata for a shard is received, it is forwarded to the client, and each subsequent row packet is streamed immediately. This reduces the proxy’s memory footprint and improves client response speed.

Conclusion

The article demonstrates how understanding the MySQL protocol and leveraging streaming result handling can significantly enhance the performance and scalability of middleware such as UPSQL Proxy, offering faster responses and lower memory consumption for large‑scale distributed database deployments.