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 2019/05/10 07:32:48 UTC
[GitHub] [incubator-shardingsphere] AlbertTao opened a new issue #2365: Will
the performance of sharding-jdbc be optimized?
AlbertTao opened a new issue #2365: Will the performance of sharding-jdbc be optimized?
URL: https://github.com/apache/incubator-shardingsphere/issues/2365
## Question
I have test a simple sql with version 4.0.0-RC1, found it's too slow compared to version 2.0.3.
4.0.0-RC1 run result(10 times):
```
Statement select test(ms):
13 11 14 13 18 12 7 18 10 11
PreparedStatement select test(ms):
3 2 3 3 2 2 2 4 2 1
```
2.0.3 run result(10 times) :
```
Statement select test(ms):
2 2 3 2 3 6 2 2 3 3
PreparedStatement select test(ms):
2 2 5 4 2 2 2 2 1 3
```
###Simple Test Demo
**dependency**
```xml
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.9</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.36</version>
</dependency>
<dependency>
<groupId>io.shardingjdbc</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>2.0.3</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.0.0-RC1</version>
</dependency>
```
**Prepare SQL**
```sql
create database test_d_0;
create database test_d_1;
CREATE TABLE test_d_0.`ts_order_0000` (`id` BIGINT NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`));
CREATE TABLE test_d_0.`ts_order_0001` (`id` BIGINT NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`));
CREATE TABLE test_d_1.`ts_order_0002` (`id` BIGINT NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`));
CREATE TABLE test_d_1.`ts_order_0003` (`id` BIGINT NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`));
```
**Java Code**
```java
public class ShardingPerformanceTest {
public static void main(String[] args) throws Exception {
String shardingConfig = loadFromResource("sharding-config-performance-simple-test.yaml");
// for version 2.x.x
// DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingConfig.getBytes("utf-8"));
// for version 4.0.0
DataSource dataSource = YamlShardingDataSourceFactory.createDataSource(shardingConfig.getBytes("utf-8"));
// exclude first time
executeWithStatement(dataSource, "select * from ts_order where id = 0");
executeWithPrepareStatement(dataSource, "select * from ts_order where id = ?", 0);
System.out.println("Statement select test(ms): ");
for (int i = 0; i < 10; i++) {
System.out.printf("%4d",
executeWithStatement(dataSource, "select * from ts_order where id = " + i));
}
System.out.println();
System.out.println("PreparedStatement select test(ms): ");
for (int i = 0; i < 10; i++) {
System.out.printf("%4d",
executeWithPrepareStatement(dataSource, "select * from ts_order where id = ?", i));
}
System.out.println();
}
private static long executeWithStatement(DataSource dataSource, String sql) throws SQLException{
Connection connection = dataSource.getConnection();
long begin = System.currentTimeMillis();
Statement st = connection.createStatement();
st.execute(sql);
long executeTime = System.currentTimeMillis() - begin;
st.close();
connection.close();
return executeTime;
}
private static long executeWithPrepareStatement(DataSource dataSource, String sql, Object... args) throws SQLException{
Connection connection = dataSource.getConnection();
long begin = System.currentTimeMillis();
PreparedStatement ps = connection.prepareStatement("select * from ts_order where id = ?");
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
ps.execute();
long executeTime = System.currentTimeMillis() - begin;
ps.close();
connection.close();
return executeTime;
}
private static String loadFromResource(String path) throws Exception{
BufferedReader reader = new BufferedReader(new InputStreamReader(Class.class
.getResourceAsStream("/" + path), "utf-8"));
StringBuffer sb = new StringBuffer();
CharBuffer charBuffer = CharBuffer.allocate(1024);
for (int count = reader.read(charBuffer); count > 0; count = reader.read(charBuffer)) {
sb.append(charBuffer.flip());
}
return sb.toString();
}
}
```
**Sharding config**
sharding-config-performance-simple-test.yaml
```yaml
dataSources:
ds_0: !!com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/test_d_0
username: root
password: root135
ds_1: !!com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/test_d_1
username: root
password: root135
shardingRule:
tables:
ts_order:
actualDataNodes: ds_0.ts_order_000${0..1},ds_1.ts_order_000${2..3}
databaseStrategy:
inline:
shardingColumn: id
algorithmExpression: ds_${new BigDecimal(id).abs().divideAndRemainder(4)[1].longValue().intdiv(2)}
tableStrategy:
inline:
shardingColumn: id
algorithmExpression: ts_order_${String.format("%04d",new BigDecimal(id).abs().divideAndRemainder(4)[1].longValue())}
```
----------------------------------------------------------------
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
With regards,
Apache Git Services