Bug #95792 DD function for fetching tables in engine
Submitted: 13 Jun 2019 15:21 Modified: 10 Jul 2019 18:39
Reporter: Magnus Blåudd Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Data Dictionary Severity:S2 (Serious)
Version:8.0.18 OS:Any
Assigned to: CPU Architecture:Any

[13 Jun 2019 15:21] Magnus Blåudd
The ndbcluster plugin need a new DD function to get a list with name of tables in a schema belonging to a certain engine. This is needed in order to efficiently synchronize the data dictionary between MySQL Servers connected to the same MySQL Cluster.

We are currently using the fetch_schema_components() function and filtering the returned list based on the dd::Tables's engine() property. This works, but have been identified as slow since every dd::Table has to be instantiated. It would be much more efficient with a function like fetch_schema_component_names() that returns just the name of those tables belonging to a certain engine.

How to repeat:
This problem was identified when running the ndb tests using valgrind, it takes ~12 seconds to get a list of 100 tables in the MySQL database. This indicates it will also be to slow in the real world as well as on slower machines. The instantiation of every table which need to be avoided.

Suggested fix:
Add new DD function fetch_schema_component_names() which takes a third argument for engine of the table.

The new function would be equivalent to:
SELECT OBJECT_NAME FROM mysql.tables WHERE SCHEMA_ID = <schema_id> and ENGINE = <engine>

Suggested function signature:
   template <typename T>
   bool fetch_schema_component_names(const Schema *schema,
                                     std::vector<String_type> *names, String_type engine) const;

or something similar where it's possible to filter the list of tables returned based on some additional arguments.
[10 Jul 2019 18:39] Daniel Price
Posted by developer:
Fixed as of the upcoming 8.0.18 release, and here's the changelog entry:

Data dictionary APIs were added for fetching table names in a schema that
use a specific storage engine, and for fetching table names in a schema
that are created and hidden by the storage engine. The former is required
by NDB, and the latter is required for DROP DATABASE operations.