Підвищене навантаження на диски сервера баз даних SQL Server

Підвищене навантаження на диски сервера баз даних

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

Як правило, підвищене навантаження на диски можна визначити у різний спосіб. Основний із них – це отримання лічильника «Середньої довжини черги до диску»:

Рис.1. Середня довжина черги до диска для читання та запису

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

Розглянемо кілька основних причин підвищеного навантаження на диски систем на базі MS SQL Server.

Навантаження на диски обумовлене швидкимвитіснення даних з кешу SQL Server.

Рис.2. Демонстрація витіснення даних із кешу SQL Server

На малюнку 2 показані 3 умовні етапи різного навантаження на диск. На етапі 1 та етапі 3 – черги до диска були мінімальними. Чому ж на етапі 2 черга різко зросла і це спричинило появу проблем продуктивності у користувачів? Відповідь на це питання легко знайти на другому графіку малюнку 2: «Очікуваний термін життя сторінки пам'яті», який показує передбачуваний час знаходження сторінки даних у кеші SQL Server. Між двома етапами бачимо різке зниження цього графіка зі значенням 3000 до 200. З погляду логіки роботи SQL Server це означає, що дані будуть до кеші не 3000 секунд як раніше, а 200 секунд, отже, якщо користувач запитає дані через 300 секунд, SQL Server з майже 100% ймовірністю не знайде їх в оперативній пам'яті (кеші) і доведеться виконувати операцію читання з диска. Цими операціями забезпечується зростання черги до диска. Протягом всього етапу 2 кеш «прогрівався» (заповнювався даними) і на етапі 3 навантаження диск впала.

Ми визначили вигляд проблеми, тепер розглянемо варіанти розв'язання.

Що треба зробити:

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

- Можлива проблема як обслуговування статистик та індексів MS SQL Server.

Що не треба робити:

- Не треба купувати нові диски (дискові масиви), це не вирішує проблему, а скоріше її посилює.

Для написання матеріалу ми використовували інструмент для моніторингу продуктивностіPerfExpert, що дозволяє забезпечити можливість збирання та глибокого аналізу даних.

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

2. Навантаження на диски, обумовлене свопуванням пам'яті на диски внаслідок нестачі вільної пам'яті.

Рис.1. Практичний приклад підвищеного навантаження на диск

На малюнку 1 показано практичну ситуацію на сервері БД SQL Server у клієнта протягом 1,5 годин. Як видно з лічильника «Середньої довжини черги до диска» диск навантажений і справляється з кількістю звернень щодо нього.

На малюнку також показано два інші показники: "Навантаження CPU", "Вільна оперативна пам'ять" для пошуку причин гальмування диска. Умовно ділимо ситуацію на два етапи: перший етап – черга до диска практично дорівнює 0 і користувачі працюють у звичайному режимі, і другий етап – протягом якого черга до диска піднімається до максимальних значень (342) та користувачі не можуть якісно працювати. Чим же обумовлене таке навантаження на диск?

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

Показник «Вільна оперативна пам'ять» якраз показує доступність реальної оперативної пам'яті для інших процесів, а отже, чим його значення більше, тим менша ймовірність свопування. На малюнку 1 значення вільної оперативної пам'яті на сервері баз даних постійно зменшується до 500 Мб, далі до 200 Мб, це у свою чергу призвело до навантаження на диск(На етапі 2).

Постає питання - а навіщо на малюнку 1 ми показали лічильник "Навантаження CPU"? Все просто, на етапі 1 середнє завантаження CPU було близько 50%, на етапі 2 - 40%, при цьому в системі працювала аналогічна кількість користувачів. Таке зменшення значення свідчить, що процесор недозавантажений і вузьке місце у продуктивності змістилося убік диска (він справляється).

Для виправлення цієї ситуації достатньо правильно розподілити споживання оперативної пам'яті та не допустити зменшення її об'єму до 500 Мб (як рекомендація). Неправильним варіантом рішення було б придбання більш продуктивного фізичного диска чи сховища.

3.Навантаження на диски, обумовлене внутрішніми механізмами роботиSQLServer.

Мал. 2. Періодичне навантаження на диск

Як видно з малюнка 2, періодично черга до диска збільшується, причому ці стрибки відбуваються через однакові часові інтервали. Це може говорити про те, що є періодично повторювані регламентні операції.

З нашого досвіду це можуть бути такі операції:

- Збільшення розміру файлів даних та лога транзакцій (особливо якщо вказано фіксований розмір приросту).

- Резервна копія файлу даних чи журналу транзакцій.

Збір та аналіз даних здійснювався з використанням моніторингу продуктивності PerfExpert.