Меню
Что нового?

Mysql Дикие тормоза при вставке в InnoDB

Фанат

Фанат

oncle terrible
Команда форума
Занявшись тут одним соседним вопросом, решил повставлять данные в таблицу без индексов.
результат меня обескуражил.
под myisam 1000 инсёртов - 0,06 сек.
под innodb - двадцать секунд

запихнуть все инсерты внутрь транзакции получается сильно лучше - 0,1 секунды

всё это на говновинде, с дефолтным мускулем из коробки.
собственно, вопросов 2:
что это было, папа?
это так и должно быть? или есть какая-то хитрая настройка, которая приводит скорость инсертов к приемлемому значению?
quick googling ясности не внёс.
 
Yoskaldyr

Yoskaldyr

"Спамер"
Партнер клуба
Фанат написал(а):
под myisam 1000 инсёртов - 0,06 сек.
под innodb - двадцать секунд
Нажмите для раскрытия...
Скорее всего это:
innodb_flush_log_at_trx_commit — жалуетесь, что InnoDB работает в 100 раз медленнее MyISAM? Вероятно, Вы забыли про настройку innodb_flush_log_at_trx_commit. Значение по умолчанию «1» означает, что каждая UPDATE-транзакция (или аналогичная команда вне транзакции) должна сбрасывать буфер на диск, что достаточно ресурсоёмко. Большинство приложений, в особенности ранее использовавшие таблицы MyISAM, будут хорошо работать со значением «2» (т.е. «не сбрасывать буфер на диск, только в кэш ОС»). Лог, однако, всё равно будет сбрасываться на диск каждые 1-2 секунды, поэтому в случае аварии Вы потеряете максимум 1-2 секунды обновлений. Значение «0» повысит производительность, но Вы рискуете потерять данные даже при аварийной остановке mySQL-сервера, в то время как при установке значение innodb_flush_log_at_trx_commit в «2» Вы потеряете данные только при аварии всей операционной системы.
Нажмите для раскрытия...
P.S. Описание настройки взял из первого результата гугла, а так детально можно почитать в документации какую настройку когда рекомендуют, например иногда самый оптимальный вариант 0 при условии использования хардварных рейдов с независимым питанием.
 
флоппик

флоппик

promotor fidei
Команда форума
Партнер клуба
Фанат написал(а):
что это было, папа?
это так и должно быть?
Нажмите для раскрытия...
ИнноДБ — ACID-совместимый, т.е. каждый инсерт = автоматически оборачивается в транзакцию. Указав ручками, что это множественый инсерт (обернув все в транзакцию) ты получаешь сравнительно чистую массовую запись.

Алсо, есть еще десяток факторов, влияющих на это вплоть до фрагментации жестяка (т.к. иннодб до версии 5.6 по умолчанию хранит все в одном файлике), innodb_flush_log_at_trx_commit != 2 в конфиге тоже должен ощутимо увеличить скорость инсерта. Правда, все мои знания в этом вопросе очень далеки от винды.
 
grigori

grigori

( ͡° ͜ʖ ͡°)
Команда форума
то же самое, что fsync в постгресе. не прошло и 10 лет, как пользователи mysql об этом задумались :)
 

Gas

может по одной?
то же самое, что fsync в постгресе. не прошло и 10 лет, как пользователи mysql об этом задумались
Нажмите для раскрытия...
что значит 10 лет, настройка innodb_flush_log_at_trx_commit была ещё в mysql 3.23 (2001 год), а fsync не в 8-ке ли появился, что было гараздо позже?
 
Фанат

Фанат

oncle terrible
Команда форума
Gas
он про пользователей, а не про авторов %)
вообще, на очевидные подколки всерьёз отвечать не стоит ;)
 
grigori

grigori

( ͡° ͜ʖ ͡°)
Команда форума
в 7ке была опция nofsync :) 2000й год
http://www.postgresql.org/docs/7.0/static/pg-options.htm

разница в другом: в pg ставится заполненный файл опций с комментариями как в php.ini,
не надо рыть мануалы с гуглом, для ключевых настроек написано: это ключевая настройка, рекомендуем так и так,
и про fsync в постгресе нельзя не подумать
 
Фанат

Фанат

oncle terrible
Команда форума
Апельсин написал(а):
Ром, ты теперь в DBA подался что ли?
Нажмите для раскрытия...
Не, я как и был - флудер на форуме :)
собственно, был вопрос, http://phpclub.ru/talk/threads/Таблица-500к-записей-innodb-простой-запрос-5-минут.73968/
мне интересно стало, решил дома попробовать. для тестов решил сгенерить таблицу на 500 000 строк.
слепил скрипт, запустил, он вылетел по таймауту. Ну - думаю - может, не 500 тыс. вставилось, а 300 - щас добью.
Лезу в базу, а там - 1400 строк. За 30 секунд. Тут я и офигел.
Ну и решил спросить - что я пропустил.

Ну а вообще, каждый пхп девелопер - немножко DBA.
Собственно, другой мой вопрос был как раз уже чисто по работе.

На днях, кстати, навесили золотую бляху Mysql на стаковерфлое. Даром что уже полгода, как забанен %)
 

Апельсин

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

Фанат

oncle terrible
Команда форума
Апельсин написал(а):
InnoDB внутренне все хранит отсортированным по первичному ключу
Нажмите для раскрытия...
Гм. Круто. Я правильно понял, что даже данные? или только ключ?
Если первое, то мой тест никуда не годится - я добавлял по порядку, а в XML у чувака может идти вразнобой.

я-то просто заливал 500 тыщ строк с id по возрастанию внутри одной транзакции. И в этом случае наличие ключа на id на скорость не влияло.
 
Redjik

Redjik

Джедай-мастер
sort_buffer = 4M
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 0
innodb_use_sys_malloc = 0
innodb_autoinc_lock_mode = 2
innodb_doublewrite = 0
table_cache = 128

У меня ssd, на виртуалке 1гиг оперативы, что еще сделать - не знаю даже.
Импорт проходит в транзакции.
Дикие тормоза начинаются после ~ 200к записей
 
Войдите или зарегистрируйтесь для ответа.
Сверху