Почему Percona pt-online-schema-change так плохо работает?
Некоторое время назад мы использовали Percona OSC для внесения изменений в нашу схему mysql без блокировки таблиц, и она отлично работала, обычно добавляя новый столбец или индекс в "большие" таблицы innodb (~3,8 миллиона строк) в паре часов.
Тем не менее, последнее обновление, которое я попробовал, было выполнено только на 40% после выполнения в течение 7 часов (в течение ночи, во время нашего самого тихого периода), с оценкой еще 11 часов для завершения (что продолжает расти). Все 4 ГБ доступной памяти на сервере RedHat использовались - 32 ГБ, которые мы недавно обновили с 16 ГБ.
Так что здесь происходит? Почему время, взятое внезапно, прыгнуло так высоко? Мы только что достигли какого-то порога, с которым percona / mysql / сервер не может справиться? Есть ли какие-нибудь настройки, которые мы можем настроить для улучшения производительности?
Таблица имеет 32 столбца и 12 индексов (включая первичный ключ и 2 других уникальных индекса). Я знаю, что это очень много, но, как я уже говорил, до недавнего времени все было хорошо.
Таблица также имеет несколько внешних ключей, указывающих на нее, которые мы устанавливаем для обновления с помощью метода drop_swap.
Полная команда, которую я использовал, была:
pt-online-schema-change --execute --ask-pass --set-vars innodb_lock_wait_timeout=50 --alter-foreign-keys-method=drop_swap
--alter "ADD is_current TINYINT(1) DEFAULT '1' NOT NULL" u=admin,p=XXXXXXX,D=xxxxx_live,t=applicant
Параметр innodb_buffer_pool_size в настоящее время имеет значение 2147483648 - это следует увеличить? Если да, то сколько? Веб-сервер (apache/php/symfony) также работает в этом окне.
Последнее изменение, которое я сделал в этой конкретной таблице, состояло в том, чтобы изменить сопоставление 1 поля на utf8_bin (остальные поля - utf8_unicode_ci) - может ли это иметь значение?
2 ответа
Насколько велика эта таблица в МБ / ГБ?
InnoDB кэширует свои страницы в пуле буферов innodb (innodb_buffer_pool_size), и это важно для производительности. На выделенных хостах с> 4 ГБ ОЗУ мы рекомендуем использовать около 70-80% памяти для пула буферов InnoDB.
Используйте SQL в этом посте, чтобы собрать логические размеры ваших таблиц и индексов.
https://www.percona.com/blog/2008/03/17/researching-your-mysql-table-sizes/
С помощью этой информации вы сможете сразу определить, не хватает ли экземпляру MySQL (движка Innodb) памяти.
Если ваш рабочий набор данных умещается в памяти, замечательно, но если нет, то вы, скорее всего, будете испытывать промахи в кеше, и тогда MySQL потребуется выполнить ввод-вывод для доступа к дисковым ресурсам, чтобы обменять страницы в пуле буферов. (IO всегда PITA на земле БД)
Суть работы pt-osc заключается в создании новой измененной копии таблицы и заполнении новой версии строками из оригинала. Новые строки также вставляются / обновляются или удаляются с помощью триггеров, которые устанавливает инструмент. Чтобы выполнить эту обратную засыпку, в какой-то момент потребуется прикоснуться ко всем строкам в этой таблице, и большая часть таблицы может быть холодной (не находящейся в пуле буферов в ОЗУ). Так что в основном у вас есть скромное количество оперативной памяти на машине, но на самом деле InnoDB видит только 2 ГБ этого.
У вас также есть приложения, работающие на сервере, поэтому для настройки потребуются некоторые наблюдения, но я ожидаю, что вы могли бы значительно повысить уровень памяти, выделенной для пула буферов. Я также ожидал бы, что большая часть вашей оперативной памяти не используется, но была выделена для кэша файловой системы.
Если ваша таблица занимает всего несколько сотен мегабайт (что я сомневаюсь в 4-метровых записях и широкой схеме), возможно, есть более глубокие проблемы для рассмотрения, но я уверен, что с изменением размера буферного пула вы увидите лучшую производительность.
Кроме того, это проверка работы вашего innodb_log_file_size настроен на вашу рабочую нагрузку. Это важно, чтобы MySQL мог отложить ввод-вывод. Каков его текущий размер?
Предполагая, что все вещи равны, я бы сказал, что какой-то порог был преодолен или какой-то другой процесс загружает базу данных. Количество используемых вами индексов очень велико. pt-osc создает новую пустую измененную таблицу, а затем начинает копирование в "чанках". Время, затрачиваемое на каждый блок, динамически адаптируется к последним 0,5 с (по умолчанию). Вы можете проверить "show processlist", чтобы увидеть, кто оказывает давление на базу данных, а также какой размер чанка использует pt-osc, чтобы получить больше информации.