Details

    • Type: Task Task
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: jdbc-1.1.3
    • Fix Version/s: None
    • Labels:
      None
    • Global Rank:
      3290

      Description

      Connector/J supports an optimized "/* ping */" query for validating a
      connection when you retrieve a connection out of a connection
      pool. This allows you to perform a ping operation at the mysql
      protocol layer, without having to actually execute a query like
      "select 1" (which in heavy applications may be too much
      overhead). This is also documented in a presentation that Mark
      Matthews once gave about high-performance with Connector/J, but I
      can't seem to find that presentation right now.

      This is documented in:

      http://dev.mysql.com/doc/refman/5.5/en/connector-j-usagenotes-j2ee-concepts-connection-pooling.html

      This is something that you would typically use in validationQuery
      parameter in a Tomcat DataSource, but unfortunately, they didn't put
      it like that in the Connector/J manual

      http://dev.mysql.com/doc/refman/5.5/en/connector-j-usagenotes-tomcat.html

      The attached patch implements this also in the MariaDB Java
      Client. The patch is sufficient to make this work for the data sources
      that I use:

      • JBoss 4.0.5
      • Apache Commons Database Pool (DBCP) 1.4. DBCP is also the underlying
        connection pool library in Tomcat.

      The patch does not implement this optimization for prepared
      statements, only for normal statements.

        Activity

        Hide
        Vladislav Vaintroub added a comment -

        Hi Davy,
        thanks for the patch .Did you measure performance improvements by the patch? There is another side of checking for ping, it means slight performance loss in normal case, due to startsWith checking.

        Show
        Vladislav Vaintroub added a comment - Hi Davy, thanks for the patch .Did you measure performance improvements by the patch? There is another side of checking for ping, it means slight performance loss in normal case, due to startsWith checking.
        Hide
        Vladislav Vaintroub added a comment -

        You can try "do 1" instead of "select 1" (I dunno if that works , since it does not create result set). "do 1" is a bit faster

        Show
        Vladislav Vaintroub added a comment - You can try "do 1" instead of "select 1" (I dunno if that works , since it does not create result set). "do 1" is a bit faster
        Hide
        Vladislav Vaintroub added a comment -

        http://assets.en.oreilly.com/1/event/21/Connector_J%20Performance%20Gems%20Presentation.pdf is the presentation given by Mark. This is the only one of this "performance" kind that I know of .There is no mentioning of /* ping */ here.

        Show
        Vladislav Vaintroub added a comment - http://assets.en.oreilly.com/1/event/21/Connector_J%20Performance%20Gems%20Presentation.pdf is the presentation given by Mark. This is the only one of this "performance" kind that I know of .There is no mentioning of /* ping */ here.
        Hide
        Davy Verstappen added a comment -

        Hello Vladislav,

        We did not measure the performance difference with the MariaDB Java Client, but we did test this (last year or so) when we reconfigured Connector/J to use this. The actual performance improvement was marginal, but then again, Connector/J already had this minor overhead ... The main improvement was that when we analyzed our queries with pt-query-digest, "select 1" obviously disappeared from the query-top-20 that pt-query-digest reports. I think this feature is also not so much about raw performance, as it is about higher scalability: less queries that reach the SQL layer means more clients can pound the server.

        As for the performance loss in the normal case: the startswith() method does not allocate any memory, and it fails-fast: so for every normal query, it will fail with the first character that it compares (which surprisingly happens to be the last one of "/* ping */"). So I believe the overhead is neglect-able, but I guess there is more optimization possible: first check: queryString.charAt(0) == '/'. That's literally in the Connector/J manual:

        http://dev.mysql.com/doc/refman/5.5/en/connector-j-usagenotes-j2ee-concepts-connection-pooling.html

        "do 1" is not an option I'm afraid: commons-dbcp checks that the validation query returns a result set with at least one row. Without that, it considers the validation query to have failed.

        That's indeed the presentation I was thinking of, but I guess my memory failed me. I must have picked this up somewhere else: either from the Connector/J manual itself, or from the Percona consulting we had last year.

        Show
        Davy Verstappen added a comment - Hello Vladislav, We did not measure the performance difference with the MariaDB Java Client, but we did test this (last year or so) when we reconfigured Connector/J to use this. The actual performance improvement was marginal, but then again, Connector/J already had this minor overhead ... The main improvement was that when we analyzed our queries with pt-query-digest, "select 1" obviously disappeared from the query-top-20 that pt-query-digest reports. I think this feature is also not so much about raw performance, as it is about higher scalability: less queries that reach the SQL layer means more clients can pound the server. As for the performance loss in the normal case: the startswith() method does not allocate any memory, and it fails-fast: so for every normal query, it will fail with the first character that it compares (which surprisingly happens to be the last one of "/* ping */"). So I believe the overhead is neglect-able, but I guess there is more optimization possible: first check: queryString.charAt(0) == '/'. That's literally in the Connector/J manual: http://dev.mysql.com/doc/refman/5.5/en/connector-j-usagenotes-j2ee-concepts-connection-pooling.html "do 1" is not an option I'm afraid: commons-dbcp checks that the validation query returns a result set with at least one row. Without that, it considers the validation query to have failed. That's indeed the presentation I was thinking of, but I guess my memory failed me. I must have picked this up somewhere else: either from the Connector/J manual itself, or from the Percona consulting we had last year.
        Hide
        Vladislav Vaintroub added a comment - - edited

        Hi Davy,

        Thanks for the explanation. Frankly speaking, I hesitate to implement this optimization at the moment- to me, it is a little obscure one. As you say, this does not bring much, and my measurements confirm it. If the main benefit is not seeing the certain query in some tool, perhaps the best for everyone would be talking to the tool author about filtering certain queries, like "/* ping / select 1" ,or / ping / / pt-query-ignore */ select 1 , or something like that

        I can confirm that nothing bad happens if query goes to SQL layer - there are no locks taken, so scalability is fine. If this query becomes a performance problem, to me it would be quite an indicator that validation query is just being called too often, and there are likely other ways to reduce the frequency.

        Show
        Vladislav Vaintroub added a comment - - edited Hi Davy, Thanks for the explanation. Frankly speaking, I hesitate to implement this optimization at the moment- to me, it is a little obscure one. As you say, this does not bring much, and my measurements confirm it. If the main benefit is not seeing the certain query in some tool, perhaps the best for everyone would be talking to the tool author about filtering certain queries, like "/* ping / select 1" ,or / ping / / pt-query-ignore */ select 1 , or something like that I can confirm that nothing bad happens if query goes to SQL layer - there are no locks taken, so scalability is fine. If this query becomes a performance problem, to me it would be quite an indicator that validation query is just being called too often, and there are likely other ways to reduce the frequency.
        Hide
        Torsten Krah added a comment - - edited

        @Davy: Why don't you use "isValid(...)" call from jdbc4. It already does a protocol.ping() - just what you are looking for - you may need to adapt your pooling code, but that shouldn't be a problem.

        Show
        Torsten Krah added a comment - - edited @Davy: Why don't you use "isValid(...)" call from jdbc4. It already does a protocol.ping() - just what you are looking for - you may need to adapt your pooling code, but that shouldn't be a problem.

          People

          • Assignee:
            Georg Richter
            Reporter:
            Davy Verstappen
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated: