You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2021/02/04 12:29:26 UTC
[GitHub] [shardingsphere] jackjoesh opened a new issue #9330: why version 3.1 is faster than version 4.1.1 for the the speed of short sql
jackjoesh opened a new issue #9330:
URL: https://github.com/apache/shardingsphere/issues/9330
I have made a simple sql test: select * from order where order = ?
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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] bwzhang2011 commented on issue #9330: why version 3.1 is faster than version 4.1.1 for the the speed of short sql
Posted by GitBox <gi...@apache.org>.
bwzhang2011 commented on issue #9330:
URL: https://github.com/apache/shardingsphere/issues/9330#issuecomment-801884612
any thing update with such issue @tuohai666
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 edited a comment on issue #9330: why version 3.1 is faster than version 4.1.1 for the the speed of short sql
Posted by GitBox <gi...@apache.org>.
tuohai666 edited a comment on issue #9330:
URL: https://github.com/apache/shardingsphere/issues/9330#issuecomment-807890187
@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% by sysbench test result of ShardingSphere-Proxy. The CPU load also decreased significantly.
It's expected the ShardingSphere-JDBC can get the similiar result. These to PRs will be included in the next release, may be 5.0.0-RC1.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 edited a comment on issue #9330: why version 3.1 is faster than version 4.1.1 for the the speed of short sql
Posted by GitBox <gi...@apache.org>.
tuohai666 edited a comment on issue #9330:
URL: https://github.com/apache/shardingsphere/issues/9330#issuecomment-807890187
@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 the ShardingSphere-JDBC can get the similiar result.
These two PRs will be included in the next release, may be 5.0.0-RC1.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 edited a comment on issue #9330: why version 3.1 is faster than version 4.1.1 for the the speed of short sql
Posted by GitBox <gi...@apache.org>.
tuohai666 edited a comment on issue #9330:
URL: https://github.com/apache/shardingsphere/issues/9330#issuecomment-807890187
@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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 edited a comment on issue #9330: why version 3.1 is faster than version 4.1.1 for the the speed of short sql
Posted by GitBox <gi...@apache.org>.
tuohai666 edited a comment on issue #9330:
URL: https://github.com/apache/shardingsphere/issues/9330#issuecomment-807890187
@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 the ShardingSphere-JDBC can get the similiar result. These to PRs will be included in the next release, may be 5.0.0-RC1.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jackjoesh commented on issue #9330: why version 3.1 is faster than version 4.1.1 for the the speed of short sql
Posted by GitBox <gi...@apache.org>.
jackjoesh commented on issue #9330:
URL: https://github.com/apache/shardingsphere/issues/9330#issuecomment-779295214
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](https://github.com/apache/shardingsphere/files/5982509/version3.1-sqlroute-log.txt)
[version4.1.1-prepare-log.txt](https://github.com/apache/shardingsphere/files/5982513/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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #9330: why version 3.1 is faster than version 4.1.1 for the the speed of short sql
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #9330:
URL: https://github.com/apache/shardingsphere/issues/9330#issuecomment-807890187
@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% by sysbench test result of ShardingSphere-Proxy. The CPU load also decreased significantly. It's expected the ShardingSphere-JDBC can get the similiar result. These to PRs will be included in the next release, may be 5.0.0-RC1.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 edited a comment on issue #9330: why version 3.1 is faster than version 4.1.1 for the the speed of short sql
Posted by GitBox <gi...@apache.org>.
tuohai666 edited a comment on issue #9330:
URL: https://github.com/apache/shardingsphere/issues/9330#issuecomment-807890187
@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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jackjoesh commented on issue #9330: why version 3.1 is faster than version 4.1.1 for the the speed of short sql
Posted by GitBox <gi...@apache.org>.
jackjoesh commented on issue #9330:
URL: https://github.com/apache/shardingsphere/issues/9330#issuecomment-779296084
> 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](https://github.com/apache/shardingsphere/files/5982522/version3.1-sqlroute-log.txt)
[version4.1.1-prepare-log.txt](https://github.com/apache/shardingsphere/files/5982523/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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #9330: why version 3.1 is faster than version 4.1.1 for the the speed of short sql
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #9330:
URL: https://github.com/apache/shardingsphere/issues/9330#issuecomment-773855313
ShardingSphere-Proxy or ShardingSphere-JDBC?
Can you give a proof that you use PreparedStatement in this scenario?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jackjoesh edited a comment on issue #9330: why version 3.1 is faster than version 4.1.1 for the the speed of short sql
Posted by GitBox <gi...@apache.org>.
jackjoesh edited a comment on issue #9330:
URL: https://github.com/apache/shardingsphere/issues/9330#issuecomment-779296084
> 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](https://github.com/apache/shardingsphere/files/5982522/version3.1-sqlroute-log.txt)
[version4.1.1-prepare-log.txt](https://github.com/apache/shardingsphere/files/5982523/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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #9330: why version 3.1 is faster than version 4.1.1 for the the speed of short sql
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #9330:
URL: https://github.com/apache/shardingsphere/issues/9330#issuecomment-773855313
ShardingSphere-Proxy or ShardingSphere-JDBC?
Can you give a proof that you use PreparedStatement in this scenario?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jackjoesh commented on issue #9330: why version 3.1 is faster than version 4.1.1 for the the speed of short sql
Posted by GitBox <gi...@apache.org>.
jackjoesh commented on issue #9330:
URL: https://github.com/apache/shardingsphere/issues/9330#issuecomment-809388576
> @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!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jackjoesh removed a comment on issue #9330: why version 3.1 is faster than version 4.1.1 for the the speed of short sql
Posted by GitBox <gi...@apache.org>.
jackjoesh removed a comment on issue #9330:
URL: https://github.com/apache/shardingsphere/issues/9330#issuecomment-779295214
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](https://github.com/apache/shardingsphere/files/5982509/version3.1-sqlroute-log.txt)
[version4.1.1-prepare-log.txt](https://github.com/apache/shardingsphere/files/5982513/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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #9330: why version 3.1 is faster than version 4.1.1 for the the speed of short sql
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #9330:
URL: https://github.com/apache/shardingsphere/issues/9330#issuecomment-781837352
Thanks for your analysis, I'll try to improve the performance.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 closed issue #9330: why version 3.1 is faster than version 4.1.1 for the the speed of short sql
Posted by GitBox <gi...@apache.org>.
tuohai666 closed issue #9330:
URL: https://github.com/apache/shardingsphere/issues/9330
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org