Як впоратися з PAGELATCH при великих INSERT-навантаженнях, Інші СУБД, Бази даних, статті

Введення

Нещодавно, ми проводили лабораторні випробування в Microsoft Enterprise Engineering Center, при яких використовувалася велика робоча навантаження, характерна для OLTP систем. Метою цієї лабораторної роботи було визначити, що трапиться при збільшенні числа процесорів з 64 до 128, при обслуговуванні Microsoft SQL Server інтенсивної робочого навантаження (примітка: ця конфігурація була орієнтована на реліз Microsoft SQL Server 2008 R2). Робоче навантаження представляла собою добре распараллеленние операції вставки, що направляються в кілька великих таблиць.
Робоче навантаження масштабувати до 128 процесорних ядер, але в статистиці очікувань було дуже багато коротких блокувань PAGELATCH_UP і PAGELATCH_EX. Середня тривалість очікування була десятки мілісекунд, і таких очікувань було дуже багато. Таке їх кількість виявилося для нас несподіванкою, очікувалося, що тривалість не перевищуватиме кілька мілісекунд.
У цій технічній замітці спочатку буде описано, як діагностувати подібну проблему і як для вирішення подібної проблеми використовувати секціонування таблиць.


Діагностика проблеми

Коли в sys.dm_os_wait_stats спостерігається велике число PAGELATCH, за допомогою sys.dm_os_waiting_tasks можна визначити сесію і ресурс, який задача очікує, наприклад, за допомогою цього сценарію:



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


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


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


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


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


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

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




  1. В журналі транзакцій створюється запис про те, що рядок змінилася.
  2. Здійснюється пошук в В-дереві місцезнаходження тієї сторінки, куди повинна буде потрапити новий запис.
  3. Здійснюється накладення на цю сторінку короткої блокування PAGELATCH_EX, яка покликана перешкодити змінам з інших потоків.
  4. Здійснюється додавання рядка на сторінку і, якщо це необхідно, здійснюється відмітка цієї сторінки як “брудною”.
  5. Здійснюється зняття короткої блокування зі сторінки.

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


Рішення

Завжди, коли кілька потоків отримують синхронний доступ до одного і того ж ресурсу, може проявитися описана вище проблема. Стандартне рішення полягає в тому, щоб створити більше ресурсів конкурентного доступу. В нашому випадку, таким конкурентним ресурсом є остання сторінка В-дерева.
Одним із способів знизити конкуренцію за одну сторінку полягає в тому, щоб вибрати в якості першого стовпця індексу інший, не збільшується монотонно стовпець. Однак, для нашого клієнта це зажадало б внесення змін на прикладному рівні в клієнтських системах. Ми повинні були знайти інше рішення, яке могло б обмежитися лише змінами в базі даних.
Пам’ятайте, що місцем конкуренції є одна сторінка в В-дерева, і конкуренція була б менше, якби було можливо використовувати для цього кілька В-дерев, і в той же час працювати тільки з однією таблицею. На щастя, така можливість є, це: Секціонірованние таблиці та індекси. Таблиця може бути секціонірована таким способом, щоб нові рядки розміщувалися в декількох секціях.
Спочатку потрібно створити функцію і схему секціонування:


Представлений вище приклад використовує чотири секції. Число необхідних секцій залежить від числа активних процесів, які виконують операції INSERT в описану вище таблицю. Є деяка складність у секціонуванні таблиці за допомогою хеш-стовпця, наприклад, в тому що всякий раз, коли відбувається вибірка рядків з таблиці, будуть порушені всі секції. Це означає, що доведеться звертатися більш ніж до одного В-дереву, тобто не буде відкинутих оптимізатором за непотрібністю непотрібних секцій. Буде додаткове навантаження на процесори і деяке збільшення часу очікувань процесорів, що спонукає мінімізувати число планованих секцій (їх має бути мінімальна кількість, при якому не спостерігається PAGELATCH). У розглянутому нами випадку, в системі нашого клієнта було досить багато резерву в утилізації процесорів, так що було цілком можливо допустити невелику втрату часу для інструкцій SELECT, і при цьому збільшити до необхідних обсягів норму інструкцій INSERT.
Ще однією складністю є те, що потрібен додатковий стовпець, по якому буде виконуватися секціонування, тобто на підставі значення якого будуть розподілятися вставки по чотирьох секціях. Такого стовпця спочатку в сценарії Microsoft Enterprise Engineering Center не було. Однак, його досить просто було створити. Для цього використовувався той факт, що стовпець ID монотонно збільшується зі збільшенням рівним одиниці, і тут легко застосовна досить проста хеш-функція:


За допомогою стовпця HashID, вставки в чотири секції будуть виконуватися циклічно.
Створюємо кластеризованих індекс наступним чином:


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

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



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


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

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


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

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

Ваш отзыв

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

*

*