MySQL InnoDB Cluster: "Key moments" while importing...

Problem description

In order to test the new environment, a relatively current database should be imported from the current productive environment to the new target architecture. The backup was created "traditionally" on the old environment with a "mysqldump", but it could not be imported into the new cluster. At least our customer reported that the import of the relatively small DB (<5 GB) did not finish even after hours.
In a joint remote session we restarted this process (import) and analyzed the behavior. It was relatively easy to see that the import of the first table did not actually finish.

The analysis phase

1. Deleting a possibly still existing table
2. Creation of the table / structure
3. Locking the table / preventing access
4. Deactivation of the indices
5. Loading the data
6. Activating the indices

DROP TABLE IF EXISTS `manufacturer`;
…
CREATE TABLE `manufacturer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
…
--
-- Dumping data for table `manufacturer`
--
LOCK TABLES `manufacturer` WRITE;
/*!40000 ALTER TABLE `manufacturer` DISABLE KEYS */;
INSERT INTO `manufacturer` VALUES (1,'VW'),(2,'BMW');
/*!40000 ALTER TABLE `manufacturer` ENABLE KEYS */;
UNLOCK TABLES;
 

Deactivating and activating the indices should accelerate the loading process. The indices only have to be built up once at the end of the import and do not have to be maintained permanently while the data is being loaded.

The Problem

To solve the problem, one must also know that an InnoDB cluster has certain requirements for the data model. For example, all tables in the DB must be managed using the InnoDB engine (ok, the product is called InnoDB Cluster for some reason ;-) ). Another requirement is that all tables must have a primary key, as this is needed for high-performance processing of transactions in the cluster. Deactivating indices is therefore not permitted.

For example, if you try to deactivate the indices in a cluster on a table, you get a warning:

MySQL localhost:3306 ssl car SQL > alter table manufacturer disable keys;
Query OK, 0 rows affected, 1 warning (0.0317 sec)
Note (code 1031): Table storage engine for 'manufacturer' doesn't have this option

On the customer's database version (and we could not reproduce this behavior in our laboratory) the DB behaved extremely strangely. The deactivation of the indices (at least the command was accepted and a warning generated) and the loading of the data went through. When activating the indices, however, nothing happened anymore. The session did not continue, but no error or warning was generated. At some point the session ran into a timeout (variable "wait_timeout") and the import was aborted. This behavior is of course a bit odd.

On one of our internal test systems, which ran on at least the same minor release, both instructions (deactivation and activation of the indices) were acknowledged with a warning. As expected, the import process ran through to the end.

The solution

In order to solve the problem, and since the amount of data and the import times were not critical, we fine-tuned the backup process. To get rid of the problematic statements, we added the additional parameter "—skip-disable-keys" to the backup process (mysqldump). This ensures that the indices are not deactivated and activated during the actual loading process. The customer was then able to successfully import the DB to the new cluster and carry out further tests.

Lessons learned:

• Insufficiently dimensioned memory areas (the customer's table had x million data records)
• Problem in the communication of the nodes (latencies, cluster variables / configuration)
• ....