CODEWITHSUNDEEP
mysqlinnodbubuntu-22.04
IvanV
IvanV

Reputation: 83

MySQL 8. Tables created too slowly

Empty server. Ubuntu 22.04
Empty database. MySQL 8.0.33.
InnoDB tables

Why each table creation lasts from 0.6 to 1 sec. There are only 3-4 columns. Without data.

mysql> show profile for query 1;
+-------------------------------+----------+
| Status                        | Duration |
+-------------------------------+----------+
| starting                      | 0.000181 |
| Executing hook on transaction | 0.000181 |
| starting                      | 0.000030 |
| checking permissions          | 0.000013 |
| creating table                | 0.733726 |
| After create                  | 0.000674 |
| waiting for handler commit    | 0.000025 |
| waiting for handler commit    | 0.088327 |
| query end                     | 0.000022 |
| closing tables                | 0.000008 |
| waiting for handler commit    | 0.000035 |
| freeing items                 | 0.000037 |
| cleaning up                   | 0.000047 |
+----------------------+----------+
14 rows in set, 1 warning (0.00 sec)

(from comment)

CREATE TABLE IF NOT EXISTS tbl (
    id smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT, 
    type tinyint(3) UNSIGNED NOT NULL, 
    limit int(10) UNSIGNED NOT NULL DEFAULT '0', 
    PRIMARY KEY (id), 
    KEY type (log_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Upvotes: 3

Views: 566

Answers (2)

Bart Mensfort
Bart Mensfort

Reputation: 1088

Current solution for our company is to force Mysql 5.7 on our computers.
https://www.devart.com/dbforge/mysql/how-to-install-mysql-on-ubuntu/

We recently changed from Ubuntu 18 (MySql 5.7) to Ubuntu 22 (MySql 8), now the testing takes 40 hours instead of 8 hours. Our customers cannot live with 5x slower performance. It's a temporary step I hope, this must be a software issue in Mysql.

Upvotes: 0

amalesh
amalesh

Reputation: 381

On my local development machine (AMD Ryzen 5 5600 / NVMe / 64GB RAM) with MySQL8.0.34 a

CREATE TABLE Test_Inno1 ( myfield CHAR(20)) ENGINE=InnoDB;

takes between 1.5sec(!) for the first time and 0.7sec for each additional CREATE. My normal scripts were twice as slow as on my 6 year old machine with SSD.

There are bug reports related to NVMe hardware issues, specifically Samsung 970 Pro and their clones: MySQL Bugreport

All attempts to solve the problem via MySQL parameters were largely ineffective or made the situation even worse.

The only thing that helped was the nobarrier way via a separate MySQL partition: MySQL write performance on I/O-barrier

Since then the first CREATE takes 0.01sec, following 0.00sec and my import scripts run 3x faster than on the old machine.

For this purpose, a separate partition is created and used as datadir for MySQL.

The .my.cnf then contains, for example:

[mysqld]
datadir = /mnt/database/mysql

In the /etc/fstab then:

UUID=the-mysql-partion-id /mnt/database/   ext4   rw,relatime,nobarrier   0   2

That's all. However, this solution is probably not recommended for production server.

Maybe helpfull: How-To Move MySQL Data Dir on Ubuntu

Upvotes: 1

Related Questions