The connection to the original library will be created synchronously during stress testing

[git-chenhao]

ShardingSphere-Proxy 5.0.0-alpha

Expected behavior

The connection to the original library will not be created during stress testing

Actual behavior

The connection to the original library will be created synchronously during stress testing

Confirm that all traffic is routed to the shadow database during the stress test. The original database traffic is very low, but the number of connections is very high. When the stress test is not performed, the number of connections to the original database is normal.

origin datasource

• Daily
  QPS <1000 hikaricp_connections_active < 5
• Stress test
  QPS < 500 hikaricp_connections_active = 30, hikaricp_connections_pending > 50

shadow datasource

• Daily
  QPS =0 hikaricp_connections_active = 0
• Stress test
  QPS > 6W hikaricp_connections_active = 30, hikaricp_connections_pending > 50

[strongduanmu]

@TeslaCN Can you help check this issue?

[TeslaCN]

Hi @git-chenhao
Could you show us your configurations?

[soulasuna]

HI @git-chenhao Could you show us your configurations?
By the way, the shadow function has been refactored, please refer to #11661 for details.
The ease of use and performance of the new shadow function have been greatly improved. It will be officially released in 5.0.0 GA.

[git-chenhao]

@TeslaCN @soulasuna shadow configurations? or all configuration？

[soulasuna]

@git-chenhao It is best to provide full configuration.

[git-chenhao]

shardingAlgorithms:
table-user_xxx:
props:
algorithm-expression: user_xxx_$->{xx_no % 100}
type: INLINE
tables:
user_xxx:
actualDataNodes: origin_database.user_xxx_$->{0..99}
logicTable: user_xxx
tableStrategy:
standard:
shardingAlgorithmName: table-user_xxx
shardingColumn: instance_no

shadowDataSourceNames:

• shadow_database
  sourceDataSourceNames:
• origin_database

@soulasuna

[soulasuna]

@git-chenhao Thanks. We will test it.
The Shadow API you used will replace to the new API in the 5.0.0 GA.

[TeslaCN]

@git-chenhao
What's your DataSource configuration?

[git-chenhao]

@soulasuna We have developed a lot of functions on the 5.0.0-alpha version, and the upgrade cost is very high. We will consider a one-time merge later. You can first help investigate the cause of this problem. Let’s solve it quickly.

[git-chenhao]

@git-chenhao What's your DataSource configuration?

dataSources:
origin_database:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
props:
password: xxx
dbName: origin_database
jdbcUrl: jdbc:mysql://domain:3306/origin_database?useUnicode=true&zeroDateTimeBehavior=convertToNull&characterEncoding=UTF-8
username: aaa
shadow_database:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
props:
password: xxx
dbName: shadow_database
jdbcUrl: jdbc:mysql://domain:3306/shadow_database?useUnicode=true&zeroDateTimeBehavior=convertToNull&characterEncoding=UTF-8
username: aaa
``

[git-chenhao]

@TeslaCN

[git-chenhao]

@TeslaCN @soulasuna Good morning, is there any progress on this question?

[TeslaCN]

Hi @git-chenhao
I think this issue is related to your testing program. Such as MySQL JDBC Driver, after the client connected to server, the client will send some SQL statements like the following. If the SQL was routed to production data source, the connection will be established.
Please confirm if your testing program will disconnect and connect to Proxy periodically during stress testing.

[INFO ] 2021-11-02 17:23:32.725 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: /* mysql-connector-java-8.0.25 (Revision: 08be9e9b4cba6aa115f9b27b215887af40b159e0) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
[INFO ] 2021-11-02 17:23:32.728 [ShardingSphere-Command-0] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2021-11-02 17:23:32.728 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: ds ::: /* mysql-connector-java-8.0.25 (Revision: 08be9e9b4cba6aa115f9b27b215887af40b159e0) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
[INFO ] 2021-11-02 17:23:32.846 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: SET NAMES utf8mb4
[INFO ] 2021-11-02 17:23:32.847 [ShardingSphere-Command-0] ShardingSphere-SQL - SQLStatement: MySQLSetStatement()
[INFO ] 2021-11-02 17:23:32.847 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: ds ::: SET NAMES utf8mb4

[git-chenhao]

@TeslaCN These SQL statements will only be executed when they are started for the first time, and will not be executed all the time, because we have statistics on the number of executions of the physical library. During the stress test, the flow of the original library is very low, and there is no situation where these SQLs have been executed all the time.

[TeslaCN]

@git-chenhao
Does your stress testing program connect to ShardingSphere Proxy directly? If so, could you show us some codes?
If the ShardingSphere Proxy was connected by Application with DataSource, you may check the configuration of the DataSource.

[TeslaCN]

Close due to no response.