Взять от железа все

Построение высокопроизводительной системы SQL Server

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

Я замечал, что специалисты по ИТ часто предпочитают сразу делать выбор в пользу «наращивания аппаратных средств». К сожалению, в большинстве случаев это неэффективный способ вложения средств. Я хотел бы поделиться методами, которые можно использовать при настройке сервера для работы с наилучшей производительностью, и дать несколько конкретных рекомендаций по процессорам, памяти и устройствам ввода/вывода.

Процессор

Тестирование значительно помогает выяснить, как сервер справляется с рабочей нагрузкой. Совместно с Джимом Дровером, экспертом по эксплуатации и масштабируемости, я провел ряд сравнительных тестов для SQL Server OLTP. После многочисленных экспериментов мы определили, что каждое увеличение в скорости процессора на 10% дает выигрыш в производительности SQL Server 6,5%. Таким образом, на процессоре с 3,3 ГГц SQL Server будет работать приблизительно на 6,5% быстрее, чем на процессоре с 2,9 ГГц. Это оказалось верно для различных типов центральных процессоров.

Сегодня многие серверы способны поддерживать многопроцессорную архитектуру (далее SMP). Однако серверы SMP не масштабируются линейно. Другими словами, получая дополнительный процессор, сервер не выдает увеличение производительности на 100%. Вместо этого каждый дополнительный процессор добавляет вычислительной мощности нелинейно, начиная с повышения на 40% для первого дополнительного процессора и в среднем на 16% для каждого следующего дополнительного процессора после третьего, в случае приложений OLTP.

Материнские платы за последние несколько лет также были усовершенствованы. Однако наши тесты показали, что увеличение полосы пропускания шины повышает производительность на 3-5% для разных материнских плат.

Кэш процессора. Существует три типа кэш-памяти, предназначенной для процессора. Кэш первого уровня L1 фиксирован на 16 Кбайт для данных и на 16 Кбайт для инструкций, а встроен в процессор, т. е. он постоянно находится на кристалле процессора. Кэш второго уровня L2 размещается отдельно, т. е. не находится на наборе микросхем процессора, но встроен в конструктив процессора и позволяет выбирать данные с полной операционной скоростью процессора. Кэш третьего уровня L3 также отделен; он расположен непосредственно по соседству с процессором, но не объединен с ним. Хотя такое крошечное различие в расположении может показаться незначительным, расстояние, которое электроны должны преодолеть на пути туда и обратно при выборке из памяти, на порядок больше для кэша L3, чем для кэша L2. Таким образом, кэш L3 намного медленнее, но он может содержать гораздо больший объем памяти, потому что не является частью процессора.

Тестирование показало, что кэш L2 размером от 512 Кбайт до 1 Мбайт улучшает выполнение на 11% и дополнительно на 18,5% для кэша от 1 до 2 Мбайт. Таким образом, процессор с кэш-памятью L2 размером 2 Мбайт имеет производительность на 31,5% выше, чем процессор с кэшем L2 размером 512 Кбайт. Большинство процессоров в настоящее время предлагает дополнительный кэш L3 размером 4 Мбайт. В нашем тестировании дополнительный кэш L3 размером 4 Мбайт улучшил работу сервера SQL Server на 39%.

Самый частый вопрос, задаваемый нашими пользователями: «Что покупать, дешевый процессор с небольшим кэшем L2 или более дорогой с большим кэшем?» Усложняет выбор и то, что более быстрый процессор имеет меньший кэш L2, а более медленный процессор имеет больший кэш L2. Ниже приводится несколько основных правил, которыми следует руководствоваться при выборе объема кэша.

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

Многопоточные процессоры. Примечательная особенность процессоров Intel — возможность многопоточности, что позволяет одному процессору обрабатывать более одной команды за цикл, создавая второй виртуальный процессор. С включенной функцией многопоточности Windows и SQL Server «видят» один физический процессор как два логических. По сдержанным оценкам Intel, многопоточность улучшает выполнение на 10-30%, но наше испытание показало более высокую производительность. С включенной многопоточностью наши тесты демонстрировали среднее увеличение производительности на 35% для SQL Server, работающего с 1000 пользователей OLTP, и увеличение на 46% при работе 500 пользователей с теми же приложениями OLTP. Такой рост производительности впечатляет. Предположительно процессору, который не имеет многопоточности, чтобы достигнуть того же самого увеличения производительности, нужно работать с тактовой частотой свыше 1 ГГц.

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

Память

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

Указание минимума и максимума оперативной памяти, доступной серверу SQL, взамен динамического ее выделения позволяет повысить производительность на системах, специально выделенных под SQL Server, в силу уменьшения числа операций подкачки страниц.
Параллельное выполнение запросов требует большего количества памяти, чем непараллельное. Рекомендуется на SQL Server отключать в sp_configure настройку Maximum Degrees of Parallelism (Max DOP), пока не будет произведено тестирование и не будет зафиксировано улучшение производительности при включении Max DOP.
Механизм Address Windowing Extensions (AWE) обычно плохо соседствует с другими приложениями и будет пытаться потреблять все ресурсы, к которым может обратиться. Таким образом, AWE рекомендуется включать только на выделенных компьютерах с SQL Server.

SQL Server 2000 Enterprise Edition позволяет задействовать до 64 Гбайт оперативной памяти при использовании AWE, но SQL Server может поддерживать память свыше 4 Гбайт только для кэширования данных. Тем не менее можно столкнуться с нехваткой ресурсов при кэшировании процедур. Заметим, что при 64-разрядной обработке все пространство памяти доступно любому элементу SQL Server, в том числе кэшированию процедур. Не забывайте, что разные версии SQL Server поддерживают различные объемы памяти. Например, SQL Server Standard Edition поддерживает максимальный размер памяти до 2 Гбайт.

Дисковый ввод/вывод

Чтобы должным образом настроить дисковый ввод/вывод, нужно изучить основные типы массивов RAID, их преимущества и недостатки. Главное достоинство RAID — надежность. Когда один диск из массива RAID выходит из строя, другие диски массива дают серверу возможность продолжать работать без остановки. Второе преимущество использования RAID — повышение производительности. Поскольку массивы RAID включают в себя множество дисков, множество головок дисков одновременно выполняют чтение и запись, таким образом ускоряя дисковый ввод/вывод.

Наиболее общие типы RAID, которые используются для баз данных SQL Server, — это RAID5, RAID1 и RAID10. Кроме того, многие предприятия теперь внедряют сетевые системы хранения (SAN), так что мы их также оценим.

RAID5. В RAID5 каждый блок данных записывается на диск с данными, а блок четности — на другой диск массива. Во время чтения RAID5 читает блок данных напрямую и проверяет блок четности. Для работы RAID5, как показано на рис. 1, требуется по крайней мере три диска. Преимущество использования RAID5 состоит в одной из самых высоких скоростей чтения данных в транзакциях (хотя там средняя скорость записи данных транзакции). Снижение в RAID5 количества дисков четности, по сравнению с дисками с данными, повышает эффективность. А большое количество дисков дает хорошую общую скорость передачи.

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

RAID1. Почти по всем показателям RAID1 превосходит RAID5. Чтобы достичь самой высокой производительности с RAID1, необходим хороший контроллер, который может параллельно выполнять две отдельные процедуры чтения или записи в зеркальной паре. Как показано на рис. 2, для RAID1 требуется по крайней мере два диска.

Использование RAID1 имеет несколько существенных преимуществ. Например, он может одновременно поддерживать два процесса чтения в зеркальной паре. Это вдвое ускоряет чтение данных транзакций и дает такую же скорость записи транзакции, как и в случае с единственным диском; кроме того, скорость передачи блока данных равна скорости для единственного диска. Также RAID1 предлагает стопроцентную избыточность данных, так что в восстановлении в случае сбоя диска необходимости нет — данные просто копируются на новый диск. Кроме того, RAID1 имеет самую простую архитектуру подсистемы хранения из всех типов RAID и может выдержать многократные последовательные сбои диска (т. е. сбой на одном диске зеркальной пары).

К сожалению, RAID1 повышает издержки также на 100%, потому что чтение или запись всегда должны дублироваться. И стоимость RAID1 высока, поскольку дисков нужно в два раза больше.

RAID10. Как показано на рис. 3, RAID10, также известный как RAID1+0, предлагает использовать все преимущества зеркалирования (RAID1) и чередования (RAID0) и имеет мало недостатков. Это самый высокопроизводительный и наиболее надежный RAID, а также наиболее дорогой. RAID 10 требует наличия как минимум четырех дисков. Плюс еще в том, что RAID10 работает как массив с чередованием (RAID0), чьи сегменты — это зеркальные массивы (RAID1). RAID10 имеет ту же самую устойчивость к сбоям и те же самые издержки, что и RAID1. RAID10 имеет более быстрый ввод/вывод, нежели RAID1. Только RAID10 дороже, потому что работу нужно начинать по крайней мере с четырех дисков, для того чтобы создать массив RAID10. Средняя производительность может снижаться, потому что дисковые головки должны оставаться на параллельных дорожках, а высокая стоимость RAID10 ограничивает возможности масштабирования системы.

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

Тестирование доказывает, что SAN высокого класса выигрывает у массивов RAID. SAN имеют еще несколько преимуществ. Например, SQL Server может использовать более двух подключенных к SAN оптоволоконных кабелей; теперь, когда необходимо, можно увеличить пропускную способность SAN. Другое значительное преимущество SAN — это эффективное использование дискового пространства. При хорошо настроенной конфигурации RAID для работы базы данных в 100 Гбайт понадобятся терабайты дискового пространства, чтобы иметь достаточное количество головок для чтения и записи данных. SAN дает системе только то, в чем она нуждается, и не имеет избыточного дискового пространства, которое образуется при использовании RAID. Кроме того, можно задействовать SAN для поддержки одновременной работы большого количества серверов, как показано на рис. 4. А dual-path и multi-path у SAN соответствует работе томов RAID1 и RAID10.

Недостатки SAN — высокая цена и сложная установка. Кроме того, должным образом настроенный SAN требует учета полосы пропускания и ограничений ввода/вывода для всех систем, которые работают с ним.

Обособление объектов базы данных SQL Server

Можно устранить или смягчить влияние узких мест при вводе/выводе, размещая объекты базы данных на отдельных томах RAID. Наше испытание, хотя и не на достаточно большом объеме выборки, будучи с научной точки зрения правильным, выявило некоторые тенденции. Изолировав файлы пользовательских баз данных и журнальные файлы, мы увидели более чем 10-процентное увеличение производительности дискового ввода/вывода. Мы отметили повышение производительности дискового ввода/вывода от 5 до 9% при изолировании файла tempdb. И мы увидели менее чем 5-процентное улучшение в производительности дискового ввода/вывода при обособлении исполняемых файлов SQL Server, Windows и файла Pagefile.sys.

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

Уроки ввода/вывода

Принято считать, что RAID5 — система классом ниже по сравнению с RAID1 и RAID10. Наше испытание подтвердило, что RAID1 и RAID10 имели очень похожую производительность. Производительность SAN в переводе на общее количество транзакций, обработанных в секунду, была почти идентична производительности RAID1 и RAID10. Однако стоит заметить, что у SAN время транзакций (время, которое требуется для завершения одной транзакции) было почти вдвое меньше и что длина очередей дискового ввода/вывода была короче, если сравнивать с RAID1 или RAID10. Возможно, это из-за лучшего кэширования дискового ввода/вывода. В известном смысле наше испытание показало, что SAN помогает облегчить время ожидания при дисковом вводе/выводе, если сравнивать с RAID.

Другой важный урок состоит в том, что чем больше дисков, тем лучше производительность. В наших тестах производительность RAID5 возросла на 1,4% в пересчете на каждый добавленный к базовой конфигурации диск. Производительность RAID1 и RAID10 улучшилась приблизительно на 5% на каждый добавленный диск. Приведем некоторые правила дискового ввода/вывода, выведенные нами из испытаний.

Добавление диска приводит к повышению скорости чтения.
Добавление контроллеров приводит к повышению скорости записи.
Кэширование диска при записи дает существенный рост производительности, но возникает риск. Некоторые кэши дисков для предотвращения потери данных в случае сбоя требуют или присутствия внутренних батарей, или непрерывного электропитания (UPS) на сервере. В терминологии SQL Server это называется "гарантированным записыванием".
Не забывайте, что диск и кэш в SAN никогда не превзойдет по скоростным и динамическим характеристикам кэш памяти. Оперативная память также дешевле, чем дисковый кэш. Тратить средства следует прежде всего на оперативную память.
Размещение файла данных на двух или более массивах RAID может повысить дисковый ввод/вывод при нехватке ресурсов ввода/вывода.
Как и ожидалось, более быстрые диски повышают количество транзакций в секунду и сокращают время ожидания.

Наконец, следует помнить о количестве «рабочих дисков». Нередко приходится слышать о том, что RAID10 быстрее RAID5. Это, конечно, верно, но производительность выше «для одного и того же числа рабочих дисков». Обычно же не покупают одинаковое количество рабочих дисков, когда отдано предпочтение RAID10, а не RAID5.

Стоит подумать об этом, а также помнить, что RAID5 может оказаться лучшим вариантом, если финансовые возможности ограниченны. Определиться с выбором при покупке аппаратных средств поможет врезка «Рекомендации».

Кевин Клайн - Технический директор по решениям SQL Server в Quest Software и президент международной ассоциации разработчиков Professional Association for SQL Server (PASS). kevin.kline@quest.com

Как проводились тесты

Тесты, которые мы проводили, были основаны на транзакциях и схеме базы данных, используемой для эталонных тестов TPC-C (http://www.tpc.org), и масштабировались от 500 до 1000 конкурирующих пользователей. Обратите внимание, что тесты TPC-C — это тесты OLTP; таким образом, конфигурации и рекомендации больше подходят для сред OLTP и не годятся для сред OLAP. Мы приобрели недорогой сервер для нашей исходной конфигурации. Затем, чтобы выразить изменения в производительности в процентах по сравнению с исходным уровнем, мы взяли другую аппаратную конфигурацию: процессор, память и дисковый адаптер.