Як впоратися з 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>

*

*