Какую схему raid лучше использовать для хранения данных севера ms sql и почему

Обновлено: 02.07.2024

На этот сервер планируется перенести ещё одну базу размером 38 ГБ, рост базы в год на 3-5 ГБ

Пока на сервере работает 2 пользователя, после переноса БД будет работать 17-20 пользователей.

В связи с грядущим переносом БД имеет ли смысл из имеющихся 4-ёх дисков сделать RAID-10 вместо RAID-6, будет ли прирост производительности?

Iwan777
Если есть возможность, купите пару SSD дисков в зеркало под базы.

Если речь про 4-е диска, то у вас перекос в сторону Раид6. Фактически сейчас работают только 2-а диска, 2-а других под контроль четности. Если переделаете в Р10, то будут работать все 4-е. Но при таком планируемом кол-ве народу и таком размере базы можете получить тормоза. В случае SSD вероятной проблемы не было бы.

На текущий момент приобрести SSD нет возможности.

Получается, что при использовании имеющегося железа, если оставить RAID-6, то будет совсем печально, а при переходе на RAID-10 производительность дисковой подсистемы должна подрасти?

Iwan777 писал(а): Получается, что при использовании имеющегося железа, если оставить RAID-6, то будет совсем печально, а при переходе на RAID-10 производительность дисковой подсистемы должна подрасти? Тут надо смотреть по месту, возможно Р5 будет лучше. На на 20 человек с базой в 40ГБ дисков маловато.
П.С. Интел 3510 на 100ГБ стоит не таких уж и больших денег. Пары дисков вам бы хватило. Спасибо!
Сначала попробуем на RAID-10 перейти, посмотрим как будет работать, и по результатам определимся с приобретением SSD. Stranger03
Уже имеет смысл смотреть на Intel S3520.
Цена такая же, как у S3510, а ресурс записи намного выше. Tert писал(а): Уже имеет смысл смотреть на Intel S3520.
Цена такая же, как у S3510, а ресурс записи намного выше. Для Ваших целей хватит совсем мелких ССД - это ж копейки.

Хм-ммм.

Stranger03 писал(а): Iwan777
Если есть возможность, купите пару SSD дисков в зеркало под базы.

М-ммм. Геннадий, некоторую неточность вижу здесь я.

В R6 же диски под чётность не выделенные, как в R2, R3 или R4 - и Ваше "2-а других под контроль чётности" должно быть переформулировано иначе: что для этой богоугодной цели из всего массива выделяется ("крадётся" у данных) объём, эквивалентный объёму двух дисков (а не два конкретных диска как физ.устройства), по которым и блоки данных, и блоки с КС "размазываются равномерно. Соответственно, в R6 "фактически работают" все диски (здесь - все 4), т.к. блоки данных читаются-пишуться с/на всех дисках R6-го.

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

У нас есть бэкэнд базы данных SQL 2005 для нашего веб-сайта, в настоящее время около 10 ГБ. Читает гораздо больше, чем пишет, хотя точной статистики у меня нет.

мы обновляем наш сервер баз данных, и я думал о том, чтобы получить 4 диска и настроить их в двух массивах RAID 1 - один для файлов данных, а другой для файлов ОС и журналов. Будет ли это оптимальной настройкой или RAID 5 будет лучше для файлов данных? RAID 10 становится немного дорогим и, вероятно, для нас это перебор.

на этом этапе SQL Server должен хранить большую часть базы данных в ОЗУ (8 ГБ), но она будет расти, поэтому я не хочу полностью полагаться на это.

Edit: мы определенно хотим избыточности на рабочем сервере, поэтому RAID 0 сам по себе отсутствует. RAID 10 хорош, но может быть немного дорогим для нас.

ваша концепция использования независимых зеркал RAID 1 является правильной стратегией.

мы реализовали аналогичные сценарии на моей работе, и они работают очень хорошо.

рейд 1

RAID 1 дает вам скорость 1 диска для записи, но 2 диска для чтения.

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

при чтении из массива RAID 1 контроллер будет считывать с обоих дисков, поскольку они имеют одинаковые данные на них.

RAID 5

Это полезно для защиты больших объемов данных. Стоимость RAID 5 увеличивается намного медленнее, чем RAID 1 (или RAID 0+1, Когда вы делаете емкости за пределами размера отдельных дисков) для того же объема данных.

Если вы хотите защитить 600gb с помощью RAID 5, Вы можете этого добиться с приводами 4x200gb или приводами 3x300gb, требуя 800-900gb полного купленного космоса привода. RAID 1 будет 2x600gb дисками, требующими 1,200 gb купленного пространства (с 600gb дисками, являющимися довольно дорогими) или RAID 0+1, позволяющими использовать менее дорогие накопители (т. е.: 4x300gb или 6x200gb), но все еще требует в общей сложности 1,200 gb купленного пространства.

RAID 0+1

предлагает подобные преимущества как RAID 1 принимая его вверх по другой зазубрине с striping через диски. Я предполагаю, что если вас беспокоят более высокие одновременные чтения, вы также будете использовать мультипроцессоры/многоядерные. Вы будете обрабатывать несколько запросов одновременно,и поэтому чередование не поможет. Вы увидите лучшее преимущество на RAID 0+1 для отдельных приложений, использующих большие файлы данных, такие как редактирование видео.

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

учитывая, что чтения намного больше, чем записи, RAID 5 будет быстрее для ваших файлов данных.

Если возможно, используйте отдельный массив RAID 1+0 для файлов журнала транзакций.

EDIT: вам понадобится не менее 3 дисков для создания массива RAID 5.

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

Это означает, что если вам интересно, как использовать 4 диска (например), две пары RAID1 должны дать вам лучшую производительность, чем один массив RAID5 с 4 дисками. Конечно, ты не получит столько полезного хранения из двух пар RAID1.

учитывая небольшой размер базы данных, я бы использовал четыре 15krpm 2.5" SFF SAS-диска, настроенных как два отдельных зеркала RAID 1. Я бы запустил их через что-то вроде контроллера Adaptec 5805 PCI-e x 8 SAS. Для безопасности я поместил данные в один массив, а журналы-в другой. Bar memory-отображение всей базы данных, очень большой / дорогой SAN или использование SSDs я уверен, что это будет самая быстрая настройка за деньги.

надеюсь, что это помогает.

теория RAID 1 дает вам два диска для чтения, но не думайте, что это равно в два раза производительности. На самом деле это не так, обычно последовательное чтение spee заканчивается точно так же, как один диск. Удивительно, но это правда . делайте тесты в реальном мире, не полагайтесь на теорию.

с учетом сказанного, я бы пошел с двумя рейдами 1. но не так, как вы сказали, один для ОС, другой для данных. У меня был бы небольшой раздел OS на одном из они, но определенно дают оба рейда sql server для данных. Дайте sql server все наборы, которые вы можете.

SQL Server может полосовать по парам, вы абсолютно не хотите идти с 0+1, несмотря на то, что кто-то здесь говорит. Они снова смотрят на теоретические критерии, не понимают, что sql server может полосовать все свои данные по дискам и может делать это оптимизированным способом.

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

У вас есть 0+1 и разбить его на два raid 1. сделайте тестирование в реальном мире, вы будете удивлены, что быстрее для работы sql server.

RAID 5 хорош, если вы используете аппаратный контроллер с приличным количеством кэш-памяти с батарейной поддержкой. Выберите размер фрагмента и настройте БД таким образом, чтобы размер полосы (диски данных * размер фрагмента) был равен размеру записи БД. Убедитесь ,что ваш раздел данных [выровнен / кратен] размеру полосы.

в противном случае RAID 1+0 всегда является хорошим выбором для серверов БД.

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

с некоторыми базовыми значениями вы также можете измерить эффективность ваших изменений и стать хорошей основой для будущих обновлений.

(Если не известно, возьмите некоторую литературу (или Википедию) по технике производительности. Это целая ветвь материала, которая занимается только этим типом проблем).

кстати, где я работаю, у них есть высокий конец san, который делает tablescan примерно за 2 минуты. Я разбил диски на простые наборы raid 1 и позволил sql server обрабатывать чередование. не Сан. За 2 минуты до 45 секунд.

есть и другие статьи в сети об этом. очень трудно заставить рейда "истинно верующих" принять это, поэтому я так подчеркиваю этот момент.

Я предпочитаю raid 10, который я уже настроил на моем сервере dell R210-II linux. Потрясающая производительность.

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

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

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

Данные и файлы журналов

Базовый принцип, который лежит в основе работы SQL Server с хранилищами данных, заключается в том, что базы данных состоят из файлов двух типов:

Файлы данных. В этих файлах хранится информация базы данных. Файлы данных SQL Server представляют собой файлы NTFS с расширением. mdf. Простейшая база данных обычно состоит из одного файла данных, но может состоять и из многих файлов данных, находящихся на одном или нескольких дисках.
Файлы журналов. В этих файлах хранятся транзакции базы данных, что позволяет восстановить базу данных на определенный момент времени. Файлы журналов транзакций SQL Server представляют собой файлы NTFS с расширением. ldf. В базе данных может быть много файлов журналов, расположенных на одном или нескольких дисках.

Рекомендуется поместить файлы данных и журналов на различные диски. SQL Server записывает все транзакции базы данных в журнал транзакций, поэтому файлы журналов удобно располагать на дисках с высокой скоростью записи. Файлы данных используются для обслуживания запросов и часто должны выполнять множество операций чтения. При создании базы данных можно указать местоположение файлов данных и журналов с помощью команды T-SQL CREATE DATABASE. Чтобы изменить местонахождение существующих файлов данных и журналов, можно запустить команду ALTER DATABASE с параметром MODIFY FILE. В листинге 1 показан пример переноса файла данных базы данных в другое место.

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

И все же AutoGrow следует рассматривать как механизм последнего рубежа защиты. Его не следует использовать в качестве основного метода управления ростом базы данных. Ростом всех файлов данных и журналов следует управлять вручную. Активность базы данных прекращается, когда происходят операции AutoGrow. Частые события AutoGrow — хороший индикатор непредвиденного роста данных. Следующая команда показывает, как установить настройку AutoGrow для файлов данных и журналов:

Почти никогда не рекомендуется активировать функцию AutoShrink для базы данных. Как и операции AutoGrow, операции AutoShrink приводят к остановке всех действий базы данных. Кроме того, администратор не может контролировать время запуска AutoShrink. Использование AutoShrink может привести к спирали операций AutoGrow, а затем AutoShrink, а результатом будет снижение производительности базы данных и чрезмерная фрагментация файлов. Запустить AutoShrink можно с помощью команды:

Еще один полезный прием при работе с хранилищами данных — немедленная инициализация файлов Instant File Initiation. В отличие от большинства рассмотренных в статье параметров, Instant File Initialization управляется политикой Windows Server. Instant File Initialization не обнуляет выделенное пространство для файла, а просто выделяет нужное количество места. SQL Server использует Instant File Initialization во время создания базы данных, AutoGrow и операции восстановления базы данных. Можно включить режим Instant File Initialization на сервере через меню Administrative, чтобы открыть Local Security Policy («Локальная политика безопасности»). Затем разверните Local Policies («Локальные политики») и дважды щелкните на пункте Performance volume maintenance tasks, как показано на экране.

Экран. Включение Instant File Initialization

В результате открывается диалоговое окно свойств Properties для Performance volume maintenance tasks («Выполнение задач по обслуживанию томов»), в котором можно ввести имя учетной записи SQL Server Service.

Хранение данных и уровни RAID

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

RAID 0 (иногда именуется чередованием дисков). На этом уровне RAID данные распределяются между всеми доступными дисками. Он часто используется в различных тестах производительности баз данных. RAID 0 обеспечивает хорошую производительность, но его никогда не следует применять на производственном сервере, так как отказ одного диска приводит к потере данных.
RAID 1 (иногда именуется зеркальным отображением дисков). В конфигурации RAID 1 данные отображаются на дисках зеркально. Скорость операций чтения и записи хорошая, но общая емкость дисков уменьшается вдвое. RAID 1 часто используется для файлов журналов SQL Server. В случае отказа одного диска данные не теряются.
RAID 5 (иногда именуется чередованием дисков с контролем четности). В конфигурации RAID 5 данные распределяются по нескольким дискам с целью обеспечить избыточность данных. Часто используется для файлов данных. Этот уровень RAID обеспечивает хорошую производительность чтения и устойчив к отказу одного диска. Однако скорость записи невелика.
RAID 10 (иногда именуется зеркальным отображением дисков с чередованием). RAID 10 сочетает в себе быстродействие вариантов с чередованием и защиту через зеркальное отображение. RAID 10 обеспечивает самые высокие уровни производительности и доступности среди всех уровней RAID. Для RAID 10 требуется вдвое больше дисков, чем для RAID 5, но конфигурация устойчива к отказу нескольких дисков. Массив RAID 10 продолжает успешно функционировать при отказе половины дисков в наборе. RAID 10 подходит как для файлов данных, так и для журналов.

Tempdb

Еще один важный компонент системы хранения данных SQL Server — tempdb. Это системная база данных SQL Server, которая представляет собой глобальный ресурс, доступный всем пользователям. Tempdb используется для временных объектов пользователя и внутренних операций ядра системы управления базами данных, в том числе объединений, статистической обработки, курсоров, сортировки, хеширования и управления версиями строк. В отличие от данных в типичной пользовательской базе данных, данные в tempdb не сохраняются после отключения экземпляра SQL Server.

Как правило, tempdb — одна из самых активных баз данных в рабочем экземпляре SQL Server, поэтому следующие рекомендации помогут обеспечить хорошую производительность базы данных SQL Server. Прежде всего, файлы данных и журналов tempdb следует разместить на других физических дисках, нежели файлы журналов и данных рабочей базы данных. По причине очень активного использования tempdb полезно защитить диски, организовав массив RAID 1 или массив RAID 10 с чередованием. Специалисты группы Microsoft SQL Server Customer Advisory Team (SQLCAT) рекомендуют, чтобы в tempdb был один файл данных для каждого ядра процессора. Но эта рекомендация эффективна для очень высоких рабочих нагрузок. Чаще рекомендуется, чтобы отношение файлов данных к ядрам процессора составляло 1:2 или 1:4. Как и в большинстве случаев, это общие рекомендации; оптимальные подходы для конкретной системы могут различаться. Если вы не знаете точно, сколько файлов использовать для tempdb, можно начать с четырех файлов данных. Обычно для tempdb достаточно одного файла журнала. Более подробные рекомендации tempdb вы найдете в материалах, перечисленных во врезке «Учебная литература».

Кроме того, размер tempdb должен быть достаточным, чтобы избежать операций AutoGrow. Как и пользовательские базы данных, tempdb будет испытывать задержки из-за операций AutoGrow. По умолчанию tempdb содержит файл данных в 8 Мбайт, файл журналов в 1 Мбайт и 10% пространства для AutoGrow, а это слишком мало для большинства производственных рабочих нагрузок. Также важно помнить, что при перезапуске SQL Server размер tempdb возвращается к последнему заданному значению.

Размер и перемещения файлов данных и журналов tempdb можно определять с помощью программного кода, приведенного в разделе «Данные и файлы журналов». Запрос в листинге 2 (с сайта MSDN) показывает, как определить размер и процент роста файлов данных и журналов tempdb.

Твердотельные диски

Благодаря нескольким ядрам увеличилась вычислительная мощь, и многие современные системы поддерживают очень большой объем оперативной памяти, из-за чего подсистема ввода-вывода стала узким местом для многих рабочих нагрузок. Традиционные жесткие диски стали более емкими, но быстродействие практически не увеличилось. Проблему можно решить с помощью твердотельных дисков (SSD). Твердотельные диски — сравнительно новая технология хранения данных, которая начала набирать вес на рынке SQL Server в течение последнего года. В прошлом цена устройств SSD была слишком велика, а информационная емкость слишком мала для многих рабочих баз данных. Одна из причин растущей популярности твердотельных дисков — преимущество в производительности перед традиционными жесткими дисками с вращающимся шпинделем. Например, диск Serial Attached SCSI (SAS) с частотой вращения шпинделя 15 000 об/мин может обеспечить пропускную способность 200 Мбайт/с. Для сравнения, SSD-диск Serial ATA (SATA) с 6-Гбайт соединением может обеспечить последовательную пропускную способность около 550 Мбайт/с. Основная причина превосходства SSD-дисков в быстродействии заключается в резком сокращении времени поиска. Когда нужно получить данные с вращающегося жесткого диска, головка должна переместиться в новое место. У SSD-диска нет движущихся частей, поэтому перемещение к новому месту хранения данных определяется быстродействием ячеек памяти.

Твердотельные и быстродействующие флэш-хранилища можно реализовать несколькими способами. Типичное применение — 2,5-дюймовые диски SSD. Эти устройства подключаются напрямую, как хранилища типа DAS, а электронный интерфейс — такой же, как у стандартного жесткого диска. Другая распространенная реализация SSD — в виде плат PCI Express (PCIe), подключаемых непосредственно к системной шине. В этом подходе используются преимущества быстродействующей шины PCIe, чтобы повысить число операций ввода-вывода в секунду (IOPS) и пропускную способность по сравнению со стандартным интерфейсом диска. Кроме того, многие хранилища SAN располагают разделами SSD и функцией автоматического распределения данных по разделам, что позволяет переместить важные рабочие нагрузки на высокопроизводительный раздел SSD, оставляя менее важные рабочие нагрузки на медленных и менее дорогостоящих жестких дисках.

Существуют хранилища SSD различных типов. Среди них — хранилище SSD на основе DRAM и хранилище SSD на основе технологии флэш-памяти, такой как одноуровневые ячейки (SLC) и многоуровневые ячейки (MLC). У каждого типа есть свои достоинства и недостатки.

DRAM. Как обычная оперативная память для компьютера, DRAM отличается очень высоким быстродействием, но ненадежна. Для DRAM требуется постоянный элемент питания, чтобы сохранить данные на время отключения данных. Такие хранилища часто выпускаются в виде плат PCIe, устанавливаемых на системной плате сервера.
SLC. Быстродействие и жизненный цикл хранилищ на SLC выше, чем у MLC, поэтому SLC используется в хранилищах SSD корпоративного уровня. Однако цена устройств SLC существенно выше, чем у MLC.
MLC. Обычно флэш-память типа MLC используется в потребительских устройствах и обходится дешевле, чем SLC. Однако у MLC более низкая скорость операций записи и существенно более высокий износ, чем у SLC.

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

Если нужно использовать диски SSD, не применяйте единственный накопитель SSD и приготовьтесь заменять диски SSD в течение срока эксплуатации сервера. Перечислим возможности применения SSD в SQL Server.

Перемещение индексов на диски SSD. Как правило, индексы не очень велики и связаны с интенсивными беспорядочными операциями чтения, поэтому идеально подходят для размещения на дисках SSD.
Перемещение файлов данных на диски SSD. С файлами данных обычно связано больше операций чтения, чем записи, поэтому в большинстве случаев они подходят для дисков SSD.
Перемещение файлов журналов на диски SSD. Файлы журналов связаны с большим числом операций записи. Поэтому если для файлов журналов применяются диски SSD, используйте диски SSD корпоративного уровня и конфигурации RAID 1 или RAID 10 с зеркальным отображением.
Перемещение tempdb на SSD-диск. Как правило, tempdb отличается высоким уровнем неупорядоченных операций записи, что может привести к порче SSD. Поэтому если диски SSD используются для tempdb, то это должны быть SSD корпоративного уровня в конфигурации RAID 1 или RAID 10 с зеркальным отображением, и нужен план замены дисков SSD. Кроме того, обратите внимание на вариант с PCIe DRAM для tempdb. Хранилище DRAM обеспечивает более высокое быстродействие при записи и имеет неограниченный срок эксплуатации. Однако цены хранилищ DRAM могут быть высокими.

Базовые уровни производительности

Другой основной подход — подготовить базовые уровни производительности и периодически сравнивать системную производительность с этими базовыми уровнями. Это может быть очень полезным для диагностики неполадок, а также отслеживания роста базы данных и других тенденций. Сопоставление с базовым уровнем — один из лучших способов упреждающего управления системами. Тема измерения производительности SQL Server выходит за рамки данной статьи, но ниже приводится обзор важнейших измеряемых показателей хранилищ данных.

Первая группа счетчиков производительности, которые необходимо отслеживать, представляет собой счетчики, относящиеся к памяти в системном мониторе Windows. Технически это не счетчики хранилища данных, но если памяти недостаточно, то остальные счетчики не имеют значения. Обязательно отслеживайте счетчик Available MBytes объекта Memory. Этот счетчик показывает объем физической памяти, доступной для выделения процессу или системе. Если показатель меньше 100 Мбайт, то полезно увеличить размер памяти. Другой важный счетчик — % Usage объекта Paging File, который показывает используемый объем файла подкачки Windows. Это значение должно быть менее 70%. Если значение выше, то, вероятно, системе требуется больше памяти.

Если экземпляр SQL Server использует DAS, то в первую очередь убедитесь, что на каждом диске NTFS свободно по крайней мере 20% пространства. Впоследствии можно проверить счетчики хранилища Windows Server с помощью системного монитора. В таблице 1 приведен список нескольких наиболее важных счетчиков; все они связаны с объектом Logical Disk.

Важные счетчики хранилища Windows Server

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

Сохраняем и движемся вперед

Хранилище — высококритичный компонент в производительности базы данных SQL Server. Знание некоторых простых приемов поможет оптимизировать доступность и производительность SQL Server. Более подробные сведения об особенностях хранения данных можно найти в материалах, перечисленных во врезке «Учебная литература».

Учебная литература

Листинг 1. Перенос файла данных базы данных в другое место

Листинг 2. Программный код для определения размера и процента роста файлов данных и журналов tempdb

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

Мы и сами на собственных проектах сталкивались с подобными «бутылочными горлышками» (bottleneck), периодически наблюдая близкую к 100% утилизацию диска в iostat.

О нашем опыте решения этого вопроса и хотим рассказать вам в этом посте.

Первое (и самое, казалось бы, очевидное) решение — надо использовать более быстрые диски.

Наиболее быстрые в настоящее время — наверное, SSD.

Требуется поддержка SSD в серверах (контроллер, драйверы).
SSD — это дорого.

Другой подход — использовать не один, а несколько дисков. RAID, иначе говоря.

Мы уже писали о том, что собственные проекты размещаем в «облаке» Amazon. И удачно и успешно работаем с software RAID, собранными из EBS дисков Amazon.

Они достаточно любопытны и интересны, однако не очень устроили нас. В основном, тем, что не корректно сравниваются очень разные результаты (например, чтение с одного диска в один поток, RAID 0 — 8 потоков, RAID 10 — 4; и т.п.)

Поэтому мы решили провести собственное тестирование. Тем же инструментом — sysbench.

Мы решили работать с RAID 10. Именно он одновременно и быстрый, и надежный. А, вот, различных его конфигураций — достаточно много.

Маленькое отступление. В процессе тестирования оценили еще одно очень важное преимущество «облака»: в «облаке» очень удобно проводить самые разные тесты, собирая и разбирая любые тестовые стенды! И при этом платить — только за время реального использования!

Итак. Мы собрали 5 стендов.

1. single disk — 100 Gb

2. RAID 10 — 4 диска по 50 Gb

Добавили в админке Амазона 4 диска, подключили их, назначив соответствующие имена, а затем создали рейд вот так:

3. RAID 10 — RAID 0 из двух RAID 1 (каждый по 2 диска по 50 Gb)

Та же процедура, но итоговый рейд создается в три приема:

4. RAID 10 — 8 дисков по 25 Gb

Аналогично пункту 2, но только подключаем 8 дисков, а не 4.

5. RAID 10 — RAID 0 из четырех RAID 1 (каждый по 2 диска по 25 Gb)

На всех тестовых стендах использовалась файловая система ext4. Параметры монтирования:

Для тестов использовался sysbench — на файле 256 Мб; режимы — random read, random write, random read/write; разным количеством потоков — от 1 до 16.

Ось X — число потоков
Ось Y — число операций в секунду.

По чтению — все сопоставимо по результатам. Рейд не дает особого преимущества.

Но картинка эта — весьма искаженная, так как на результаты очень сильно повлиял файловый кэш (тестовый файл помещается в RAM целиком).

По записи рейды несколько проигрывают (сказываются некоторые накладные расходы).

Любой вопрос, начинающийся со слов «Что лучше. » не имеет смысла сам по себе.

Какая CMS лучше?

Какую выбрать базу данных?

Что лучше выбрать в качестве RAID'а?

При любом выборе всегда важны поставленные и решаемые задачи!

Мы выбираем дисковую систему для базы. Формат хранения данных у нас — InnoDB.

Это значит, что, в основном, мы работаем с большими файлами (несколько Гб) ibdata.

Типичный профиль нагрузки — random read/write (чтений больше).

И вот уже исходя из более понятной реальной задачи, делаем новую серию тестов — на файле размером 16 Гб.

Чтение — один диск сразу упирается в потолок. Увеличение количества потоков не дает прироста производительности.
Рейды из 4 дисков на нескольких потоках дают прирост производительности в 3-4 раза. Рейды из 8 дисков — в 6-7 раз.
На запись — примерно та же картина, что и с одним диском.

Типичная работы базы MySQL — random read/write, чтений больше, чем записи. Самые производительные для такой задачи — RAID 10 с большим количеством дисков.

Минус такого решения — в удвоенной стоимости дисков (что при текущей их стоимости не является критичным).

Главный плюс — у нас есть простое решение (software RAID можно собрать как на физическом сервере, так и в «облаке») для масштабирования производительности дисковой системы.

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

Данные и файлы журналов

Базовый принцип, который лежит в основе работы SQL Server с хранилищами данных, заключается в том, что базы данных состоят из файлов двух типов.

Файлы данных.В этих файлах хранится информация базы данных. Файлы данных SQL Server представляют собой файлы NTFS с расширением .mdf. Простейшая база данных обычно состоит из одного файла данных, но может состоять и из многих файлов данных, находящихся на одном или нескольких дисках.
Файлы журналов. В этих файлах хранятся транзакции базы данных, что позволяет восстановить базу данных на определенный момент времени. Файлы журналов транзакций SQL Server представляют собой файлы NTFS с расширени ем .ldf. В базе данных может быть много файлов журналов, расположенных на одном или нескольких дисках.

Если для создания базы данных используется среда SQL Server Management Studio (SSMS), то файлы данных и журналов хранятся на том же диске по умолчанию. Если не указано иное, то файлы данных и журналов создаются в том же каталоге, что и системные базы данных SQL Server, то есть <диск>:\Рrogram Files\Microsoft SQL Server\MSSQL. MSSQLSERVER\MSSQL\DATA. Например, для экземпляра SQL Server 2014, установленного на диске С, файлы данных и журналов по умолчанию будут находиться в каталоге C:\Program Files\ Microsoft SQL Server\MSSQL12. MSSQLSERVER\MSSQL\DATA. Рекомендуется поместить файлы данных и журналов на различные диски. SQL Server записывает все транзакции базы данных в журнал транзакций, поэтому файлы журналов удобно располагать на дисках с высокой скоростью записи. Файлы данных используются для обслуживания запросов и часто должны выполнять множество операций чтения. При создании базы данных можно указать местоположение файлов данных и журналов с помощью команды T-SQL CREATE DATABASE. Чтобы изменить местонахождение существующих файлов данных и журналов, можно запустить команду ALTER DATABASE с параметром MODIFY FILE. В листинге показан пример переноса файла данных базы данных в другое место.

WHERE database_id = DBJD( 'AdventureWorks2012' ); ALTER DATABASE AdventureWorks2012 ALTER DATABASE AdventureWorks2012 MODIFY FILE ( NAME = AdventureWorks2012_Data, FILENAME "E:\Data\AdventureWorks2012_Data.mdf" ) ALTER DATABASE AdventureWorks2012 MODIFY FILE ( NAME = AdventureWorks2012_Log, FILENAME = "F:\Logs\AdventureWorks2012_log.ldf" )

ALTER DATABASE AdventureWorks2012 MODIFY FILE ( NAME = 'AdventureWorks2012_data' . ALTER DATABASE AdventureWorks2012 MODIFY FILE ( NAME = 'AdventureWorks 2012Jog' ,

ALTER DATABASE AdventureWorks2012 SET AUT0_SHRINK OFF

Еще один полезный прием при работе с хранилищами данных — немедленная инициализация файлов Instant File Initiation.

SELECT name AS FileName, size * 1.0 / 128 AS FileSizeinMB, WHEN growth = 0 THEN 'Size is fixed and will not grow.'

В отличие от большинства рассмотренных в статье параметров, Instant File Initialization управляется политикой Windows Server. Instant File Initialization не обнуляет выделенное пространство для файла, а просто выделяет нужное количество места. SQL Server использует Instant File Initialization во время создания базы данных, AutoGrow и операции восстановления базы данных. Можно включить режим Instant File Initialization на сервере через меню Administrative, чтобы открыть Local Security Policy («Локальная политика безопасности»). Затем разверните Local Policies («Локальные политики») и дважды щелкните на пункте Performance volume maintenance tasks, как показано на экране. В результате открывается диалоговое окно свойств Properties для Performance volume maintenance tasks («Выполнение задач по обслуживанию томов»), в котором можно ввести имя учетной записи SQL Server Service.

Хранение данных и уровни RAID

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

RAID0 (иногда именуется чередованием дисков).На этом уровне RAID данные распределяются между всеми доступными дисками. Он часто используется в различных тестах производительности баз данных. RAID 0 обеспечивает хорошую производительность, но его никогда не следует применять на производственном сервере, так как отказ одного диска приводит к потере данных.
RAID1 (иногда именуется зеркальным отображением дисков).В конфигурации RAID 1 данные отображаются на дисках зеркально. Скорость операций чтения и записи хорошая, но общая емкость дисков уменьшается вдвое. RAID 1 часто используется для файлов журналов SQL Server. В случае отказа одного диска данные не теряются.
RAID5 (иногда именуется чередованием дисков с контролем четности).В конфигурации RAID 5 данные распределяются по нескольким дискам с целью обеспечить избыточность данных. Часто используется для файлов данных. Этот уровень RAID обеспечивает хорошую производительность чтения и устойчив к отказу одного диска. Однако скорость записи невелика.
RAID10 (иногда именуется зеркальным отображением дисков с чередованием).RAID 10 сочетает в себе быстродействие вариантов с чередованием и защиту через зеркальное отображение. RAID 10 обеспечивает самые высокие уровни производительности и доступности среди всех уровней RAID. Для RAID 10 требуется вдвое больше дисков, чем для RAID 5, но конфигурация устойчива к отказу нескольких дисков. Массив
RAID 10 продолжает успешно функционировать при отказе половины дисков в наборе. RAID 10 подходит как для файлов данных, так и для журналов.

Tempdb

Еще один важный компонент системы хранения данных SQL Server — tempdb. Это системная база данных SQL Server, которая представляет собой глобальный ресурс, доступный всем пользователям. Tempdb используется для временных объектов пользователя и внутренних операций ядра системы управления базами данных, в том числе объединений, статистической обработки, курсоров, сортировки, хэширования и управления версиями строк. В отличие от данных в типичной пользовательской базе данных, данные в tempdb не сохраняются после отключения экземпляра SQL Server.

Твердотельные диски

Благодаря нескольким ядрам увеличилась вычислительная мощь, и многие современные системы поддерживают очень большой объем оперативной памяти, из-за чего подсистема ввода-вывода стала узким местом для многих рабочих нагрузок. Традиционные жесткие диски стали более емкими, но быстродействие практически не увеличилось. Проблему можно решить с помощью твердотельных дисков (SSD). Твердотельные диски — сравнительно новая технология хранения данных, которая начала набирать вес на рынке SQL Server в течение последнего года. В прошлом цена устройств SSD была слишком велика, а информационная емкость слишком мала для многих рабочих баз данных. Одна из причин растущей популярности твердотельных дисков — преимущество в производительности перед традиционными жесткими дисками с вращающимся шпинделем. Например, диск Serial Attached SCSI (SAS) с частотой вращения шпинделя 15 000 об./мин может обеспечить пропускную способность 200 Мбайт/с. Для сравнения, SSD-диск Serial ATA (SATA) с 6-Гбайт соединением может обеспечить последовательную пропускную способность около 550 Мбайт/с. Основная причина превосходства SSD-дисков в быстродействии заключается в резком сокращении времени поиска. Когда нужно получить данные с вращающегося жесткого диска, головка должна переместиться в новое место. У SSD-диска нет движущихся частей, поэтому перемещение к новому месту хранения данных определяется быстродействием ячеек памяти.

DRAM.Как обычная оперативная память для компьютера, DRAM отличается очень высоким быстродействием, но ненадежна. Для DRAM требуется постоянный элемент питания, чтобы сохранить данные на время отключения данных. Такие хранилища часто выпускаются в виде плат PCIe, устанавливаемых на системной плате сервера. SLC. Быстродействие и жизненный цикл хранилищ на SLC выше, чем у MLC, поэтому SLC используется в хранилищах SSD корпоративного уровня. Однако цена устройств SLC существенно выше, чем у MLC.
MLC.Обычно флэш-память типа MLC используется в потребительских устройствах и обходится дешевле, чем SLC. Однако у MLC более низкая скорость операций записи и намного более высокий износ, чем у SLC.По быстродействию устройства SSD превосходят жесткие диски с вращающимся шпинделем, но срок их эксплуатации значительно ниже. Приложения с интенсивным вводом-выводом, такие как SQL Server, сокращают срок жизни накопителя SSD. Кроме того, чем больше используемая часть диска, тем меньше продолжительность жизни. Рекомендуется убедиться, что по крайней мере 20% накопителя SSD не занято. Скорость чтения стабильна в течение всего времени эксплуатации устройства. Однако быстродействие при записи в процессе эксплуатации ухудшается, то есть время, необходимое для записи, увеличивается. Важно также помнить, что нет необходимости деф-рагментировать диски SSD, потому что метод доступа к данным иной, чем у жестких дисков. В сущности, дефрагментация этого типа дисков приведет только к сокращению их жизненного цикла. Если нужно использовать диски SSD, не применяйте единственный накопитель SSD и приготовьтесь заменять диски SSD в течение срока эксплуатации сервера. Перечислим возможности применения SSD в SQL Server.
Перемещение индексов на диски SSD.Как правило, индексы не очень велики и связаны с интенсивными беспорядочными операциями чтения, поэтому идеально под ходят для размещения на дисках SSD.
Перемещение файлов данных на дискиSSD.С файлами данных обычно связано больше операций чтения, чем записи, поэтому в большинстве случаев они подходят для дисков SSD.
Перемещение файлов журналов на дискиSSD. Файлы журналов связаны с большим числом операций записи. Поэтому, если для файлов журналов применяются диски SSD, используйте диски SSD корпоративного уровня и конфигурации RAID 1 или RAID 10 с зеркальным отображением.
ПеремещениеtempdbнаSSD-диск. Как правило, tempdb отличается высоким уровнем неупорядоченных операций записи, что может привести к порче SSD. Поэтому если диски SSD используются для tempdb, то это должны быть SSD корпоративного уровня в конфигурации RAID 1 или RAID 10 с зеркальным отображением, и нужен план замены дисков SSD. Кроме того, обратите внимание на вариант с PCIe DRAM для tempdb. Хранилище DRAM обеспечивает более высокое быстродействие при записи и имеет неограниченный срок эксплуатации. Однако цены хранилищ DRAM могут быть высокими.

Базовые уровни производительности

Если показатель меньше 100 Мбайт, то полезно увеличить размер памяти. Другой важный счетчик — % Usage объекта Paging File, который показывает используемый объем файла подкачки Windows. Это значение должно быть менее 70%. Если значение выше, то, вероятно, системе требуется больше памяти.

Помимо счетчиков, связанных с памятью Windows, имеется несколько счетчиков производительности хранилища Windows Server. Однако показания этих счетчиков полезны лишь в том случае, если экземпляр SQL Server работает с системой хранения данных с прямым подключением DAS. Если используется SAN, то нужно обращать внимание на метрики производительности SAN. Если экземпляр SQL Server использует DAS, то в первую очередь убедитесь, что на каждом диске NTFS свободно по крайней мере 20% пространства. Впоследствии можно проверить счетчики хранилища Windows Server с помощью системного монитора. В таблице 1 приведен список нескольких наиболее важных счетчиков; все они связаны с объектом Logical Disk.

Сохраняем и движемся вперед

Читайте также: