[CONJ-998] Question regarding useServerPrepStmts documentation Created: 2022-08-15  Updated: 2023-12-13

Status: Open
Project: MariaDB Connector/J
Component/s: documentation
Affects Version/s: 3.0.7
Fix Version/s: 3.0

Type: Task Priority: Minor
Reporter: laurent perez Assignee: Diego Dupin
Resolution: Unresolved Votes: 0
Labels: documentation

Issue Links:
PartOf

 Description   

Hello

I'm migrating from mysql connector J to mariadb connector J.
I have some legacy projects using server side prepared statements.
I have read Marias' connector source code both 2.x and 3.x.
Maria's connector documentation states the following about useServerPrepStmts

The applications that repeatedly use the same queries have value to activate this option, but the general case is to use the direct command (text protocol)

My question is : why is the general case documented as set to use the direct command ?

AFAIU binary protocol is better because it has less overhead and execution benefits from a cached execution plan. There is also a safe upper limit of 8192 bytes (on the connector side when clientPrepStmts are used) so cached ones do not add much memory usage.

Of course preparing triggers a prepare & execute instead of a raw query. I'm not sure it may alter performances.

Or is the text protocol just "safer" or "better" ? Is it to avoid cache misshits ?

Thanks

 Comments   
Comment by Diego Dupin [ 2022-08-16 ]

Text is a globaly a safe default behavior. Always working without issue.

Binary protocol has usually good benefits, but that depends:

  • if missing cache, it will has an overhead of preparing before execution
  • if hitting cache, this perform better, but difference usually isn't huge, because most of the queries have simple execution plan.

This is totally depending on queries, but here is some numbers i usually see when benchmarking, in order to explain:

  • missing cache : 50% performance loss
  • hitting cache: 5-10% performance gain (because simple execution plan).

Now there is 2 others thing to consider :

  • specifically when using MySQL server : MySQL has not actively supported binary protocol, and there is still lot of errors with prepared statement on second execution (like not using the good index the second time on certain queries for example). Those are corrected on MariaDB.
  • Since MariaDB 10.6 Server with MDEV-19237, server now permits to avoid resending metadata when they haven’t changed. This concerns SQL commands that return a resultset, when using option `useServerPrepStmts`. This avoids useless information transiting on the network and parsing those metadata, and that permit huge gain (around 10-30% depending on query, metadata can be huge compare to resultset data)

So, if you use a MariaDB server version 10.6, and application doesn't execute completly differents queries, binary protocol (option 'useServerPrepStmts') is recommended.

So, yes good you ask, documentation has to be improved.

Comment by laurent perez [ 2022-08-16 ]

Thanks a lot this is a very good and detailed explanation !

Generated at Thu Feb 08 03:19:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.