Базы данных. Несколько шагов до серьезного обслуживания

22.05.22

База данных - Администрирование СУБД

Практические примеры настройки обслуживания баз данных для SQL Server. Актуально для любых приложений.

Об этом много сказано

Тема обслуживания баз данных (индексов и статистик) поднимается достаточно часто в разных статьях, официальной документации, на форумах и так далее. Обычно дается описание процессов обслуживания, зачем они нужны и какие бывают, на что влияют. И, конечно же, как сделать настройку обслуживания. Последнее обычно преподносится на базовом уровне, но, конечно, не всегда.

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

Мы сосредоточимся на решении именно практических задач. Теорию Вы можете найти по ссылкам в конце статьи.

Общие слова

Прежде чем мы перейдем к примерам, определимся с тем, что будем делать.

Обслуживание подразумевает две больших части:

  1. Поддержание фрагментации индексов на приемлемом уровне. В идеале процент фрагментации у каждого индекса должен быть равен 0 или близок к этому значению.
  2. Состояние статистики должно быть в максимально в актуальном состоянии. При изменении строк в таблице, объекты статистики должны иметь гистограмму распределения значений, которая отражает состояние объектов базы наиболее актуальным образом.

Все это влияет на формируемые планы запросов. В самых общих чертах, если индекс имеет высокую фрагментацию или статистика знатно устарела, то оптимизатор SQL Server даже не будет пытаться использовать индекс и выполнит полное сканирование таблицы. Последнее, как Вы понимаете, не самая быстрая и оптимальная ситуация. Есть и другие последствия отсутствия обслуживания, но это совсем другая история. Один из таких примеров был рассказан в статье "Обслуживание баз данных. Не так просто, как кажется".

Также сразу отметим, что никаких сторонних программ для настройки обслуживания мы использовать не будем. Чистый TSQL, возможно, в готовых скриптах и/или хранимых процедурах. Но никакого стороннего софта.

Итак, настало время первого примера!

Примеры, примеры, примеры

На старт, внимание, марш!

Типичное обслуживание

Когда речь заходит об обслуживании, то обычно начинают с создания плана обслуживания, где используют готовые компоненты. Для этого создаем план обслуживания через SQL Server Managment Studio в разделе "Managment -> Maintenance Plans" с субпланом "FullMaintenance".

Механизм планов обслуживания базируется на "обрезанной" версии SSIS, если так можно выразиться. Для использования готовых компонентов нужно использовать именно его.

Альтернативным вариантом является использование заданий (job'ов) агента SQL Server, просто указывая явно скрипты TSQL для выполнения.

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

 
 Rebuild Index Task
 
 Reorganize Index Task
 
 Update Statistics Task

Мы пробежались по основным настройкам каждого доступного компонента.

Предположим, что у нас типичная задача - небольшая база на SQL Server, работ ночью не ведется и в качестве технологического окна у нас время с 21:00 до 06:00. Идеально! Тогда настроим запуск обслуживания как на схеме выше, чередуя операции следующим образом:

  1. Перестроение индексов с указанием нужной базы и выбрав все таблицы и представления. При этом не используем онлайн-перестроение, оставляем отключенной сортировку в TempDB и так далее. В общем все настройки стандартные как на скрине выше, разве что MAXDOP можно поставить максимальный, пусть все ресурсы сервера будут выделены под перестроение.
  2. Вторым шагом реорганизация индексов. Также оставляем все настройки по умолчанию. Реорганизацию оставляем именно вторым шагом, т.к. в первую очередь нужно выполнить обслуживание индексов, у которых фрагментация выше 30%.
  3. И последней операцией устанавливаем обновление статистики. Пусть выполняется для всех объектов статистики и методом полного сканирования.

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

Так как база небольшая, то, скорее всего, весь процесс обслуживания завершится за 30-60 минут ночью и за 5-15 минут днем, а может и еще быстрее.

Подведем небольшой итог.

Плюсы:

  • Простота настройки

Минусы:

  • Отсутствие контроля выполнения
  • Блокирование работы запросов в момент обслуживания
  • Нет возможности проанализировать результаты выполнения обслуживания в динамике, нет истории работы обслуживания

Но если база небольшая, то все перечисленные минусы несущественны. Что такое небольшая база? Понятие относительное :). Главное, что нужно понять, так это если такое обслуживание Вас полностью устраивает, то делать что-то более сложное нет смысла.

Первые проблемы

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

Выше мы уже говорили, что SQL Server поддерживает онлайн-перестроение индексов и мы можем его использовать для тех объектов, которые это поддерживают. Те объекты, для которых онлайн-перестроение нельзя выполнить из-за использования legacy-типов в полях (text, ntext, image), будут обслужены обычным образом с блокировкой работы с ними. Так что от всех проблем мы не избавимся, но других настроек в стандартном компоненте нет. Поэтому следует выполнить следующие изменения:

  • В шаге перестроения индексов, который выполняется в ночное время, установить использование онлайн перестроения. По сравнению с обычной операцией обслуживания онлайн перестроение требует больше ресурсов по CPU и по использованному месту, да и лог транзакций при полной модели восстановления будет заполнен больше. Такова цена беспрерывной работы запросов.

  • Степень параллелизма желательно установить ограниченным значением, чтобы CPU не был загружен на 100% во время обслуживания. Ведь блокировки не единственная проблема, которая может помешать работе информационной системе. Высокая нагрузка от обслуживания тоже может остановить работу. Общая рекомендация поставить MAXDOP = 30% от общего количества ядер. Например, если на сервере 24 ядра, то под перестроение индексов выделить только 8.
  • Остальные настройки оставляем как есть.

Тут также подведем небольшой итог.

Плюсы:

  1. Простота настройки
  2. Минимальное влияние на работу системы во время обслуживания

Минусы:

  1. Отсутствие контроля выполнения
  2. Блокирование работы запросов в момент обслуживания для тех объектов, в которых онлайн перестроение невозможно
  3. Нет возможности проанализировать результаты выполнения обслуживания в динамике, нет истории работы обслуживания

Опять же, если последние 3 причины не критичны, то все отлично!

Большой шаг

Компания развивается, и информационная база растет. У нас появились большие таблицы и много, а технологическое окно уменьшилось: с 21:00 до 23:00. Появились склады, которые работают 24/7. Кроме этого остается старая проблема с объектами, которые не поддерживают онлайн перестроение. Мы должны их обслуживать так, чтобы они минимально создавали блокировки. Кроме этого, нужно гарантировать, что обслуживание не выйдет за пределы установленного технологического окна с 21:00 до 23:00.

К сожалению, стандартными компонентами обслуживания, которыми пользовались до этого момента, данную задачу решить уже нельзя. Поэтому мы пойдем другим путем. Мы создадим служебную базу обслуживания и мониторинга с именем "SQLServerMaintenance", а далее наполним ее объектами следующим скриптом.

 
 Скрипт создания служебной базы мониторинга и обслуживания

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

Вместо трех шагов теперь только 2, в каждом свои скрипты для выполнения операций.

 
 Шаг обслуживания индексов
 
 Шаг обслуживания статистик

На первом шаге мы обязательно установим таймаут выполнения операции в 2 часа. Это соотносится с указанным в настройках диапазоном времени с 21:00 до 22:30 (1.5 часа). Таймаут в 2 часа это последний рубеж защиты, чтобы процедуры обслуживания не вышли за 23:00. Такое может произойти, если индекс начал операцию перестроения или реорганизации в 22:20 и к 23:00 не завершился, то таймаут выполнения команды ее прервет "насильно".

При этом для операции обновления статистики таймаут выполнения мы не ставим, т.к. она не мешает работе других запросов. При этом если обслуживание индексов работало до 23:00 (с таймаутом или без), то обслуживание статистик не будет запущено. Считаем это проблемными ситуациями и они не должны возникать часто.

Также добавим дополнительный субплан для обслуживания индексов, которые не поддерживают онлайн перестроение. Считаем, что таких объектов не много и их можно обслуживать раз в неделю. Для этого добавим субплан с запуском раз в неделю, например в субботу в 23:00 и отдаем на работу скрипта 30 минут. Скрипт будет таким.

 
 Еженедельное обслуживание индексов без поддержки онлайн перестроения

Таймаут для операции установим в 1 час, то есть в 00:00 операция будет завершена в любом случае.

И последнее изменение - это дополнительные шаги обслуживания статистики. Ранее мы запускали в дневное время отдельное обновление статистики, а теперь будем выполнять это 3 раза (не считая основного ночного обслуживания): в 06:00, 13:00 и в 18:00.

 
 Регулярное обслуживание статистики

Готово! Небольшие итоги:

Плюсы:

  1. Минимальное влияние на работу запросов во время обслуживания (как по блокировкам, так и по нагрузке на базу).
  2. Максимально актуальная статистика во время всего рабочего дня.
  3. Полный контроль над выполняемыми операциями. Приоритет отдается выполняемым запросам, а не обслуживанию. Работа информационной системы не будет нарушена.
  4. Практически полное покрытие задач обслуживания баз данных в большинстве мелких, средних и крупных баз данных.

Минусы:

  1. Более сложная схема настройки, требующая понимания работы процессов обслуживания и их сопровождения.
  2. Необходимость прочитать инструкции и документацию по SQL Server в нештатных ситуациях.
  3. Желательны навыки работы с TSQL.

Может ли потребоваться изменять обслуживание еще как-то?

А вот и полная модель

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

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

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

Чтобы этого не случилось - установим ограничения на использование файла логов транзакций. Дополним предыдущий скрипт параметром ограничения.

 
 Ограничиваем использование лога транзакций

Параметры @maxTransactionLogSizeUsagePercent и @maxTransactionLogSizeMB как раз и решают эту задачу. Но нужно учитывать, что проверка выполняется перед началом выполнения операции обслуживания. Если индекс начал перестраиваться, то ограничение на текущий процесс уже не повлияет. Поэтому также рекомендуется спланировать резерв места на диске с файлом лога транзакций. А если, мало ли, размер логов при перестроении может превысить 2 ТБ, то нужно создать несколько файлов логов транзакций, чтобы обойти это ограничение. Да, максимальный размер одного файла лога транзакций равен 2 ТБ.

Разделяй и властвуй

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

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

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

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

  -- Отбор по конкретной таблице
  ,@ConditionTableName = 'IN (''_AccumRg1265'',''_AccumRg505'')'

или даже поставить отбор на конкретный индекс:

  -- Отбор на конкретный индекс (отбор по таблице в этом случае не обязателен)
  ,@ConditionIndexName = '= ''_AccumRg505_1'''

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

Слишком большие объекты

Следующая проблема, которая может появиться в больших базах - это обслуживание ооооооочень больших, огромных объектов в пределах окна обслуживания. Например, у нас есть 2 часа на обслуживание индексов. Но что, если для перестроения индекса, размер которого 1 ТБ, нужно 5 часов.

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

Тут на помощь приходят возобновляемые операции перестроения индексов, доступных со SQL Server 2017. Работает это так:

  • Вы запускаете операцию перестроения индекса в онлайн режиме, указав параметр RESUMABLE = ON.
ALTER INDEX PK_1 
ON [dbo].[_Acc1]
REBUILD
WITH (ONLINE = ON, RESUMABLE = ON);
GO
  • Процесс перестроения выполняется, пока Вы не прервете его явно командой завершения сессии (например, если таймаут сработал) или не указав остановку явно.
ALTER INDEX PK_1 ON [dbo].[_Acc1]  PAUSE
  • После остановки операции файл журнала транзакций может быть освобожден после выполнения бэкапа логов. Да, перестроение индекса еще не завершено, но освободить файл журнала транзакций можно.
  • Можно проверить список операций перестроения, доступных для продолжения, а также состояние прогресса перестроения
SELECT 
  total_execution_time, 
  percent_complete, 
  name,
  state_desc,
  last_pause_time,
  page_count
FROM sys.index_resumable_operations;
  • Возобновляем операцию перестроения при необходимости. Например, в следующем технологическом окне обслуживания.
ALTER INDEX PK_1 ON [dbo].[_Acc1]  RESUME
  • Или можно прервать операцию окончательно.
ALTER INDEX PK_1 ON [dbo].[_Acc1]  ABORT

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

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

 
 Возобновляемое перестроение индексов

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

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

  1. Проверяются списки объектов, для которых нужно выполнить возобновление перестроения. При этом учитываются условия по таблицам и индексам.
  2. Если такая операция есть в списке, то возобновляем ее работу. Если нет, то идем дальше.
  3. Далее выполняем обычные операции обслуживания. При этом если на прошлом шаге было завершено возобновляемое перестроение индекса, то на следующих шагах обслуживания эти объекты будут пропущены. Это сделано для того, чтобы один и тот же объект не был перестроен дважды.

Этот механизм является спасительным для многих больших баз данных. А некоторые люди еще говорят, что разницы между SQL Server 2012 и SQL Server 2019 нет, вот им пример :)

Да здравствует AlwaysOn

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

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

Подобная ситуация может возникнуть не только при сбое связи, а, например, если после перестроения 1 ТБ индекса эти изменения будут отправляться на копии. Пока все изменения после перестрое ния не "уйдут" на копии, то лог транзакций также не сможет быть освобожден.

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

Также нужно понимать, что операции обслуживания нужно выполнять только в первичном узле (основной базе), а на копиях баз настраивать обслуживание просто нет смысла. Это же копии в режиме "только для чтения". Перестроение индексов или обновление статистик там недоступно.

Информацию про использование AlwaysOn Вы можете посмотреть здесь, в том числе описание некоторых нюансов и настроек.

Комбо!

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

Если Вы будете использовать служебную базу, о конторой идет речь в этой статье, то все операции перестроения индексов или обновления объектов статистики логируются. Например, в таблице "MaintenanceActionsLog" можно посмотреть когда, как и почему объект обслуживался.

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

Также может возникнуть необходимость запускать скрипты перестроения индексов параллельно друг другу, для ускорения обслуживания, например. Но тут возникает нюанс! Если есть активная операция перестроения индекса, то DMV sys.dm_db_index_physical_stats не сможет получить текущее состояние индексов, пока перестроение не будет завершено. В итоге операции перестроения параллельно и не будут запущены. Но и тут есть выход!

Вы можете запустить сбор информации о состоянии объектов базы данных заранее через команду:

EXECUTE [SQLServerMaintenance].[dbo].[sp_FillDatabaseObjectsState] 
   @databaseName = 'BSL-ORIG'

Команда вызовет sys.dm_db_index_physical_stats и сохранит результаты в таблицу "DatabaseObjectsState". А в скриптах перестроения индексов можно указать параметр @usePreparedInformationAboutObjectsStateIfExists, тогда повторного анализа объектов выполнено не будет и обслуживание будет использовать информацию из таблицы "DatabaseObjectsState". 

-- Используем ранее сохраненную информацию о состоянии объектов базы данных
,@usePreparedInformationAboutObjectsStateIfExists = 1

Но есть нюанс! Информация об объектах базы должна быть собрана в последние 12 часов на момент вызова обслуживания. Иначе информация будет считаться устаревшей и запустится обычный анализ объектов.

Таким образом, можно запускать 2 и более процессов обслуживания индексов, не опасаясь их блокировки друг другом на этапе анализа объектов базы.

Как отслеживать качество обслуживания

Это отдельная тема, но в самом простом виде можно действовать так:

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

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

Но в целом это отдельный разговор. Сегодня об этом речь не идет.

Это еще не конец

Мы прошли долгий путь и каждый шаг дался нам не просто:

  1. Сначала настроили базовое обслуживание с помощью поставляемых компонентов SQL Server.
  2. Затем усложнили обслуживание для уменьшения влияния на информационную систему.
  3. После вынужденно отказались от штатных компонентов и использовали свои скрипты и наработки, поставили ограничения на операции обслуживания, повысили надежность работы и пресекли выход за рамки технологического окна. А также улучшили онлайн обслуживание индексов.
  4. Далее рассмотрели нюансы при работе в полной модели восстановления базы.
  5. Разбили обслуживание на регулярное и еженедельное для оптимизации работы.
  6. Изменили стратегию обслуживания для огромных объектов, внедрив возобновляемое перестроение индексов.
  7. Рассмотрели особенности обслуживания при использовании AlwaysOn.
  8. И напоследок обсудили логирование и контроль обслуживания.

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

Желаю создать свой идеальный план и стратегию обслуживания!

А эта статья и полезные материалы в конце статьи должны в этом помочь.

P.S. Для PostgreSQL тоже можно настроить обслуживание на любой вкус и цвет. Если к этому есть интерес, то можно тему также проработать.

Другие ссылки

Авторские разработки

 
 Другие разработки (бесплатные и за $m)

sql server база данных обслуживание индексы статистика СУБД

См. также

Администрирование СУБД Системный администратор Программист

В крупных компаниях, где много типовых и сильно доработанных баз с режимом работы 24/7, переход с MS SQL на PostgreSQL затягивается. Получается гетерогенная структура – когда прод уже на PostgreSQL, а разработка и тестирование – пока на MS SQL. О том, какие варианты помогут постепенно перевести прод с несколькими базами MS SQL на PostgreSQL, не сломав среду тестирования и разработки, пойдет речь в статье.

21.11.2024    3151    a.doroshkevich    7    

15

Администрирование СУБД Системный администратор Платформа 1С v8.3 Бесплатно (free)

Пользовался ранее https://infostart.ru/1c/articles/1120161/#, но она устарела, т.к. службы запускаются через systemctl, да и сами службы слегка изменились. Возможно, где-то на ИТС уже есть нужная инструкция, но мне не попалась.

15.11.2024    418    Baser    2    

1

HighLoad оптимизация Администрирование СУБД Системный администратор Программист Платформа 1С v8.3 Россия Бесплатно (free)

Мы исследуем проблему долгого выполнения запросов PostgreSQL при использовании конструкции VALUES: когда она возникает, как на нее можно повлиять, а главное, почему ее продуманная отработка важна для более быстрого функционирования решений на базе 1С

12.11.2024    1033    Tantor    20    

15

HighLoad оптимизация Администрирование СУБД Механизмы платформы 1С Программист Платформа 1С v8.3 ИТ-компания Россия Бесплатно (free)

В данной статье мы рассмотрим, как работает механизм временных таблиц на postgres на платформе 8.3.23 и что изменилось в нем при добавлении новых возможностей в платформе 8.3.25. А также на примере покажу, как понимание работы платформы позволяет оптимизировать СУБД для работы с 1С.

29.10.2024    3653    Tantor    38    

35

Администрирование СУБД Системный администратор Программист Бесплатно (free)

CDC - очень мощный механизм, который можно использовать во многих сценариях, возможность развернуть его в Docker показывает простоту и лёгкость данной технологии.

08.10.2024    892    AlexSvoykin    1    

7

Администрирование СУБД Системный администратор Программист Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Анализ и решение ошибок СУБД. Во время реиндексации базы Ошибка СУБД: Microsoft SQL Server Native Client 11.0: Не удалось найти объект "ИмяБазы.dbo._RefSInf21806", так как он не существует, или отсутствуют разрешения. Во время проверки целостности Ошибка СУБД: Microsoft SQL Server Native Client 11.0: Недопустимое имя объекта "dbo._RefSInf21806".

19.09.2024    4735    Xershi    10    

17

HighLoad оптимизация Администрирование СУБД Архивирование (backup) Системный администратор Программист Платформа 1С v8.3 Бесплатно (free)

Бэкап в Postgres состоит из набора граблей, которые нужно обойти для успешного восстановления. Они заложены в самых неожиданных местах от предмета резервного копирования (база или кластер) до структуры каталогов. Один неверный шаг и восстановление будет невозможным. Почему нельзя было сделать проще, как в MS SQL или Oracle? Почему бэкап в Postgres оставляет впечатление чьей-то лабораторной работы? Статья адресована прежде всего специалистам 1С, избалованным комфортом в MS SQL, в суровых буднях импортозамещения на Postgres.

13.08.2024    3099    1CUnlimited    9    

5
Вознаграждение за ответ
Показать полностью
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. e][tend 22.05.22 23:08 Сейчас в теме
Спасибо, однозначно в избранное.
2. sapervodichka 6916 23.05.22 02:00 Сейчас в теме
Голосую за выпуск твоей статьи по обслуживанию PostgreSQL, ждём! (за эту статью также огромное спасибо)
gigas116; sunduk85; fatman78; skykill1983; Oxygraphis; spawn_a; a_n_d_rey; INSh; SuhoffGV; malutinss; KonstK; slavek; user720820720; zakiap; kuzyara; PaltSS; AlexKo; ivanov660; vetgol; Aleskey_K; parshin; user1433102; gigapevt; s22; TormDV; cdiamond; shtinalex; PowerBoy; pavlov_dv; davdykin; METAL; +31 Ответить
3. redfred 23.05.22 07:23 Сейчас в теме
Порог в 30% фрагментации для ребилда ещё релевантен в наше время? Тем более на ssd?
6. пользователь 23.05.22 09:45
11. redfred 23.05.22 10:36 Сейчас в теме
(6) Этой рекомендации два десятка лет, носители сильно продвинулись с той поры. Да и цифру ту, емнип, по большому счёту чуть ли не с потолка взяли. В последнее время всё чаще слышу рекомендацию ориентироваться на 60-80% и, честно говоря, это кажется весьма разумным в условиях быстрого случайного доступа.
12. пользователь 23.05.22 10:38
(11) конечно, я бы мог написать длинный комментарий по этому поводу, описать что да как влияет. Но зачем :)

Информации много в интернете по этому поводу. А так это отдельная тема, где нужно затрагивать как эти показатели влияют на планы, эффективность работы с памятью и даже блокировки.

Комментария не хватит.

Поэтому, рекомендую просто почитать доступную информацию и провести тесты. Не стоит полагаться на слухи.
4. capitan 2511 23.05.22 09:39 Сейчас в теме
ИМХО
Rebuild Index Task
Reorganize Index Task
в таком порядке взаимоисключающие
Update Statistics task
корифеи рекомендуют
DBCC FREEPROCCACHE
5. пользователь 23.05.22 09:45
13. capitan 2511 23.05.22 16:00 Сейчас в теме
(5)Юрий, пардоньте.
Я в цикле
Работа мешает читать ИС внимательно, а ИС мешает внимательно делать работу)
Но есть нюанс
Если я так прочитал план обслуживания то ненулевая вероятность что следующий админ так же его прочитает и грохнет дефрагментацию
Плюс минус мы теряем в таком плане контроль когда стартанет перестроение
Я бы лучше разделил на два
и FREEPROCCACHE как по вашему надо делать?
15. пользователь 23.05.22 17:46
7. MVK80 23.05.22 09:48 Сейчас в теме
(0), абзац про Типичное обслуживание. А разве перестроение индексов не дефрагментирует их? Я к тому, что есть ли смысл использовать реорганизацию сразу после перестроения индекса?
8. пользователь 23.05.22 09:50
(7) Вы же понимаете, что перестроение сработает для индексов с 30% фрагментации и выше. А дефрагментация сработает для остальных (от 15 до 30%).

Посмотрите, пожалуйста, внимательно.
9. MVK80 23.05.22 09:52 Сейчас в теме
(8), тогда вопросов нет :). Всё понятно. Я просто видел планы обслуживания где без анализа процента фрагментации делались подряд эти две операции.
10. пользователь 23.05.22 09:52
14. Silenser 613 23.05.22 17:36 Сейчас в теме
Дружище, снимаю шляпу. А можно выдать несекретную инфу, что же это за база, которая при ежедневном обслуживании требует обновления индексов и/или статистики, что может занимать более 2х часов? Размер, число пользователей, конфигурация или самописная? У меня было пару раз, когда база на УПП 8.3 под 800 Гб обновляла индексы и статистику более 4х часов, но это исключение, когда перелопатили кучу документов в закрытом периоде. Обычно же обслуживание занимало в пределах часа.
16. пользователь 23.05.22 17:47
(14) дружище, без комментариев :)
17. vit59 59 24.05.22 09:38 Сейчас в теме
Спасибо, много полезного для себя нашел !
18. fhqhelp 348 24.05.22 22:20 Сейчас в теме
Ну а где же про ssd и новомодное "отказ от дефрагментации на основе avg_fragmentation_in_percent"?
Вот это вот все: https://blogs.msmvps.com/gladchenko/nodefrag/ ? .. схожие мысли были вроде и у https://www.brentozar.com/blog/ - мол не увлекайтесь древним avg_fragmentation_in_percent >10-30%.

Сам сижу на массивах смешанных - там ssd, сям шпиндели, а вот тут - смесь первого и второго и хитрый контроллер еще и сам распихивает кому куда.
Сделал несколько выводов
- на шпиндельном массиве можно пользоваться старой схемой, но задрать пороги с 10-30 до скажем 30-80 - без особо заметной деградации
- на ssd фрагментация внешняя не шибко влияет на index seek
- зато влияет на скан (но как часто бывает тот скан?)
- и влияет на буферпул! иные индексы раздувает так безбожно, что ниприведдихосспидя их вытащит в память - задержки pageiolatch, падение page life expectancy .. а если они постоянно в памяти?
- и влияет на объем бд, и бывает сильно - а место не резиновое, и бекапы медленные
- и реиндексация на ssd все равно нужна, но лучше понятное дело смотреть на avg_page_space_used_in_percent - и пороги делать разные для "оно постоянно в памяти" и "оно редко в памяти"
- можно, как ни странно, довольно точно отличить где ssd, а где нет - через sys.dm_io_virtual_file_stats (в случае смешанных массивов конечно ой)


А так да.. напарывался на "от вашей реиндексации место в логе кончилось", и сделал аналогичные предохранители
И "alwaysOn загнулось от реиндексации" тож бывает.
И еще блокировки на ней бывают, и WAIT_AT_LOW_PRIORITY не всегда устраивает..
19. mikukrnet 182 25.05.22 15:53 Сейчас в теме
Так как полное сканирование не используется, то даже для многотеррабайтных баз эта операция будет выполняться за 5-30 минут.


С каких это пор апдейт статистик в многотерабайтных базах выполняются за минуты? Для таких баз не лишним будет рассказать про многопоточный апдейт
20. пользователь 25.05.22 18:23
(19) рассказать то можно.

И про шторм IO в таких случаях и как ускорить обновление для этих самых террабайтных баз. Но делать из статьи книгу было бы странно :)))

Вы, видимо, говорите про обновление статистики через FULLSCAN, что не всегда обязательно. Посмотрите на обновление через процентную выборку данных для анализа.
21. mikukrnet 182 26.05.22 09:47 Сейчас в теме
(20) вопрос только в том, насколько полезен будет апдейт статистик без фулсскана...
22. пользователь 26.05.22 09:59
(21) И отвечать я на него, конечно, не буду :)))))

Вопросы в вакууме все дела)))
23. user1628996 23.11.22 13:38 Сейчас в теме
Огромное спасибо за статью! Все написано по существу, читать легко, и понятными становятся даже те вещи, с которыми столкнулся впервые. С удовольствием читаю все Ваши публикации.

Подскажите знатоки:
Framnetation > - фрагментация должна быть больше определенного процента. Общепринятым правилом считается, что реорганизацию нужно выполнять, если процент фрагментации находится между 15 и 30%.
- Есть ли ссылка на оф даташит по этой информации, или же эти показатели выявлены эмпирическим путем? В разных статьях и комментах встречал эти показатели, но на офе так и не нашел. И относится ли такие же значение к базам другой структуры (не 1с)?
24. rom-x 152 17.04.23 09:38 Сейчас в теме
Интересно какая продолжительность реорганизации индекса размером 1гб и 10 гб, 50гб, понятно что на разных системах это время будет разное, но все же хотелось бы знать среднюю температуру по больнице) Кто-нибудь может поделиться статистикой?
25. FReIM 9 22.11.23 18:01 Сейчас в теме
Добрый день. Все публикации на которые внизу статьи - удалены.
Оставьте свое сообщение