vertx-sql-client icon indicating copy to clipboard operation
vertx-sql-client copied to clipboard

Add support for caching MySQL resultset metadata info

Open BillyYccc opened this issue 5 years ago • 1 comments

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.

BillyYccc avatar Apr 06 '20 17:04 BillyYccc

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.

BillyYccc avatar Apr 08 '20 12:04 BillyYccc