Shardingsphere: why version 3.1 is faster than version 4.1.1 for the the speed of short sql

[jackjoesh]

I have made a simple sql test: select * from order where order_id = ?
And the speed of version 3.1 is always 1-2 milliseconds faster than version 4.11.
Why having this gap? I guess because the class structure of version 4.11 is more complicated than version 3.1, so cost more time.
This question is very important to us, because our system concurrency is greater than 100k qps, we can't bear one more 1-2 milliseconds slower for all clusters.
Can anyone give me an answer? And can official provide an reference performance test report for version 4.11 or version 5.0?
Thank you very much.

[tuohai666]

ShardingSphere-Proxy or ShardingSphere-JDBC?

Can you give a proof that you use PreparedStatement in this scenario?

[jackjoesh]

ShardingSphere-Proxy or ShardingSphere-JDBC?

Can you give a proof that you use PreparedStatement in this scenario?

I use the ShardingSphere-JDBC. I have made a test for testing speed of version 3.1 and speed of version 4.1.1.

test method: use the same select sql to test the speed of different version, such as : select * from order_history where order_id = ? and symbol = ? and user_id = ?

monitor method: monitor each method cost time in stacktrace by using aspectj load time weaver in spring boot

monitor log example: sharding-monitor|io.shardingsphere.shardingjdbc.jdbc.adapter.AbstractShardingPreparedStatementAdapter|getParameters|0
the column 1 is just a header, the column 2 is target class, the column 3 is invoke method, the column 4 is cost time (time unit is MICROSECONDS)

the test result: the phase "sqlRoute" of version 3.1 is always 1-1.5 milliseconds(1000 MICROSECONDS - 1500MICROSECONDS) faster than the phase "prepare" of version 4.1.1.
version 3.1: sharding-monitor|io.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement|sqlRoute|850
version 4.1.1: sharding-monitor|org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement|prepare|2062
I think these two phases have the same funtions: parse, rewrite, route, so compare them.

the monitor log is in attachment, you can check it.
version3.1-sqlroute-log.txt
version4.1.1-prepare-log.txt

So I insist because the class structure of version 4.11 is more complicated than version 3.1, cost more time. @tuohai666

[tuohai666]

Thanks for your analysis, I'll try to improve the performance.

[bwzhang2011]

any thing update with such issue @tuohai666

[tuohai666]

@jackjoesh @bwzhang2011

I have improved the performance recently, please refer to https://github.com/apache/shardingsphere/issues/9616 and https://github.com/apache/shardingsphere/issues/9826 .

The TPS is proved to be increased by 12% and 15% respectively by sysbench test result of ShardingSphere-Proxy. The CPU load also decreased significantly.

It's expected that the ShardingSphere-JDBC can get the similiar result.

These two PRs will be included in the next release, may be 5.0.0-RC1.

[jackjoesh]

@jackjoesh @bwzhang2011

I have improved the performance recently, please refer to #9616 and #9826 .

The TPS is proved to be increased by 12% and 15% respectively by sysbench test result of ShardingSphere-Proxy. The CPU load also decreased significantly.

It's expected that the ShardingSphere-JDBC can get the similiar result.

These two PRs will be included in the next release, may be 5.0.0-RC1.

thank you very much, except this improve!