SQL в вопросах и ответах: Размер действительно имеет значение

  • Статья

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

Пол С. Рэндал

Бойтесь фрагментации

Вопрос Я прочитал несколько записей в блогах, в которых вроде бы подразумевается, что не нужно заботиться о фрагментации индексов в базах данных, размещенных на твердотельных (SSD) дисках, так как они работают намного быстрее обычных жестких дисков. Я правильно понимаю, что падение производительности будет остановлено, но могу ли просто игнорировать фрагментацию индексов?

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

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

Однако снижение плотности данных из-за фрагментации индексов все равно может представлять проблему. Фрагментация индексов происходит в основном из-за операций, которые называются «разбиением страниц». Дело в том, что новое свободное пространство на странице образуется за счет переноса половины строк индекса в новую страницу. При этом старая и новая страницы оказываются заполненными примерно наполовину. В случае высокой фрагментации индекса очень часто случается, что средняя плотность страниц составляет 70% или меньше (то есть в странице 30% свободного пространства).

А это означает, что при наличии большого числа индексов ваших базах данных, дорогие твердотельные диски хранят большой объем пустого пространства, Понятно, что такая ситуация далека от оптимальной. Также хотя дополнительные операции ввода/вывода, необходимые в страницах низкой плотности, не составляют значительной проблемы на SSD-дисках, они занимают больше места в буферном пуле SQL Server (кеше файлов данных в памяти). Это также означает, что ценная память сервера используется неоптимально.

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

Не смотрите в зеркало

Вопрос Мы совершенствуем свою стратегию обеспечения доступности, но дело застопорилось на повышении доступности нескольких баз данных с репликацией на основе подписки. Мы не можем использовать зеркальное отображение SQL Server 2005, потому что после перехода при сбое нужно повторно инициализировать подписку. Есть у нас лучший вариант теперь, когда мы перешли на SQL Server 2008 R2?

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

В SQL Server 2008 появился новый механизм репликации транзакций, который поддерживает частичную реинициализацию подписки. Этот параметр называется инициализацией из LSN. Он задается как параметр @sync\_type parameter при вызове sp_addsubscription.

Одноранговая репликация транзакций в SQL Server 2008 была улучшена и позволяет добавлять и удалять узлы в одноранговой топологии без необходимости полностью останавливать работу топологии. Это значительное усовершенствование в области доступности данных, обеспечиваемое одноранговой топологией. Параметр «инициализация из lsn» был добавлен после реализации этих усовершенствований.

В механизме зеркального отображения баз данных отсутствует поддержка подписки на зеркальную БД (так как для зеркального отображения публикуемых БД существует агент чтения журнала). Вместе с тем, вы можете использовать метод «инициализация из lsn» для обеспечения быстрой реинициализации после перехода в системе зеркального отображения.

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

Некоторые операции также зеркально отображаются на зеркало БД перед переходом. Это может, к примеру, создавать номер LSN3, который находится чуть дальше по времени, чем LSN2. Будут также операции, которые вообще не применены к БД подписчика. Они более ранние, чем LSN2 или LSN3. Мы назовем их LSN1.

Все операции вплоть до LSN2 применены к основной базе данных подписки. Все операции плоть до LSN3 применены к основной базе данных подписки и скопированы на зеркальную базу данных. Для выполнения операции инициализации из LSN для новой подписки после перехода на зеркало в качестве аргумента в sp_addsubscription надо передать LSN3.

Задержку распространения надо также задать так, чтобы какое-то время операции сохранялись в базе данных распространителя после применения в базе данных подписчика. Короче говоря, теперь зеркальное отображение баз данных можно использоваться для обеспечения более высокой доступности БД подписчика, при этом после перехода на зеркало потребуется минимальная реинициализация. Подробнее об этом см. технический документ «SQL Server Replication: Providing High-Availability Using Database Mirroring».

Слишком большая

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

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

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

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

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

В таких обстоятельствах восстановление в случае сбоя также выполняется быстрее (если используется редакция Enterprise Edition). Вам достаточно будет быстро восстановить файловые группы, которые необходимы для работы OLTP-систем.  Для этого можно воспользоваться частичным восстановлением, после которого использовать частичную доступность базу данных для возвращения базы данных в интерактивный режим. Восстановить файловые группы, содержащие более старые данные, можно позже, воспользовавшись частичным восстановлением, когда OLTP-операции происходят в уже доступных файловых группах.

Подробнее об этом можно почитать в следующих документах:

В условиях перегрузки

Вопрос Очень часто администраторы баз данных затрудняются с тем, как определить, находится ли буферный пул в условиях перегрузки. Есть масса противоречивой информации о том, какие счетчики монитора производительности использовать (PerfMon) и какие задавать пороги. В большинстве прочитанных мной статей рекомендуется использовать счетчик Page Life Expectancy (PLE), задав в нем 300 в качестве порогового значения. Не могли бы вы прояснить ситуацию?

Ответ Не только вы испытываете затруднения. Число 300 впервые упоминалось в «белой книге» Microsoft, опубликованной пять лет тому назад. Эта информация безнадежно устарела.

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

Само по себе одно значение PLE малоинформативно. Нужно смотреть на тенденции значений. Вполне возможно, что вполне легитимные операции SQL Server приведут к значительному снижению показаний PLE. Часто показания возвращаются к норме. Если показания PLE снижаются и остаются низкими, тогда стоит волноваться.

Порог, при котором нужно реагировать, не является фиксированным значением, как об этом часто пишут. Значение 300 означает, что весь буферный пул заменяется каждые 300 секунд. Если у вас пул размером 100 ГБ, то это значит, что 100 ГБ новых данных считываются в память каждые пять минут. Налицо проблема с производительностью. Однако это становится огромной проблемой с производительностью задолго до того, как PLE достигает значения 300. Можно подсчитать более разумное значение: (<размер буферного пула в ГБ> / 4) × 300, как описано в следующей записи в блоге:

Нужно также учитывать наличие на сервере технологии NUMA. Счетчик PLE в объекте производительности Buffer Manager является средним значением всех счетчиков PLE на каждом узле NUMA, если, конечно, NUMA присутствует на сервере. А это означает, что показания PLE не совсем корректно отображают ситуацию в системе. В этом случае нужно следить за счетчиком PLE на каждом объекте производительности Buffer Partition. Подробнее о PLE и NUMA см. следующую запись в блоге:

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

Paul S. Randal

Пол С. Рэндал(Paul S. Randal) носит звание SQL Server MVP и занимает посты исполнительного директора SQLskills.com и регионального директора Microsoft. Пол работал в команде ядра хранения SQL Server в Microsoft с 1999 до 2007 года. Рэндал написал DBCC-инструкцию CHECKDB /repair для SQL Server 2005 и отвечал за ядро хранения при разработке SQL Server 2008. Он является экспертом по восстановлению после аварий, высокой доступности и обслуживанию баз данных и регулярно выступает с докладами на конференциях в разных странах. Адрес его блога — SQLskills.com/blogs/paul, а также его можно найти на Twitter по адресу twitter.com/PaulRandal.