Як впоратися з PAGELATCH при великій кількості одночасних операцій INSERT

Введення

Нещодавно, ми проводили лабораторні випробування в 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 можна визначити сесію і ресурс, що призначення очікує, наприклад, за допомогою цього сценарію:




SELECT session_id, wait_type, resource_description

FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE “PAGELATCH%”

Приклад результату:              
























session_id


  wait_type


resource_description


42


PAGELATCH_EX


    7:1:122


46


PAGELATCH_EX


    7:1:122


48


PAGELATCH_EX


    7:1:122


53


PAGELATCH_EX


    7:1:122

У стовпці resource_description вказані місця розташування сторінок, до яких очікують доступ сесії, місце розташування представлено в такому форматі:

<database_id>:<file_id>:<page_id>


Спираючись на значення в стовпці resource_description, Можна скласти досить складний запит, який надасть вибірку всіх попали в список очікування сторінок:




SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms   


, s.name AS schema_name , o.name AS object_name ,

i.name AS index_name 

FROM sys.dm_os_buffer_descriptors bd JOIN (     
SELECT *          

, CHARINDEX (":", resource_description) AS file_index, CHARINDEX (":", resource_description



, CHARINDEX(“:”, resource_description)) AS page_index         



 , resource_description AS rd
FROM sys.dm_os_waiting_tasks wt WHERE wait_type LIKE "PAGELATCH%") AS wt ON bd.database_id = SUBSTRING (wt.rd, 0, wt.file_index) AND bd.file_id = SUBSTRING (wt.rd, wt.file_index, wt.page_index ) AND bd.page_id = SUBSTRING (wt.rd, wt.page_index, LEN (wt.rd)) JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_idJOIN sys.partitions p ON au.container_id = p.partition_idJOIN sys.indexes i ON  p.index_id = i.index_id


AND p.object_id = i.object_id


JOIN sys.objects o ON i.object_id = o.object_id JOIN sys.schemas s ON o.schema_id = s.schema_id

Запит показав, що очікувані сторінки відносяться до кластерізованному індексом, визначеним первинним ключем таблиці з представленою нижче структурою:


CREATE TABLE HeavyInsert (   


ID INT PRIMARY KEY CLUSTERED    

, col1 VARCHAR(50)

) ON [PRIMARY]

Що відбувається, чому виникає чергу очікувань до сторінок даних індексу – все це буде розглянуто в цій технічній замітці.


Основна інформація


Щоб визначити, що відбувається з нашої великої OLTP-навантаженням, важливо розуміти, як SQL Server виконує вставку індекс нового рядка. При необхідності вставки в індекс нового рядка, SQL Server буде слідувати наступним алгоритмом внесення змін:



У підсумку, сторінка буде скинуто на диск процесом контрольної точкою або відкладеної запису.


Однак, що станеться якщо всі вставляються рядки потрапляють на одну і ту ж сторінку? У даному випадку можна спостерігати зростання черги до цієї сторінки. Навіть враховуючи, що коротка блокування дуже нетривала, вона може стати причиною конкуренції при високій паралельної робочого навантаження. У нашого клієнта, перший і єдиний стовпець в індексі був монотонно зростаючим ключем. Через це, кожна нова вставка йшла на ту ж саму сторінку в кінці У-дерева, поки ця сторінка не була заповнена. Робочі навантаження, які використовують в якості первинного ключа IDENTITY або інші стовпці з послідовно збільшуються значеннями, також можуть зіткнутися з подібною проблемою, якщо одночасно виконувана навантаження досить висока.


Рішення


Завжди, коли кілька потоків отримують синхронний доступ до одного і того ж ресурсу, може проявитися описана вище проблема. Стандартне рішення полягає в тому, щоб створити більше ресурсів конкурентного доступу. У нашому випадку, таким конкурентним ресурсом є остання сторінка У-дерева.


Один із способів знизити конкуренцію за одну сторінку полягає в тому, щоб вибрати в якості першого стовпця індексу інший, не збільшується монотонно стовпець. Проте, для нашого клієнта це зажадало б внесення змін на прикладному рівні в клієнтських системах. Ми повинні були знайти інше рішення, яке могло б обмежитися лише змінами в базі даних.


Пам'ятайте, що місцем конкуренції є одна сторінка в В-дерева. Якщо б тільки було можливо використовувати для цього декілька По-дерев для однієї таблиці!. На щастя, така можливість є, це: Секціонованими таблиці та індекси. Таблиця може бути секціонованими таким способом, щоб нові рядки розміщувалися в декількох секціях.


Спочатку потрібно створити функцію і схему секціонування:



CREATE PARTITION FUNCTION pf_hash (INT) AS RANGE LEFT FOR VALUES (0,1,2)

CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY])

Представлений вище приклад використовує чотири секції. Число необхідних секцій залежить від кількості активних процесів, що виконують операції 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, Вставки у чотири секції будуть виконуватися циклічно. Створюємо кластерізованний індекс наступним чином:




CREATE UNIQUE CLUSTERED INDEX CIX_Hash

ON HeavyInsert_Hash (ID, HashID) ON ps_hash(HashID)

Використовуючи нову схему таблиці з секціонуванням замість початкового варіанту таблиці, ми зуміли позбутися черг PAGELATCH і підвищити швидкість вставки. Цього вдалося досягти за рахунок балансування одночасних вставок між кількома секціями, де кожна секція має своє По-дерево. Нам вдалося підвищити продуктивність вставки на 15 відсотків, і позбутися від великої черги PAGELATCH до гарячої сторінці індексу однієї таблиці. При цьому у нас залишився досить великий резерв процесорів, що робить можливим дальнешую отімізацію, якщо ми захочемо застосувати аналогічний прийом для іншої таблиці, теж з високою нормою вставки.


Строго кажучи, суть цієї виверти в оптимізації логічної схеми первинного ключа таблиці. Однак, тому що ключ просто став довшим на величину хеш-функції щодо початкового ключа, дублікатів для стовпця ID вдалося уникнути.


Унікальні індекси за єдиним стовпцем таблиці часто стають причиною проблем з чергами PAGELATCH. Але навіть якщо цю проблему вдасться усунути, у таблиці може виявитися інший, некластерізованний індекс, який буде іспитвать аналогічну проблему. Як правило, проблема спостерігається для унікальних ключів на єдиному стовпці, де кожна вставка потрапляє на одну і ту ж сторінку. Якщо і у інших таблиць індекси відчувають високу конкуренцію з PAGELATCH, можна застосувати той самий прийом з секціонуванням до індексів таких таблиць, використовуючи такий же хеш-ключ як первинного ключа.


Не завжди можливо внести зміни в додаток, особливо, якщо воно є продуктом третіх фірм. Але якщо зміна запитів можливо, стає доступною їх оптимізація за рахунок додавання до них умов фільтрації по предикатам первинного ключа.


Приклад: Щоб відкинути непотрібні секції, можна внести наступні зміни в сценарій:


 SELECT * FROM HeavyInsert_Hash 

 WHERE ID = 42


Який після змін буде виглядати так:

      SELECT * FROM HeavyInsert_Hash      
 WHERE ID = 42 AND HashID  = 42 % 4

Виняток оптимізатором непотрібних секцій за значенням хеша не буде вам нічого коштувати, якщо тільки не вважати великою платою за це збільшення на один байт кожного рядка кластерізованного індексу.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*