EnУдаление большого количества записей
Судя по письмам в конференции fido7.su.dbms.interbase, существует определенный процент (около 15) задач, которые требуют периодического удаления большого количества записей. Это либо просто чистка устаревшей информации, либо перенос части данных в архив, но почти всегда – выполнение операции DELETE FROM... над количеством записей от десятков и сотен тысяч до нескольких миллионов.
При отсутствии знаний по архитектуре Firebird и InterBase такая операция действительно становится проблемой. Если DELETE проходит относительно быстро, то последующие операции выборки могут "встать" на длительный период времени (возможно часы или даже десятки часов), во время которого Firebird/InterBase будет интенсивно обращаться к базе данных.
Для начала нужно разобраться, что же происходит при операции DELETE. А происходит вот что – в силу архитектуры многоверсионности записей удаление записей не приводит к их действительному удалению. Просто рядом с версией записи пишется ее новая версия, но без значений полей, а с индикатором удаления. При этом, понятно, занимаемое таблицей дисковое пространство (количество страниц) может увеличиться, хотя на первый взгляд это кажется парадоксальным. (подробнее о сборке мусора и многоверсионности см. список статей внизу).
Далее, в силу уже правил сборки мусора, записи будут реально удалены (удалены предыдущие версии и delete-stub) только при попытке чтения этих записей. Пока никто к ним не обратился – устаревшие записи с диска вычищены не будут.
Итак, после массового удаления даем SELECT COUNT(*)..., возможно с тем же условием что и DELETE, для "вычистки" удаленных записей. Разумеется, результат этого запроса будет равен нулю, т. к. записей нет. Но "мусорные" записи будут собраны. Причем процесс сборки мусора будет никак не быстрее, чем время выполнения DELETE, а зачастую и много дольше (отчасти и потому, что старых версий записей была не одна, а несколько). Лучше всего select count выполнять в следующей после удаления транзакции.
Но самое большое влияние на скорость чистки удаленных записей оказывают неуникальные индексы. В качестве пояснения и примера лучше процитировать одно из писем Анны Харрисон (конец 90х) на эту тему:
"Если возможно, посмотрите статистику сервера (gstat). Найдите индексы с наиболее длинными цепочками дубликатов у таблиц, которым предстоит пережить массовое удаление. Если цепочка больше 7000 строк (ключей) то стоимость сборки мусора будет меньше, если сделать индексы более селективными, например, изменив одиночый индекс на композитный с оригинальным полем в качестве первого поля индекса и полем первичного ключа в качестве второго поля индекса.
Я попробовала удалить 20 тысяч записей из таблицы и собрать мусор – при записи небольшого размера и одном уникальном индексе (на очень медленном процессоре и антикварном винчестере) удаление заняло 47.54 секунды, а сборка мусора – 75.90 секунд (1 минута, 15.90 секунд). После этого я добавила индекс с 20000 дубликатов значений и после этого удаление заняло 38.04 секунды, но сборка мусора заняла 865.47 секунд (14 минут, 25.47 секунд).
Вот статистика запроса, который привел к сборке мусора в обоих случаях:
Обратите внимание на elapsed time и fetches – они отличаются более чем в 10 раз."
Харрисон дальше выводов не делает, кроме упомянутого в цитате изменения неуникального индекса. На самом деле зачастую (в зависимости от процентного соотношения записей которые удаляют, и которые остаются) бывает проще удалить или "выключить" этот неуникальный индекс.
Рекомендуемое изменение индекса – добавление уникального столбца последним сегментом – имеет смысл только если действительно есть проблемы с медленной сборкой мусора у данного индекса. Кроме того, в базах данных, созданных в InterBase 7.1/7.5/2007 и Firebird 2.0 и выше такой проблемы нет.
Уже знакомые с Firebird и InterBase могут спросить – а почему не был предложен способ backup/restore для избавления от мусора? Действительно, этим способом можно пользоваться (не забыв включить опцию Disable garbage collection, то есть gbak -b -g), но при определенных размерах базы данных (несколько десятков гигабайт) бывает выгоднее по скорости удалить индексы, чем делать backup-restore.
Разумеется, сюда не включено время активирования или повторного создания неуникального индекса. Но этот процесс в основном зависит от скорости винчестера, а кроме того, вероятно что количество оставшихся в таблице записей намного меньше, чем количество удаленных, поэтому индексирование пройдет быстро. Сравнительные цифры и другую информацию по пересозданию индексов и их влиянию уже на вставку записей можно посмотреть в статье.
При отсутствии знаний по архитектуре Firebird и InterBase такая операция действительно становится проблемой. Если DELETE проходит относительно быстро, то последующие операции выборки могут "встать" на длительный период времени (возможно часы или даже десятки часов), во время которого Firebird/InterBase будет интенсивно обращаться к базе данных.
Для начала нужно разобраться, что же происходит при операции DELETE. А происходит вот что – в силу архитектуры многоверсионности записей удаление записей не приводит к их действительному удалению. Просто рядом с версией записи пишется ее новая версия, но без значений полей, а с индикатором удаления. При этом, понятно, занимаемое таблицей дисковое пространство (количество страниц) может увеличиться, хотя на первый взгляд это кажется парадоксальным. (подробнее о сборке мусора и многоверсионности см. список статей внизу).
Далее, в силу уже правил сборки мусора, записи будут реально удалены (удалены предыдущие версии и delete-stub) только при попытке чтения этих записей. Пока никто к ним не обратился – устаревшие записи с диска вычищены не будут.
Итак, после массового удаления даем SELECT COUNT(*)..., возможно с тем же условием что и DELETE, для "вычистки" удаленных записей. Разумеется, результат этого запроса будет равен нулю, т. к. записей нет. Но "мусорные" записи будут собраны. Причем процесс сборки мусора будет никак не быстрее, чем время выполнения DELETE, а зачастую и много дольше (отчасти и потому, что старых версий записей была не одна, а несколько). Лучше всего select count выполнять в следующей после удаления транзакции.
Но самое большое влияние на скорость чистки удаленных записей оказывают неуникальные индексы. В качестве пояснения и примера лучше процитировать одно из писем Анны Харрисон (конец 90х) на эту тему:
"Если возможно, посмотрите статистику сервера (gstat). Найдите индексы с наиболее длинными цепочками дубликатов у таблиц, которым предстоит пережить массовое удаление. Если цепочка больше 7000 строк (ключей) то стоимость сборки мусора будет меньше, если сделать индексы более селективными, например, изменив одиночый индекс на композитный с оригинальным полем в качестве первого поля индекса и полем первичного ключа в качестве второго поля индекса.
Я попробовала удалить 20 тысяч записей из таблицы и собрать мусор – при записи небольшого размера и одном уникальном индексе (на очень медленном процессоре и антикварном винчестере) удаление заняло 47.54 секунды, а сборка мусора – 75.90 секунд (1 минута, 15.90 секунд). После этого я добавила индекс с 20000 дубликатов значений и после этого удаление заняло 38.04 секунды, но сборка мусора заняла 865.47 секунд (14 минут, 25.47 секунд).
Вот статистика запроса, который привел к сборке мусора в обоих случаях:
С уникальным индексом С дубликатами
Elapsed time= 75.90 sec Elapsed time= 865.47 sec
Reads = 1694 Reads = 1814
Writes = 1499 Writes = 1732
Fetches = 225,541 Fetches = 2,540,593
Elapsed time= 75.90 sec Elapsed time= 865.47 sec
Reads = 1694 Reads = 1814
Writes = 1499 Writes = 1732
Fetches = 225,541 Fetches = 2,540,593
Обратите внимание на elapsed time и fetches – они отличаются более чем в 10 раз."
Харрисон дальше выводов не делает, кроме упомянутого в цитате изменения неуникального индекса. На самом деле зачастую (в зависимости от процентного соотношения записей которые удаляют, и которые остаются) бывает проще удалить или "выключить" этот неуникальный индекс.
Рекомендуемое изменение индекса – добавление уникального столбца последним сегментом – имеет смысл только если действительно есть проблемы с медленной сборкой мусора у данного индекса. Кроме того, в базах данных, созданных в InterBase 7.1/7.5/2007 и Firebird 2.0 и выше такой проблемы нет.
Уже знакомые с Firebird и InterBase могут спросить – а почему не был предложен способ backup/restore для избавления от мусора? Действительно, этим способом можно пользоваться (не забыв включить опцию Disable garbage collection, то есть gbak -b -g), но при определенных размерах базы данных (несколько десятков гигабайт) бывает выгоднее по скорости удалить индексы, чем делать backup-restore.
Внимание! Данные в статье приведены на конец 90х годов, в настоящее время "железо" (диски и процессоры) гораздо быстрее.
Напоследок, немного данных по реальному проекту от Владимира Мамзикова:
- База данных – 1.5Гб, несколько таблиц с ~1 млн записей, одна таблица с 9 млн записей.
- Удаление ~4 млн записей (delete) – несколько минут
- Сборка мусора (4 млн записей) select count – 20 и более часов
- Операция backup/restore (с disable garbage collection) – в сумме 15 минут
- Отключение неуникального индекса (alter index inactive) – 6 минут
- Сборка мусора (4 млн записей) select count без индекса – 12 минут
- Удаление неуникального индекса (drop index) – несколько секунд (против 6 минут alter index inactive).
Разумеется, сюда не включено время активирования или повторного создания неуникального индекса. Но этот процесс в основном зависит от скорости винчестера, а кроме того, вероятно что количество оставшихся в таблице записей намного меньше, чем количество удаленных, поэтому индексирование пройдет быстро. Сравнительные цифры и другую информацию по пересозданию индексов и их влиянию уже на вставку записей можно посмотреть в статье.