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

DatabaseMetaData.getImportedKeys() performance is poor

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.1.2
    • Fix Version/s: 1.1.3
    • Component/s: None
    • Labels:
      None

      Description

      We have experienced performance issues (relative to the MySQL Connector/J) when performing a Hibernate SchemaUpdate on a database with ~300 tables.

      This has been tracked down to the difference in performance of DatabaseMetaData.getImportedKeys() between the two drivers. First with mariadb:

      time java -cp .:/opt/jboss7/modules/org/mariadb/main/mariadb-java-client-1.1.2.jar MariaDBMetadata
      Added tables [327]
      ...
      
      real	2m7.308s
      user	0m0.849s
      sys	0m0.150s
      

      And then with MySQL Connector/J:

      $ time java -cp .:/opt/jboss7/modules/com/mysql/main/mysql-connector-java-5.1.21-bin.jar MariaDBMetadata
      Added tables [327]
      ...
      
      real	0m1.435s
      user	0m1.312s
      sys	0m0.125s
      

      127sec vs 1.5s.

      The attached example code gets the each table defined, and then calls getImportedKeys() on each one in turn.

      Given the performance penalty of querying the information schema, it would probably be quicker to parse the output of 'show create table'.

        Attachments

          Activity

          Hide
          wlad Vladislav Vaintroub added a comment -

          Out of historical reasons, I believe. Connector/J precedes information schema, it had to use what was available back then.

          Show
          wlad Vladislav Vaintroub added a comment - Out of historical reasons, I believe. Connector/J precedes information schema, it had to use what was available back then.
          Hide
          jellis Jon Ellis added a comment -

          While i agree that querying the information schema is a much more attractive solution, it's also several orders of magnitude slower. This is impractical for our application (and i presume any other Hibernate applications using the Schema Update utility).

          We're pretty much forced to go back to using the MySQL Connector/J driver.

          Show
          jellis Jon Ellis added a comment - While i agree that querying the information schema is a much more attractive solution, it's also several orders of magnitude slower. This is impractical for our application (and i presume any other Hibernate applications using the Schema Update utility). We're pretty much forced to go back to using the MySQL Connector/J driver.
          Hide
          wlad Vladislav Vaintroub added a comment -

          I'll see what I can do here.

          Show
          wlad Vladislav Vaintroub added a comment - I'll see what I can do here.
          Hide
          jellis Jon Ellis added a comment -

          Still validating, but my initial reaction is that things look really good. Thanks!

          $ time java -cp .:/Volumes/Work/src/mariadb-java-client/target/mariadb-java-client-1.1.2.jar MariaDBMetadata
          Added tables [328]
          ...
          
          real	0m0.821s
          user	0m1.144s
          sys	0m0.118s
          

          Any schedule for a 1.1.3 release?

          Show
          jellis Jon Ellis added a comment - Still validating, but my initial reaction is that things look really good. Thanks! $ time java -cp .:/Volumes/Work/src/mariadb-java-client/target/mariadb-java-client-1.1.2.jar MariaDBMetadata Added tables [328] ... real 0m0.821s user 0m1.144s sys 0m0.118s Any schedule for a 1.1.3 release?
          Hide
          wlad Vladislav Vaintroub added a comment -

          Release plans are all in JIRA . 1.1.3 is slated for release end of this month. https://mariadb.atlassian.net/browse/CONJ/fixforversion/13100

          Show
          wlad Vladislav Vaintroub added a comment - Release plans are all in JIRA . 1.1.3 is slated for release end of this month. https://mariadb.atlassian.net/browse/CONJ/fixforversion/13100

            People

            • Assignee:
              wlad Vladislav Vaintroub
              Reporter:
              jellis Jon Ellis
            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 1 day, 2 hours
                1d 2h