On my home machine running Ubuntu 18.04 and MySQL version 8.0.12 with better hardware, table creation, database creation and so on are very slow. On my other computer with older hardware (slower SSD), older version of MySQL (5.7) and Ubuntu 16.04 everything is much faster. I copied the config in /etc/mysql/mysql.conf.d/mysqld.cnf from the faster computer to the slow one but nothing changed. How can I trace from where is this problem coming?

For example


finished for roughly 0.12~0.90 seconds and from

SHOW PROFILE for query 7;

I get

'starting', '0.000132'
'checking permissions', '0.000025'
'Opening tables', '0.000877'
'creating table', '0.092163'
'After create', '0.007109'
'query end', '0.000024'
'closing tables', '0.000007'
'freeing items', '0.000019'
'cleaning up', '0.000013'

Which is very slow compared to my work machine where the same query executes for 0.01~0.009. Any ideas on how can I see where is the problem coming from?

  • I would expect 8.0 to be significantly faster for most DDL statements. Are you doing so many DDLs that it matters?
    – Rick James
    Oct 11, 2018 at 4:25

1 Answer 1


I would put my money on the new features of MySQL 8.0, like Atomic DDL https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html

MySQL 8.0 supports atomic Data Definition Language (DDL) statements. This feature is referred to as atomic DDL. An atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic transaction. The transaction is either committed, with applicable changes persisted to the data dictionary, storage engine, and binary log, or is rolled back, even if the server halts during the operation.

Atomic DDL is made possible by the introduction of the MySQL data dictionary in MySQL 8.0. In earlier MySQL versions, metadata was stored in metadata files, nontransactional tables, and storage engine-specific dictionaries, which necessitated intermediate commits. Centralized, transactional metadata storage provided by the MySQL data dictionary removed this barrier, making it possible to restructure DDL statement operations into atomic transactions.

Note the change to a data dictionary from metadata files.

  • 1
    Well downgrading to mysql 5.7 doesn't seem to make it any faster, so it's not an issue related to the version of mysql Sep 30, 2018 at 7:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.