ok， we will checkout the env load，it may be some performance problem another question，we have no experience on the server.yaml and Performance Tuning about this config, can you give some advice as you see in our server.yaml?
________________________________ [email protected] From: Zhang Yonglun<mailto:[email protected]> Date: 2020-01-20 12:24 To: dev<mailto:[email protected]> Subject: Re: Re: use sharding-proxy, query is slow The hostname xxx you omitted is what I want to see. Just tell me how many hosts or how may MySQL instance there are. Show me the 'load' on any host of MySQL instance when query. If a query with key costs 8s, the load will be very high. -- Zhang Yonglun Apache ShardingSphere Daniel Yu <[email protected]> 于2020年1月20日周一 下午12:14写道： > Hi: > thx for you reply. > > 1. What's the cost when mysql client query database1 directly without > sharding-proxy? (use member_id as where condition) > it cost 8 seconds > the cost means the SQL query time spent, i use the command like " mysql > -u -p xxx" on mysql server, and i run the sql ,it print the cost result. > > 2. Is 'member_id' the primary key? > yes , the member_id is primary key , before sharding it is primary key, > after sharding to 16 databases, it is still primary key . > > 3. Please show me the config-sharding.yaml and server.yaml. > I omitted some cotent > > the config-sharding.yaml : > dataSources: > db0: > url: jdbc:mysql://xxx:3306/db0?useSSL=false > username: xxx > password: xxx > connectionTimeoutMilliseconds: 30000 > idleTimeoutMilliseconds: 60000 > maxLifetimeMilliseconds: 1800000 > maxPoolSize: 65 > ... > db15: > url: jdbc:mysql://xxx:3306/db1?useSSL=false > username: root > password: root > connectionTimeoutMilliseconds: 30000 > idleTimeoutMilliseconds: 60000 > maxLifetimeMilliseconds: 1800000 > maxPoolSize: 65 > > shardingRule: > tables: > member: > actualDataNodes: db${0..15}.member > databaseStrategy: > complex: > shardingColumns: member_id > algorithmClassName: > com.toonyoo.shardingjdbc.algorithm.ComplexAlorithm > > the server.yaml: > authentication: > users: > root: > password: xxx > props: > max.connections.size.per.query: 32 > acceptor.size: 32 > executor.size: 32 > proxy.frontend.flush.threshold: 256 > sql.show: false > ________________________________ > [email protected] > > From: Zhang Yonglun<mailto:[email protected]> > Date: 2020-01-20 11:51 > To: dev<mailto:[email protected]> > Subject: Re: use sharding-proxy, query is slow > 1. What's the cost when mysql client query database1 directly without > sharding-proxy? (use member_id as where condition) > 2. Is 'member_id' the primary key? > 3. Please show me the config-sharding.yaml and server.yaml. > > -- > > Zhang Yonglun > Apache ShardingSphere > > > Daniel Yu <[email protected]> 于2020年1月20日周一 上午11:17写道： > > > Hi : > > we have a table 'member' with 160,000,000 rows , > > > > sharding key is member_id > > after sharding the database to 16 databases, the 'member' 's table > > struct is same in 16 databases, > > one database the 'member' table have 10,000,000 rows > > > > we query data by SQL "select * from member where member_id = > 'abc' > > " on database 1 ,it cost 8s, > > > > > > when i used proxy query on 16 databases by the same sql , it cost > > 60s , as far as I know the proxy used threadpool to Parallel execution > the > > query , but the query is slow , why? > > > > > > >
