Перемога над фрагментацією (документація), Документація, Програмування, статті

Попереджаємо зниження продуктивності

Одного разу кілька місяців назад я був розбуджений наполегливим гудінням свого BlackBerry: клієнти, які використовували одну з моїх баз даних, скаржилися, що звернення до створеного Web-додатком при завантаженні часто використовуваної сторінки займає від 20 до 30 секунд. Протягом попередніх тижнів продуктивність поступово погіршувалася і знаходилася тепер на такому рівні, що невелика додаткове навантаження могла зупинити систему. Я повинен був знайти причину проблеми, причому зробити це слід було невідкладно. Як я з’ясував, мала місце несправність, яка виникла через поєднання фрагментації в таблицях і файлах бази даних і неефективного наповнення сторінки. І, розібравши завдання послідовно, я вирішив її.


Позначимо проблему

Першим ділом я відкрив монітор продуктивності, щоб побачити, станом якого з чотирьох апаратних компонентів – процесора, пам’яті, диска або мережі – обумовлено уповільнення. Лічильник Processor:% Processor Time був в області норми, SQL Server: Buffer Manager: Free Pages показував більше 2000 відкритих сторінок, а Network Interface: Bytes Total / sec був тільки на 1/20 від можливостей мережі гігабітного Ethernet. Physical Disk: Disk Bytes / sec були, відповідно, від 100 до 200% вище норми для нашого сервера, так що джерелом проблеми, здавалося, було використання диска.

В цьому випадку, безсумнівно, жорсткий диск був надмірно активним протягом відслідковується періоду, що уповільнювало всі інші операції. Оскільки ADO.NET має до певної міри агресивний алгоритм додавання з’єднань до пулу з’єднань, навіть у невеликий проміжок часу для користувача процес містить безліч зв’язків. Велика кількість сполук з одним і тим же джерелом даних зазвичай збільшує ймовірність конфліктуючих блокувань до даних загального доступу, що в свою чергу уповільнює відгук SQL Server. Як на дорозі в годину пік, коли один автомобіль, рухаючись повільно, затримує рух усіх інших, незначне збільшення часу відгуку на сервері бази даних може мати ефект каскадного уповільнення в залежному від нього прикладному оточенні.


Жорсткий диск як стримуючий фактор

Оскільки існують фізичні межі можливості передачі даних між пам’яттю і диском, максимальне збільшення продуктивності диска – ключ до оптимальної і повної продуктивності бази даних. Жорсткі диски не тільки мають фізичний межа, обумовлений можливою швидкістю читання головками електромагнітних змін на диску, але й обмежені обертальної швидкістю диска. Якщо сторінка, необхідна для роботи SQL Server, зараз знаходиться не під читаючої / друкарській головкою, контролер диска чекає, поки секція диска, що містить сторінку, виявиться під нею. Тому від обертальної швидкості диска залежить, як швидко дані будуть надані читаючої / друкарській голівці і як швидко вона зможе читати і записувати розряди.

Отже, обмеженість обертання диска визначає порядок і довжину значного числа запитів за даними. Компонент введення / виводу SQL Server читає і записує дані з максимально можливою швидкістю і вибудовує сторінки в тій послідовності, в якій вони розташовані на диску. Мета полягає в тому, щоб читати дані зі сторінок в порядку, близькому до їх нумерації, наскільки це можливо, тому що в результаті ми отримаємо найменший час очікування того моменту, коли потрібна секція опиниться під голівкою. Лічильники монітора продуктивності Avg Disk Bytes / Transfer, Avg Disk Bytes / Read і Avg Disk Bytes / Write повідомляють, скільки байтів задіюється при кожній операції введення / виводу. Буфера диска гарантують, що база даних SQL Server ніколи не буде мати менш ніж 8196 байт за оборот диска, але що нам потрібно, так це послідовні 65,536 (або більше) байт за оборот (65,536 байт, або 64 Кбайт). Якщо ви бачите, що дана величина менше, ніж 65,536, значить, виникли проблеми з фрагментацією даних.

Монітор продуктивності показував надмірне використання диска, але, щоб з’ясувати причину, мені потрібен був інший інструмент. FileMon – це вільно поширюваний інструмент від Sysinternals (www.sysinternals.com), Який описує все, що читає і пише, і число байтів за операцію для кожного файлу. Встановивши в FileMon фільтр для контролю тільки файлів бази даних, можна побачити, скільки байтів SQL Server читає і пише по кожному з групи файлів. Коли я зауважив, що FileMon повідомляє тільки про 8196 байт, прочитаних у файлі, який мав таблицю в 2,5 і 1 Гбайт в індексах, я подумав, що, ймовірно, виявив винуватця погіршення продуктивності.

Важливо пам’ятати, що кожен раз процес введення / виводу включає затримку, під час якої контролер чекає, коли диск повернеться під читає / друкарську голівку. Тому завжди на прочитання восьми сторінок потрібно більше часу, ніж на одну сторінку такого ж об’єму. Крім того, компонент введення / виводу для ефективного доступу не може упорядковувати читання, тому що не в змозі передбачити, які запити його очікують у майбутньому. Оптимізація диска відбувається, коли комбінують зберігання даних великими порціями з мінімально можливим зверненням до диска. Звіт FileMon повідомляє, що компонент введення / виводу прочитав табличну сторінку в 2,5 Гбайт за один раз, значить, щось заважало двигуну згрупувати запити в послідовність. Тому потрібно поглянути на деякі таблиці і з’ясувати причину.


Виявлення фрагментації

Корисний інструмент для ідентифікації ступеня фрагментації в таблиці – команда SQL Server DBCC SHOWCONTIG. На жаль, її запуск погіршує продуктивність, яку треба як раз поліпшити, тому що команда створює велику кількість операцій введення / виводу. C ключем FAST вплив на продуктивність сервера менше, ніж якщо запускати команду без нього. Але навіть виконання команди DBCC SHOWCONTIG FAST уповільнює систему досить сильно, якщо вона вже перевантажена. Найкраще при використанні Database Consistency Checker (DBCC) – якщо, звичайно, є час – виконати повне сканування і встановлювати TABLERESULTS, щоб сканувати таблицю у вихідний файл і потім зберегти результати.

Я написав на мові T-SQL збережену процедуру uspBuildFraglist, текст якої показаний в лістингу 1. Вона повинна автоматизувати роботу команди DBCC SHOWCONTIG WITH TABLERESULTS. UspBuildFraglist в циклі проходить за списком таблиць у вказаній базі даних і для кожної таблиці запускає DBCC SHOWCONTIG WITH TABLE RESULTS, ALL_INDEXES, який показує інформацію про фрагментацію даних та індексів зазначеної таблиці. Результати спочатку зберігаються в тимчасовій таблиці, потім переміщаються в постійну таблицю в базі даних, яка була призначена для утримуваних даних, створених адміністративними процесами підтримки на зразок цього.

Лістинг 1. Процедура uspBuildFraglist

Процедура UspDefragTables виконує команду DBCC DBREINDEX для будь-якого індексу, який має логічну фрагментацію або фрагментацію в даних, або якщо середня щільність заповнення сторінки становить менше ніж 100 – @ MaxFrag. Для кластеризованих індексів ця команда реорганізує всю таблицю. Для некластеризованих індексів вона реорганізує індекс на рівні листя.

Зауважимо, що процедура uspDefragTables виразно ігнорує невпорядковані дані (indexID = 0), тому що команда DBCC DBREINDEX з такими даними не працює. Замість DBCC DBREINDEX можна створити дефрагментовані некластеризованих індекси. Для поліпшення організації будуть потрібні інші методи, які часто доводиться застосовувати вручну.

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


Підтримка дефрагментації

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

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

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


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

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

Ваш отзыв

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

*

*