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

titelbild-key-moments
This article refers to a problem of one of our customers, which arose when importing a database into a new InnoDB cluster. 
Our customer is in the process of migrating his existing (outdated) MySQL 5.5 single instance DB. The target environment should consist of a three-node InnoDB cluster.

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

It could be seen that the data of the first table (several million rows) was loaded into the cluster, but the following import session was busy for several minutes (the customer reported from his attempts of hours) activating the indices on this table.
In order to better understand the import process of a mysqldump file you have to know that the import of a table consists of the following basic process steps (see also example code from one of our dump files):

  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:

Even if the error was relatively easy to explain in the end (indices are essential for the InnoDB cluster), the behavior of this system was more than strange in the beginning and could not be explained at first glance. Especially with more complex solutions such as clusters, one quickly thinks in very complex contexts:

  • Insufficiently dimensioned memory areas (the customer's table had x million data records)
  • Problem in the communication of the nodes (latencies, cluster variables / configuration)
  • ....
Often, a step back and a top view of a DBA who is still uninvolved helps to get a look at simple solutions again.

By accepting you will be accessing a service provided by a third-party external to https://blog.ordix.de/