search

apache / shardingsphere

Distributed SQL transaction & query engine for data sharding, scaling, encryption, and more - on any database.
Apache License 2.0
19.84k stars 6.72k forks source link

Improve performance of Proxy by increase prepStmtCacheSize #9616

Closed tuohai666 closed 3 years ago

tuohai666 commented 3 years ago

For now the prepStmtCacheSize is 250. Much more time will be cost when the prepared statements count is over this size. The effect is significant.

After I increase the prepStmtCacheSize, the qps of oltp_point_select is increased by 12% (from 77853 to 87284).

image

If a preparedStatement is in cache, execute an SQL will always send a Request Close Statement.

If a preparedStatement is removed from the cache, The command Request Close Statement will be sent from the Proxy to MySQL server. And the following same SQL needs to be prepared again via command Request Prepare Statement.

Both the Request Close Statement and the new Request Prepare Statement are additional cost compare with the single Request Execute Statement

jackjoesh commented 3 years ago

For now the prepStmtCacheSize is 250. Much more time will be cost when the prepared statements count is over this size. The effect is significant.

After I increase the prepStmtCacheSize, the qps of oltp_point_select is increased by 12% (from 77853 to 87284).

image

If a preparedStatement is in cache, execute an SQL will always send a Request Close Statement.

If a preparedStatement is removed from the cache, The command Request Close Statement will be sent from the Proxy to MySQL server. And the following same SQL needs to be prepared again via command Request Prepare Statement.

Both the Request Close Statement and the new Request Prepare Statement are additional cost compare with the single Request Execute Statement

if increase the prepStmtCacheSize from 250 to 200000 , will it cost much memory? If a application has sharded to 20 dbs， is it dangerous?

tuohai666 commented 3 years ago

@jackjoesh You are right, I understand your concern. PreparedStatement will be cached in a LRUCache, the cache will finally reach the prepStmtCacheSize if there're so many different SQLs.

I think big prepStmtCacheSize is appropriate for ShardingSphere scenarios which contain so many shardings(means so many different SQLs). Maybe we should make this parameter customized like username/passwordconnectionTimeoutMilliseconds etc.

I suggest that the default value to be big enough. If the users occur memory problems, they can turn down this parameter.

Are you interested in submitting a PR?