CODEWITHSUNDEEP
mysqlperformanceinnodb
user121196
user121196

Reputation: 30990

mysqldump behavior and related InnoDB performance

I have the following mysqldump file . I have 3 questions:

1. Are the commands within /* */ commented out or are they actually getting executed?

2. According to the official MySQL documentation it says:

The mysqldump option --opt creates dump files that are fast to import into an InnoDB table, even without wrapping them with the SET autocommit and COMMIT statements. opt is enabled by default

But mysqldump file didn’t include SET auto commit and COMMIT statements, I’m wondering if those are implicit? Or do I need to add them.

3. Even when I set unique_checks=0 and foreign_key_checks=0, if I have non-unique key, I should probably remove these keys prior to the bulk insert, and then re-add them afterwards, in order to gain performance, right?

-- Server version   5.6.10-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;



LOCK TABLES `xxx` WRITE;
/*!40000 ALTER TABLE `xxx` DISABLE KEYS */;

Upvotes: 1

Views: 903

Answers (1)

Giacomo1968
Giacomo1968

Reputation: 26066

1. Are the commands within /* */ commented out or are they actually getting executed?

Yes, they are actually being executed, but only for versions of MySQL equal to or higher than the version number of /*!40101 as shown in your example. As per the MySQL manual on comment syntax:

If you add a version number after the “!” character, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number.

Then as far as mysqldump & InnoDB performance goes:

2. But mysqldump file didn’t include SET auto commit and COMMIT statements, I’m wondering if those are implicit? Or do I need to add them.

As explained in the official MySQL performance tips:

When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET auto commit and COMMIT statements.

This is completely different from this:

If you use the mysqldump option --opt, you get dump files that are fast to import into an InnoDB table, even without wrapping them with the SET autocommit and COMMIT statements.

It’s not actually disabling SET auto commit and COMMIT statements. It simply acts on the defaults of your InnoDB setup which means it is implicitly set.

3. Even when I set unique_checks=0 and foreign_key_checks=0, if I have non-unique key, I should probably remove these keys prior to the bulk insert, and then re-add them afterwards, in order to gain performance, right?

I am pretty confident that the act of exporting via mysqldump and then importing again will automatically do house cleaning like this. Okay, dug around some more & it seems that “random” keys can negatively impact InnoDB import performance. The clear advice from Paul Dixon’s answer (bold emphasis is mine), “Your ‘real’ key field could still be indexed, but for a bulk insert you might be better off dropping and recreating that index in one hit after the insert in complete.”

Upvotes: 2

Related Questions