Dolt performance comparison to postgres and mysql #6536
Description
I'm running a migration script that map objects from json files to sql statements running.
These statements are then executed using hibernate and hikari as part of an integration test in a Micronaut application, the dbms used in the test was run using docker, all containers run with the same default CPU and memory, CPU is the one of the host machine(Apple M1 Pro), memory reserved is ~ 2GiB, the container running the dolt sql server never went above ~40% of CPU and memory usage during the migration.
I collected the hibernate stats related to the performance with which the statements are run, the performance using dolt seems to be worse of a factor of 26:
Dolt
docker container dolthub/dolt-sql-server:latest no configurations specified
Session Metrics {
3360584 nanoseconds spent acquiring 1 JDBC connections;
70500 nanoseconds spent releasing 1 JDBC connections;
640493065 nanoseconds spent preparing 21238 JDBC statements;
**265238251341 nanoseconds spent executing 21238 JDBC statements; -> 265.23 secs**
0 nanoseconds spent executing 0 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
267447755375 nanoseconds spent executing 1 flushes (flushing a total of 8680 entities and 9365 collections);
0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}
Postgres
docker container postgres:latest
Session Metrics {
1234459 nanoseconds spent acquiring 1 JDBC connections;
28750 nanoseconds spent releasing 1 JDBC connections;
29524904 nanoseconds spent preparing 21238 JDBC statements;
9003947583 nanoseconds spent executing 21238 JDBC statements; -> ~ 9 secs
0 nanoseconds spent executing 0 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
9803555084 nanoseconds spent executing 1 flushes (flushing a total of 8680 entities and 9365 collections);
0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}
Mysql
docker container mysql:latest
Session Metrics {
1621750 nanoseconds spent acquiring 1 JDBC connections;
27084 nanoseconds spent releasing 1 JDBC connections;
427769459 nanoseconds spent preparing 21238 JDBC statements;
10115899259 nanoseconds spent executing 21238 JDBC statements; -> 10 secs
0 nanoseconds spent executing 0 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
11399842875 nanoseconds spent executing 1 flushes (flushing a total of 8680 entities and 9365 collections);
0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}
H2
in memory database
Session Metrics {
334958 nanoseconds spent acquiring 1 JDBC connections;
29250 nanoseconds spent releasing 1 JDBC connections;
31661047 nanoseconds spent preparing 21238 JDBC statements;
359971058 nanoseconds spent executing 21238 JDBC statements; -> ~ 0.35 secs
0 nanoseconds spent executing 0 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
1176050042 nanoseconds spent executing 1 flushes (flushing a total of 8680 entities and 9365 collections);
0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}
Is there any specific optimisation configuration i'm missing to improve the performance of dolt? I can provide a reproducible scenario with an anonymised sql dump but was wondering if these numbers make sense to you and if i was missing something obvious first.