[ https://forge.continuent.org/jira/browse/SEQUOIA-885?page=all ]
Mathieu Peltier reopened SEQUOIA-885:
-------------------------------------
Assign To: (was: Mathieu Peltier)
Reopened as new sped-up foreign key fetching mechanism committed does not seem
to work with mysql: foreign keys are not fetched and will cause locking pbs. I
think Olivier will take care of this.
DB1(admin) > enable c1b1
Enabling backend c1b1 from its last known checkpoint
2007-01-30 09:32:07,045 INFO backend.DatabaseBackend.c1b1 Detected backend as:
MySQL
2007-01-30 09:32:07,060 WARN backend.DatabaseBackend.c1b1 Unable to test
ResultSet.getObject
2007-01-30 09:32:07,129 WARN backend.DatabaseBackend.c1b1 Failed to get
foreign keys
java.sql.SQLException: Table not specified.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at
com.mysql.jdbc.DatabaseMetaData.getExportedKeys(DatabaseMetaData.java:2583)
at
org.continuent.sequoia.controller.backend.DatabaseSQLMetaData.getForeignKeys(DatabaseSQLMetaData.java:621)
at
org.continuent.sequoia.controller.backend.DatabaseSQLMetaData.createDatabaseSchema(DatabaseSQLMetaData.java:184)
at
org.continuent.sequoia.controller.backend.DatabaseBackendMetaData.createDatabaseSchemaDynamically(DatabaseBackendMetaData.java:918)
at
org.continuent.sequoia.controller.backend.DatabaseBackendMetaData.getDatabaseSchema(DatabaseBackendMetaData.java:954)
at
org.continuent.sequoia.controller.backend.DatabaseBackend.checkDatabaseSchema(DatabaseBackend.java:1799)
at
org.continuent.sequoia.controller.virtualdatabase.VirtualDatabase.getAndCheckBackend(VirtualDatabase.java:2105)
at
org.continuent.sequoia.controller.virtualdatabase.VirtualDatabase.enableBackendFromCheckpoint(VirtualDatabase.java:1723)
at
org.continuent.sequoia.controller.virtualdatabase.VirtualDatabase.enableBackendFromCheckpoint(VirtualDatabase.java:1777)
at
org.continuent.sequoia.controller.virtualdatabase.management.VirtualDatabase.enableBackendFromCheckpoint(VirtualDatabase.java:141)
> Fetching schema is too slow on PostgreSQL when there are too many tables
> ------------------------------------------------------------------------
>
> Key: SEQUOIA-885
> URL: https://forge.continuent.org/jira/browse/SEQUOIA-885
> Project: Sequoia
> Type: Improvement
> Components: Core
> Environment: This problem concerns all versions of Sequoia released so far.
> At least with PostgreSQL backends. I didn't test it on other RDBMS.
> Reporter: Guillaume Smet
> Fix For: Sequoia 2.10.5, Sequoia 3.0
> Attachments: foreign_keys_detection.diff
>
>
> Hi Sequoia team and happy new year to everyone.
> This improvement request is a followup of the following thread:
> https://forge.continuent.org/pipermail/sequoia/2006-December/004229.html .
> When fetching the database schema of a database containing a lot of tables
> (PostgreSQL backend), it is far too slow (5 minutes for 200+ tables in our
> case). It's really a problem because the schema is fetched every time a
> backend is enabled (and backuped).
> This is due to the discovery of foreign keys because the query generated by
> the getExportedKeys/getImportedKeys methods of the PostgreSQL JDBC driver is
> slow. It can be seen on this SQL queries profile obtained during a the
> activation of a backend:
> http://people.openwide.fr/~gsmet/sequoia/enable_backend_optimization/sequoia_enable_backend.html
> I tested a few things and it happens that the query is far faster without any
> schema/table name filter (100 ms instead of 2000 ms). It's not really normal
> and I'll see with PostgreSQL JDBC drivers developer what we can do about this
> problem.
> Anyway, I decided to test what I can do by removing the per table foreign
> keys detection for a global one. I obtained far better results: it's nearly
> instantaneous. I only tested this change with PostgreSQL but it should work
> with any database as the behaviour of getExportedKeys when we don't pass any
> filter is fixed per spec.
> The SQL queries profile with my patch applied can be found here:
> http://people.openwide.fr/~gsmet/sequoia/enable_backend_optimization/sequoia_enable_backend_new.html
> I attached a patch against 2.10 branch as it's the one I used currently and
> it's a real problem for us. I checked there's no conflict with what has been
> done in HEAD atm.
> Feedback really welcome. I'd like to be sure I didn't do anything wrong.
> --
> Guillaume
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://forge.continuent.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
_______________________________________________
Sequoia mailing list
[email protected]
https://forge.continuent.org/mailman/listinfo/sequoia
