Hi Guillaume,
We have the same problem here (a database with 208 tables and a lot of
foreign keys). It's really slow to enable a backend and it makes also
the backup very slow.
When we fetch the schema, we also fetch information about primary keys
and foreign keys to properly handle locking. When we enable a backend,
we entirely re-fetch the schema to check its compatibility with the
virtual database (or use it as the vdb schema if this is the first
backend).
Do you see any way to optimize this process?
One thing that we could do is to reload the last known schema if the
backend is enabled from a known state. That would limit the complete
schema fetching to the initialization phase. That would probably require
to store the schema in the recovery log to associate it to known
checkpoints.
I speak generally ie in
Sequoia or in the JDBC driver or even in the system catalogs in the
case of PostgreSQL. I'm not sure the JDBC driver and catalogs were
designed for this sort of thing as I really see _a lot_ of queries
when enabling a PostgreSQL backend for this database and a few of them
are quite slow.
The way metadata fetching is implemented in PostgreSQL's JDBC driver
involves many complex joins on system tables. It happens to be very slow
(you have the same problem if you need ResultSetMetadata and you don't
enable Sequoia's MetadataCache). We should probably have a 'schema
cache' to reduce the schema fetching time especially when a lot of
tables or key constraints are enforced.
Don't hesitate to file an improvement or new feature request in JIRA to
track this issue.
Thanks for your feedback,
Emmanuel
--
Emmanuel Cecchet
Chief Scientific Officer, Continuent
Blog: http://emanux.blogspot.com/
Open source: http://www.continuent.org
Corporate: http://www.continuent.com
Skype: emmanuel_cecchet
Cell: +33 687 342 685
_______________________________________________
Sequoia mailing list
[email protected]
https://forge.continuent.org/mailman/listinfo/sequoia
