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

Еще каких-то 5-7 лет назад мы рекомендовали пересчитывать индексы ежедневно, т.к. далеко не все использовали или успели перейти на дисковые хранилища на основе SSD дисков и строили свои дисковые подсистемы на базе HDD. Соответственно, чем фрагментированнее индекс, тем труднее HDD перескакивать с одного сегмента диска на другой – магнитная головка, последовательное запись/чтение и всё такое. Казалось бы, в SSD эта проблема решена – нет механических частей, данные изначально записываются в произвольные сектора для равномерного использования ресурса диска и это не влияет на скорость чтения. Но нюансы есть.

Примерно год назад наткнулся на полезное видео. Видеохостинг заблокирован РКН, но ссылку всё равно даю, т.к. сходу не смог найти его рабочую версию на других ресурсах – https://www.youtube.com/watch?v=sBEHBq-wKiI. Очень интересное и глубокое исследование о том, нужно ли всё-таки пересчитывать индексы на SSD-дисках или нет. Влияет ли фрагментация индекса на скорость доступа к данным или нет. По её следам и в продолжение первой части цикла статей по регламентному обслуживанию базы данных и родился этот пост.

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

Если совсем кратко, то да, фрагментация влияет на производительность, даже если база данных находится на SSD-дисках. Дело в физическом расположении ключей индекса по страницам дискового массива. У объекта с низкой фрагментацией страницы с данными находятся на физическом уровне одна за одной, и механизм read-ahead (упреждающее чтение) может инициировать чтение в buffer pool большими порциями – до 64 протяженных (смежных) страниц общим объемом 512 кБ. Читать 64 страницы протяженным участком быстрее, чем читать по несколько страниц, инициируя каждый раз операцию ввода/вывода. То есть для высокофрагментированного объекта потребуется больше времени на операции ввода/вывода (IO Time), а для низкофрагментированного объекта, где read-ahead может прочитать данные большими блоками и время ввода/вывода, намного меньше.

Всё верно, результаты исследования это подтвердили, но для 1С:Предприятие, как обычно, есть особенность. А именно, первичный ключ, по которому индекс упорядочивается.

Подавляющее большинство первичных ключей в 1С – это ссылки: на элементы номенклатуры, контрагентов, счетов БУ, первичные документы и т.д. Ссылка генерируется в виде GUID. В базе данных GUID’ы имеют тип binary(16) и сравниваются/сортируются побитно как бинарные данные.

Что это значит? А значит это то, что у ссылки нет автоинкремента, потому как GUID имеет условно случайную структуру – они так задумывались. Даже если использовать Time-Based GUIDs для первичных ключей, они получаются более сгруппированными (GUID’ы выдаются пулом по 32 штуки для каждого сеанса), но всё равно не последовательными. Некоторое ускорение при вставке в кластерный индекс Time-Based GUID даёт, но в целом индекс получается плохо кластеризован и как сказал сам Б.Нуралиев: «Механизм генерации ссылок обеспечивает только их уникальность. Возрастающая последовательность при их генерации не обеспечивается.».

Рассмотрим пример.

Дано: достаточно высоконагруженная база 1С (25-30 тыс запросов/сек). Размер базы 14 Тб. Только регистр бухгалтерии занимает более 1 Тб. На него, точнее на его таблицы, и посмотрим.

Перестроение индексов в базе данных настроено каждую ночь:

Если вернуться к предыдущему рисунку с индексами регистра бухгалтерии, то обратите внимание на процент фрагментации по одному из индексов, который в течение недели стабильно держится в диапазоне 38-39%:

Опрос индексов в мониторинге настроен на 00:00, т.е. до запуска регламентных заданий по пересчету индексов. Таким образом, утром индекс будет перестроен и дефрагментирован, а к концу дня фрагментация индекса Accnt21737ByExtDim_RR достигает тех самых 38-39%. По остальным индексам регистра бухгалтерии примерно такая же картина.

К сожалению, не могу показать картину фрагментации по часам, но, учитывая все вышесказанное про GUID’ы, ситуация с фрагментацией частоизменяемых таблиц будет схожей. Уже через несколько часов после начала активной пользовательской работы весь эффект от дефрагментации сойдет на нет.

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

Еще один важный момент, про который многие забывают – фрагментация индексов влияет на производительность в том случае, если в запросах чтение данных идет с диска, а не из buffer pool. Но если оперативной памяти достаточно, а может быть даже у вас вся база помещается в памяти, то это вообще не так важно.

Как это увидеть? Есть счетчик PLE (Ожидаемый срок жизни страницы памяти), который показывает эффективность использования буферного кэша. Идеальное его поведение – это рост в течение рабочего дня и "обрушение" только во время регламентных работ. На рисунке ниже видим, что график PLE (зеленый) за несколько дней явно далек от идеальной картины мира. Поэтому при выполнении запросов SQL-сервер обращается за данными к диску. Обратите внимание на синий график – как только PLE падает, начинаются значительные очереди к диску.

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

Посмотрим в мониторинге Perfexpert на трассу тяжелых запросов, которые нагружают память и диск:

В нижней панели рисунка – группы запросов, отсортированные по нагрузке на диск (доле физических чтений). Возьмем первый вид запроса с хешем 11630710602545325702. В верхней панели выведен весь список из 840 запросов этой группы. Хорошо видно, что каждый такой запрос делает очень много не только логических чтений (из памяти), но и физических (с диска). Возьмем, к примеру верхние строчки. Видим 20-30 млн. логических чтений, из которых почти столько же делается с диска. Т.е. запросу почти всегда не хватает буферного пула, и он обращается к диску. В целом 30 млн. логических чтений – это очень много и кэша, конечно, не хватит. Поэтому главный вектор улучшения производительности здесь должен лежать в плоскости оптимизации самих запросов, чтобы они выполнялись с меньшим количеством логических чтений и, как следствие, с меньшим количеством физических чтений или вообще без них. А фрагментация индекса – это дело даже не второго порядка. Под оптимизацией подразумевается не только переписывание запроса на стороне приложения, но и индексный тюнинг.

Если запрос оптимизации не подлежит (что вряд ли), также имеет смысл подумать в сторону увеличения памяти для расширения буферного пула. Но такая возможность не всегда имеется.

Еще один параметр, связанный с индексами, о котором еще не сказал – fill factor. Может устанавливаться как для всей СУБД, так и для каждого индекса. Именно эта настройка определяет, сколько свободного места для новых данных будет в индексе после перестроения. По умолчанию всем индексам параметр fill factor установлен в 0, то есть значение считывается из параметров базы данных. В параметрах сервера СУБД также установлено по умолчанию значение 0 (оно же 100%). Это означает, что после перестроения индекса на странице не остается свободного места для вставки новых данных. Если СУБД добавляет данные в такой индекс, ей требуется выделять место в другой секции файла данных. Другими словами, любое добавление или изменение данных моментально приводит к росту фрагментации по такой таблице и росту количества страниц при чтении.

Соответственно, если значение fill factor указывается менее 100%, например, 80%, то значит на каждой странице индекса остается 20% свободного пространства для вставки данных без разбиений страниц.

Чтобы понять нужно ли вам в вашей базе данных что-то с этим делать, можно обратиться к счетчику Количества разбиений страниц в секунду (page splits/sec) и сопоставить это с дисковыми очередями.

Если корреляция есть, как на рисунке ниже, то значит идет частое разбиение страниц с повышенной нагрузкой на диск. Надо попробовать снизить fill factor и оценить эффект.

Слишком низкое значение fill factor (<70-80%) приводит к уже значительному и не всегда оправданному росту места, занимаемого базой. Поэтому «золотой серединой» считаются значения из диапазона 80–90%. Но это не догма. Размер базы при этом увеличится не на много – процентов на 5-10% и остаётся место под вставки новых данных. Но если в какие-то таблицы данные вставляются большими пачками (массовые изменения), то эта настройка не поможет. Поэтому нужно смотреть по месту – конкретно для вашей ситуации, для ваших потоков, для ваших наборов индексов.

Выводы

В качестве выводов ранжирую механизмы улучшения производительности ИТ-системы как это видим мы, спустя 20+ лет практики в этой области.

В список не попали рекомендации по процессору и по другим ресурсам. Например, только нюансам с процессором мы посвятили аж две статьи (статья 1 и статья 2). Здесь только связка память-диск:

1. Оптимизация запросов, индексный тюнинг.
   Как писал выше, цель этой операции – не только ускорить запрос, но и по возможности снизить количество логических чтений. Чем меньше логических чтений, тем меньше запрос обращается к оперативной памяти и тем меньше к физической (диску).

2. Обслуживание статистик.
   Вовремя пересчитанные статистики – это увеличение вероятности выбора правильного плана выполнения запроса, а значит запрос будет стабильно выполняться.

3. Добавление оперативной памяти (увеличение buffer pool).
   Это самая простая и, пожалуй, доступная рекомендация. Чем больше данных будет в кэше, тем меньше СУБД будет обращаться к диску.

4. Выбор значения FILL FACTOR.
   Цель – найти компромисс между скоростью фрагментации индексов и размером базы данных.

5. Дефрагментация индексов (таблиц).
   Под дефрагментацией я буду подразумевать перестроение индекса командой
   ALTER INDEX ...REBUILD.
   Использование REORGANIZE для высоконагруженных систем не эффективно.

   Дефрагментацию делать нужно, даже если у вас SSD. Очень часто ее не делают потому, что база высоконагруженная, большая. Соответственно в тех. окно не успевают, а рабочее время – это блокировки. Кроме того, сильно фрагментированный индекс может занимать почти в 2 раза больше страниц, чем ему требуется. А значит снизится процент попадания в кэш (Buffer Cache Hit Ratio).

   Особенно эффективна дефрагментация для баз данных на чтение, архивных, где данные меняются не интенсивно, но при этом пользователи часто работают с отчетами за огромные периоды, поэтому этих данных точно нет в кэше – читаются с диска. Если будем считать, что запросы выполняются оптимально по плану и по логике (выполнены пп.1 и 2 данного перечня), то в таких случаях должен быть хорошо заметен эффект от дефрагментации. Но это только 5-й шаг в оптимизации и ускорении вашей ИТ-системы!

   Так что, периодичность перестроения индексов выбирайте самостоятельно. Это может быть и раз в неделю, и раз в месяц. У нас есть даже примеры, когда клиенты перестраивает индексы раз в год.

6. Улучшение скорости дискового хранилища.
   Эта рекомендация на последнем месте, т.к. встречается проблема с дисками все реже и реже. Хотя, зачастую, многие администраторы начинают оптимизацию производительности с этого шестого (!) шага. И в большинстве случаев это, мягко говоря, является преждевременным.

Продолжение следует…

Ссылки на остальные части Записок оптимизатора 1С:

1. Записки оптимизатора 1С (ч.1). Странное поведение MS SQL Server 2019: длительные операции TRUNCATE

2. Записки оптимизатора 1С (ч.2). Полнотекстовый индекс или как быстро искать по подстроке

3. Записки оптимизатора 1С (ч.3). Распределенные взаимоблокировки в 1С системах

4. Записки оптимизатора 1С (ч.4). Параллелизм в 1С, настройки, ожидания CXPACKET

5. Записки оптимизатора 1С (ч.5). Ускорение RLS-запросов в 1С системах

6. Записки оптимизатора 1С (ч.6). Логические блокировки MS SQL Server в 1С: Предприятие

7. Записки оптимизатора 1С (ч.7). «Нелогичные» блокировки MS SQL для систем 1С предприятия

8. Записки оптимизатора 1С (ч.8). Нагрузка на диски сервера БД при работе с 1С. Пора ли делать апгрейд?

9. Записки оптимизатора 1С (ч.9). Влияние сетевых интерфейсов на производительность высоконагруженных ИТ-систем

10. Записки оптимизатора 1С (ч.10): Как понять, что процессор — основная боль на вашем сервере MS SQL Server?

11. Записки оптимизатора 1С (ч.11). Не всегда очевидные проблемы производительности на серверах 1С.

12. Записки оптимизатора 1С (ч.12).  СрезПоследних в 1C:Предприятие на PostgreSQL. Почему же так долго?

13. Записки оптимизатора 1С (ч.13). Что не так в журнале регистрации 1С в формате SQLitе?

14. Записки оптимизатора 1С (ч.14.1). Любите свою базу данных и не забывайте обслуживать

15. Записки оптимизатора 1С (ч.14.2). Пересчет индексов на SSD-дисках. Делаем или игнорируем?