повышение производительности файла данных загрузки mysql

Я пытаюсь массово загрузить около 12 миллионов записей в таблицу InnoDB в (локальном) mysql, используя LOAD DATA INFILE (из CSV), и обнаружил, что это занимает очень много времени.

Тип первичного ключа — UUID, и ключи в файлах данных не отсортированы.

Я разделил файл данных на файлы, содержащие 100000 записей, и импортировал их как:

mysql -e 'ALTER TABLE customer DISABLE KEYS;'
for file in *.csv
    mysql -e "SET sql_log_bin=0;SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0;
    SET AUTOCOMMIT=0;LOAD DATA INFILE '${file}' INTO TABLE table 
    FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';  COMMIT"

Это отлично работает для первых нескольких сотен тысяч записей, но затем время вставки для каждой последующей загрузки, кажется, продолжает расти (примерно с 7 секунд до примерно 2 минут на загрузку, прежде чем я ее убил).

Я работаю на машине с 8 ГБ ОЗУ и установил параметры InnoDB:

innodb_buffer_pool_size =1024M
innodb_additional_mem_pool_size =512M
innodb_log_file_size = 256M
innodb_log_buffer_size = 256M

Я также безуспешно пытался загрузить один CSV, содержащий все строки - это длилось более 2 часов, прежде чем я его убил.

Есть ли что-нибудь еще, что могло бы ускорить это, поскольку это кажется чрезмерным временем для загрузки только 12-миллионных записей?


mysql sql innodb bulk-load
person Michael    schedule 09.01.2012    source источник
comment
DISABLE KEYS не работает в InnoDB ответы об удалении и добавлении вторичных индексов хороши.   -  person KCD    schedule 26.03.2012
comment
Не могу отключить PRIMARY KEY. Или, если бы вы могли, ремонт стоил бы ужасно долго.   -  person Rick James    schedule 12.09.2016

Ответы (2)


arrow_upward
2
arrow_downward

Всегда трудно сказать, в чем причина проблем с производительностью, но вот мои 2 цента: ваш ключ, являющийся uuid, распределяется случайным образом, что затрудняет поддержку индекса. Причина в том, что ключи хранятся по диапазону в блоке файловой системы, поэтому наличие случайных uuid, следующих друг за другом, заставляет ОС читать и записывать блоки в файловую систему без использования кеша. Я не знаю, можете ли вы изменить ключ, но вы могли бы отсортировать uuid во входном файле и посмотреть, поможет ли это. К вашему сведению, чтобы лучше понять эту проблему, я бы взглянул на этот сообщение в блоге и, возможно, прочитать эту книгу высокая производительность mysql в нем есть хорошая глава о кластерном индексе innodb. Удачи!

person Assaf Karmon    schedule 09.01.2012
comment
да, кажется, что первичный ключ на основе UUID вызывал большинство проблем - изменение его в файле данных на что-то, что представляет собой просто отсортированную последовательность строк, дало скорость около 6. - person Michael; 10.01.2012
comment
innodb_buffer_pool_size = 5G тоже поможет. - person Rick James; 12.09.2016
comment
Подробнее об злах индексации UUID. И он включает обходной путь, если они относятся к типу 1. - person Rick James; 12.09.2016

arrow_upward
7
arrow_downward

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

В противном случае каждая запись вызывает пересчет индекса, и если у вас есть несколько индексов, это может ДЕЙСТВИТЕЛЬНО замедлить работу.

person cdeszaq    schedule 09.01.2012
comment
В этом случае это не поможет, потому что индекс-нарушитель является первичным ключом. Для innodb первичный ключ реализован как кластеризованный индекс, который хранит данные и индекс первичного ключа в одной и той же структуре. - person Assaf Karmon; 09.01.2012
comment
Поскольку OP не указал, и, как правило, неплохо иметь альтернативные индексы для любой таблицы, в которой хранится нетривиальная информация (и много), я предположил, что существуют другие индексы. Удаление этих дополнительных индексов повысит скорость вставки, поскольку потребуется выполнять меньше вычислений. Я согласен с тем, что индекс первичного ключа, вероятно, также вызовет проблемы, но удаление индексов все равно ускорит вставку. - person cdeszaq; 09.01.2012
comment
да таблица имела индексы на 4 других столбца. удаление их действительно улучшило производительность, но только задержало момент, когда он практически останавливался при выполнении вставок. - person Michael; 10.01.2012
comment
Всегда можно отключить индексы перед запросом LOAD DATA INFILE, а затем снова включить их после выполнения запроса. Таким образом, вы обновляете индексы только один раз вместо x раз, и ваш LOAD DATA INFILE выполняется намного быстрее. - person srchulo; 18.10.2012