Как удалить большие данные из базы данных Firebird SQL

У меня очень большая база данных (по крайней мере, для меня) - более 1000000 записей, и мне нужно удалить все записи с меткой времени ниже, чем что-то. Как например:

DELETE FROM table WHERE TS < 2020-01-01;

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

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


sql firebird firebird2.5
person T0ny1234    schedule 18.02.2021    source источник
comment
Что вы имеете в виду под невосприимчивыми и непригодными для использования? Вы случайно не выдаете select * from table или что-то еще, что сканирует большую часть таблицы? Это может означать, что вы запускаете совместную сборку мусора, что означает, что транзакция, выполняющая сканирование таблицы, требует затрат на очистку мусора после удаления.   -  person Mark Rotteveel    schedule 18.02.2021
comment
может тебе нужен индекс?   -  person OldProgrammer    schedule 18.02.2021
comment
@Mark Rotterveel: Да, это именно то, с чем у меня проблемы или с следующим удалением, которое я пытаюсь сделать. Как я могу это пережить? Должен ли я сделать сначала DELETE, затем выполнить резервное копирование и восстановление, а после этого следующее удаление и снова резервное копирование и восстановление?   -  person T0ny1234    schedule 18.02.2021
comment
@ Tony1234. . . Часто лучше сохранить данные, которые вы хотите сохранить, в новой таблице. Затем обрежьте существующую таблицу и повторно вставьте данные.   -  person Gordon Linoff    schedule 18.02.2021
comment
@GordonLinoff Firebird не имеет оператора усечения таблицы, поэтому вам нужно будет удалить все записи, что вызовет еще большую версию проблемы, так как это приведет к созданию большего количества мусора для сбора.   -  person Mark Rotteveel    schedule 18.02.2021
comment
@MarkRotteveel. . . Было бы RECREATE более эффективным?   -  person Gordon Linoff    schedule 19.02.2021
comment
@GordonLinoff Да, но с большим количеством предостережений: RECREATE выполняет DROP и CREATE, что означает, что любые зависимости (например, внешние ключи к таблице) заблокируют удаление, вы потеряете все индексы в таблице и любые привилегии на таблица, присвоенная другим объектам (пользователям и т. д.). Кроме того, если таблица используется (например, подготовленным оператором), ее также нельзя отбросить.   -  person Mark Rotteveel    schedule 19.02.2021
comment
Сделает ли переход на классический или суперклассический сборщик мусора лучше или хуже?   -  person Arioch 'The    schedule 19.02.2021
comment
@ Arioch'The Обычно будет только хуже, потому что тогда единственный выход - кооператив. Переключение на SuperServer (или оставление его включенным) и установка GCPolicy на background - это простейшее решение (но есть некоторые компромиссы, потому что я считаю, что он не будет собирать мусор сразу после combined или cooperative, что означает некоторое снижение производительности из-за более длинные цепочки версий.   -  person Mark Rotteveel    schedule 19.02.2021

Ответы (2)


arrow_upward
1
arrow_downward

Судя по вашему описанию в вопросе и вашим комментариям, проблема связана с тем, как работает сборка мусора в Firebird. Firebird - это так называемая база данных Multi-Version Concurrency Control (MVCC), каждое изменение, которое вы вносите к строке (записи), включая удаления, создаст новые версии этой записи и сохранит предыдущие версии доступными для других транзакций, которые были запущены до того, как транзакция, которая внесла изменение, будет зафиксирована.

Если больше нет транзакций, «заинтересованных» в предыдущей версии записи, эта предыдущая версия получает право на сборку мусора. Firebird имеет два варианта сборки мусора: кооперативный (поддерживается всеми режимами сервера) и фон (поддерживается SuperServer), а третий - комбинированный режим. который делает и то, и другое (это значение по умолчанию для SuperServer).

Режим background - это выделенный поток, который очищает мусор, он сигнализирует активным операторам, если они видят мусор.

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

См. Также слайды Подробнее о механизме сборки мусора и очистке.

Есть несколько возможных решений:

  1. Если вы используете SuperServer, измените политику, установив для параметра GCPolicy в firebird.conf значение background.

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

  2. После фиксации транзакции, которая произвела много мусора, выполните инструкцию, которая выполняет полное сканирование таблицы (например, select count(*) from table), чтобы запустить сборку мусора, используя отдельный рабочий поток, чтобы не блокировать остальную часть вашего процесса.

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

  3. Создайте резервную копию из базу данных (нет необходимости восстанавливать, кроме проверки правильности работы резервной копии).

    По умолчанию (если вы не укажете параметр -g для отключения сборки мусора), инструмент gbak будет выполнять сборку мусора во время резервного копирования. Это имеет то же ограничение, что и вариант 2, поскольку он работает, потому что gbak выполняет эквивалент select * from table

  4. Выполните «чистку» базы данных с помощью _ 8_.

    Это имеет те же ограничения, что и предыдущие два варианта.

  5. Для подключений, которые не могут замедлить сборку мусора, укажите параметр подключения isc_dpb_no_garbage_collect (подробности различаются в зависимости от драйверов и библиотек подключений).

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

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

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

person Mark Rotteveel    schedule 18.02.2021
comment
Спасибо за исчерпывающий ответ. Еще один вопрос, который пришел в голову: когда мусор будет очищен select * from table из того, что я выберу для его очистки, уменьшится ли база данных в размере? - person T0ny1234; 20.02.2021
comment
@ T0ny1234 Нет, база не сжимается. Пространство останется выделенным, но станет доступным для повторного использования для хранения новых записей (или новых версий существующих записей). В некоторых случаях, если страница данных становится полностью пустой, страница данных, выделенная для конкретной таблицы, может быть освобождена, но это просто означает, что она будет добавлена ​​в список свободных страниц, которые будут использоваться повторно. Единственный способ уменьшить базу данных - это резервное копирование с помощью gbak и ее восстановление. - person Mark Rotteveel; 20.02.2021
comment
@ Mark Rotterveel Еще раз спасибо. - person T0ny1234; 20.02.2021

arrow_upward
1
arrow_downward

На самом деле фоновая сборка мусора - это именно то, что может привести к зависанию базы данных из-за высокого напряжения между сборщиком мусора и рабочими потоками. Кооперативный сборщик мусора может замедлить работу, но сохранит отзывчивость базы данных. По крайней мере, для версии 2.5.

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

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

person user13964273    schedule 19.02.2021
comment
Фоновая сборка мусора не должна приводить к зависанию базы данных. Его вызывает кооперативный механизм, потому что оператор, касающийся таблицы, выполняет дополнительную работу по сборке мусора, что может значительно замедлить ее, если требуется собрать много мусора. - person Mark Rotteveel; 19.02.2021
comment
большие кеши RAM тоже могут помочь, поскольку Interbase была разработана много десятилетий назад и по умолчанию очень консервативна. Но мне интересно, принадлежит ли эта подпотка к общей теме оптимизации движка FB больше, чем к этой конкретной теме массового удаления. - person Arioch 'The; 20.02.2021
comment
Да, это не должно приводить к зависанию базы данных, но, к сожалению, в некоторых случаях это так. Вот почему для Firebird 2.5 Classic Server часто оказывается лучшим вариантом. В версии 3.0 дела обстоят лучше, а в 4.0 намного лучше. - person user13964273; 20.02.2021