Очень медленно добавляется таблица mysql8. В чем причина?

Question

[Игорь]

Очень медленно добавляется таблица mysql8. В чем причина?

CREATE TABLE `XXXX` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `external_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

Сервер с 64G оперативы.

Конфиг

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log

sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

max_allowed_packet = 16M
key_buffer_size = 16M
innodb_buffer_pool_size = 32768M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0

innodb_flush_method = O_DIRECT

max_connections = 6000

slow_query_log = /var/log/mysql/mysql-slow.log
long_query_time = 1

skip-log-bin

Comments

[idShura]

Очень медленно

Это сколько в минутах/секундах?

Answer 1

[Sergey В.]

Если разовое добавление - отключите индексы и включите их обратно после INSERTa. Это поможет заметно ускорить добавление данных.

Comments

[Игорь]

create table - добавление таблиц

Answer 2

[Vitaly Karasik]

Действительно странно, должно быть очень быстро.
Кстати, медленно - это сколько в секундах?
А сервер нагружен?

Что-то пишет в error.log?

Comments

[Игорь]

В error.log ничего. Медленных запросов сейчас не висит.

[Vitaly Karasik]

Игорь,
1) и это поведение постоянно?
2) как другие операции?
3) что показывает 'top' в линуксе?

Answer 3

[d'Ivan]

Из MySQL table creation insanely slow

SET profiling = 1;
CREATE TABLE blah (id BIGINT UNSIGNED NOT NULL PRIMARY KEY);
SET profiling = 1;

На самом деле, в конце должно быть = 0, чтобы убрать профилирование.

Есть подозрение, что есть проблема с диском.

Comments

[Игорь]

+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000137 |
| Executing hook on transaction | 0.000012 |
| starting | 0.000017 |
| checking permissions | 0.000009 |
| Opening tables | 0.046477 |
| creating table | 5.741879 |
| After create | 0.000259 |
| waiting for handler commit | 0.000033 |
| waiting for handler commit | 0.496008 |
| query end | 0.000066 |
| closing tables | 0.000010 |
| waiting for handler commit | 0.000034 |
| freeing items | 0.000137 |
| logging slow query | 0.000043 |
| cleaning up | 0.000019 |
+--------------------------------+----------+
15 rows in set, 1 warning (0.00 sec)

[Игорь]

SHOW PROFILE CPU FOR QUERY 1;
+--------------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting | 0.000137 | 0.000000 | 0.000000 |
| Executing hook on transaction | 0.000012 | 0.000000 | 0.000000 |
| starting | 0.000017 | 0.000000 | 0.000000 |
| checking permissions | 0.000009 | 0.000000 | 0.000000 |
| Opening tables | 0.046477 | 0.004000 | 0.000000 |
| creating table | 5.741879 | 1.840000 | 0.468000 |
| After create | 0.000259 | 0.000000 | 0.000000 |
| waiting for handler commit | 0.000033 | 0.000000 | 0.000000 |
| waiting for handler commit | 0.496008 | 0.304000 | 0.036000 |
| query end | 0.000066 | 0.000000 | 0.000000 |
| closing tables | 0.000010 | 0.000000 | 0.000000 |
| waiting for handler commit | 0.000034 | 0.000000 | 0.000000 |
| freeing items | 0.000137 | 0.000000 | 0.000000 |
| logging slow query | 0.000043 | 0.000000 | 0.000000 |
| cleaning up | 0.000019 | 0.000000 | 0.000000 |
+--------------------------------+----------+----------+------------+
15 rows in set, 1 warning (0.00 sec)

[d'Ivan]

Игорь, из лога видно, что "creating table " выполняется 5.7сек, что действительно долго.
Есть два предложения:

1. замерить создание таблицы в памяти:
   CREATE TABLE t (i INT) ENGINE = MEMORY;
   Если время создания будет быстрым, то причина тогда в дисковом I/O, возможно.
   Интересно мнение Vitaly Karasik на этот счет.
   
   
2. Советую провести более тщательный мониторинг при помощи утилиты atop в промежуток создания таблицы.
   https://haydenjames.io/use-atop-linux-server-perfo...
   https://haydenjames.io/linux-server-performance-di...
   Ее можно быстро установить, определить причину и затем удалить.
   
   

[Vitaly Karasik]

Роман Мирр, согласен!
Как я уже писал, интересно увидеть насколько машина загружена.

[Игорь]

[Игорь]

Увеличил innodb_log_file_size = 512M
Ускорилось

SHOW PROFILE CPU FOR QUERY 1;
+--------------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting | 0.000235 | 0.000000 | 0.000000 |
| Executing hook on transaction | 0.000017 | 0.000000 | 0.000000 |
| starting | 0.000033 | 0.000000 | 0.000000 |
| checking permissions | 0.000019 | 0.000000 | 0.000000 |
| Opening tables | 0.001020 | 0.000000 | 0.000000 |
| creating table | 0.716728 | 0.124000 | 0.156000 |
| After create | 0.000670 | 0.000000 | 0.000000 |
| waiting for handler commit | 0.000026 | 0.000000 | 0.000000 |
| waiting for handler commit | 0.212517 | 0.000000 | 0.032000 |
| query end | 0.000031 | 0.000000 | 0.000000 |
| closing tables | 0.000011 | 0.000000 | 0.000000 |
| waiting for handler commit | 0.000052 | 0.000000 | 0.000000 |
| freeing items | 0.000047 | 0.000000 | 0.000000 |
| cleaning up | 0.000060 | 0.000000 | 0.000000 |
+--------------------------------+----------+----------+------------+
14 rows in set, 1 warning (0.00 sec)