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? > > > > > > >
