MySQL creates tables very slow

MySQLmysql-8.0performanceperformance-tuning

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?

Best Answer

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.

Related Solutions

MySQL Indexing – How to Optimize MySQL Setup for Faster Index Creation

For starters, I would not touch the buffer sizes just yet. The sizes youhave in the question are monstrously too big.

Here is another observation: You have BLOB data. Ouch, your temp table is going to eat space rather quickly. You could do somehting like this:

Create a 32GB RAM Disk called /var/tmpfs by adding this line to /etc/fstab

none                    /var/tmpfs              tmpfs   defaults,size=32g        1 2

Next, create a folder called /mysqltmp and mount the RAM disk on it

mkdir /mysqltmp
chown mysql:mysql /mysqltmp
mount /mysqltmp /var/tmpfs

Add this to my.cnf and restart mysql

[mysqld]
tmpdir=/mysqltmp

Now, any tmp table made via DDL lands in the RAM disk.

Here is yet another observation: Why not create a separate table that keeps the BLOB data away from the unique names?

CREATE TABLE `data_store_name` SELECT id,uniqname FROM `data_store` WHERE 1=2;
ALTER TABLE `data_store_name` ADD PRIMARY KEY (id);
ALTER TABLE `data_store_name` ADD UNIQUE KEY (uniqname);
ALTER TABLE `data_store_name` ADD INDEX name_id_ndx (uniqname,id);
INSERT INTO `data_store_name` SELECT id,uniqname FROM `data_store`;

This will prevent any moving around of BLOB data when indexing.

From here, you would have to always join data_store using its name like this:

SELECT
    A.uniqname,B.data
FROM
    (SELECT * FROM data_store_name WHERE uniqname = 'mydataname') A
    LEFT JOIN
    data_store B USING (id)
;

Making these changes will sidestep this whole mess of dealing with keycache, RAM disks, and tmp tables.

Give it a Try !!!

Mysql – Very slow performance from MySQL

Why are you using a cursor for this type of operation?

Most RDBMS are optimised for set-based operation, a cursor or a loop are iterative operations which perform row-by-row operations.

Whenever possible, you want to use declarative set-based code.

Try this:

DELETE sb
FROM stockbatches sb
INNER JOIN batchestodelete btd
ON sb.batch_batch = btd.batchno
AND sb.batch_part = btd.batchpart

Best Answer

Related Solutions

MySQL Indexing – How to Optimize MySQL Setup for Faster Index Creation

Mysql – Very slow performance from MySQL

Related Question