Как увеличить скорость вставки в InnoDB таблицу?

Question

[astrotrain]

Если ли возможность увеличить скорость insert в innodb? Myisam таблица почти мгновенно заполняется результатом в 2 миллиона записей, а вот innodb - жутко долго. В противовес этому update в myisam выполняется очень медленно, а в innodb (по информации) намного быстрее. Мне нужно какой-нибудь один тип выбрать и настроить базу так чтобы и встака и обновление происходили быстро. Может можно как-то твикнуть настройки? Спасибо!

Answer 1

[Станислав Макаров]

BEGIN TRANSACTION
-- Вставляете строки порциями по 10-100К штук
COMMIT

Comments

[astrotrain]

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

[Станислав Макаров]

astrotrain можете попробовать внутри транзакции вставить ВСЕ записи, я только не могу обещать что вам хватит места в лог-файле для обработки такой транзакции. Поэтому и предложил бить на части по 10 тыщ. Если вы не начинаете транзакцию явно, то MySQL будет ее запускать и коммитить на каждый insert. В случае движка InnoDB, который ДЕЙСТВИТЕЛЬНО поддерживает транзакции, это выливается в тяжелые операции с лог-файлом и основным файлом данных.

[astrotrain]

Станислав Макаров: ну размер файла можно и увеличить, если потребуется. Меня больше интересует, как эту конструкцию на PHP реализовать запросе?

[Станислав Макаров]

astrotrain BEGIN TRANSACTION это обыкновенный SQL, просто выполните эту конструкцию. Если у того API, которым вы пользуетесь, есть поддержка транзакций, используйте ее.

[Макс]

astrotrain: у MySQL по дефолту стоит автокоммит. т.е. вставляете 10 000 строк - 10 000 раз происходит коммит. Делайте коммит на пакет инсертов.
а так - MyISAM всегда был очень быстрый на инсерт. Такой вот движок.

[astrotrain]

Макс: я просто не знаю как с помощью mysqli такое реализовать, на самом деле.

[Станислав Макаров]

astrotrain что ж тут знать-то, вот я вбил в гугле "mysqli begin transaction", получил полное совпадение вот на этой странице, php.net/manual/en/mysqli.begin-transaction.php , заодно любопытства ради заглянул сюда php.net/manual/en/mysqli.autocommit.php и сюда php.net/manual/en/mysqli.commit.php - все, все ответы получены. По кр. мере, есть что попробовать.

[astrotrain]

Станислав Макаров: да, благодарствую, барин.

Answer 2

[Пума Тайланд]

да вроде бы и апдейты были побыстрее в майисаме.

Comments

[astrotrain]

Мб, я не специалист. Заметил что таблицы в майисаме крашатся постоянно без повода.

[astrotrain]

И да, апдейт довольно медленный. Может я что-то не так, конечно, делаю.

[Пума Тайланд]

astrotrain: не встречал чтобы они крашились без причины.

[astrotrain]

Пума Тайланд: проблема в общем-то так и не решилась, что myisam что innodb - тормозят при селекте и апдейте. База не такая и большая, пару миллионов записей. А на выборку, например, select count(field) уходит до трех секунд. Даже не знаю какие настройки базы крутить.

[Пума Тайланд]

astrotrain: если по field нету индекса он и будет долго делаться не так то просто пробежать миллион другой записей

[astrotrain]

Пума Тайланд: да, действительно, спасибо, индекс улучшил картину по произовдительности.

[astrotrain]

Пума Тайланд: но, не столько как хотелось бы =\

Answer 3

[Aleksey Ratnikov]

Используйте INSERT с множественными VALUES:

INSERT INTO table (col1, col2)
VALUES
(1,2),
(3,4),
....

Это намного быстрее, чем вставлять по одной строке - индексы будут перестроены один раз после завершения транзакции. Или же же более быстрый способ - LOAD DATA INFILE, массовая загрузка данных из внешнего (локального относительно mysqld) файла.

Comments

[astrotrain]

Myisam нормально кстати вставляет, скорость вполне приемлемая. Для innodb возьму на заметку, спасибо.

[Aleksey Ratnikov]

Если вам не принципиальна целостность данных, с бд работает один пользователь или таблица используется в основном для SELECT'ов - то можете оставить MyISAM. В остальных случаях настоятельно рекомендуется использовать InnoDB. (сам Oracle и рекомендует:
> InnoDB is the default and most general-purpose storage engine, and Oracle recommends using it for tables except for specialized use cases.
dev.mysql.com/doc/refman/5.6/en/storage-engines.html)

[astrotrain]

Aleksey: Да, я вижу, но innodb очень тормознутая и постоянно винтом шевелит. Я пробовал выключать автокоммит, чтобы ускорить вставку, но это не очень помогло. При этом майисам почти мгновенно вставляет данные. Есть ли способ оттюнинговать innodb чтобы он быстро вставку делал, вариант множество values не подходит, потому что у меня слишком много данных.

[Aleksey Ratnikov]

Можно попробовать выключать проверку foreign и unique constraints: SET unique_checks=0;
SET foreign_key_checks=0; (Не забудьте включить обратно после вставки), увеличить размер буфера на вставку: https://dev.mysql.com/doc/refman/5.5/en/server-sys... Рассмотрите использование BIGINT AUTO_INCREMENT в качестве первичного ключа, это тоже хорошо повышает производительность INSERT'ов. Кроме того, если вы вставляете настолько много данных, что множественные VALUES не подходит, воспользуетесь инструкцией массовой загрузки LOAD DATA INFILE (https://dev.mysql.com/doc/refman/5.6/en/load-data.html), которая была создана специально для этих целей и работает ультра быстро.

[astrotrain]

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

[Aleksey Ratnikov]

SET autocommit = 0 делает неявный START TRANSACTION, которую нужно будет обязательно COMMIT или ROLLBACK. По умолчанию он включен, и каждая операция является транзакцией. И все это работает только для InnoDB, в MyISAM нет понятия транзакций в принципе.
Почему вы не хотите рассмотреть вариант с LOAD DATA INFILE? Вы можете сформировать CSV файл (например), положить его куда-нибудь в /tmp, а потом зачитать его инструкцией LOAD DATA INFILE, в таком случае вставка будет ультра быстрая.
Или, насколько я понял, у вас это все локально происходит и пользователь один - в таком случае можно оставить MyISAM и не забывать следить за его состоянием

[astrotrain]

Aleksey: дело в том что с load data infile невозможно отображать состояние процесса загрузки данных, непонятно на каком этапе сейчас вставка. И по времени, получается, что Myisam опять же выигрывает у load data (что странно). Я еще "поиграюсь" с этой функцией, и если результаты будут такими же, то наверное, действительно, остановлюсь на myisam. С другой стороны странно, почему все-таки такая разница в производительности, особенно в движке котоый по дефолту используется.

[astrotrain]

Aleksey: да, спасибо вам за внимание к моей теме!

[Aleksey Ratnikov]

на здоровье:) при выборе движка для таблицы необходимо принимать во внимание, что основное назначение InnoDB - обеспечение ACID (dev.mysql.com/doc/refman/5.6/en/mysql-acid.html) принципов за счет проигрыша в скорости, заметного на больших данных, а MyISAM - это просто хранилище, причем legacy, как уже было сказано, там нет всех этих механизмов и поэтому INSERT там работает быстрее по определению. Можно даже провести аналогию: NTFS vs. FAT32. Соотвественно, если вам важны целостность, консистентность и транзакции - InnoDB ваш выбор. Если важна скорость, таблица не подверженна конкуретным операциям - оставьте MyISAM. Не забывайте после массовых удалений делать REPAIR TABLE для дефрагментации занимаемого места и перестройки индексов(осторожно, на больших таблицах эта операция занимает много времени - на двух миллионах записей у меня занимало примерно 15 минут). И да, SSD даст вам прирост в скорости)

Answer 4

[shagguboy]

отключить индексы, отключить транзакционность нафик, залить данные через LOAD DATA INFILE, включить.