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 2020/08/10 03:35:43 UTC

[GitHub] [shardingsphere] cobstergo opened a new issue #6744: query data slowly by use sharding proxy

cobstergo opened a new issue #6744:
URL: https://github.com/apache/shardingsphere/issues/6744


   our project use sharding proxy,  we find that query data more slowly than not useing sharding proxy ,even the table not sharding,
   for example , we plan to sharding table B into 50 tables , and table A is not sharding , we query 15k data from table A cost about 4 secs by use proxy, and same data query cost 2 secs by not use proxy , very simple sql like this: select * from table A.  and also feel query slowly from sharding table B, is there some config wrong? cloud you give me some advice to impove query speed  here is my some configs :
   config-sharding.yaml
   maxPoolSize: 10000
   
   this is server.yaml
   max.connections.size.per.query: 32
     acceptor.size: 16  # The default value is available processors count * 2.
     executor.size: 16  # Infinite by default.
     proxy.frontend.flush.threshold: 256  # The default value is 128.
     proxy.transaction.type: XA
     proxy.opentracing.enabled: false
     query.with.cipher.column: true
     sql.show: false
     sql.simple: false


----------------------------------------------------------------
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 #6744: query data slowly by use sharding proxy

Posted by GitBox <gi...@apache.org>.

tuohai666 commented on issue #6744:
URL: https://github.com/apache/shardingsphere/issues/6744#issuecomment-671187406

Besides fill in the following template, please answer the question:

How did you judge one SQL is slow or not? I think you might like to show the time cost by use direct MySQL server, not only by use proxy.

```
## Bug Report

**For English only**, other languages will not accept.

Before report a bug, make sure you have:

- Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
- Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).

Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will **close it**.

Please answer these questions before submitting your issue. Thanks!

### Which version of ShardingSphere did you use?

### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

### Expected behavior

### Actual behavior

### Reason analyze (If you can)

### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

### Example codes for reproduce this issue (such as a github link).
```

----------------------------------------------------------------
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] cobstergo commented on issue #6744: query data slowly by use sharding proxy

Posted by GitBox <gi...@apache.org>.

cobstergo commented on issue #6744:
URL: https://github.com/apache/shardingsphere/issues/6744#issuecomment-672549243


   thanks for you help , we have improved query speed by  increase proxy jvm memory and add mysql union Index and so on.
   we use proxy to pressure test ,we insert data about 3 days and when finish we find query very slowy , but when restart proxy ,it return to normal.
   


----------------------------------------------------------------
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] cobstergo commented on issue #6744: query data slowly by use sharding proxy

Posted by GitBox <gi...@apache.org>.

cobstergo commented on issue #6744:
URL: https://github.com/apache/shardingsphere/issues/6744#issuecomment-671255118

thanks for you reply.
for example, before we use proxy, we open web page , it may cost 0.5 sec , and we use proxy ,same web page maybe cost 3 sec , after we see the log , find query cost most time, so I use navicat to connect Mysql directly and use navicat to connect Mysql by proxy to compare query cost time. specifically ,
we find if our sql contains left join which we sharding table , it will cost much more time to query ,cloud you give me some advice to deal with this ?

### Which version of ShardingSphere did you use?
Apache ShardingSphere - Version: 4.1.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
query data more quickly just like by use direct MySQL server
### Actual behavior
on some condition query slowly
### Reason analyze (If you can)
maybe my sharding config yaml is wrong , The query thread is a single thread, not multiple threads. The capacity of the query thread pool is too small. when I restart proxy , it seems more quickly than which The proxy longer the running time is.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
no exception
### Example codes for reproduce this issue (such as a github link).

this is config-sharding.yaml
connectionTimeoutMilliseconds: 300000
idleTimeoutMilliseconds: 600000
maxLifetimeMilliseconds: 18000000
maxPoolSize: 10000

this is server.yaml
max.connections.size.per.query: 32
acceptor.size: 16 # The default value is available processors count * 2.
executor.size: 16 # Infinite by default.
proxy.frontend.flush.threshold: 256 # The default value is 128.
proxy.transaction.type: XA
proxy.opentracing.enabled: false
query.with.cipher.column: true
sql.show: false
sql.simple: false

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] cobstergo closed issue #6744: query data slowly by use sharding proxy

Posted by GitBox <gi...@apache.org>.

cobstergo closed issue #6744:
URL: https://github.com/apache/shardingsphere/issues/6744


   


----------------------------------------------------------------
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 #6744: query data slowly by use sharding proxy

Posted by GitBox <gi...@apache.org>.

tuohai666 commented on issue #6744:
URL: https://github.com/apache/shardingsphere/issues/6744#issuecomment-671687608


   1. please show full config-sharding.yaml and full server.yaml.
   2. what condition?
   > on some condition query slowly
   3. I think you didn't answer my question.
   Please answer straightforwardly and explicitly for:
   ```
   sql1: xxxx
   proxy cost: xxxx
   direct mysql cost:
   
   sql2: xxxx
   proxy cost: xxxx
   direct mysql cost:
   ```
   4. If you  use left join. Please try to use bindingTables like:
   ```
     bindingTables:
       - t_order,t_order_item
   ```


----------------------------------------------------------------
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] rouxiaoxiao commented on issue #6744: query data slowly by use sharding proxy

Posted by GitBox <gi...@apache.org>.

rouxiaoxiao commented on issue #6744:
URL: https://github.com/apache/shardingsphere/issues/6744#issuecomment-738502515


   > thanks for you help , we have improved query speed by increase proxy jvm memory and add mysql union Index and so on.
   > we use proxy to pressure test ,we insert data about 3 days and when finish we find query very slowy , but when restart proxy ,it return to normal.
   
   I encountered the same problem, and how did you increase the jvm memory?


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