Почему очень долго выполняеться alter table в mysql 8 ubuntu?

Question

[xiiicool]

Здравствуйте, у меня стоит ubuntu 20.04 и MySQL 8.0.31
Конфиг такой

skip-host-cache
skip-name-resolve

log_error = /var/log/mysql/error.log
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

log_bin = ON
#Из документации: If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMM                                                                                                                        ITTED,
#only row-based logging can be used. It is possible to change the logging format to STATEMENT,
#but doing so at runtime leads very rapidly to errors because InnoDB can no longer perform inserts.
binlog_format = ROW
#для дев окружения зватит 12 часов
binlog-expire-logs-seconds = 43200
max_binlog_size = 1G

#хранить каждую таблицу в отдельном файле
innodb_file_per_table = 1
#Метод сброса из памяти на диск для дев окружения O_DSYNC вместо O_DIRECT (рассмотреть возможность использования на п                                                                                                                        роде)
innodb_flush_method = O_DSYNC

#У нас нет и возможно не будет MyISAM таблиц
disabled_storage_engines = MyISAM

thread_cache_size = 16

#Рекомендуется 15-30% от ОЗУ
key_buffer_size = 3G

#каждый поток при последовательном сканировании таблиц выделяет указанный объем памяти для каждой таблицы
read_buffer_size = 512K

#актуально для запросов с "ORDER BY", т.е. для запросов, результат которых должен быть отсортирован и которые обращаю                                                                                                                        тся к таблице
read_rnd_buffer_size = 1M

# размер памяти, выделяемый InnoDB для хранения и индексов и данных.
innodb_buffer_pool_size = 8G

#на каждый инст по 1 Гб, исходя из предыдущей настройки
innodb_buffer_pool_instances = 8

#0 - лог каждую секунду, 1 - каждую транзакцию, 2 - на откуп ОС. На деве норм 0
innodb_flush_log_at_trx_commit = 0

#Режим блокировки для автоинкремента. Мы не используем автоинкремента. Из доки: Setting it to 0 or 1 can cause a huge                                                                                                                         hit in concurrency for certain workloads.
#Потому 2. Также необходимо для этого режима BINLOG_FORMAT=ROW
innodb_autoinc_lock_mode = 2

innodb_log_file_size = 512M
innodb_io_capacity_max = 6000
innodb_io_capacity = 3000
thread_cache_size = 16
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_online_alter_log_max_size = 512M
query_cache_size=50M

#innodb_flush_log_at_trx_commit = 1 
#innodb_flush_method=fdatasync

#
# * Basic Settings
#
user		= mysql
# pid-file	= /var/run/mysqld/mysqld.pid
# socket	= /var/run/mysqld/mysqld.sock
# port		= 3306
# datadir	= /var/lib/mysql


# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir		= /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 127.0.0.1
mysqlx-bind-address	= 127.0.0.1
#
# * Fine Tuning
#
#key_buffer_size		= 16M
# max_allowed_packet	= 64M
# thread_stack		= 256K

# thread_cache_size       = -1

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP

 max_connections        = 151

# table_open_cache       = 4000

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
#
# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file        = /var/log/mysql/query.log
# general_log             = 1
#
# Error log - should be very few entries.
#
#log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
 slow_query_log		= 1
 slow_query_log_file	= /var/log/mysql/mysql-slow.log
# long_query_time = 2
# log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
# server-id		= 1
# log_bin			= /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds	= 2592000
#max_binlog_size   = 100M
# binlog_do_db		= include_database_name
# binlog_ignore_db	= include_database_name

и рекомендации releem
key_buffer_size = 8388608 ### Previous value : 8388608
max_allowed_packet = 1073741824 ### Previous value : 67108864
innodb_buffer_pool_instances = 5 ### Previous value : 1
innodb_buffer_pool_size = 5368709120 ### Previous value : 134217728
max_heap_table_size = 16777216 ### Previous value : 16777216
tmp_table_size = 16777216 ### Previous value : 16777216
max_connections = 151 ### Previous value : 151
innodb_file_per_table = 1 ### Previous value : 1
innodb_redo_log_capacity = 1342177280 ### Previous value : 104857600

В таблице 6,5 млн записей, и по сути за день он не выполняется

ALTER TABLE `rates` ADD `billIncUUID` CHAR(36) BINARY, ADD CONSTRAINT `rates_billIncUUID_foreign_idx` FOREIGN KEY (`billIncUUID`) REFERENCES `billInc` (`uuid`) ON DELETE SET NULL ON UPDATE NO ACTION

Comments

[Модератор]

Фрагменты кода надо размещать в виде текста и оборачивать тэгом code для корректного отображения. Удобно делать кнопкой </>
Это обязательно, см.п.3.8 Регламента.
Сюда же относится traceback, ввод и вывод в консоли и другая структурированная текстовая инфа.

Answer 1

[AUser0]

А ALTER TABLE `rates` DISABLE KEYS перед большим запросом - делали?

Comments

[xiiicool]

Здравствуйте, не делал, сейчас проверю

[Василий]

таблица изменится быстро конечно, но потом также долго будет не доступна когда индексы будут включены.

[Akina]

Вы правда думаете, что автор на указанной версии MySQL работает с MyISAM-таблицами?

Answer 2

[Василий]

Можно попробовать добавить значение по умолчанию null, затем его удалить.
Если это не поможет, я бы сделал новую таблицу и в нее уже скопировал данные из старой.

Comments

[xiiicool]

я использую sequelize orm, она при миграции делает tmp таблицу переписывает данные

[Akina]

Можно попробовать добавить значение по умолчанию null, затем его удалить.

Ели учесть, что в запросе для поля DEFAULT не указан, что для NULLable полей эквивалентно DEFAULT NULL, то как-то не прослеживается смысл совета...

Answer 3

[Akina]

1. Вставьте в запрос явное создание индекса для работы внешнего ключа.
   
2. Выполните действия тремя отдельными ALTER TABLE и выясните, какая именно операция занимает так много времени. Попробуйте явно указать для неё алгоритм выполнения.
   
3. Спецификация CHAR(36) BINARY намекает, что там будет текстовое представление UUID. Да и имя поля намекает на то же самое. Но если так - то какой смысл в BINARY? чтобы потом надо было думать, что делать с регистрозависимостью? И вообще - почему бы не упаковать UUID в BINARY(16)? да, потребуется преобразование при вводе-выводе, зато ускорится обработка.
   
4. ON UPDATE NO ACTION - это что, `billInc` (`uuid`) неуникальное, что ли? А сколько вообще записей в `billInc`?