Improve performance of INSERT statement #9218

walter-weinmann · 2021-09-12T13:28:36Z

walter-weinmann
Sep 12, 2021

We are pleased to release version 3.0.3 of DBSeeder (https://github.com/KonnexionsGmbH/db_seeder).

DBSeeder also supports the latest version of trino available on Docker Hub (DBMS 361) and Maven Repository (JDBC 361).

Unfortunately, the performance of INSERT operations is still very unsatisfactory.

hashhar · 2021-09-12T14:48:11Z

hashhar
Sep 12, 2021
Collaborator

Since Trino distributes writes across multiple nodes it follows something similar to a 2-phase implementation. Data is inserted into a randomly named staging table and once all tasks have written data the staging table, another task moves all data from staging table into final table. The 2nd step is where the majority of time is spent.

If you don't want the strong transactional guarantees you can disable it by setting the config property insert.non-transactional-insert.enabled to true. It'll remove any transactional guarantees but you'll gain performance. However be aware that a failed insert in that case may leave partial data written to the table.

I'll also try to get a profile where most of the time is spent to see places for improvement.

0 replies

walter-weinmann · 2021-09-12T15:57:18Z

walter-weinmann
Sep 12, 2021
Author

HI @hashhar - thanks for the response.

I suppose this parameter goes into config.properties:

coordinator=true
discovery.uri=http://localhost:8080
http-server.http.port=8080
insert.non-transactional-insert.enabled=true
node-scheduler.include-coordinator=true
query.max-memory-per-node=1GB
query.max-memory=5GB
query.max-total-memory-per-node=2GB

Unfortunately, this gives me the following issue with MySQL for example:

2021-09-12 17:56:39,564 [DatabaseSeeder.java] INFO  tickerSymbolAnyCase='mysql_trino'
2021-09-12 17:56:39,564 [DatabaseSeeder.java] INFO  Start MySQL Database via trino
2021-09-12 17:56:39,569 [AbstractDbmsSeeder.java] INFO  tickerSymbolIntern =mysql
2021-09-12 17:56:39,576 [AbstractJdbcSeeder.java] INFO  tickerSymbolExtern =mysql_trino
java.sql.SQLException: Error executing query
        at io.trino.jdbc.TrinoStatement.internalExecute(TrinoStatement.java:287)
        at io.trino.jdbc.TrinoStatement.execute(TrinoStatement.java:240)
        at ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.executeSQLStmnts(AbstractJdbcSeeder.java:1358)
        at ch.konnexions.db_seeder.jdbc.mysql.MysqlSeeder.setupDatabase(MysqlSeeder.java:143)
        at ch.konnexions.db_seeder.jdbc.AbstractJdbcSeeder.createData(AbstractJdbcSeeder.java:410)
        at ch.konnexions.db_seeder.DatabaseSeeder.main(DatabaseSeeder.java:202)
Caused by: java.io.UncheckedIOException: java.net.ConnectException: Failed to connect to localhost/[0:0:0:0:0:0:0:1]:8080
        at io.trino.jdbc.$internal.client.JsonResponse.execute(JsonResponse.java:148)
        at io.trino.jdbc.$internal.client.StatementClientV1.<init>(StatementClientV1.java:109)
        at io.trino.jdbc.$internal.client.StatementClientFactory.newStatementClient(StatementClientFactory.java:24)
        at io.trino.jdbc.TrinoConnection.startQuery(TrinoConnection.java:750)
        at io.trino.jdbc.TrinoStatement.internalExecute(TrinoStatement.java:252)
        ... 5 more
Caused by: java.net.ConnectException: Failed to connect to localhost/[0:0:0:0:0:0:0:1]:8080
        at io.trino.jdbc.$internal.okhttp3.internal.connection.RealConnection.connectSocket(RealConnection.java:265)
        at io.trino.jdbc.$internal.okhttp3.internal.connection.RealConnection.connect(RealConnection.java:183)
        at io.trino.jdbc.$internal.okhttp3.internal.connection.ExchangeFinder.findConnection(ExchangeFinder.java:224)
        at io.trino.jdbc.$internal.okhttp3.internal.connection.ExchangeFinder.findHealthyConnection(ExchangeFinder.java:108)
        at io.trino.jdbc.$internal.okhttp3.internal.connection.ExchangeFinder.find(ExchangeFinder.java:88)
        at io.trino.jdbc.$internal.okhttp3.internal.connection.Transmitter.newExchange(Transmitter.java:169)
        at io.trino.jdbc.$internal.okhttp3.internal.connection.ConnectInterceptor.intercept(ConnectInterceptor.java:41)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:142)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:117)
        at io.trino.jdbc.$internal.okhttp3.internal.cache.CacheInterceptor.intercept(CacheInterceptor.java:94)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:142)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:117)
        at io.trino.jdbc.$internal.okhttp3.internal.http.BridgeInterceptor.intercept(BridgeInterceptor.java:93)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:142)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RetryAndFollowUpInterceptor.intercept(RetryAndFollowUpInterceptor.java:88)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:142)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:117)
        at io.trino.jdbc.$internal.client.OkHttpUtil.lambda$userAgent$0(OkHttpUtil.java:65)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:142)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:117)
        at io.trino.jdbc.$internal.okhttp3.RealCall.getResponseWithInterceptorChain(RealCall.java:229)
        at io.trino.jdbc.$internal.okhttp3.RealCall.execute(RealCall.java:81)
        at io.trino.jdbc.$internal.client.JsonResponse.execute(JsonResponse.java:130)
        ... 9 more
Caused by: java.net.ConnectException: Connection refused: no further information
        at java.base/sun.nio.ch.Net.pollConnect(Native Method)
        at java.base/sun.nio.ch.Net.pollConnectNow(Net.java:669)
        at java.base/sun.nio.ch.NioSocketImpl.timedFinishConnect(NioSocketImpl.java:542)
        at java.base/sun.nio.ch.NioSocketImpl.connect(NioSocketImpl.java:597)
        at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:333)
        at java.base/java.net.Socket.connect(Socket.java:645)
        at io.trino.jdbc.$internal.okhttp3.internal.platform.Platform.connectSocket(Platform.java:130)
        at io.trino.jdbc.$internal.okhttp3.internal.connection.RealConnection.connectSocket(RealConnection.java:263)
        ... 31 more

0 replies

ebyhr · 2021-09-12T16:00:46Z

ebyhr
Sep 12, 2021
Collaborator

@walter-weinmann You should add to connectors' properties file, not config.properties.

0 replies

walter-weinmann · 2021-09-13T01:02:32Z

walter-weinmann
Sep 13, 2021
Author

With this parameter, there is a serious improvement. But even so, the performance of trino is still inferior to the worst case of all other databases (Firebird):

1'213'648 ms trino MySQL without parameter
  499'625 ms trino MySQL with parameter
   76'446 ms Firebird 
    7'272 ms MySQL direct

The same problem exists with Oracle, PostgreSQL and Microsoft SQL Server.

insert.non-transactional-insert.enabled=false:

2021-09-13 02:08:11,893 [DatabaseSeeder.java] INFO  tickerSymbolAnyCase='mysql_trino'
2021-09-13 02:08:11,894 [DatabaseSeeder.java] INFO  Start MySQL Database via trino
2021-09-13 02:08:11,898 [AbstractDbmsSeeder.java] INFO  tickerSymbolIntern =mysql
2021-09-13 02:08:11,907 [AbstractJdbcSeeder.java] INFO  tickerSymbolExtern =mysql_trino
2021-09-13 02:08:51,462 [AbstractJdbcSeeder.java] INFO  database table COUNTRY           -        200 rows in total
2021-09-13 02:08:51,463 [AbstractJdbcSeeder.java] INFO       37431 ms - total DML database table COUNTRY
2021-09-13 02:08:53,110 [AbstractJdbcSeeder.java] INFO  database table TIMEZONE          -         11 rows in total
2021-09-13 02:08:53,110 [AbstractJdbcSeeder.java] INFO        1647 ms - total DML database table TIMEZONE
2021-09-13 02:08:53,241 [AbstractJdbcSeeder.java] INFO  database table COUNTRY_STATE     -        500 rows so far
2021-09-13 02:10:41,852 [AbstractJdbcSeeder.java] INFO  database table COUNTRY_STATE     -        600 rows in total
2021-09-13 02:10:41,853 [AbstractJdbcSeeder.java] INFO      108743 ms - total DML database table COUNTRY_STATE
2021-09-13 02:10:41,960 [AbstractJdbcSeeder.java] INFO  database table CITY              -        500 rows so far
2021-09-13 02:10:42,025 [AbstractJdbcSeeder.java] INFO  database table CITY              -       1000 rows so far
2021-09-13 02:10:42,096 [AbstractJdbcSeeder.java] INFO  database table CITY              -       1500 rows so far
2021-09-13 02:15:30,151 [AbstractJdbcSeeder.java] INFO  database table CITY              -       1800 rows in total
2021-09-13 02:15:30,152 [AbstractJdbcSeeder.java] INFO      288298 ms - total DML database table CITY
2021-09-13 02:15:30,336 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -        540 rows so far
2021-09-13 02:15:30,454 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       1080 rows so far
2021-09-13 02:15:30,571 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       1620 rows so far
2021-09-13 02:15:30,685 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       2160 rows so far
2021-09-13 02:15:30,791 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       2700 rows so far
2021-09-13 02:15:30,901 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       3240 rows so far
2021-09-13 02:15:31,008 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       3780 rows so far
2021-09-13 02:15:31,128 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       4320 rows so far
2021-09-13 02:15:31,233 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       4860 rows so far
2021-09-13 02:15:31,338 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       5400 rows so far
2021-09-13 02:28:25,573 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       5400 rows in total
2021-09-13 02:28:25,573 [AbstractJdbcSeeder.java] INFO      775421 ms - total DML database table COMPANY
2021-09-13 02:28:25,574 [Statistics.java] INFO        2106 ms - total DDL
2021-09-13 02:28:25,574 [Statistics.java] INFO     1211542 ms - total DML
2021-09-13 02:28:25,574 [Statistics.java] INFO     1213648 ms - total
2021-09-13 02:28:25,576 [DatabaseSeeder.java] INFO  End   MySQL Database via trino
2021-09-13 02:28:25,576 [DatabaseSeeder.java] INFO  End

insert.non-transactional-insert.enabled=true:

2021-09-13 02:31:34,614 [DatabaseSeeder.java] INFO  tickerSymbolAnyCase='mysql_trino'
2021-09-13 02:31:34,614 [DatabaseSeeder.java] INFO  Start MySQL Database via trino
2021-09-13 02:31:34,620 [AbstractDbmsSeeder.java] INFO  tickerSymbolIntern =mysql
2021-09-13 02:31:34,628 [AbstractJdbcSeeder.java] INFO  tickerSymbolExtern =mysql_trino
2021-09-13 02:31:52,557 [AbstractJdbcSeeder.java] INFO  database table COUNTRY           -        200 rows in total
2021-09-13 02:31:52,558 [AbstractJdbcSeeder.java] INFO       16537 ms - total DML database table COUNTRY
2021-09-13 02:31:53,256 [AbstractJdbcSeeder.java] INFO  database table TIMEZONE          -         11 rows in total
2021-09-13 02:31:53,256 [AbstractJdbcSeeder.java] INFO         698 ms - total DML database table TIMEZONE
2021-09-13 02:31:53,392 [AbstractJdbcSeeder.java] INFO  database table COUNTRY_STATE     -        500 rows so far
2021-09-13 02:32:27,121 [AbstractJdbcSeeder.java] INFO  database table COUNTRY_STATE     -        600 rows in total
2021-09-13 02:32:27,122 [AbstractJdbcSeeder.java] INFO       33865 ms - total DML database table COUNTRY_STATE
2021-09-13 02:32:27,222 [AbstractJdbcSeeder.java] INFO  database table CITY              -        500 rows so far
2021-09-13 02:32:27,285 [AbstractJdbcSeeder.java] INFO  database table CITY              -       1000 rows so far
2021-09-13 02:32:27,356 [AbstractJdbcSeeder.java] INFO  database table CITY              -       1500 rows so far
2021-09-13 02:34:03,923 [AbstractJdbcSeeder.java] INFO  database table CITY              -       1800 rows in total
2021-09-13 02:34:03,924 [AbstractJdbcSeeder.java] INFO       96801 ms - total DML database table CITY
2021-09-13 02:34:04,098 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -        540 rows so far
2021-09-13 02:34:04,217 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       1080 rows so far
2021-09-13 02:34:04,334 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       1620 rows so far
2021-09-13 02:34:04,445 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       2160 rows so far
2021-09-13 02:34:04,551 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       2700 rows so far
2021-09-13 02:34:04,657 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       3240 rows so far
2021-09-13 02:34:04,763 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       3780 rows so far
2021-09-13 02:34:04,866 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       4320 rows so far
2021-09-13 02:34:04,983 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       4860 rows so far
2021-09-13 02:34:05,086 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       5400 rows so far
2021-09-13 02:39:54,273 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       5400 rows in total
2021-09-13 02:39:54,274 [AbstractJdbcSeeder.java] INFO      350350 ms - total DML database table COMPANY
2021-09-13 02:39:54,275 [Statistics.java] INFO        1372 ms - total DDL
2021-09-13 02:39:54,275 [Statistics.java] INFO      498253 ms - total DML
2021-09-13 02:39:54,275 [Statistics.java] INFO      499625 ms - total
2021-09-13 02:39:54,277 [DatabaseSeeder.java] INFO  End   MySQL Database via trino
2021-09-13 02:39:54,277 [DatabaseSeeder.java] INFO  End

pure MySQL:

2021-09-13 02:42:53,480 [DatabaseSeeder.java] INFO  tickerSymbolAnyCase='mysql'
2021-09-13 02:42:53,480 [DatabaseSeeder.java] INFO  Start MySQL Database
2021-09-13 02:42:53,486 [AbstractDbmsSeeder.java] INFO  tickerSymbolIntern =mysql
2021-09-13 02:42:53,494 [AbstractJdbcSeeder.java] INFO  tickerSymbolExtern =mysql
2021-09-13 02:42:54,798 [AbstractJdbcSeeder.java] INFO  database table COUNTRY           -        200 rows in total
2021-09-13 02:42:54,798 [AbstractJdbcSeeder.java] INFO         476 ms - total DML database table COUNTRY
2021-09-13 02:42:54,812 [AbstractJdbcSeeder.java] INFO  database table TIMEZONE          -         11 rows in total
2021-09-13 02:42:54,813 [AbstractJdbcSeeder.java] INFO          14 ms - total DML database table TIMEZONE
2021-09-13 02:42:54,923 [AbstractJdbcSeeder.java] INFO  database table COUNTRY_STATE     -        500 rows so far
2021-09-13 02:42:55,585 [AbstractJdbcSeeder.java] INFO  database table COUNTRY_STATE     -        600 rows in total
2021-09-13 02:42:55,585 [AbstractJdbcSeeder.java] INFO         772 ms - total DML database table COUNTRY_STATE
2021-09-13 02:42:55,664 [AbstractJdbcSeeder.java] INFO  database table CITY              -        500 rows so far
2021-09-13 02:42:55,717 [AbstractJdbcSeeder.java] INFO  database table CITY              -       1000 rows so far
2021-09-13 02:42:55,773 [AbstractJdbcSeeder.java] INFO  database table CITY              -       1500 rows so far
2021-09-13 02:42:57,033 [AbstractJdbcSeeder.java] INFO  database table CITY              -       1800 rows in total
2021-09-13 02:42:57,033 [AbstractJdbcSeeder.java] INFO        1447 ms - total DML database table CITY
2021-09-13 02:42:57,197 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -        540 rows so far
2021-09-13 02:42:57,371 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       1080 rows so far
2021-09-13 02:42:57,538 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       1620 rows so far
2021-09-13 02:42:57,699 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       2160 rows so far
2021-09-13 02:42:57,843 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       2700 rows so far
2021-09-13 02:42:57,984 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       3240 rows so far
2021-09-13 02:42:58,138 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       3780 rows so far
2021-09-13 02:42:58,283 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       4320 rows so far
2021-09-13 02:42:58,424 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       4860 rows so far
2021-09-13 02:42:58,573 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       5400 rows so far
2021-09-13 02:43:00,781 [AbstractJdbcSeeder.java] INFO  database table COMPANY           -       5400 rows in total
2021-09-13 02:43:00,782 [AbstractJdbcSeeder.java] INFO        3748 ms - total DML database table COMPANY
2021-09-13 02:43:00,784 [Statistics.java] INFO         812 ms - total DDL
2021-09-13 02:43:00,784 [Statistics.java] INFO        6460 ms - total DML
2021-09-13 02:43:00,785 [Statistics.java] INFO        7272 ms - total
2021-09-13 02:43:00,786 [DatabaseSeeder.java] INFO  End   MySQL Database
2021-09-13 02:43:00,786 [DatabaseSeeder.java] INFO  End

Total overview:

1 reply

hashhar Sep 13, 2021
Collaborator

Thanks for testing this. Some overhead is to be expected (since writes are done in a distributed manner and require co-ordination).

I'll look into what other places there are for improvement when writing to RDBMs. Thanks a lot for testing this and sharing the numbers.

walter-weinmann · 2021-09-13T04:19:25Z

walter-weinmann
Sep 13, 2021
Author

I am very happy to help with this problem as far as it is within my means. According to my observations, all processing at COMMIT is extremely CPU intensive. Interestingly, PostgreSQL seems to be by far the fastest and Oracle by far the worst.

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Improve performance of INSERT statement #9218

{{title}}

Replies: 5 comments 1 reply

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

Improve performance of INSERT statement #9218

walter-weinmann Sep 12, 2021

Replies: 5 comments · 1 reply

hashhar Sep 12, 2021 Collaborator

walter-weinmann Sep 12, 2021 Author

ebyhr Sep 12, 2021 Collaborator

walter-weinmann Sep 13, 2021 Author

insert.non-transactional-insert.enabled=false:

insert.non-transactional-insert.enabled=true:

pure MySQL:

Total overview:

hashhar Sep 13, 2021 Collaborator

walter-weinmann Sep 13, 2021 Author

walter-weinmann
Sep 12, 2021

Replies: 5 comments 1 reply

hashhar
Sep 12, 2021
Collaborator

walter-weinmann
Sep 12, 2021
Author

ebyhr
Sep 12, 2021
Collaborator

walter-weinmann
Sep 13, 2021
Author

hashhar Sep 13, 2021
Collaborator

walter-weinmann
Sep 13, 2021
Author