Этот миф я слышу снова, и снова, и снова…
У tempdb всегда должно быть по одному файлу данных на каждое ядро процессора.
ЛОЖЬ
Это один из самых разочаровывающих мифов, потому что существует так много «официальной» информации от Microsoft и других записей в блогах, которые увековечивают этот миф.
Один из самых больших источников путаницы заключается в том, что команда SQL CAT рекомендует соотношение 1:1, но они исходят из перспективы чисто масштабирования, а не с точки зрения общей производительности, и они работают с крупными заказчиками, у которых первоклассные серверы и подсистемы ввода-вывода. Большинство людей таковыми не являются.
На экземпляр приходится только одна tempdb, и множество процессов её используют, поэтому она часто становится узким местом производительности. Вам это уже известно. Но когда проблема с производительностью оправдывает создание дополнительных файлов данных tempdb?
Когда вы видите ожидания PAGELATCH_XX в tempdb, у вас возникает конкуренция за битовые карты распределения в памяти. Когда вы видите ожидания PAGEIOLATCH_XX в tempdb, у вас возникает конкуренция на уровне подсистемы ввода-вывода. Вы можете думать о краткой блокировке (latch) как о чём-то вроде традиционной блокировки, но гораздо более лёгкой, гораздо более временной и используемой внутри подсистемы хранения для управления доступом к внутренним структурам (например, к копиям страниц базы данных в памяти).
Ознакомьтесь с моей подборкой статей «Статистика ожиданий» для получения полезных скриптов, использующих динамическое административное представление sys.dm_os_wait_stats, которые покажут вам, какой тип ожидания наиболее распространён на вашем сервере. Если вы видите, что это ожидания PAGELATCH_XX, вы можете использовать этот скрипт Роберта Дэвиса. Он использует динамическое административное представление sys.dm_os_waiting_tasks для декомпозиции ресурса ожидания и позволяет вам узнать, что именно ожидается в tempdb.
Если вы наблюдаете ожидания PAGELATCH_XX в tempdb, вы можете смягчить их с помощью флага трассировки 1118 (полностью задокументирован в KB 328551) и создания дополнительных файлов данных tempdb. Я написал большую статью в блоге, развенчивающий некоторые мифы об этом флаге трассировки и объясняющий, почему он всё ещё потенциально требуется во всех версиях, включая SQL Server 2012.
В SQL Server 2000 рекомендация заключалась в использовании одного файла данных tempdb на каждое логическое ядро процессора. Во всех более поздних версиях, включая SQL Server 2012, эта рекомендация сохраняется, но из-за некоторых оптимизаций (см. мою статью в блоге) вам обычно не нужно соотношение 1:1 — вы можете обойтись количеством файлов данных tempdb, равным от 1/4 до 1/2 количества логических ядер процессора — именно это рекомендуют все, кроме официального руководства Microsoft.
Но теперь есть ещё более качественная рекомендация, и именно её я также рекомендую. На конференции PASS в 2011 году мой хороший друг Боб Уорд, ведущий специалист службы поддержки продуктов SQL, предложил новую формулу: если у вас менее 8 ядер, используйте #файлов = #ядер. Если у вас более 8 ядер, используйте 8 файлов, и если вы наблюдаете конкуренцию в памяти, добавляйте по 4 файла за раз. Это документированный совет Microsoft в статье Базы Знаний: №2154845 Рекомендации по сокращению состязания выделения ресурсов в базе данных tempdb SQL Server..
Всё это большое обобщение. Только на прошлой неделе я слышал о клиенте, у которого рабочая нагрузка на tempdb была настолько высокой, что им пришлось использовать 64 файла данных tempdb в системе с 32 логическими ядрами процессора — и это был единственный способ ослабить конкуренцию. Означает ли это, что это наилучшая практика? Абсолютно нет!
Итак, почему соотношение 1:1 не всегда хорошая идея? Слишком много файлов данных tempdb может вызвать проблемы с производительностью по другой причине. Если у вас есть рабочая нагрузка, использующая операторы плана запроса, которые требуют много памяти (например, сортировки), велика вероятность, что на сервере не будет достаточно памяти для выполнения операции, и она вытеснится в tempdb. Если файлов данных tempdb слишком много, то запись временно вытесненных данных может быть значительно замедлена, в то время как система распределения выполняет циклическое распределение (round-robin). То же самое может произойти с очень большими временными таблицами в tempdb.
Почему циклическое распределение может замедлить вытеснение памяти в tempdb при большом количестве файлов? Несколько возможностей:
- Циклическое распределение выполняется для каждой файловой группы, а в tempdb может быть только одна файловая группа. При 16, 32 или более файлах в tempdb и очень больших распределениях, выполняемых всего несколькими потоками, дополнительная синхронизация и работа, необходимые для выполнения циклического распределения (просмотр весовых коэффициентов распределения для каждого файла и принятие решения о распределении или уменьшении веса, плюс довольно частый пересчёт весовых коэффициентов для всех файлов — каждые 8192 распределения), начинают накапливаться и становятся заметными. Это сильно отличается от множества потоков, выполняющих множество мелких распределений. Это также сильно отличается от распределения из файловой группы с одним файлом — которое оптимизировано (очевидно) так, чтобы не выполнять циклическое распределение.
- Ваши файлы данных tempdb не одинакового размера, и поэтому автоматическое расширение увеличивает только один файл (алгоритм, к сожалению, нарушен), что приводит к неравномерному использованию и возникновению «горячей точки» ввода-вывода.
- Наличие слишком большого количества файлов может привести к, по сути, случайным шаблонам ввода-вывода, когда пулу буферов необходимо освободить пространство через фоновый процесс записи «lazywriter» (контрольные точки tempdb не сбрасывают страницы данных) для систем с не очень большими пулами буферов, но огромным объёмом данных в tempdb. Если подсистема ввода-вывода не может справиться с нагрузкой на нескольких файлах, она начнёт замедляться.
Мне действительно нужно написать статью в блоге с тестированием производительности, чтобы показать, что я имею в виду — но тем временем я слышал это от нескольких клиентов, которые создавали большое количество файлов tempdb, и я знаю это из того, как работает код (моя команда разработчиков владела кодом распределения).
Таким образом, если вы это сделаете — вы в проигрыше, если не сделаете — вы тоже в проигрыше, верно? Потенциально — да, это создаёт для вас некоторую дилемму: сколько файлов данных tempdb у вас должно быть? Что ж, я не могу ответить на это за вас — за исключением того, чтобы дать вам следующие рекомендации, основанные на разговорах со многими клиентами и участниками конференций/занятий. Будьте осторожны, создавайте несколько файлов данных tempdb только для ослабления конкуренции, с которой вы сталкиваетесь — и не увеличивайте их количество до слишком большого, если вам это действительно не нужно — и помните о потенциальных недостатках, если вам это необходимо. Возможно, вам придётся тщательно сбалансировать масштабируемость и производительность, чтобы не помогать одной рабочей нагрузке и не вредить другой.
Надеюсь, это поможет.
P.S. Чтобы ответить на поступивший комментарий — нет, дополнительные файлы не обязательно должны быть на отдельном хранилище. Если всё, что вы видите, это конкуренция PAGELATCH_XX, отдельное хранилище не имеет значения, так как конкуренция происходит на страницах в памяти. Для ожиданий PAGEIOLATCH_XX вам, скорее всего, потребуется использовать отдельное хранилище, но не обязательно — возможно, вам нужно переместить саму tempdb на другое хранилище, отличное от других баз данных, а не просто добавлять больше файлов данных tempdb. Анализ того, что где хранится, будет необходим для выбора правильного пути.
Комментариев нет:
Отправить комментарий