SQL в вопросах и ответах: Управляйте своими данными
В этом месяце наш автор колонки по SQL Server поможет читателям разобраться с данными из двух разных источников, разработать стратегию HA и управлять данными, используя файловые группы.
Вопрос Я пытаюсь измерить задержку ввода-вывода для некоторых наших баз данных и получаю разные результаты из Dynamic Management View (динамическое представление управления, DMV) и по счетчикам Performance Monitor. Не могли бы объяснить, почему эти числа не совпадают?
Ответ В этих двух методах используется DMV sys.dm_io_virtual_file_stats (см. сообщение в моем блоге, где об этом рассказывается подробнее) и счетчики Avg. Disk sec/Read и Avg. Disk sec/Write в объекте Physical Disk в Performance Monitor. Вполне возможно, что эти два механизма дают разные результаты.
В DMV измеряется общее время простоя (задержка) при чтении и записи. Это делается для каждого файла каждой базы данных с момента перевода базы данных в онлайн (как правило, с момента последнего перезапуска экземпляра SQL Server).Чтобы получить среднее время задержки ввода-вывода для чтения и записи, эти итоговые значения делятся на количество операций чтения и записи. Эти средние значения вычисляют за весь период времени с момента, когда вы в первый раз запросили данные из DMV до момента, когда интересующая вас база данных перешла в онлайновый режим.
Счетчики Performance Monitor вычисляют среднее значение за более короткий период времени. В блогах группы Windows Server Core имеется два отличных сообщения — Windows Performance Monitor Disk Counters Explained и Measuring Disk Latency with Windows Performance Monitor (Perfmon), в которых это объясняется подробнее. Вы получаете данные о задержках при чтении и записи, которые относятся к текущему моменту. Как видите, эти два метода измерения задержки сильно отличаются, поэтому и результаты могут быть разными.
DMV измеряет задержку только для файлов баз данных SQL Server. The Performance Monitor измеряет ее для всех операций ввода-вывода на данном томе. В среде с общим хранилищем это может означать, что имеется множество других файлов, не относящихся к SQL Server, но участвующих во вводе-выводе этого тома. В результате среднее значение может уменьшиться, поскольку Performance Monitor смотрит большое количество операций ввода-вывода для большого количество файлов. Среднее значение, полученное из DMV, может оказаться выше, поскольку в измерении участвует меньшее количество операций ввода-вывода и файлов.
По тем же самым причинам в какой-то момент времени производительность тома может быть низкой, но SQL Server может не осуществлять ввод-вывод в этот момент. Счетчики Performance Monitor покажут низкую производительность. Но, поскольку DMV измеряет ввод-вывод только SQL Server, низкая производительность в этот период не повлияет на результаты, получаемые из DMV.
Кроме того, надо помнить, что DMV собирает агрегированные данные. Если был период, когда производительность была низкой, и в течение этого периода осуществлялся только ввод-вывод SQL Server, то низкая производительность ввода-вывода повлияет на результаты, получаемые из DMV, даже после того как производительность снова вырастет. Счетчики Performance Monitor отразят тот факт, что в период низкой производительности задержки были высокими, а затем, когда производительность вырастет, покажут низкие задержки.
Как видите, чтобы понять, почему значения различны, нужно посмотреть, что на самом деле измеряется. К сожалению, счетчики DMV нельзя сбросить без перевода требуемой базы данных в офлайновый режим на какой-то момент времени.
Вопрос Меня попросили разработать новую стратегию обеспечения высокой доступности (high availability, HA) для наших SQL Server. Мне хотелось бы найти рекомендации относительно того, что принять во внимание и с чего начать. Не могли бы вы что-нибудь посоветовать?
Ответ Одна из проблем, возникающих при проектировании стратегии, — выяснить, какую HA-технологию использовать. Часто бывает, что компания выбирает существующую технологию просто потому, что эта технология уже имеется. Хуже того, компания может выбрать произвольную технологию из-за отсутствия четких требований.
Чтобы спроектировать правильную стратегию HA, вы должны собрать и проанализировать требования. Без этого важнейшего этапа нельзя надеяться на то, что ваша стратегия будет отвечать бизнес-требованиям. Для каждой порции данных, к которой будет применяться стратегия HA, нужно ответить на следующие вопросы:
- Насколько важны эти данные в сравнении со всеми остальными? Утверждение, что все данные имеют первостепенное значение и их надо защитить в равной степени, работает в случае небольших количеств данных, но такой подход неосуществим, если у вас терабайты данных, распределенные по нескольким экземплярам SQL Server.
- Какое количество данных предприятие может позволить себе потерять? По понятным причинам владельцы бизнеса могут желать, чтобы потери данных были нулевыми.
- Как долго данные могут оставаться недоступными? Владельцам бизнеса хотелось бы, чтобы простои также были нулевыми. Хотя к этому можно приблизиться, это, к сожалению, не осуществимо на практике.
- Изменятся ли ответы на предыдущие два вопроса, если брать различные времена дня или дни недели? Это может существенно повлиять на выполнимость требований. Гораздо больше шансов добиться нулевых простоев и потерь данных в течение ограниченного периода времени, например, с 9 до 17 часов по будням, чем в режиме 24х365.
- Допустимо ли пожертвовать производительностью, чтобы обеспечить доступность данных и надежность? Единственные технологии, которые могут обеспечить нулевые потери данных, требуют синхронное зеркалирование записей журнала транзакций (зеркалирование баз данных или группы доступности SQL Server 2012) или операций записи, выполняемых подсистемой ввода-вывода (репликация в SAN). Оба подхода приводят к задержкам в обработке, но это плата за надежность.
- Должна ли вторая копия данных быть доступной для записи или только для чтения?
Как только вы получили эти требования, можно проработать ограничения, устанавливаемые предприятием, а затем найти компромисс. Важно осознавать, что ограничения, с которыми вы имеете дело, могут означать, что вы не сможете выполнить все требования. В этом случае вы и бизнес-менеджеры должны придти к компромиссу. В противном случае любая разработанная вами стратегия HA не оправдает ожиданий. Более того, скорее всего, ваше решение будет состоять из нескольких технологий и для успеха крайне важно знать ограничения каждой из них, а также то, как они взаимодействуют.
Вы можете столкнуться со следующими ограничениями:
- Бюджет.
- Доступные электрические мощности.
- Физическое пространство для новых серверов, стоек и кондиционеров.
- Кадры — может оказаться, что новыми системами некому управлять или что в настоящее время ни у кого нет навыков по внедрению и администрированию необходимых новых технологий.
Подробные описания технологий и примеров стратегий приведены в следующих двух статьях:
- High Availability with SQL Server 2008
- Proven SQL Server Architectures for High Availability and Disaster Recovery
Хот эти статьи написаны для SQL Server 2008/2008 R2, они по-прежнему актуальны. И еще ознакомьтесь со статьей «AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups», в которой рассказывается о новейших технологиях SQL Server 2012.
Вопрос Коллеги по работе сказали мне, что в новых базах данных следует использовать файловые группы вместо одного файла данных. Не могли бы вы объяснить преимущества и недостатки такого подхода?
Ответ Я не могу придумать, какие «темные стороны» могут быть у файловых групп, если вы не зайдете слишком далеко и не будете создавать их сотнями. Что касается преимуществ, файловые группы становятся необходимыми, когда ваша база данных растет (становится больше 50 ГБ – 100 ГБ). И все-таки имеется три основных причины создания файловых групп.
Файловые группы позволяют осуществить быстрое целенаправленное восстановление в случае аварии. Представьте, что у вас база данных размером 1 ТБ, большую часть пространства которой занимает таблица с данными о продажах, начиная с 2009 года и по сей день. Если база данных погибнет при аварии, то какую минимальную порцию данных вы сможете восстановить? Если у вас все хранится в одной файловой группе, у вас нет выбора — придется полностью восстанавливать 1 ТБ, в том числе все старые данные.
Лучший подход к аварийному восстановлению — использовать несколько файловых групп: основную и за 2009, 2010, 2011, 2012 и 2013. В случае аварии, вам будет нужно как можно быстрее перевести в онлайновый режим данные за 2013. Это данные, необходимые для работы OLTP-системы (Online Transaction Processing, онлайновая обработка транзакций), управляющей продажами. Если у вас SQL Server в редакции Enterprise, вы можете обеспечить частичную доступность базы данных. Начните процесс с восстановления файловой группы PRIMARY с использованием синтаксиса WITH PARTIAL. Затем восстановите другие файловые группы, которые вы хотите сразу же перевести в онлайновый режим. Затем можно прекратить процесс восстановления.
Можно восстановить другие файловые группы в онлайновом режиме в удобное для вас время. В данном случае используется еще одна возможность редакции Enterprise — Online Piecemeal Restore (фрагментарное онлайновое восстановление). Это комбинация функций для очень больших баз данных (very large databases, VLDB), которые сокращают время простоя и обеспечивают большую гибкость, когда требуется назначить приоритеты в последовательности восстановления данных при аварии. Кроме того, это означает, что вы можете выполнить фрагментарное восстановление, если по какой-то причине повреждена только часть базы данных. Это еще больше снижает время простоя, необходимое для восстановления в случае аварии.
Вторая причина использования файловых групп — поддержка секционирования и улучшение управления. Секционирование позволяет без усилий реализовать загрузку и удаление данных из больших таблиц без генерации большого количества данных журнала транзакций. Полное описание преимуществ секционирования выходит за рамки моей колонки, но имеются следующие статьи, в которых проделана отличная работа, в частности, приведены примеры сценариев:
- Partitioned Tables and Indexes in SQL Server 2005
- Partitioned Table and Index Strategies Using SQL Server 2008
Еще одно усовершенствование управления касается фрагментации. Воспользуемся предыдущим примером с таблицей с данными о продажах: если индексы этой таблицы стали фрагментированными и таблица и индексы не секционированы, то команда ALTER INDEX … REBUILD или REORGANIZE будет выполнять дефрагментацию всего индекса. Это будет происходить, даже если старые данные не фрагментированы. Если вы разобьете таблицу на несколько секций и будет хранить каждую секцию в отдельной файловой группе, можно будет дефрагментировать только секции индекса, которые фрагментированы. Это сэкономит много времени и ресурсов.
Наконец, файловые группы позволяют изолировать различные рабочие нагрузки в базе данных, распределив их между различными частями подсистемы ввода-вывода. Например, представьте, что у вас имеются кое-какие малоиспользуемые таблицы и несколько таблиц, которые интенсивно используются и обновляются. Если все хранится в одной файловой группе, может оказаться, что производительность работы с малоиспользуемыми таблицами упала из-за операций, выполняемых с интенсивно обновляемыми таблицами.
В этом случае можно выделить малоиспользуемые таблицы в одну файловую группу, для которой используется своя собственная часть подсистемы ввода-вывода. Затем поместить каждую интенсивно используемую и обновляемую таблицу в отдельную файловую группу. Каждой из этих файловых групп также сопоставить свою часть подсистемы ввода-вывода. Тогда нагрузки ввода-вывода будут отделены друг от друга и не будут мешать друг другу.
Вы можете так поступить и в случае одной таблицы, если имеются OLTP-нагрузка по обращению к самым свежим данным (как в приведенном выше примере таблицы с данными о продажах) и нагрузка хранилища данных, работающая с более ранними данными. В этом случае будет необходимо секционирование, а нагрузки будут ограничены секциями таблицы, хранящимися в разных файловых группах, то есть нагрузки также будут отделены друг от друга.
Пол. С. Рэндал (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.