MySQL Blog Archive
For the latest blogs go to
Automatic Schema Synchronization in NDB Cluster 8.0: Performance Schema Tables

The Automatic Schema Synchronization mechanism, detailed in a two part series: part 1 and part 2, automatically detects and synchronizes mismatches in metadata between NDB Dictionary and MySQL Server’s Data Dictionary. Information about the mechanism is exposed through different MySQL Server status variables. These variables deal with the count of metadata objects in different states of detection and synchronization. Users had to delve into the error log for granular details about the objects. This has been improved in NDB Cluster 8.0.21 by the introduction of two Performance Schema tables: ndb_sync_pending_objects and ndb_sync_excluded_objects. These tables are present only if support for the NDB Storage Engine has been enabled for the MySQL Server.

The ndb_sync_pending_objects table displays information about NDB metadata objects that have been detected as mismatches by the Metadata Change Monitor component. The contents of the table represent the metadata objects awaiting synchronization by the NDB Event Handling component. The row corresponding to the object is removed when the Event Handler picks the object up for synchronization.

Should the synchronization of the object fail due to a temporary error, the object remains eligible for detection by the Metadata Change Monitor in its subsequent runs. The mismatch will then be detected again, as can be seen by monitoring the ndb_sync_pending_objects table, and its synchronization will be retried. If the synchronization of the object fails with a permanent error, the object will be excluded from mismatch detection by the Metadata Change Monitor. Information about these excluded objects can be found in the ndb_sync_excluded_objects table.

Using The New Performance Schema Tables

To demonstrate how the two new tables can be used to track the progress of automatic schema synchronization, we consider a scenario involving the native backup and restore of metadata objects in the NDB Dictionary.

Create NDB Metadata

Create A Native NDB Cluster Backup

A backup of NDB metadata and data is created from the ndb_mgm management client.

Drop Metadata

Restore The NDB Cluster Backup

The backup taken earlier is restored using the ndb_restore utility. For the sake of demonstration, the synchronization of tables t1 and t2 should fail in order for their information to be displayed in the ndb_sync_excluded_objects table. This is achieved by using the disable-indexes command-line option which disables the restoration of indexes from the backup. The anomaly in table definitions is detected by the NDB Event Handler and the tables aren’t synchronized successfully.

Querying The Performance Schema Tables

Schema db1 and tables t1 and t2 are synchronized to the MySQL Server by using the ndb_metadata_sync system variable. The variable is automatically set to OFF once all mismatched objects have been synchronized.

Querying the ndb_sync_pending_objects table at this point in time will result in an empty set returned since all objects have been synchronized.

The mechanism is designed such that detection and synchronization is done by different components as explained earlier. This leads to mismatched objects being synchronized fairly quickly depending on a number of factors. As a result, rows in the ndb_sync_pending_objects table may be short-lived. The below example information has been captured by enabling a special debug flag that delays the synchronization of the detected objects.

The SCHEMA_NAME column represents the schema in which the object resides (NULL for tablespaces and logfile groups), the NAME column represents the name of the object (NULL for schemas), and the TYPE column denotes the type of the object which is one of LOGFILE GROUP, TABLESPACE, SCHEMA, or TABLE.

The next step is to check if the metadata objects have been synchronized successfully.

The above tells us that schema db1 has been successfully synchronized but tables t1 and t2 are still missing from the MySQL Server’s perspective. The ndb_sync_excluded_objects table is our next stop.

The first three columns are equivalent to their namesakes in the ndb_sync_pending_objects table. The additional REASON column sheds light on why the synchronization of the table was unsuccessful.

Another change in this area in NDB 8.0.21 is that the status variable Ndb_metadata_blacklist_size is now deprecated and will be replaced in a subsequent release. In any case, it is recommended that the ndb_sync_excluded_objects table is used in place of the status variable.

Deprecation (and eventual replacement) of the above status variable is a part of our broader effort to remove terms with negative origins.