You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@iotdb.apache.org by 本来无一物 <33...@qq.com> on 2019/12/14 01:34:39 UTC
线程插入只有布尔值的共2000w条记录查询慢的问题
创建的结构如上：修改了JDBCExample如下:环境是CentOS release 6.10 (Final) 内存8G 插入到500w的时候明显开始count时间有上升的趋势，2000w插满后count
需要26秒，重新启动lotDB后count 只要0.01秒
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class JDBCExample {
/**
* Before executing a SQL statement with a Statement object, you need to create a Statement object using the createStatement() method of the Connection object.
* After creating a Statement object, you can use its execute() method to execute a SQL statement
* Finally, remember to close the 'statement' and 'connection' objects by using their close() method
* For statements with query results, we can use the getResultSet() method of the Statement object to get the result set.
*/
public static void main(String[] args) throws SQLException, ParseException {
Connection connection = getConnection();
if (connection == null) {
System.out.println("get connection defeat");
return;
}
int[] days={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20};
int i=days.length;
while (i-->0){
AddInThread addInThread=new AddInThread(getConnection(),days[i]);
addInThread.start();
}
}
public static Connection getConnection() {
// JDBC driver name and database URL
String driver = "org.apache.iotdb.jdbc.IoTDBDriver";
String url = "jdbc:iotdb://192.168.5.244:6667/";
// Database credentials
String username = "root";
String password = "root";
Connection connection = null;
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
/**
* This is an example of outputting the results in the ResultSet
*/
private static void outputResult(ResultSet resultSet) throws SQLException {
if (resultSet != null) {
System.out.println("--------------------------");
final ResultSetMetaData metaData = resultSet.getMetaData();
final int columnCount = metaData.getColumnCount();
for (int i = 0; i < columnCount; i++) {
System.out.print(metaData.getColumnLabel(i + 1) + " ");
}
System.out.println();
while (resultSet.next()) {
for (int i = 1; ; i++) {
System.out.print(resultSet.getString(i));
if (i < columnCount) {
System.out.print(", ");
} else {
System.out.println();
break;
}
}
}
System.out.println("--------------------------\n");
}
}
}
class AddInThread extends Thread {
final Connection connection;
final int day;
final String datetimeText;
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS");
AddInThread(Connection connection,int days) {
this.connection = connection;
day = days;
datetimeText="2019-11-"+days+" 10:34:59:000";
}
@Override
public void run() {
try {
Date datetime = format.parse(datetimeText);
long times=datetime.getTime();
Statement statement = connection.createStatement();
Integer i = 100;
while (i-- >0) {
statement.execute("insert into root.ch.baby.d01(timestamp,status) values("+times+++",true)");
}
statement.close();
connection.close();
} catch (ParseException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Re: 线程插入只有布尔值的共2000w条记录查询慢的问题
Posted by Jialin Qiao <qi...@apache.org>.
Hi,
Thanks for reporting issues. This is really an extreme case that only one
time series is written. In this case, most data is in the write buffer and
does not trigger the flush.
Currently, our memory structure does not have statistics and needs to sort
when query.
In most cases we face, there are tens of thousands of time series being
written simultaneously. Therefore, data points in memory of each time
series could not reach that much.
There are some approaches to accelerate:
(1) Executing a "flush" statement periodically.
(2) Turn down the memtable_size_threshold in iotdb-engine.properties. Then,
the first approach is not needed.
We will consider improving the performance in this extreme case in the next
version.
Thank again.
Jialin Qiao
本来无一物 <33...@qq.com> 于2019年12月14日周六 下午3:53写道：
>
> 创建的结构如上：
> 修改了JDBCExample如下:环境是CentOS release 6.10 (Final) 内存8G
> 插入到500w的时候明显开始count时间有上升的趋势，2000w插满后count
> 需要26秒，重新启动lotDB后count 只要0.01秒
>
> import java.sql.*;
> import java.text.ParseException;
> import java.text.SimpleDateFormat;
> import java.util.Date;
>
> public class JDBCExample {
> /**
> * Before executing a SQL statement with a Statement object, you need to create a Statement object using the createStatement() method of the Connection object.
> * After creating a Statement object, you can use its execute() method to execute a SQL statement
> * Finally, remember to close the 'statement' and 'connection' objects by using their close() method
> * For statements with query results, we can use the getResultSet() method of the Statement object to get the result set.
> */
> public static void main(String[] args) throws SQLException, ParseException {
> Connection connection = getConnection();
> if (connection == null) {
> System.out.println("get connection defeat");
> return;
> }
> int[] days={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20};
>
> int i=days.length;
> while (i-->0){
> AddInThread addInThread=new AddInThread(getConnection(),days[i]);
> addInThread.start();
> }
> }
>
> public static Connection getConnection() {
> // JDBC driver name and database URL
> String driver = "org.apache.iotdb.jdbc.IoTDBDriver";
> String url = "jdbc:iotdb://192.168.5.244:6667/";
>
> // Database credentials
> String username = "root";
> String password = "root";
>
> Connection connection = null;
> try {
> Class.forName(driver);
> connection = DriverManager.getConnection(url, username, password);
> } catch (ClassNotFoundException e) {
> e.printStackTrace();
> } catch (SQLException e) {
> e.printStackTrace();
> }
> return connection;
>
>
> }
>
> /**
> * This is an example of outputting the results in the ResultSet
> */
> private static void outputResult(ResultSet resultSet) throws SQLException {
> if (resultSet != null) {
> System.out.println("--------------------------");
> final ResultSetMetaData metaData = resultSet.getMetaData();
> final int columnCount = metaData.getColumnCount();
> for (int i = 0; i < columnCount; i++) {
> System.out.print(metaData.getColumnLabel(i + 1) + " ");
> }
> System.out.println();
> while (resultSet.next()) {
> for (int i = 1; ; i++) {
> System.out.print(resultSet.getString(i));
> if (i < columnCount) {
> System.out.print(", ");
> } else {
> System.out.println();
> break;
> }
> }
> }
> System.out.println("--------------------------\n");
> }
> }
> }
>
> class AddInThread extends Thread {
> final Connection connection;
> final int day;
> final String datetimeText;
> SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS");
>
> AddInThread(Connection connection,int days) {
> this.connection = connection;
> day = days;
> datetimeText="2019-11-"+days+" 10:34:59:000";
> }
>
> @Override
> public void run() {
> try {
> Date datetime = format.parse(datetimeText);
> long times=datetime.getTime();
> Statement statement = connection.createStatement();
> Integer i = 100;
> while (i-- >0) {
> statement.execute("insert into root.ch.baby.d01(timestamp,status) values("+times+++",true)");
> }
> statement.close();
> connection.close();
> } catch (ParseException e) {
> e.printStackTrace();
> } catch (SQLException e) {
> e.printStackTrace();
> }
> }
> }
>
>
--
—————————————————
Jialin Qiao
School of Software, Tsinghua University
乔嘉林
清华大学 软件学院