search

aspnet / DataAccessPerformance

Benchmarks, prototypes and discussions for database access performance investigation
MIT License
115 stars 23 forks source link

MySQL: Can providers leverage CLIENT_OPTIONAL_RESULTSET_METADATA? #34

Open divega opened 6 years ago

divega commented 6 years ago

As commented by @bgrainger on email:

we'd want to use MySQL Server 8.0 with the new CLIENT_OPTIONAL_RESULTSET_METADATA flag (https://dev.mysql.com/doc/refman/8.0/en/c-api-optional-metadata.html, https://dev.mysql.com/worklog/task/?id=8134) to dramatically reduce MySQL protocol bandwidth usage from sending unnecessary column metadata. This would require the "direct" API, because the client would need knowledge of the DB schema to be able to deserialise the bytes off the wire correctly.

Adding this to our list of issues here so that we can discuss it later with @bgrainger or other MySQL experts as possible optimizations. FWIW, @roji mentioned that this (the ability to execute queries without retrieving unnecessary column metadata) is one of the advantages of using prepared statements in PostgreSQL.

roji commented 6 years ago

Just to provide more detail on @divega's words above: yes, one of the advantages of a good implementation of DbCommand.Prepare() is that all necessary resultset metadata (and anything else necessary for executing the query and interpreting its result) is exchanged at preparation time, making actual execution as slim as possible. In the PostgreSQL case this includes both the resultset metadata and the actual SQL, which does not need to be transferred on every execution.

bgrainger commented 6 years ago

Prepared statements in MySQL use a (theoretically) more efficient binary format on the wire, but still send all the column metadata every time the prepared statement is executed: https://dev.mysql.com/doc/internals/en/binary-protocol-resultset.html

bgrainger commented 6 years ago

I wrote a prototype implementation of client-side support for this feature (that supports the RawDb.ReadSingleRow use case): https://github.com/mysql-net/MySqlConnector/compare/e79e8193a645a68e63b088a0c4912cbaaf70fd21...bgrainger:optional-result-set-metadata?expand=1

The results are not encouraging (I suspect they are identical within experimental inaccuracy):

_ Docker Windows
Before 36.49s 5481.0 req/s 17.27s 46,327.9 req/s
After 36.07s 5545.1 req/s 17.20s 46,514.3 req/s

I'm putting this case aside for now; can revisit when MySQL Server 8.0 is the standard benchmark DB.