Использование диска дроссельной заслонки ALTER TABLE

Я начну с чего-то из MySQL Online Ограничения DDL:

Нет механизма приостановки операции DDL в режиме онлайн или дросселирования ввода-вывода или использования ЦП для онлайн-операции DDL.

Однако меня все еще интересуют решения, которые я, возможно, пропустил.

Ситуация: индексы становятся все больше и больше, и они становятся настолько большими, что не будет достаточно памяти для используемых запросов, что приведет к ускорению ввода-вывода диска, и все, чтобы спуститься в полный хаос, Созданы новые составные индексы, которые меньше, но проблема заключается в запуске ALTER TABLE без нарушения.

Факты заключаются в следующем:

Это таблица InnoDB.
В таблице нет первичного ключа или уникального индекса.
Никакая комбинация столбцов не подходит в качестве первичного ключа или уникального индекса.
В таблице нет внешних ключей.
Таблица разбивается на месяц (в настоящее время 50).
Таблица должна принимать записи в любое время.
Новые разделы 3-6 должны принимать сообщения.
Существует столбец id, но это не уникально.
Таблица состоит из приблизительно 2 миллиардов строк.
Разделение текущего месяца является единственным, который получает записи.
Перегородки производятся за 1 месяц; всегда есть один пустой раздел.

SHOW CREATE TABLE (я не включил все разделы):

CREATE TABLE `my_wonky_table` (
  `id` bigint(20) unsigned NOT NULL,
  `login` varchar(127) DEFAULT NULL,
  `timestamp` int(10) unsigned NOT NULL,
  `ip` varchar(32) CHARACTER SET ascii DEFAULT NULL,
  `val_1` int(10) unsigned DEFAULT NULL,
  `val_2` varchar(127) DEFAULT NULL,
  `val_3` varchar(255) DEFAULT NULL,
  `val_4` varchar(127) DEFAULT NULL,
  `val_5` int(10) unsigned DEFAULT NULL,
  KEY `my_wonky_table_id_idx` (`id`),
  KEY `my_wonky_table_timestamp_idx` (`timestamp`),
  KEY `my_wonky_table_val_1_idx` (`val_1`,`id`),
  KEY `my_wonky_table_val_2_idx` (`val_2`,`id`),
  KEY `my_wonky_table_val_4_idx` (`val_4`,`id`),
  KEY `my_wonky_table_val_5_idx` (`val_5`,`id`),
  KEY `my_wonky_table_ip_idx` (`ip`,`id`),
  KEY `my_wonky_table_login_idx` (`login`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION pdefault VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Относительно запросов: всегда SELECT на id, причем все, что используется для фильтрации.

Чего я бы хотел избежать:

Отключение экземпляра базы данных.
Дисковый ввод-вывод 100%

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

Существуют ли другие решения и/или инструменты?

Ответ 1

Создайте таблицу new, похожую на таблицу real, но с пересмотренными индексами. Включите PRIMARY KEY, чтобы снова не оказаться в ловушке. - Это ALTER, но еще не "заполняется".
В новой таблице используйте квартальные или годовые разделы для старых вещей; ежемесячно для текущих и (будущих) будущих разделов. - Это должно уменьшить общее количество разделов. Мое правило большого пальца - "не более 50 разделов". (Дайте мне знать, если у вас есть проблемы с этим планом.)
Напишите script, чтобы медленно скопировать все данные из старых разделов в таблицу new. Мой совет по chunking может быть полезен здесь.
Перед тем, как вы пойманы, создайте новый раздел. Но не копируйте его еще. Остановите "копию" script в конце предыдущего раздела.
При завершении, за исключением этого нового раздела, прекратите запись.
Скопируйте последний раздел. - Здесь, где шаг № 4 окупается.
Атомный своп: RENAME TABLE real TO old, new TO real;. И снова включите запись.

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

Ответ 2

Я представляю это как отдельный ответ, так как самая внутренняя часть совершенно другая.

Как и в моем другом ответе, вам нужна таблица new с новыми индексами плюс script, чтобы скопировать все данные. Однако мясо должно имитировать триггер в вашем приложении.

К счастью, у вас есть id, хотя это не PRIMARY KEY. И даже если это не UNIQUE, его можно использовать (если у вас нет тысяч строк с одним и тем же идентификатором - если вы это сделаете, мы можем поговорить дальше).

"copy script" и приложение разговаривают друг с другом.

Копия script находится в длинном цикле:

SELECT GET_LOCK('copy', 5), high_water_mark FROM tbl; - (или другой таймаут)
Скопируйте строки с помощью id BETWEEN high_water_mark AND high_water_mark + 999.
UPDATE tbl SET high_water_mark = high_water_mark + 1000;
Временно приостановить (1 секунду?)
Цикл до тех пор, пока не будет ids

Приложение при чтении продолжает читать из старой таблицы. Но при написании это делает:

SELECT GET_LOCK('copy', 5), high_water_mark FROM tbl; - (или другой таймаут)
При истечении времени необходимо что-то исправлять.
Пишите на старую таблицу - (следовательно, чтение продолжает работать)
Если id <= high_water_mark, напишите также в новую таблицу.
SELECT RELEASE_LOCK('copy');

Следить за прогрессом. В какой-то момент вам нужно будет остановить все, скопировать последние несколько строк и сделать RENAME TABLE.

Я не знаю ваших оптимальных значений для тайм-аутов, сна или размера блока. Но я не думаю, что разумно, чтобы размер куска был больше 1К.

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

Ответ 3

Это будет связано с тем, какой вариант и версия MySQL вы используете, но если это один поток для каждого соединения (my.cnf thread_handling=one-thread-per-connection, который может быть по умолчанию в вашей сборке), и вы можете поместить свой ALTER TABLE workload в новом соединении, тогда рабочая нагрузка - это уникальный PID, и вы можете использовать ionice/renice на нем.

У меня несколько дерьмовый ответ, но он менее инвазивен, чем другие варианты.

Если вы посмотрите ps -eLf |grep mysql, вы можете увидеть потоки/облегченные процессы и просто нужно выяснить, какой PID принадлежит вашему конкретному соединению. Если вы подключаетесь через TCP, вы можете сопоставить свой локальный порт подключения и сопоставить его с lsof, чтобы найти конкретный поток. Возможны другие способы: w/strace, systemtap и т.д. Или запуск начального запроса, который вы можете наблюдать.

После этого вы можете использовать ionice/renice, чтобы повлиять на PID в системе. Вы действительно захотите убедиться, что вы зафиксируете, что такое PID, и reset хороший и приоритетный уровень впоследствии, чтобы не повлиять ни на что другое.

Как и в случае с другими, вам действительно нужно переформатировать эту таблицу в долгосрочной перспективе. Разделы полезны, но не эндшпиль, поскольку вы используете 1.3TiB онлайн-данных, и утверждаете, что вам нужно читать только из последних 3-6 разделов. Начиная с MySQL перед добавлением собственных разделов, я думаю, что это будет хорошим примером для VIEW и отдельных таблиц (атомизируйте обновление VIEW, когда вам нужно опрокинуться). Это также позволило бы тривиально переместить некоторые старые таблицы в автономное хранилище.