Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Очистка большой таблицы  [new]
alister
Member

Откуда:
Сообщений: 91
Есть база размером более терабайта, в ней таблица (журнал) размером около 500 ГБ, около 100 млн записей, таблица выполняет функции лога.
Задача, удалить из этой таблицы 90% старых записей (фактически по id).
База включена в мирроринг (асинхронный), и настроена реплика, но сама таблица не в публикации.
Практически все время идет вставка записей в таблицу, кроме выходных, процедура будет выполняться на выходные.
Приоритет - сохранность данных.
Есть вариант удалять записи батчами по 10 тыс записей, чтобы успевал отрабатывать бекап лога и мирроринг.
Второй вариант, перенести нужную часть таблицы в новую таблицу через select * into , потом переименовать старую таблицу, переименовать индексы, констрейнты..., новую таблицу переименовать в старую, удалить старую таблицу.
Из-за мирроринга и реплики нельзя менять Recovery Model, оно может быть только FULL.
Места на партишине баз данных (свободно 100 ГБ), на партишине лога (свободно 100 ГБ).
Дополнительное место на партишинах можно в принципе заранее запросить.
С точки зрения простоты и минимального роста транзакционного лога я бы отдал предпочтение удалению батчами без открытия транзакции, но опасаюсь непредсказуемых проблем с недостатком производительности и того, что процедура не успеет отработать за разумное время.
Если использовать второй вариант select * into будет сильный рост в первую очередь транзакционного лога, ну и самой базы.
25 май 15, 19:20    [17687399]     Ответить | Цитировать Сообщить модератору
 Re: Очистка большой таблицы  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Эта Ваша таблица секционированная и данные в этих секциях не нужны более? Если да вы можете переключить ненужные секции на другую таблицу и для нее сделать транкейт. Операция практически мгновенная.
25 май 15, 19:35    [17687489]     Ответить | Цитировать Сообщить модератору
 Re: Очистка большой таблицы  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Сделать новую таблицу, старую переименовать, сделать представление (названное как изначальная таблица), которое объединяет данные двух таблиц, на него повесть instead-триггер, который будет лить данные в новую таблицу. После небольшими порциями перелить из старой таблицы в новую нужные данные, или подождать, пока новая не наполнится данными и просто грохнуть старую.

Сообщение было отредактировано: 25 май 15, 20:37
25 май 15, 20:35    [17687651]     Ответить | Цитировать Сообщить модератору
 Re: Очистка большой таблицы  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
alister
Дополнительное место на партишинах можно в принципе заранее запросить.
С точки зрения простоты и минимального роста транзакционного лога я бы отдал предпочтение удалению батчами без открытия транзакции, но опасаюсь непредсказуемых проблем с недостатком производительности и того, что процедура не успеет отработать за разумное время.
Если использовать второй вариант select * into будет сильный рост в первую очередь транзакционного лога, ну и самой базы.



Скорее всего, вам придется чередовать операции удаления и бекапа/обрезания лога. Со стратегией переливки в новую таблицу тоже самое.

Вам надо изменить схему партиционирования. И подумать про переключение партиций. Переносите партицию в другую таблицу и просто эту таблицу грохаете. Надо тестировать сколько лога будет расти при такой операции и действовать соответственно.
25 май 15, 21:13    [17687834]     Ответить | Цитировать Сообщить модератору
 Re: Очистка большой таблицы  [new]
alister
Member

Откуда:
Сообщений: 91
AlanDenton
Эта Ваша таблица секционированная и данные в этих секциях не нужны более? Если да вы можете переключить ненужные секции на другую таблицу и для нее сделать транкейт. Операция практически мгновенная.

Таблица не секционирована, учитывая, что это просто лог, журнал, вроде как нет особой необходимости ни секционировать, ни хранить все данные.
26 май 15, 09:52    [17689314]     Ответить | Цитировать Сообщить модератору
 Re: Очистка большой таблицы  [new]
alister
Member

Откуда:
Сообщений: 91
Гавриленко Сергей Алексеевич
Сделать новую таблицу, старую переименовать, сделать представление (названное как изначальная таблица), которое объединяет данные двух таблиц, на него повесть instead-триггер, который будет лить данные в новую таблицу. После небольшими порциями перелить из старой таблицы в новую нужные данные, или подождать, пока новая не наполнится данными и просто грохнуть старую.

Спасибо, решение красивое, особенно, если просто подождать, пока таблица не наполнится данными :)
Насчет переливки данных из старой в новую, там поле Identity, придется в момент переливки включать Set Identity_Insert.
26 май 15, 09:55    [17689332]     Ответить | Цитировать Сообщить модератору
 Re: Очистка большой таблицы  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
Если у Вас нет foreign key и подобного на таблице - можно залить нужные данные в новую таблицу, truncat-нуть старую, вернуть данные назад, дропнуть новую
плюс по сравнению с переименованием в том что не надо переименовывать сопутствующие обьекты.
26 май 15, 09:59    [17689353]     Ответить | Цитировать Сообщить модератору
 Re: Очистка большой таблицы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
alister
Таблица не секционирована, учитывая, что это просто лог, журнал, вроде как нет особой необходимости ни секционировать
Секционирование делается (одна из задач) как раз для лёгкого удаление старых данных.
Секционируете по суткам, или, например, неделям, месяцам, и удаляете время от времени секцию.
26 май 15, 10:00    [17689354]     Ответить | Цитировать Сообщить модератору
 Re: Очистка большой таблицы  [new]
alister
Member

Откуда:
Сообщений: 91
a_voronin
alister
Дополнительное место на партишинах можно в принципе заранее запросить.
С точки зрения простоты и минимального роста транзакционного лога я бы отдал предпочтение удалению батчами без открытия транзакции, но опасаюсь непредсказуемых проблем с недостатком производительности и того, что процедура не успеет отработать за разумное время.
Если использовать второй вариант select * into будет сильный рост в первую очередь транзакционного лога, ну и самой базы.



Скорее всего, вам придется чередовать операции удаления и бекапа/обрезания лога. Со стратегией переливки в новую таблицу тоже самое.

Вам надо изменить схему партиционирования. И подумать про переключение партиций. Переносите партицию в другую таблицу и просто эту таблицу грохаете. Надо тестировать сколько лога будет расти при такой операции и действовать соответственно.

Да, включить партиционирование таблицы вариант, но опять же, либо ждать, пока наполнится данными новая партиция (6 месяцев) либо делать перенос из одной партиции в другую, если не ошибаюсь, это практически тоже самое, что сразу делать перенос из старой таблицы в новую.
26 май 15, 10:03    [17689370]     Ответить | Цитировать Сообщить модератору
 Re: Очистка большой таблицы  [new]
alister
Member

Откуда:
Сообщений: 91
Кот Матроскин
Если у Вас нет foreign key и подобного на таблице - можно залить нужные данные в новую таблицу, truncat-нуть старую, вернуть данные назад, дропнуть новую
плюс по сравнению с переименованием в том что не надо переименовывать сопутствующие обьекты.

foreign key нет, dependencies, view тоже нет поэтому переименование неболезненное
А дважды переносить из таблицы в таблицу 15 млн записей все таки затратно.
26 май 15, 10:05    [17689378]     Ответить | Цитировать Сообщить модератору
 Re: Очистка большой таблицы  [new]
Alexander Us
Member

Откуда:
Сообщений: 1133
alister,

если не подходят варианты с партиционированием и переливкой:

сделайте джоб который удалаяет ночью в цикле небольшими батчами по ХХХ записей по условиям например:

- чтоб в таблице оставалось не более ХХХХХ строк (это через системные таблицы, не используйте count(*))
- чтоб самая старая запись была не более ххххх дней
- чтоб таблица занимала места на диске не более ххххх ГБайт
26 май 15, 10:24    [17689457]     Ответить | Цитировать Сообщить модератору
 Re: Очистка большой таблицы  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Не очень давно была похожая тема, можно ее почитать, ссылку даю на свое сообщение с примером удаления по партициям (если решите использовать партиции): 17391072.
26 май 15, 10:45    [17689536]     Ответить | Цитировать Сообщить модератору
 Re: Очистка большой таблицы  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Как пишутся записи в таблицу:

insert into из приложения?
вызовом процедуры в приложении?
в триггере?

Как читаются и кем читаются? или не читаются, а на всякий случай хранятся?
26 май 15, 10:52    [17689581]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить