Почему MySQL InnoDB вставки/обновления на больших таблицах становятся очень медленными, когда есть несколько индексов?

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

Мы перестроили одну из таблиц, создав копию, восстанавливающую индексы, а затем выполнив переименование и скопировав все новые строки, это сработало, потому что эта таблица только добавляется. Сделав это, вы быстро вставляли и обновляли молнию.

Мои вопросы:

Почему вставки со временем замедляются? Почему воссоздание таблицы и выполнение импорта исправить это? Есть ли способ восстановить индексы без блокировки таблицы для обновлений?

Ответ 1

Звучит так, как

Индексный дисбаланс с течением времени
Дробление диска
Внутренняя фрагментация файлов данных innodb

Вы можете попробовать analyze table foo, который не использует блокировки, всего несколько погружений индекса и занимает несколько секунд.

Если это не исправить, вы можете использовать

mysql> SET PROFILING=1;
mysql> INSERT INTO foo ($testdata);
mysql> show profile for QUERY 1;

и вы должны увидеть, где большую часть времени тратится.

По-видимому, innodb лучше работает, когда вставки выполняются в порядке PK, это ваш случай?

Ответ 2

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

Если вы только вставляете в таблицу, MyISAM лучше подходит для этого. У вас не будет проблем с блокировкой, если только добавление, так как запись добавляется в конец файла. MyISAM также позволит вам использовать таблицы MERGE, которые действительно хороши для того, чтобы выполнять части данных в автономном режиме или архивировать без необходимости экспорта и/или удаления.

Ответ 3

Для обновления таблицы требуются индексы, которые необходимо перестроить. Если вы делаете массовые вставки, попробуйте сделать их в одной транзакции (как это делает дамп и восстановление). Если таблица смещена в обратном порядке, я бы подумал о том, чтобы отбросить индексы в любом случае или позволить фоновой задаче читать-обрабатывать таблицу (например, скопировав ее в индексированную).

Ответ 4

Может быть, это связано с фрагментацией XFS?

Скопировать/вставить из http://stevesubuntutweaks.blogspot.com/2010/07/should-you-use-xfs-file-system.html:

Чтобы проверить уровень фрагментации диска, например, расположенный на /dev/sda 6:

sudo xfs_db -c frag -r/dev/sda6

Результат будет выглядеть примерно так:

фактический 51270, идеальный 174, коэффициент фрагментации 99.66%

Это фактический результат, который я получил с первого раза, когда я установил эти утилиты, ранее не имея знаний об обслуживании XFS. Довольно противный. В принципе, 174 файла на разделе были распределены по 51270 отдельным частям. Для дефрагментации выполните следующую команду:

sudo xfs_fsr -v/dev/sda6

Пусть он работает некоторое время. опция -v позволяет показать прогресс. После завершения проверки попробуйте снова проверить уровень фрагментации:

sudo xfs_db -c frag -r/dev/sda6

фактический 176, идеальный 174, коэффициент фрагментации 1,14%

Гораздо лучше!

Ответ 5

отследить использование my.ini и увеличить key_buffer_size У меня была таблица 1,5 ГБ с большим ключом, где количество запросов в секунду (все записи) сократилось до 17. Мне было странно, что в администрации (в то время как таблица была заблокирована для записи, чтобы ускорить процесс), она выполняла 200 запросов InnoDB в секунду до 24 записей в секунду.

Он был вынужден прочитать индексную таблицу с диска. Я изменил key_buffer_size с 8M до 128M, и производительность увеличилась до 150 запросов в секунду, и мне пришлось выполнять только 61 чтение, чтобы получить 240 записей. (после перезапуска)