Как справиться с PAGELATCH при большом количестве одновременных операций INSERTИсточник: sqlcat
Введение Недавно, мы проводили лабораторные испытания в Microsoft Enterprise Engineering Center, при которых использовалась большая рабочая нагрузка, характерная для OLTP систем. Целью этой лабораторной работы было взять Microsoft SQL Server c интенсивной рабочей нагрузкой и посмотреть, что случится при увеличении числа процессоров с 64 до 128 (примечание: эта конфигурация была ориентирована на релиз Microsoft SQL Server 2008 R2). Рабочая нагрузка представляла собой бльшое количесво одновеменных операций вставки, направляемых в несколько больших таблиц. Как только мы начали масштабировать нагрузку до 128 процессорных ядер, сразу же в статистике ожиданий стали доминировать блокировоки PAGELATCH_UP и PAGELATCH_EX. Средняя продолжительность ожиданияй была десятки миллисекунд, и таких ожиданий было очень много. Такое их количество оказалось для нас неожиданностью, ожидалось, что их продолжительность не будет превышать несколько миллисекунд. В данной технической заметке будет описано, как мы сначала диагностировали подобную проблему и как для разрешения подобной проблемы можно использовать секционированые таблицы. Диагностика проблемы Когда в sys.dm_os_wait_stats наблюдается большое число ожиданий PAGELATCH, с помощью sys.dm_os_waiting_tasks можно определить сессию и ресурс, который задача ожидает, например, с помощью этого сценария:
Пример результата:
В столбце resource_description указаны местоположения страниц, к которым ожидают доступ сессии, местоположение представлено в таком формате: <database_id>:<file_id>:<page_id> Опираясь на значения в столбце resource_description, можно составить довольно сложный запрос, который предоставит выборку всех попавших в список ожидания страниц:
Запрос показал, что ожидаемые страницы относятся к кластеризованному индексу, определённому первичным ключом таблицы с представленной ниже структурой: CREATE TABLE HeavyInsert ( ID INT PRIMARY KEY CLUSTERED) ON [PRIMARY] Что происходит, почему возникает очередь ожиданий к страницам данных индекса - всё это будет рассмотрено в этой технической заметке. Основная информация Чтобы определить, что происходит с нашей большой OLTP-нагрузкой, важно понимать, как SQL Server выполняет вставку в индекс новой строки. При необходимости вставки в индекс новой строки, SQL Server будет следовать следующему алгоритму внесения изменений:
В итоге, страница будет сброшена на диск процессом контрольной точкой или отложенной записи. Однако, что произойдет если все вставляемые строки попадают на одну и ту же страницу? В данном случае можно наблюдать рост очереди к этой странице. Даже учитывая, что краткая блокировка весьма непродолжительна, она может стать причиной конкуренции при высокой параллельной рабочей нагрузке. У нашего клиента, первый и единственный столбец в индексе являлся монотонно возрастающим ключом. Из-за этого, каждая новая вставка шла на ту же самую страницу в конце В-дерева, пока эта страница не была заполнена. Рабочие нагрузки, которые используют в качестве первичного ключа IDENTITY или другие столбцы с последовательно увеличивающимися значениями, также могут столкнуться с подобной проблемой, если одновременно выполняемая нагрузка достаточно высока. Решение Всегда, когда несколько потоков получают синхронный доступ к одному и тому же ресурсу, может проявиться описанная выше проблема. Стандартное решение состоит в том, чтобы создать больше ресурсов конкурентного доступа. В нашем случае, таким конкурентным ресурсом является последняя страница В-дерева. Один из способов снизить конкуренцию за одну страницу состоит в том, чтобы выбрать в качестве первого столбца индекса другой, не увеличивающийся монотонно столбец. Однако, для нашего клиента это потребовало бы внесения изменений на прикладном уровне в клиентских системах. Мы должны были найти другое решение, которое могло бы ограничиться только изменениями в базе данных. Помните, что местом конкуренции является одна страница в В-дерева. Если бы только было возможно использовать для этого несколько В-деревьев для одной таблицы!. К счастью, такая возможность есть, это: Секционированные таблицы и индексы. Таблица может быть секционирована таким способом, чтобы новые строки размещались в нескольких секциях. Сначала нужно создать функцию и схему секционирования:
Представленный выше пример использует четыре секции. Число необходимых секций зависит от числа активных процессов, выполняющих операции INSERT в описанную выше таблицу. Есть некоторая сложность в секционировании таблицы с помощью хэш-столбца,например в том, что всякий раз, когда происходит выборка строк из таблицы, будут затронуты все секции. Это означает, что придётся обращаться более чем к одному В-дереву, т.е. не будет отброшенных оптимизатором за ненадобностью ненужных секций. Связанная сэтим дополнительная нагрузка на процессоры и некоторое увеличение времени ожиданий процессоров, побуждает минимизировать число планируемых секций (их должно быть минимальное количество, при котором не наблюдается PAGELATCH). В рассматриваемом нами случае, в системе нашего клиента имелось достаточно много резерва в утилизации процессоров, так что было вполне возможно допустить небольшую потерю времени для инструкций SELECT, и при этом увеличить до необходимых объёмов норму инструкций INSERT. Далее нам требуется столбец, который поможет распределить вставки по четырем секциям. Такого столбца изначально в сценарии Microsoft Enterprise Engineering Center не было. Однако, его всегда достаточно просто создать. Используя тот факт, что столбец ID монотонно увеличивается с приращением равным единице, и здесь легко применима довольно простая хеш-функция: CREATE TABLE HeavyInsert_Hash( ID INT NOT NULL , col1 VARCHAR(50) , HashID AS ID % 4 PERSISTED NOT NULL) С помощью столбца HashID, вставки в четыре секции будут выполняться циклически. Создаём кластеризованный индекс следующим образом:
Используя новую схему таблицы с секционированием вместо первоначального варианта таблицы, мы сумели избавиться от очередей PAGELATCH и повысить скорость вставки. Этого удалось достичь за счёт балансировки одновременных вставок между несколькими секциями, где каждая секция имеет своё В-дерево. Нам удалось повысить производительность вставки на 15 процентов, и избавиться от большой очереди PAGELATCH к горячей странице индекса одной таблицы. При этом у нас остался достаточно большой резерв процессоров, что делает возможным дальнешую отимизацию, если мы захотим применить аналогичный прием для другой таблицы, тоже с высокой нормой вставки. Строго говоря, суть этой уловки в оптимизации логической схемы первичного ключа таблицы. Однако, потому что ключ просто стал длиннее на величину хеш-функции относительно изначального ключа, дубликатов для столбца ID удалось избежать. Уникальные индексы по единственному столбцу таблицы зачастую становятся причиной проблем с очередями PAGELATCH. Но даже если эту проблему удастся устранить, у таблицы может оказаться другой, некластеризованный индекс, который будет испытвать аналогичную проблему. Как правило, проблема наблюдается для уникальных ключей на единственном столбце, где каждая вставка попадает на одну и ту же страницу. Если и у других таблиц индексы испытывают высокую конкуренцию с PAGELATCH, можно применить тот же прием с секционированием к индексам таких таблиц, используя такой же хэш-ключ в качестве первичного ключа. Не всегда возможно внести изменения в приложение, особенно, если оно является продуктом третьих фирм. Но если изменение запросов возможно, становится доступной их оптимизация за счёт добавления к ним условий фильтрации по предикатам первичного ключа. Пример: Чтобы отбросить ненужные секции, можно внести следующие изменения в сценарий:
Который после изменений будет выглядеть так: SELECT * FROM HeavyInsert_HashWHERE ID = 42 AND HashID = 42 % 4 Исключение оптимизатором ненужных секций по значению хэша не будет вам ничего стоить, если только не считать большой платой за это увеличение на один байт каждой строки кластеризованного индекса. |