Question about thread parallelization

[makssent]

Hi everyone. I’d like to clarify one nuance related to scaling and comparing single and cluster in ShardingSphere. I think it would be great to get an answer from @terrymanu when he has time.

There is a single node that performs inserts, for example, using 6 parallel operations. Then I scale the system via ShardingSphere to 2 nodes and effectively get two single nodes. The question is: how will these threads (connections) behave in this case? Will the Proxy distribute these 6 threads across the nodes (roughly 3 per node), or will each node effectively have 6 threads (or 6 connections)?

If the threads are “split,” then it’s not entirely clear to me where a performance gain close to ×2 should come from. It’s clear that sharding usually provides benefits by parallelizing INSERT and other operations across different nodes, reducing contention on CPU, locks, and I/O, etc. In this case, the load on each node becomes lower, but inserts are still executed with a total of 6 threads. Then it’s not entirely clear what exactly provides ×2 system capacity if the overall level of parallelism remains the same.

In sysbench tests that I’ve seen, the configuration on each node explicitly set the connection pool sizes: HikariPool.minimumPoolSize = 200 and HikariPool.maximumPoolSize = 200, after which sysbench was run with the --threads=200 parameter. Am I correct in understanding that with 200 threads this means that for single there are 200 connections to one database, and for cluster with two nodes there are 200 connections per node, i.e. 400 connections in total?

In my tests, acceptable QPS values were obtained only when I explicitly set HikariPool = 200 for both single and cluster with --threads=200. In this scenario, single had 200 connections to one database, while cluster had 200 connections per node. At the same time, without explicitly setting the HikariPool sizes, single was always faster, even in cases where single was already starting to hit resource limits (CPU, I/O, etc.).

The main question is: how should single and cluster be compared correctly from a scalability perspective? Is it correct to assume that when scaling we are effectively “adding one more node,” and then the comparison should look like this: single — one node with --threads = 200 and HikariPool = 200 (200 connections), and cluster — N nodes with the same --threads = 200 and HikariPool = 200 on each node, resulting in 200 × N connections in total? If this logic is incorrect, what approach is considered correct in ShardingSphere for a fair comparison: fixing the same number of client threads, the same total number of connections for the whole setup, or the same connection limits per node?

[terrymanu]

The Proxy doesn’t split or create client threads. Performance gain comes from routing by sharding key to different nodes, where each node executes independently, reducing single-node resource contention.
Connection pool sizing is per data source (node); active connections depend on routing and concurrency, not a fixed “all threads per node.”

• Client threads: remain 6 (or 200 for sysbench); the Proxy only routes/forwards, it doesn’t split threads.
• Routing & parallelism: for sharded writes, each SQL goes to one shard/node based on the sharding key. With even distribution, two nodes each handle roughly half the requests, reducing lock/CPU/IO contention per node, so throughput rises. The lift isn’t from doubling thread count but from splitting work across independent resource pools.
• Proxy/backend connections: Hikari pool config applies per data source; two nodes each have up to 200 connections, actual usage depends on active requests routed there. Sysbench’s 200 concurrency roughly splits under even sharding; it’s not “each node must have 200 active threads.”
• Near-2× gain requires: balanced sharding-key distribution, sufficient front-end concurrency, adequate node resources, and no other bottlenecks (cross-shard/broadcast SQL will reduce gains).

[makssent]

Okay, as I understand it, the threads remain the same both for the proxy and for the single setup. Most likely, I simply mixed up connections and threads. Roughly speaking, in sysbench with 200 threads, the test on single will have 200 threads, and on proxy it will also have 200 threads.

What I don’t understand next is what exactly is meant by “even distribution across nodes” and how this is related to the HikariPool configuration. Based on my tests, performance gains appeared only when I explicitly set the connection pool size. The situation was as follows: in the single setup, with threads = 200 and HikariPool = 200, I had 200 connections to a single database, while when working through the Proxy with the same threads = 200, but with HikariPool = 200 on each node, I ended up with 400 connections to the backends in total. In this scenario, requests were indeed sent in parallel to different shards (ds_0, ds_1), each node processed its own part of the load, and as a result I observed a performance gain close to ×2. Am I correct in understanding that this is exactly what is meant by load distribution?

[makssent]

I want to slightly adjust the overall picture. As I understand it, we have two types of connections: frontend and backend.

Frontend connections are how the client connects to the Proxy: for example, with threads = 200, 200 connections to the Proxy are created.

Next, the Proxy creates backend connections internally and looks at the configuration where the limits for backend connections are defined. If maxPoolSize = 200 and minPoolSize = 200 are specified there, then the Proxy creates 200 connections to each node.

[makssent]

In short, the setup is as follows:

200 sysbench threads

• Single

  • 200 frontend connections → up to 200 backend connections to a single database (HikariPool).
  • All 200 concurrent requests are executed on one node, competing for the same CPU / IO / locks.

• Cluster via Proxy

  • 200 frontend connections to the Proxy.

  • Backend connections are managed by HikariCP per data source (per node) and depend on the pool configuration.

  • With maxPoolSize = 200 and minPoolSize = 200

    • The Proxy creates up to 200 backend connections per node (400 total for two nodes).
    • This provides sufficient headroom so the connection pool is not a bottleneck.
    • With even sharding-key distribution, the same 200 concurrent requests are split across two independent nodes (≈100 per node), reducing contention and allowing throughput to approach ~2× compared to single.

  • With maxPoolSize = 100 and minPoolSize = 100

    • The Proxy creates up to 100 backend connections per node (200 total).
    • This can still achieve near ~2× throughput compared to single if request distribution is close to 50/50 and there are no traffic spikes.
    • However, the pool now becomes a tighter limit: any imbalance (e.g. 150/50) or burst can cause queuing on the hotter node and reduce performance.

It is important to note that having 400 backend connections does not itself produce a 2× performance gain.
The gain comes from splitting the workload across independent nodes.
Larger pool sizes only provide safety margin so the connection pool does not become an additional bottleneck under skewed distribution or load spikes.

[makssent]

Sorry @terrymanu for the large number of messages — there’s a lot of information in my head, and it’s hard to structure it all at once. If this is inconvenient to read, I can consolidate everything into a single, well-structured message for your convenience. Please let me know if that would be helpful, thank you.

[terrymanu]

Thanks for the detailed info; sharing how we look at this:

• Threads: Proxy doesn’t create or split client threads. If sysbench runs 200 threads, both single and sharded setups see 200.
• Frontend vs backend connections: Client→Proxy are frontend; backend pools are per data source. With two shards and maxPoolSize=200, Proxy can open up to 200 per shard (~400 total), but actual usage follows routing/concurrency.
• Performance lift: It comes from sharding-key routing that spreads requests across independent nodes, cutting per-node CPU/IO/lock contention—not from doubling thread/connection counts.
• Fair comparison: Keep the same client concurrency (e.g., --threads=200). Size each shard’s pool so it isn’t the bottleneck (e.g., 100–200 per node depending on skew/spikes). Near-2× is realistic only when the sharding key is evenly distributed and SQLs are single-shard; cross-shard/broadcast will lower the gain.

Summary: More Proxy instances (behind LVS or similar) let you fan out more frontend connections and raise end-to-end throughput. More DB shards raise total backend connections while keeping roughly the same per-DB pool, letting you break a single-DB bottleneck on CPU/IO/locks.

[makssent]

Thank you for the detailed explanation. The puzzle has finally come together for me.