MariaDBMetadata.java
[CONJ-35] DatabaseMetaData.getImportedKeys() performance is poor Created: 2013-05-07 Updated: 2013-06-14 Resolved: 2013-06-14 |
|
| Status: | Closed |
| Project: | MariaDB Connector/J |
| Component/s: | None |
| Affects Version/s: | 1.1.2 |
| Fix Version/s: | 1.1.3 |
| Type: | Bug | Priority: | Major |
| Reporter: | Jon Ellis | Assignee: | Vladislav Vaintroub |
| Resolution: | Fixed | Votes: | 1 |
| Labels: | None | ||
| Attachments: |
MariaDBMetadata.java
|
| 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:
And then with MySQL Connector/J:
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'. |
| Comments |
| Comment by Vladislav Vaintroub [ 2013-05-07 ] | |||||||
|
Please also attach DDL for tables in question, to be able to reproduce. thanks! | |||||||
| Comment by Jon Ellis [ 2013-05-08 ] | |||||||
|
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. | |||||||
| Comment by Vladislav Vaintroub [ 2013-05-09 ] | |||||||
|
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. | |||||||
| Comment by Jon Ellis [ 2013-05-10 ] | |||||||
|
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. | |||||||
| Comment by Vladislav Vaintroub [ 2013-05-13 ] | |||||||
|
Out of historical reasons, I believe. Connector/J precedes information schema, it had to use what was available back then. | |||||||
| Comment by Jon Ellis [ 2013-05-15 ] | |||||||
|
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. | |||||||
| Comment by Vladislav Vaintroub [ 2013-05-16 ] | |||||||
|
I'll see what I can do here. | |||||||
| Comment by Jon Ellis [ 2013-06-14 ] | |||||||
|
Still validating, but my initial reaction is that things look really good. Thanks!
Any schedule for a 1.1.3 release? | |||||||
| Comment by Vladislav Vaintroub [ 2013-06-14 ] | |||||||
|
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 |
