MySQL использует невероятное количество памяти для импорта 1-2 ГБ файлов SQL

У меня есть сервер MySQL, работающий на машине Ubuntu 18.04 через Docker. В машине 32 ГБ оперативной памяти.

У меня около 300 файлов SQL размером 1-2 ГБ, которые необходимо импортировать в базу данных на сервере в одну таблицу.

Сама база данных полностью пуста, а таблица всего одна.

Когда я пытаюсь импортировать некоторые файлы, я вижу всплеск использования памяти до 32 ГБ (100%), а после выделения 100 ГБ памяти подкачки максимальное значение, которое я видел, составляет 60 ГБ. (60 ГБ + 32 ГБ = 92 ГБ !!!)

Принимая во внимание, что MySQL пытается использовать 92 ГБ ОЗУ для импорта файла SQL размером 1 ГБ в одну таблицу в пустой базе данных, что он может делать? Это не похоже на утечку памяти, потому что после завершения импорта файла память становится нераспределенной.

Я должен, вероятно, упомянуть, что MySQL использует том Docker, к которому хост имеет прямой доступ к файлам для хранения своих данных.

Я пробовал много разных конфигураций, чтобы решить эту проблему, и, кроме того, иногда я получаю MySQL server has gone away ошибка.

Я пробовал следующее:

Изменение таблицы с InnoDB на MyISAM и запуск ALTER TABLE tbl_name DISABLE KEYS
Настройка autocommit=0, unique_checks=0, foreign_key_checks=0
Настройка max_allowed_packet=999999999G и соответствующие переменные тайм-аута на аналогичные значения
С помощью mysqltuner.pl для генерации некоторых оптимальных параметров конфигурации InnoDB (показано ниже)

Сами файлы SQL представляют собой буквально один оператор INSERT с тысячами строк.

Что я могу сделать? Я подумал о том, чтобы разделить оператор INSERT на несколько разных INSERT, но это потребует значительного рефакторинга кода из-за многопроцессорной обработки моей программы, которая генерирует файлы SQL.

my.cnf:

[mysqld]
max_allowed_packet = 9999999G
wait_timeout = 99999999999
key_buffer_size=10M
innodb_buffer_pool_size=21G
innodb_log_file_size=2G
innodb_buffer_pool_instances=21
innodb_file_per_table
net_read_timeout=999999999999
net_write_timeout=999999999999

pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Custom config should go here
!includedir /etc/mysql/conf.d/

Проблема постоянно сохранялась даже после добавления и работы с innodb_* переменные.

показать глобальные переменные:

https://pastebin.com/raw/pXK4MgFb

Большое спасибо

Никакой объем оперативной памяти или дискового пространства в цивилизованном мире не может вместить то, что max_allowed_packet = 9999999G хочет выделить! Верните это значение по умолчанию или самое большее 256M.

Сами файлы SQL представляют собой буквально один оператор INSERT с тысячами строк.

Это оптимально.

Итак, общий размер таблицы где-то около полтерабайта? Время, необходимое для записи такого количества диска, выражается в часах, в зависимости от типа диска.

Не не использовать пространство подкачки; это только замедляет MySQL. Много. Однако ваши настройки, похоже, на самом деле не используют пространство подкачки. 21 ГБ для buffer_pool должно поддерживать использование ОЗУ в пределах физического размера 32 ГБ. Однако вы говорите, что он вырос до 100%? Думаю, я что-то упускаю. Если есть какая-либо подкачка, MySQL замедлится, поэтому немного уменьшите buffer_pool_size, чтобы избежать подкачки.

autocommit=0 с InnoDB неэффективен - если нет COMMIT, затем данные вставляются, а затем выполняется откат. Если есть COMMIT, то ему нужно проделать большую работу, чтобы подготовиться к откату. Установлен в ON.

Это 300 раз отключение и повторное включение "ключей"? Это означает, что индексы перестраиваются 300 раз. Файлы работали все медленнее и медленнее, когда вы проходили через 300?

По своему опыту могу сказать:

во время набора импорта:
- max_allowed_packet=10G (чтобы сделать память доступной для INSERT)
- innodb_buffer_pool_size=10G (или меньше), чтобы освободить память сервера, вы можете увеличить это позже, когда будете использовать сервер для запросов, но для вставок это почти бесполезно
- innodb_flush_log_at_trx_commit = 0 для лучшей производительности ввода-вывода (обязательно удалите или установите 1 или 2 для производственного использования!)

Также есть проблема с INSERT заявления, которые слишком велики для одной транзакции. Обычно, если ваша транзакция слишком велика для журнала повторов, она завершится ошибкой. Определенно установлен AUTOCOMMIT=1удалите все START TRANSACTION строки из ваших файлов дампа, и если это не помогает, увеличьте размер файла журнала: https://dba.stackexchange.com/a/1265/12685

Вдобавок я понял, что вы программно генерируете эти файлы SQL. Будет более эффективно запускать эти вставки одну за другой на сервер базы данных, особенно с настройками выше, и это будет быстро, если вы используете INSERT DELAYED.