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 2021/02/01 08:36:16 UTC
[GitHub] [shardingsphere] wangzhuangwei opened a new issue #9250: sharding-jdbc optimization
wangzhuangwei opened a new issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250
Before sharding JDBC was used, the TPS of a single database was more than 10000. After sharding, the TPS of a single database can only reach 1500. Is there any optimization method?
----------------------------------------------------------------
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 #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770735723
click_0_0 is logic SQL or actual SQL?
----------------------------------------------------------------
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] wangzhuangwei commented on issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
wangzhuangwei commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770705824
Before sharding JDBC4.1.1 was used, the TPS of a single database was more than 10000. After sharding, the TPS of a single database can only reach 1500. It is divided into 10 warehouses, each of which stores three weeks' data, 64 tables per week and 192 tables per warehouse，Is there any optimization method?
----------------------------------------------------------------
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] wangzhuangwei commented on issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
wangzhuangwei commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770762669
Actual SQL: ds_themis_service9 ::: insert into click_1_5(app_id, uid, click_time, chain_type, chain_value, chain_value_crc, create_time, update_time) values(?, ?, ?, ?, ?, ?, ?, ?) ::: [1235393433, 673044035337723904, 1612072603320, 8, vivoxplay5a|5.1.1|106.8.242.221, 2719662132958632361, 2021-01-31 13:56:42.0, 2021-01-31 13:56:42.0]
----------------------------------------------------------------
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 #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770726571
> The JSON object is parsed and divided into 10 matching objects and 1 detail object, which are respectively dropped into the matching table and detail table. The matching table uses two columns as the key of the sub-table, and the detail table uses one column as the key of the sub-table for data insertion.
It's not easy to understand. Can you provide the sharding key + sharding algorithm and the SQL directly?
----------------------------------------------------------------
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] wangzhuangwei commented on issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
wangzhuangwei commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770717272
The JSON object is parsed and divided into 10 matching objects and 1 detail object, which are respectively dropped into the matching table and detail table. The matching table uses two columns as the key of the sub-table, and the detail table uses one column as the key of the sub-table for data insertion.
----------------------------------------------------------------
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] wangzhuangwei commented on issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
wangzhuangwei commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770747383
public BaseSqlBuilder(JdbcFrameWork jdbcFrameWork) {
this.jdbcFrameWork = jdbcFrameWork;
switch (this.jdbcFrameWork) {
case SPRING_JDBC:
case HIBERNATE:
placeholderPrefix = ":";
break;
case MYBATIS:
placeholderPrefix = "#{";
placeholderSuffix = "}";
break;
default:
placeholderPrefix = "";
placeholderSuffix = "";
break;
}
}
----------------------------------------------------------------
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] wangzhuangwei commented on issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
wangzhuangwei commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770737838
With Sharding-JDBC, even if I write the tables and libraries dead, the performance still doesn't improve
----------------------------------------------------------------
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 #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770712342
What's the sharding key and what's the SQL?
----------------------------------------------------------------
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] wangzhuangwei commented on issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
wangzhuangwei commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770735809
public class ClickTableComplexShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
try {
Long startTime = SystemClock.getInstance().getCurrentTime();
Set<String> availableTargetNameList = new HashSet<String>();
Map<String, Collection<Long>> columnNameAndShardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap();
if(log.isDebugEnabled()) {
log.debug("get click table name by columnNameAndShardingValuesMap:{}", JSON.toJSONString(columnNameAndShardingValuesMap));
}
Map<String, Range<Long>> columnNameAndRangeValuesMap = shardingValue.getColumnNameAndRangeValuesMap();
if(log.isDebugEnabled()) {
log.debug("get click table name by columnNameAndRangeValuesMap:{}", JSON.toJSONString(columnNameAndRangeValuesMap));
}
Collection<Long> shardClickTimes = columnNameAndShardingValuesMap.get(ClickConstants.SHARDING_COLUMN_CLICK_TIME);
Range<Long> rangeClickTimes = columnNameAndRangeValuesMap.get(ClickConstants.SHARDING_COLUMN_CLICK_TIME);
Collection<Long> crcs = columnNameAndShardingValuesMap.get(ClickConstants.SHARDING_COLUMN_CRC);
Iterator<Long> crcIterator = crcs.iterator();
while (crcIterator.hasNext()) {
Long crc = crcIterator.next();
log.info("click table column crc's values:{}", crc);
Long serialNo = crc % ClickConstants.SHARDING_CRC_COUNT;
serialNo = Math.abs(serialNo);
setStandardTableName(availableTargetNameList, shardClickTimes, serialNo);
setRangeTableName(availableTargetNameList, rangeClickTimes, serialNo);
}
if (availableTargetNameList.size() > 0) {
if(log.isDebugEnabled()) {
log.debug("get click tables:{}", JSON.toJSONString(availableTargetNameList));
}
Long timeRegion = SystemClock.getInstance().getCurrentTime() - startTime;
if(timeRegion > 3) {
log.error("get click table name waste time:{}ms", timeRegion);
}
return availableTargetNameList;
}
log.error("not found click table");
throw new RuntimeException("dot found real table name");
} catch (Exception e) {
log.error(e.getMessage(), e);
throw new UnsupportedOperationException();
}
}
----------------------------------------------------------------
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 #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770746106
> ```
> public boolean clickPartitionAdd(List<ClickSharding> shardings) {
> for (ClickSharding sharding : shardings) {
> namedJdbcDao.insert(sharding);
> }
> return true;
> }
> ```
These codes can't indicate the SQLs are executed by PreparedStatement.
----------------------------------------------------------------
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] wangzhuangwei removed a comment on issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
wangzhuangwei removed a comment on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770732646
----------------------------------------------------------------
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] wangzhuangwei commented on issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
wangzhuangwei commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770744890
public boolean clickPartitionAdd(List<ClickSharding> shardings) {
for (ClickSharding sharding : shardings) {
namedJdbcDao.insert(sharding);
}
return true;
}
----------------------------------------------------------------
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 #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-807894531
@wangzhuangwei
I have improved the performance recently, please refer to #9616 and #9826 .
The TPS is proved to be increased by 12% and 15% respectively by sysbench test result of ShardingSphere-Proxy. The CPU load also decreased significantly.
It's expected that the ShardingSphere-JDBC can get the similiar result.
These two PRs will be included in the next release, may be 5.0.0-RC1.
--
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] wangzhuangwei commented on issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
wangzhuangwei commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770690329
>
>
> @wangzhuangwei Please fill in the template.
> ## 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)
@tuohai666 Added
----------------------------------------------------------------
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 #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770790667
1. Use PreparedStatement will cache the parse result and improve the performance. I'd suggest never use Statement for high SQLs.
2. If you'd like to compare the TPS. Please confirm one actual SQL for a logic SQL. If not so, there's nothing to be compared.
----------------------------------------------------------------
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 #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770683200
@wangzhuangwei Please fill in the template.
## 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] wangzhuangwei commented on issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
wangzhuangwei commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770745922
@Data
@Entity
@Table(name = "click")
@NoArgsConstructor
@AllArgsConstructor
----------------------------------------------------------------
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] wangzhuangwei commented on issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
wangzhuangwei commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770734017
insert into `click_0_0` (`id`, `app_id`, `uid`, `chain_type`, `chain_value`, `chain_value_crc`, `click_time`, `create_time`, `update_time`) values('13','100','671601485913522176','1','d071877438586ac6c61f319f065b0529','2294368112819423690','1611727572000','2021-01-27 14:24:31','2021-01-27 14:24:31');
----------------------------------------------------------------
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] wangzhuangwei commented on issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
wangzhuangwei commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770732646
spring.shardingsphere.sharding.default-data-source-name = ds_service
spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column = chain_value_crc
spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name = com.sharding.algorithm.DatabasePreciseShardingAlgorithm
spring.shardingsphere.sharding.tables.click.actual-data-nodes = ds_service${0..9}.click_${0..2}_${0..63}
spring.shardingsphere.sharding.tables.click.table-strategy.complex.shardingColumns = click_time,chain_value_crc
spring.shardingsphere.sharding.tables.click.table-strategy.complex.algorithmClassName = com.sharding.algorithm.ClickTableComplexShardingAlgorithm
----------------------------------------------------------------
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] wangzhuangwei commented on issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
wangzhuangwei commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770763283
insert into click(app_id, uid, click_time, chain_type, chain_value, chain_value_crc, create_time, update_time) values(:appId, :uid, :clickTime, :chainType, :chainValue, :chainValueCrc, :createTime, :updateTime)
----------------------------------------------------------------
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 #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770741411
> With Sharding-JDBC, even if I write the tables and libraries dead, the performance still doesn't improve
Do you use PreparedStatement? If so, can you provide the codes?
----------------------------------------------------------------
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] wangzhuangwei commented on issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
wangzhuangwei commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770736028
it's actual SQL
----------------------------------------------------------------
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] wangzhuangwei removed a comment on issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
wangzhuangwei removed a comment on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770745922
@Data
@Entity
@Table(name = "click")
@NoArgsConstructor
@AllArgsConstructor
----------------------------------------------------------------
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] wangzhuangwei removed a comment on issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
wangzhuangwei removed a comment on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770744890
----------------------------------------------------------------
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 closed issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
tuohai666 closed issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250
--
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 #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770744545
I have read the algorithm, there's range value. How many tables will be routed for one logic SQL?
----------------------------------------------------------------
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] wangzhuangwei commented on issue #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
wangzhuangwei commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770749119
EntityMapper<?> entityMapper = MappingBuilder.ME.getTableMapper(object.getClass());
String tableName = entityMapper.getTableName(object);
StringBuilder tableSql = new StringBuilder();
StringBuilder valueSql = new StringBuilder();
tableSql.append("insert into ").append(tableName).append("(");
valueSql.append("values(");
boolean allFieldNull = true;
int columnCount = 0;
Collection<FieldMapper> fields = entityMapper.getFieldMapperList();
for (FieldMapper fieldMapper : fields) {
String fieldName = fieldMapper.getFieldName();
String dbFieldName = fieldMapper.getDbFieldName();
Object value = PropertyUtil.getProperty(object, fieldName);
if (value == null) {
continue;
}
allFieldNull = false;
columnCount++;
if (columnCount > 1) {
tableSql.append(", ");
valueSql.append(", ");
}
tableSql.append(dbFieldName);
valueSql.append(placeholderPrefix).append(fieldName).append(placeholderSuffix);
//valueSql.append(",").append("jdbcType=").append(fieldMapper.getJdbcType().toString());
}
//XXX:allFieldNull
if (allFieldNull) {
throw new RuntimeException(object.getClass().getName() + "'s all fields are null, how can i build sql for it?!");
}
String sql = tableSql.append(") ").append(valueSql).append(")").toString();
SqlModel<Object> model = new SqlModel<Object>(sql, object, entityMapper);
----------------------------------------------------------------
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 #9250: sharding-jdbc optimization
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #9250:
URL: https://github.com/apache/shardingsphere/issues/9250#issuecomment-770754250
Can you find out is there any #{} or ${} in your mapper?
----------------------------------------------------------------
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