Копирование большого объёма данных из одной таблицы в другую

Question

[Иван Пантелеев]

Есть два таблицы: pos, temp_pos. В первой таблице содержится немногим более 350 млн записей, во второй около 3 млн записей. Требуется максимально эффективно перенести данные из одной таблицы в другую. Интересует вот какие моменты:
1. Какой движок для этой задачи лучше подойдёт InnoDB или всё же MyISAM?
2. Требуется ли отключать проверку ключей и включать её после? Ускоряет ли это процесс или наоборот замедляет?

Сейчас я делаю это так:

ALTER TABLE pos DISABLE KEYS;
INSERT INTO pos (id, position, url_id, dataset_id) SELECT id, position, url_id, dataset_id from temp_pos;
ALTER TABLE pos ENABLE KEYS;

Сейчас используется MyISAM и операция переноса данных занимает в среднем от 40 минут до 2.5 часа. Хочется делать это максимально эффективно.

Answer 1

[Виталий]

MyISAM для вставок должен быть побыстрее чем InnoDB ( www.opennet.ru/tips/info/1958.shtml )

попробуйте такой метод:

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET SESSION tx_isolation='READ-UNCOMMITTED';
SET sql_log_bin = 0;
ALTER TABLE pos DISABLE KEYS;

INSERT INTO pos (id, position, url_id, dataset_id) SELECT id, position, url_id, dataset_id from temp_pos;

ALTER TABLE pos ENABLE KEYS;
SET sql_log_bin = 1;
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
SET SESSION tx_isolation='READ-REPEATABLE';

Comments

[Виталий]

И еще, если таблицы одинаковые по структуре, то стоит попробовать сначала скопировать temp_pos в другую временную таблицу, потом во временную добавить записи из pos, удалить pos и переименовать временную таблицу в pos.
MySQL должен соптимизировать полное копирование табицы (insert into temp_pos_temp select * from temp_pos). Дальнейшая вставка 3млн должна пройти заметно быстрее.

[Иван Пантелеев]

А что вообще скажите, по поводу работы с таким большим объёмом данных? Использование реляционных БД в чистом виде тут допустимо или лучше всё же использовать какие-нибудь кешируюшие средства в данном случае?

[Иван Пантелеев]

Ещё подскажите, что скажите по поводу следующего метода, он тут продуктивнее будет?
А то поговаривают, что во время загрузки таблицы из текстового файла, использование LOAD DATA INFILE работает в среднем в 20 раз быстрее, чем INSERT.

SELECT * FROM tbl1 INFO OUTFILE '/tmp/tbl1.txt';
LOAD DATA INFILE '/tmp/tbl1.txt' INTO TABLE tbl2;

вместо:

INSERT INTO tbl2 SELECT * from tbl1;

[Виталий]

>> А что вообще скажите, по поводу работы с таким большим объёмом данных?
Опишите задачу. Нужно знать как эти данные дальше используются. Мне, например, сложно представить, зачем 300млн копировать из одного места в другое, в котором в 100 раз меньше данных.
>> Ещё подскажите, что скажите по поводу следующего метода, он тут продуктивнее будет?
Протестируйте, но думаю что не будет. Большие расходы на создание и чтение файла (парсинг).

[Иван Пантелеев]

Прошу прощения, я перепутал местами количество данных в таблицах.

Answer 2

[KEKSOV]

На офсайте есть много полезного на эту тему. И Google по запросу mysql speedup insert select может помочь

Если будете использовать InnoDB, то добавьте в заголовок еще SET autocommit=0; а после инсерта добавьте COMMIT;

Answer 3

[edogs]

Доступ к ФС есть?
Если да. то
а) Можно скопировать файлы баз данных (myd,frm,myi), предварительно остановив БД.
б) Можно воспользоваться mysqlhotcopy (делает почти то же что пункт А, только умнее и не останавливая всю БД).

p.s.: Если данные из одной таблицы в другую надо именно добавлять, тогда вышеописанное годится для «выброса» данных из рабочей таблицы во временную, что бы рабочая уже дальше работала пока идет долгое копирование через insert into

Answer 4

[wartur]

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

Answer 5

[Vampiro]

В первой таблице содержится около 3 млн записей, во второй немногим более 350 млн записей

Мне кажется, выгоднее переименовать таблицы, поменяв их местами. Вставлять 3 проще, чем 350. Если же вам необходимо постоянно гонять 350кк записей из таблицы в таблицу — вероятно у вас что-то не так с архитектурой, и следует подумать в сторону триггеров или чего-то кеширующего по дороге от данных к мускулю. Вроде мемкеш сейчас делают простенькую бд, но я тут не владею информацией. надо смотреть по приложению — куда складывать.

Comments

[Иван Пантелеев]

Прошу прощения, я перепутал местами количество данных в таблицах.

[Vampiro]

тогда смените тип таблицы на Inno, поскольку сейчас вся ваша таблица лочится при операции вставки.

[Иван Пантелеев]

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

[Vampiro]

а запостите show create table сюда, плиз.

[balloon]

А в каких случаях MyISAM выигрывает в скорости InnoDB на текущих версиях MySQL? www.mysqlperformanceblog.com/2013/05/22/mysql-and-...

Answer 6

[balloon]

У нас похожие задачи с сравнимым объемом данных. SELECT + LOAD на InnoDB занимает минуты (SSD + ~120Gb RAM). Ключи не отключаем, как и autocommit - имхо при атомарных запросах смысла в этом нет, индексы и так будут обновлены только после окончания записи всех данных. Делаем LOAD IGNORE что бы игнорить дубликаты. Еще одно переимущество SELECT + LOAD перед SELECT INSERT - отсутствие блокировки на время SELECT.