MySQL creates tables very slow

Question

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

CREATE TABLE blah (id BIGINT UNSIGNED NOT NULL PRIMARY KEY) ENGINE='InnoDB';

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?

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.