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
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
