You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "cyt574 (via GitHub)" <gi...@apache.org> on 2023/12/07 14:03:45 UTC
[I] create metadata in h2 database generate so many key(uuid) and value(3794 records), it cause first query very slow. [shardingsphere]
cyt574 opened a new issue, #29317:
URL: https://github.com/apache/shardingsphere/issues/29317
## 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?
5.4.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
take less time to finish first query
### Actual behavior
take 10 seconds to finish first query,sometimes take more than 20 seconds
### Reason analyze (If you can)
i turn it to MySQL mode and it generate `repository` tables and insert 3794 records (some records were repeated, it looks so wierd) and program trys to select those records. it takes so many time.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
it happened at work, i try to reproduce but fail on my laptop.
### 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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] create metadata in h2 database generate so many key(uuid) and value(3794 records), it cause first query very slow. [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #29317:
URL: https://github.com/apache/shardingsphere/issues/29317#issuecomment-3551711594
## Problem Understanding
In ShardingSphere-JDBC 5.4.1 you enabled Standalone mode so that metadata is persisted into the H2/MySQL repository table. The first SQL takes 10–20 seconds, and you suspect it is because thousands of UUID-like key/value records are generated.
## Root Cause
Per the official YAML mode configuration spec (https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-jdbc/yaml-config/mode/ (https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-jdbc/yaml-config/mode/)), when mode
is configured as Standalone (or omitted, which still means Standalone by default) ShardingSphere persists all logical-schema metadata in the external repository instead of running purely in memory. Before the first SQL is executed, Standalone mode must load every node
from that repository to rebuild MetaDataContexts; the number of nodes grows with your logical tables/columns/rules. Therefore thousands of path-like keys are expected. Loading them requires multiple queries against the repository; if the repository sits on a slower disk
or remote database, this cold-start work becomes visible on the very first SQL.
## Problem Analysis
- The official metadata-repository document (https://shardingsphere.apache.org/document/current/en/user-manual/common-config/builtin-algorithm/metadata-repository/ (https://shardingsphere.apache.org/document/current/en/user-manual/common-config/builtin-algorithm/
metadata-repository/)) states that Standalone mode can use JDBC/H2/MySQL providers and stores all metadata nodes as key/value. Each logical table, column, rule, and datasource gets its own node, so seeing ~3k entries is normal rather than duplicated data.
- The first SQL is slow because Standalone mode intentionally performs a “cold load”: read every record from the repository and deserialize them into memory. Once this completes, the context stays in-memory and later SQLs run fast. The delay is not the SQL execution
itself but the metadata bootstrap.
- If the repository uses H2/MySQL but resides on high-latency storage or network, the cold-load cost increases further; JDBC drivers also fetch all rows eagerly, so 3794 rows align with the Standalone persistence mechanism.
## Conclusion
This behavior is intrinsic to Standalone mode, not a product bug. If metadata sharing is unnecessary, keep the default (effectively Standalone without external persistence) or remove the repository configuration so ShardingSphere works entirely in memory. If Standalone
is required, host the repository on low-latency storage and issue a warm-up DistSQL/health check during application startup so the first business SQL doesn’t pay the bootstrap cost. Should you still suspect an anomaly, please provide (1) the exact mode configuration,
(2) the scale of logical schemas/tables/columns, and (3) DEBUG logs or timing breakdown for the first SQL to help us investigate further.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] create metadata in h2 database generate so many key(uuid) and value(3794 records), it cause first query very slow. [shardingsphere]
Posted by "cyt574 (via GitHub)" <gi...@apache.org>.
cyt574 commented on issue #29317:
URL: https://github.com/apache/shardingsphere/issues/29317#issuecomment-1846466210
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] create metadata in h2 database generate so many key(uuid) and value(3794 records), it cause first query very slow. [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #29317:
URL: https://github.com/apache/shardingsphere/issues/29317#issuecomment-3583494823
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] create metadata in h2 database generate so many key(uuid) and value(3794 records), it cause first query very slow. [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #29317:
URL: https://github.com/apache/shardingsphere/issues/29317#issuecomment-1880157509
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] create metadata in h2 database generate so many key(uuid) and value(3794 records), it cause first query very slow. [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #29317: create metadata in h2 database generate so many key(uuid) and value(3794 records), it cause first query very slow.
URL: https://github.com/apache/shardingsphere/issues/29317
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org