Свёртка, которая не лечит. Часть 2: музей архитектурных ужасов

Часть 2. Музей архитектурных ужасов: что копилось годами, пока все смотрели на размер базы

Это вторая часть цикла. В первой: производственной компании с базой 2,5 ТБ поставили диагноз «слишком большая, надо сворачивать», подрядчик занялся свёрткой, а первая же приёмка показала, что в присланной базе нет признаков свёртки — зато есть следы прерванного обновления конфигурации. Мы тем временем сняли реальную нагрузку и увидели, что ни одна из причин не связана с объёмом. Пришло время посмотреть внутрь.

Сразу оговорка, чтобы не сложилось неверного впечатления. Ниже — не «что натворил подрядчик по свёртке». Ниже — то, что копилось годами, от разных команд и под разное «срочно, к понедельнику». Что-то писали в спешке, что-то настроили и забыли, что-то, судя по всему, ставили «на пробу» и не выключили.

Подрядчик по свёртке в этом музее занимает ровно один зал — последний в этой части. Зато вход в него самый дорогой.

Зал 1. Контур обмена документами, который сошёл с ума

В один прекрасный день электронный документооборот с контрагентами просто умер — на обработку одного цикла уходило больше двух часов. Начинаем копать.

В журнале за один рабочий день — 5 183 ошибки от сервиса ЭДО. Все — на одних и тех же 756 документах, и все с одним и тем же смыслом: «документ в финальном состоянии, изменять нельзя».

Система честно пыталась подписать документы, которые уже давно подписаны, получала отказ, обижалась и через два часа пыталась снова. Так — с прошлого года. Обработка была настроена по расписанию каждые два часа, и каждые два часа совершала одну и ту же бессмысленную работу. Как человек, который каждое утро проверяет, не открылась ли дверь, которую он сам заколотил.

Отдельная находка там же: самый тяжёлый запрос всей базы по данным SQL Server оказался запросом к справочнику сообщений этого же контура — 1,6 миллиарда чтений, до 8 минут на один вызов.

Лечилось одним покрывающим индексом: 750 тысяч чтений на вызов превратились в 4. Не в 4 тысячи — в четыре. Без единой строки кода. Самый дешёвый выигрыш за весь проект, нам даже немного неловко.

Что сделали: привели в порядок статусы зависших документов, чтобы система перестала подписывать уже подписанное, и добавили индекс. Теперь цикл отрабатывает за 2–5 минут.

Зал 2. Ночное обслуживание, которое никогда не заканчивалось

Кто-то когда-то настроил в SQL регламентное задание по обслуживанию индексов и статистики. Звучит умно, и по идее так и должно быть: ночью база приводит себя в порядок, утром люди работают на свежей статистике.

Задание запускалось каждые пять минут в окне с половины девятого до половины двенадцатого вечера. Один проход длился 47 минут, то есть окно было занято целиком — а очередь работ при этом не убывала.

Открыли, посмотрели. Критерий устаревания статистики для мелких объектов был выставлен в «ноль дней» — то есть любая мелкая статистика считалась устаревшей каждый день, независимо от того, менялись ли данные.

В очереди на пересчёт стояло почти 15 тысяч объектов, обрабатывалось по десять за вызов, а весь план целиком на каждом вызове записывался в журнал. Очередь была отсортирована по размеру, крупные объекты возвращались в неё через два дня — и до хвоста, до мелких, она не доходила никогда. Классическое голодание: те, кто в начале очереди, обслуживаются ежедневно, те, кто в конце, — никогда.

За 1 801 запуск журнал накопил 25,8 миллиона строк, служебная база регламента разрослась примерно до 25 гигабайт.

Замер по-честному, по факту изменений, показал: реально требовали обновления 666 статистик. Ещё 34 650 пересчитывались впустую каждый день. А 55 тысяч «никогда не обновлявшихся» оказались статистиками на пустых таблицах — их и обновлять было не к чему.

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

Что сделали: переписали отбор — по факту изменений, а не по календарю. Обновление статистики теперь занимает секунды: боевой прогон обработал 664 объекта за 17 секунд, контрольный следом вернул ноль — очередь закрывается за один заход. Журнал очищен и больше не растёт. Обслуживание индексов переписали отдельно — с ограничением по времени, чтобы проход физически не мог залезть в рабочее утро.

Зал 3. Мёртвые души

Эту находку мы сделали случайно — измеряли совсем другое, время записи документов маркировки, и упёрлись в цифру, которая не сходилась ни с чем. Оказалось, в базе лежит 45 гигабайт регистрации изменений для обмена: 378 миллионов строк в служебных таблицах.

Стали смотреть, для кого это всё регистрируется. Два плана обмена, две брошенные настройки.

Первая — узел обмена со второй ERP-базой, который кто-то когда-то начал настраивать: запустил первичную выгрузку, не дождался её окончания и ушёл. Узел так и завис в состоянии «Выгрузка данных…», а в графах «отправлено» и «получено» — честное «Никогда».

Платформа с тех пор добросовестно регистрировала для него каждое изменение по трём тысячам объектов: зарегистрировано 370 миллионов изменений, выгружено — 2 911. Если считать это КПД, то он получается меньше одной тысячной процента, и это, пожалуй, худший показатель, который я видел за карьеру.

Вторая настройка — незавершённое обновление конфигурации «через копию», брошенное в ноябре прошлого года со статусом «Настройка не завершена».

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

Сорок пять гигабайт аккуратно сложенных писем адресату, который никогда не придёт. Настройки почистили, данные удалили.

Зал 4. Что там с «размером»

Раз уж все смотрели на объём — мы тоже посмотрели, из чего он состоит. Разложили базу по объектам и получили простую арифметику:

• журнал транзакций SQL — 200 гигабайт, который никто не обрезал;
• присоединённые файлы — больше 300 гигабайт, и все лежат прямо в базе (об этом отдельно в следующей части, там есть на что посмотреть);
• регистрация изменений для мёртвых обменов — ещё 45.

Это больше 500 гигабайт, которые убираются штатными средствами, без вмешательства в учётные данные и без остановки производства. Оговорка для тех, кто будет повторять: усечение журнала освобождает место внутри файла, но сам файл на диске от этого не уменьшается — сжатие отдельная операция, и решать её надо с оглядкой на модель восстановления и причину роста.

Остатки регистров накопления — то, что свёртка собственно и режет, — примерно треть объёма. Самая рискованная треть.

И ещё одна деталь — план переноса. Крупнейшие объекты по нему либо не трогались, либо переносились целиком. Заметного уменьшения ждать было неоткуда — и это видно из плана ещё до старта. А цель «уменьшить базу» достигалась и без свёртки: штатными настройками и обслуживанием журнала.

Зал 5. Вечный двигатель. Единственный зал подрядчика — и самый дорогой

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

Примерно через неделю база встала колом.

Начав разбираться в чужом коде, мы ахнули. Механизм писал служебную запись на любое действие пользователя — вплоть до каждого штрихкода маркировки. И перед КАЖДОЙ записью делал запрос к этой же служебной таблице: «а нет ли уже такой?»

Индекса под этот поиск не было. Поле со значением было объявлено строкой в две тысячи символов, при том что в него всегда клали 36-символьный идентификатор. Таблица выросла до 1,4 миллиона строк, и SQL Server на каждое действие пользователя пересканировал её целиком.

Итог в цифрах за сутки: 330 тысяч вызовов, 10,9 миллиарда чтений, около 45 часов процессорного времени. Да, в сутках 24 часа. У сервера много ядер — и все они были заняты этим. Одна служебная таблица одного расширения потребляла больше, чем весь остальной топ нагрузки вместе взятый.

А отключить механизм было нельзя — иначе свёртка потеряла бы накопленную дельту. Инструмент, который должен был бережно перенести данные в новую лёгкую базу, медленно душил старую: чем дольше шёл перенос, тем больше становилась таблица — а с ней и нагрузка. Нагрузку механизма до установки на рабочую базу не проверили — ни при разработке, ни при приёмке.

Мы сняли основную часть индексом (45 часов → около 4), остальное — правка на стороне подрядчика.

А теперь — самое неприятное

Всё, что описано выше, — архитектура. Контуры обмена, регламентные задания, чужие расширения. Их можно найти, если сесть и методично читать код и журналы SQL-сервера. Рано или поздно нашёл бы кто-то другой.

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

Продолжение — в части 3: как документ выпуска продукции блокировал сам себя, что бывает, если не поставить галочку, и почему одна и та же болезнь возвращалась ровно каждое воскресенье в семь утра, будто по расписанию.

Случай обезличен, отдельные детали обобщены. Оценки — мнение автора по результатам замеров, а не оценка свёртки как метода или исполнителей в целом.

Технические подробности для коллег

Зал 1 — тяжёлый запрос ЭДО. Лидер Query Store по логическим чтениям во всей базе: 1,62 млрд чтений за 2 153 выполнения, до 481 с на вызов. План: поиск по некластерному индексу (разделитель, дата), затем Key Lookup для проверки строковых условий и сортировка TOP N. Индекс на стороне СУБД под этот запрос дал 481 с → 0,4 мс. Нюанс, о который мы потом споткнулись: индекс, созданный в SQL в обход конфигурации, платформа не знает и молча теряет при реструктуризации таблицы. Такие индексы надо вести в отдельном реестре и проверять после каждого обновления конфигурации.

Зал 2 — отбор статистик. Вместо возраста — sys.dm_db_stats_properties.modification_counter с порогом 5 % строк. Пустые таблицы отсекаются по числу строк (sys.dm_db_partition_stats, row_count для index_id 0 и 1), а не по выделенным страницам: страницы остаются за таблицей и после удаления данных, а статистика пустой таблицы не получает гистограммы и навсегда числится «никогда не обновлявшейся». Очередь — по дате последнего обновления, а не по размеру; в журнал пишется только обрабатываемая порция. Итоговый расклад по 90 319 статистикам: 54 989 — на пустых таблицах, 34 650 — стабильные, 666 — реальная суточная нагрузка, 13 — разовый долг. Автообновление статистики в базе асинхронное, поэтому ночной регламент здесь не формальность, а основной механизм её точности.

Зал 3 — регистрация изменений. Объём считали по таблицам изменений планов обмена через sys.partitions и sys.allocation_units, подтверждали штатной формой «Регистрация изменений для обмена». Признак брошенного узла: состояние «Выгрузка данных…», отправлено и получено — «Никогда», а зарегистрировано на порядки больше, чем выгружено.

Зал 5 — поиск без индекса. Проверка «нет ли уже такой записи» шла сканом всей служебной таблицы: по Query Store около 330 тыс. вызовов, 10,9 млрд логических чтений и около 45 ч CPU в сутки — больше, чем весь остальной топ вместе взятый. Индекс на стороне СУБД под условие поиска снял основную часть, остаток — около 3,8 ч CPU в сутки. Строка на 2 000 символов под 36-символьный GUID — отдельный вопрос к автору расширения.