vertx-sql-client
vertx-sql-client copied to clipboard
Add support for caching MySQL resultset metadata info
Describe the feature
- Add support for MySQL CLIENT_OPTIONAL_RESULTSET_METADATA flag so we can tell the server whether the resultset metadata is necessary in the query response.
- We can cache the resultset metadata after it's retrieved from the resultset response.
Investigate whether it's worth adding this to improve MySQL query/preparedQuery performance.
I did some experiments in https://github.com/BillyYccc/vertx-sql-client/commit/da52dd1022a7e3e5a98c32072eb029ce3ca71934.
Here's the benchmark result, all queries are executed in a pool with single connection.
localhost disable cache
# Run complete. Total time: 00:02:07
Benchmark (database) (host) (password) (port) (username) Mode Cnt Score Error Units
LargeSelectBenchmark.poolPreparedQuery testschema localhost password 32776 mysql thrpt 10 475.416 ± 2.195 ops/s
LargeSelectBenchmark.pooledConnectionPreparedQuery testschema localhost password 32776 mysql thrpt 10 473.190 ± 7.482 ops/s
SingleSelectBenchmark.poolPreparedQuery testschema localhost password 32776 mysql thrpt 10 55210.490 ± 1135.469 ops/s
SingleSelectBenchmark.pooledConnectionPreparedQuery testschema localhost password 32776 mysql thrpt 10 46901.801 ± 1273.558 ops/s
localhost enable cache
# Run complete. Total time: 00:02:08
Benchmark (database) (host) (password) (port) (username) Mode Cnt Score Error Units
LargeSelectBenchmark.poolPreparedQuery testschema localhost password 32785 mysql thrpt 10 460.850 ± 3.820 ops/s
LargeSelectBenchmark.pooledConnectionPreparedQuery testschema localhost password 32785 mysql thrpt 10 451.109 ± 5.375 ops/s
SingleSelectBenchmark.poolPreparedQuery testschema localhost password 32785 mysql thrpt 10 57096.613 ± 1635.393 ops/s
SingleSelectBenchmark.pooledConnectionPreparedQuery testschema localhost password 32785 mysql thrpt 10 49239.124 ± 1509.114 ops/s
disable cache with avg 30ms latency
# Run complete. Total time: 00:02:44
Benchmark (database) (host) (password) (port) (username) Mode Cnt Score Error Units
LargeSelectBenchmark.poolPreparedQuery testschema 192.168.1.4 password 3306 mysql thrpt 10 99.001 ± 6.662 ops/s
LargeSelectBenchmark.pooledConnectionPreparedQuery testschema 192.168.1.4 password 3306 mysql thrpt 10 104.247 ± 0.986 ops/s
SingleSelectBenchmark.poolPreparedQuery testschema 192.168.1.4 password 3306 mysql thrpt 10 2582.522 ± 104.331 ops/s
SingleSelectBenchmark.pooledConnectionPreparedQuery testschema 192.168.1.4 password 3306 mysql thrpt 10 2574.744 ± 77.545 ops/s
enable cache with avg 30ms latency
# Run complete. Total time: 00:02:12
Benchmark (database) (host) (password) (port) (username) Mode Cnt Score Error Units
LargeSelectBenchmark.poolPreparedQuery testschema 192.168.1.4 password 3306 mysql thrpt 10 109.363 ± 5.294 ops/s
LargeSelectBenchmark.pooledConnectionPreparedQuery testschema 192.168.1.4 password 3306 mysql thrpt 10 113.268 ± 1.731 ops/s
SingleSelectBenchmark.poolPreparedQuery testschema 192.168.1.4 password 3306 mysql thrpt 10 2636.481 ± 99.652 ops/s
SingleSelectBenchmark.pooledConnectionPreparedQuery testschema 192.168.1.4 password 3306 mysql thrpt 10 2639.276 ± 64.608 ops/s
I found the benchmark result not promising, this approach is error-prone and does not bring significant performance improvement. I think we can revisit this when a better solution is come up with in the future.