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

DatabaseMetaData.getImportedKeys() performance is poor

    Details

    • Type: Bug
    • Status: Closed
    • 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'.

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            wlad Vladislav Vaintroub added a comment -

            Please also attach DDL for tables in question, to be able to reproduce. thanks!

            Show
            wlad Vladislav Vaintroub added a comment - Please also attach DDL for tables in question, to be able to reproduce. thanks!
            Hide
            jellis Jon Ellis added a comment -

            Unfortunately i'm not able to make the schema public.

            However, you should be able to see this performance difference in any schema with a large number (hundreds) of tables.

            Show
            jellis Jon Ellis added a comment - Unfortunately i'm not able to make the schema public. However, you should be able to see this performance difference in any schema with a large number (hundreds) of tables.
            Hide
            wlad Vladislav Vaintroub added a comment - - edited

            The only thing that I'm interested in the schema is how many foreign keys are there. I thought about a compromise solution , SHOW CREATE TABLE to check if foreign constraint is present, and if so, issue the original query. SHOW is cheap, I expect foreign keys in mysql not to be in very common use , so this sounds as OK solution to me. fully parsing SHOW CREATE a la ConnectorJ is something I'd like to avoid.

            Show
            wlad Vladislav Vaintroub added a comment - - edited The only thing that I'm interested in the schema is how many foreign keys are there. I thought about a compromise solution , SHOW CREATE TABLE to check if foreign constraint is present, and if so, issue the original query. SHOW is cheap, I expect foreign keys in mysql not to be in very common use , so this sounds as OK solution to me. fully parsing SHOW CREATE a la ConnectorJ is something I'd like to avoid.
            Hide
            jellis Jon Ellis added a comment - - edited

            In the database used to generate the above timings there are 479 FKs. I'm not sure why you think that they would be uncommon; in schemas generated by tools like Hibernate they are common place. Your compromise solution would make little to no difference for us as the overhead is coming from the repeat execution of the problem query more times than there are tables.

            Understand the reluctance to resort to a solution parsing SHOW CREATE, but have had very little luck trying to optimize the query that you are using. There are some notes on the issue here:

            https://dev.mysql.com/doc/refman/5.5/en/information-schema-optimization.html

            Perhaps you'll find something i've missed. However, my expectation is that the Connector/J code takes the SHOW CREATE route out of necessity, and not choice.

            Show
            jellis Jon Ellis added a comment - - edited In the database used to generate the above timings there are 479 FKs. I'm not sure why you think that they would be uncommon; in schemas generated by tools like Hibernate they are common place. Your compromise solution would make little to no difference for us as the overhead is coming from the repeat execution of the problem query more times than there are tables. Understand the reluctance to resort to a solution parsing SHOW CREATE, but have had very little luck trying to optimize the query that you are using. There are some notes on the issue here: https://dev.mysql.com/doc/refman/5.5/en/information-schema-optimization.html Perhaps you'll find something i've missed. However, my expectation is that the Connector/J code takes the SHOW CREATE route out of necessity, and not choice.
            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