#mysql #partitioning
#mysql #разделение
Вопрос:
У меня есть разделенная таблица InnoDB mysql, и мне нужно вставить сотни миллионов строк.
В настоящее время я использую LOAD DATA INFILE команду для загрузки многих (думаю, 10 тысяч) csv-файлов в указанную таблицу.
Каковы последствия для производительности, если я одновременно вставляю большие блоки данных в разные отдельные разделы?
Могу ли я извлечь выгоду из запуска нескольких процессов, каждый из которых выполняет пакеты LOAD DATA INFILE инструкций?
Прочая информация:
Аппаратное обеспечение: Intel i7, 24 ГБ оперативной памяти, Ubuntu 10.04 с MySQL 5.5.11, хранилище Raid 1
#mysql на freenode IRC сказал мне, что последствия для производительности будут такими же, как при использовании обычного InnoDB или MyISAM — InnoDB будет выполнять блокировку на уровне строк, а MyISAM — на уровне таблиц.
Структура таблицы:
CREATE TABLE `my_table` (
`short_name` varchar(10) NOT NULL,
`specific_info` varchar(20) NOT NULL,
`date_of_inquiry` datetime DEFAULT NULL,
`price_paid` decimal(8,2) DEFAULT NULL,
`details` varchar(255) DEFAULT '',
UNIQUE KEY `unique_record` (`short_name`,`specific_info`,`date_of_inquiry`),
KEY `short_name` (`short_name`),
KEY `underlying_quotedate` (`short_name`,`date_of_inquiry`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY LIST COLUMNS(short_name)*/
(PARTITION pTOYS_R_US VALUES IN ('TOYS-R-US') ENGINE = InnoDB,
PARTITION pZAPPOS VALUES IN ('ZAPPOS') ENGINE = InnoDB,
PARTITION pDC VALUES IN ('DC') ENGINE = InnoDB,
PARTITION pGUCCI VALUES IN ('GUCCI') ENGINE = InnoDB,
...on and on...
);
Комментарии:
1. «Могу ли я извлечь выгоду из запуска нескольких процессов, каждый из которых запускает пакеты инструкций LOAD DATA INFILE?» Я не знаю, я знаю, что это работает таким образом со вставками, но я не знаю ‘о загрузке данных в файл.
Ответ №1:
Не полный список, но некоторые указания…
Самый быстрый способ вставки строк — использовать LOAD DATA INFILE
Смотрите: http://dev.mysql.com/doc/refman/5.1/en/load-data.html
Если это не вариант, и вы хотите ускорить процесс, вам нужно найти узкое место и оптимизировать для этого.
Если разделы расположены по сети, сетевой трафик может привести к сбоям в работе процессора, дискового ввода-вывода и памяти, об этом скажет только профилирование образца.
Отключить обновления ключей
Если вы не можете этого сделать load data infile убедитесь, что вы отключили обновления ключей
ALTER TABLE table1 DISABLE KEYS
... lots of inserts
ALTER TABLE table1 ENABLE KEYS
Обратите внимание, что disable key updates отключаются только неуникальные ключи, уникальные ключи всегда обновляются.
Двоичный журнал
Если у вас запущен двоичный журнал, в котором будут записаны все эти вставки, рассмотрите возможность его отключения, вы можете отключить его при запущенном MySQL, используя символическую ссылку и указывая на /dev/null на время массовой вставки.
Если вы хотите, чтобы двоичный журнал сохранялся, вы можете выполнить одновременную вставку в параллельную базу данных с включенными blackhole таблицами и двоичным журналом.
Ключ автоинкремента
Если вы позволите MySQL вычислять ключ автоинкремента, это создаст конфликт вокруг генерации ключа. Рассмотрите возможность предоставления MySQL предварительно рассчитанного значения основного ключа с автоматическим добавлением вместо NULL
Уникальные ключи
Уникальные ключи проверяются при каждой вставке (на уникальность) и они отнимают много времени. Потому что MySQL необходимо выполнять полное сканирование этого индекса при каждой вставке.
Если вы знаете, что значения, которые вы вставляете, уникальны, лучше отказаться от этого требования и добавить его после того, как вы закончите.
При добавлении его обратно в MySQL потребуется много времени на проверку, но, по крайней мере, это будет делаться только один раз, а не при каждой вставке.
Комментарии:
1. Я уже выполняю вставки с загрузкой ДАННЫХ в ФАЙЛ. Я поясню это в своем вопросе. Спасибо!
2. Кроме того, поскольку я использую функцию разделения MySQL 5.5 в varchar, у меня фактически нет первичного ключа в таблице. Я знаю, что отсутствие PK обычно опрометчиво, но на самом деле мне это не нужно, и существуют ограничения с разделением, в соответствии с которыми каждый уникальный ключ в таблице должен использовать каждый столбец в выражении разделения таблицы, см.: dev.mysql.com/doc/refman/5.5/en /… . Я предоставлю более конкретную информацию о структуре моей таблицы в моем вопросе.
3. @pyrony, О да, у вас есть PK, если у вас его нет, MySQL создает его для вас за кулисами, поэтому проблема с автоинкрементом PK по-прежнему относится к вам.
4. хорошо, так что, если бы я сделал это уникальное ограничение первичным составным ключом? Устранит ли это некоторые разногласия?
5. @pyrony, не совсем / немного да (выбирайте сами) это решит только проблему с автоинкрементным первичным ключом, что составляет небольшой процент затрат времени по сравнению с проверкой уникальности для этого составного ключа.
Ответ №2:
Если вы хотите получить от этого максимальную производительность ввода-вывода, вам понадобятся разные разделы на разных дисковых томах.
Я не уверен в последствиях для производительности, если все разделы находятся на одних и тех же физических дисках, но, очевидно, что таким образом у вас больше шансов исчерпать возможности ввода-вывода.
Ответ №3:
Вероятно, это зависит от характеристик вашего компьютера, но, как бы то ни было, я попробовал это, и это определенно ускоряет выполнение моей конкретной задачи. Т.е. мне требуется около часа, чтобы загрузить все данные в один раздел. Если я не разделяю, мне приходится выполнять задачу последовательно, так что это занимает 12 * 1 = 12 часов. Однако на моей машине с 24 ядрами я могу распараллелить задачу, чтобы завершить ее всего за 1 час.