Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-998

Question regarding useServerPrepStmts documentation

Details

    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

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          lperez laurent perez created issue -
          diego dupin Diego Dupin added a comment -

          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.

          diego dupin Diego Dupin added a comment - 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.
          diego dupin Diego Dupin made changes -
          Field Original Value New Value
          Fix Version/s 3.0 [ 25708 ]
          lperez laurent perez added a comment -

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

          lperez laurent perez added a comment - Thanks a lot this is a very good and detailed explanation !
          JIraAutomate JiraAutomate made changes -
          diego dupin Diego Dupin added a comment -

          documentation improved

          diego dupin Diego Dupin added a comment - documentation improved
          diego dupin Diego Dupin made changes -
          Fix Version/s N/A [ 22125 ]
          Fix Version/s 3.0 (EOL) [ 25708 ]
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          julien.fritsch Julien Fritsch made changes -

          People

            diego dupin Diego Dupin
            lperez laurent perez
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.