Какие способы очистки таблиц innodb mysql посоветуете?

Question

[Александра Калинина]

Исходные данные:

1) 2 сервера, на каждом из которых поднят mysql

2) на каждом из серверов находится таблица типа innodb

3) эти таблицы реплицируются (запросами) между собой по типу мастер-мастер (вдруг это важно, автоинкриментное поле на одном из серверов четное, на другом нечетное)

4) таблицы содержат в себе порядка 50 млн записей (примерно по 128 байт) и каждый день в любую из таблиц может сваливаться до 10-20 млн записей (не единоразово, а в течении дня)

5) чистить таблицы необходимо ежедневно, порядок чистки: 10-20 млн записей

6) на данный момент размер каждой БД на винте — 50 гигабайт (вместе с индексами)


Какие есть варианты, исключая вариант DELETE, OPTIMIZE TABLE?

Comments

[Александра Калинина]

условие трудно прогнозируемого отсутствия загрузки оказалось критичным, т.к. в один момент времени нагрузка на БД может быть очень маленькая и уже в следующую секунду она может подскочить.

[Ruslan_Y]

какая у вас версия mysql?
по какому условию удаляются данные (по одинаковому условию или каждый раз разные критерии)?

Answer 1

[Ruslan_Y]

Еще один вариант, не знаю подойдет ли он вам, партиции в 5.1. Для вас думаю возможен вариант с alter table truncate partition ..., и факт того, что индексы локальные тоже должен помочь, нет глобального индекса — не останется хвостов от удаленных данных. Из минусов, ну их даже в официальной документации много. И еще тут много зависит от того, как у вас организованы данные, простейший вариант с датой временем, по ним можно побить данные и очищать устаревающие данные.

Comments

[Александра Калинина]

> Еще один вариант, не знаю подойдет ли он вам, партиции в 5.1. Для вас думаю возможен вариант с alter table truncate partition ..., и факт того, что индексы локальные тоже должен помочь, нет глобального индекса — не останется хвостов от удаленных данных. Из минусов, ну их даже в официальной документации много. И еще тут много зависит от того, как у вас организованы данные, простейший вариант с датой временем, по ним можно побить данные и очищать устаревающие данные.
про партиции, спасибо, почитаю
про биение данных, мысль хороша, но есть определенные трудности. текущая таблица используется во множестве отчетов, которые прийдется переписывать, тк они строятся на локальном времени пользователей, а время в таблице установлено в гринвиче.
хотя я вот сейчас подумала, может есть возможность использовать для этого редставления? хотя я не видела, чтобы именно таким образом ими пользовались

[Ruslan_Y]

Проблемы вообщем-то и не возникнет, прелесть партиций именно в прозрачности для запроса, у пользователя всегда 1 таблица, просто в зависимости от условий могут использоваться 1 или несколько (или все) партиций (partition pruning). В данном случае с датами — вообще отличный вариант, можно дропнуть ненужные партиции когда они устареют.

Answer 2

[sajgak]

Уже ж сто раз обсуждалось. Ставьте удалемым записям флаг на удаление, а реально убаляйте только тогда, когда нагрузки на базу нет.

Comments

[Александра Калинина]

предполагается что нагрузка на базу есть всегда

[Александра Калинина]

и предполагается что трудно спрогнозировать отсутствие загрузки

[Александра Калинина]

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

[mark_ablov]

> и предполагается что трудно спрогнозировать отсутствие загрузки
ставим на крон проверку нагрузки сервера, и если она ниже какого-то предела, то физически удаляем часть записей?

[Александра Калинина]

>> и предполагается что трудно спрогнозировать отсутствие загрузки
> ставим на крон проверку нагрузки сервера, и если она ниже какого-то предела, то физически удаляем часть записей?
окей, вариант принят, но думалось может есть другие варианты

[AntonioK]

решение в стиле говнокод: использовать сиквенс-функцию вместо автоинкремента, у которой ограничивать максимальное количество id в таблице, а последовательность айдишников зацикливать, и использовать insert ignore для затирания старых новыми.

[Александра Калинина]

> и предполагается что трудно спрогнозировать отсутствие загрузки
ставим на крон проверку нагрузки сервера, и если она ниже какого-то предела, то физически удаляем часть записей?
Кстати, а если понемногу удалять записи, например по 10000 в минуту, по идее, если запрос успеет отработаться за минуту, то полное удаление 20 млн записей должно пройти за примерно 2000 минут, т.е меньше часа. Что в принципе может устроить… однако я вот не в курсе на счет а) того что за минуту 10000 записей успеет удалится, б) оптимизацию таблицы следует проводить после того как все данные будут удалены.

[WebSpider]

20 млн записей должно пройти за примерно 2000 минут, т.е меньше часа

Отличный у вас час! :)

[Александра Калинина]

>Отличный у вас час! :)
ой :)

Answer 3

[Александра Калинина]

>решение в стиле говнокод: использовать сиквенс-функцию вместо автоинкремента, у которой ограничивать максимальное количество id в таблице, а последовательность айдишников зацикливать, и использовать insert ignore для затирания старых новыми.
да, решение сильно в стиле говнокод, и в данной ситуации не получится, тк есть нюансы, например пакеты делятся по какому-то признаку, и в каждом конкретном признаке количество пакетов может быть от одного до нескольких миллионов. кроме того, удаление должно производиться не случайным образом, а строго за заданный период времени (поступивших данных), т.е. грубо говоря должен чистится архив, удаление старых ненужных записей.