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

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

Одного разу кілька місяців тому я був розбуджений наполегливим гудінням свого 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 (http://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

Постійна таблиця має поле з ім'ям LastScanTime, що служить для відстеження переглядів таблиці. Умова IF в лістингу 1 існує для перевірки такого поля в кожній таблиці і ігнорує виконання оператора DBCC, якщо в цей день таблиця була просканувати. Така перевірка дозволяє процедурі uspBuildFraglist багаторазово запускатися без дублювання роботи, виконаної попередніми переглядами.

Друга властивість процедури uspBuildFraglist – це затримка після кожного сканування. З тексту коду у фрагменті B видно, що перевіряється таблиця sysprocesses, щоб визначити, блокує чи процес перегляду які-небудь процеси. Якщо так, процедура чекає 30 секунд. Якщо ні, процедура очікує 5 секунд, а потім запускає наступне сканування. Можливість робити паузу між переглядами – елементарне перевага сканування по кожній таблиці окремо замість сканування відразу всієї бази даних. Це простий спосіб мінімізувати можливі проблеми з потенційними блокуваннями, які можуть бути викликані DBCC.

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


Інтерпретація результатів роботи DBCC

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

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

Найкращий індикатор фрагментирования індексів доступний при оцінці результатів сканування логічної фрагментації за допомогою команди DBCC SHOWCONTIG. Ці результати показують відсоток часу, коли сторінки індексу не слідують один за одним у порядку, заданому індексом. Так як більшість даних в індексі зберігається на рівні листя, будь-яке значення більше 0% означає великий відсоток операцій введення / виводу на диску і менше ймовірність, що компонент введення / виводу в SQL Server може читати окремий блок додаткових даних замість восьми індивідуальних сторінок.

Інша оцінка за допомогою DBCCSHOWCONTIG служить для перевірки середньої щільності наповнення сторінки, яка показує, наскільки середня сторінка наповнена даними. Хоча, можливо, в таблиці немає ніякої фрагментації сторінок, тому що всі сторінки дійсно неперервні, а сама сторінка може мати порожні місця через стирання, модернізації та розщеплення сторінок. Оскільки SQL Server завжди читає мінімум даних з повною сторінки, сканування таблиці або сканування листя індексу буде завжди вимагати читання більшого числа сторінок на кількість використовуваних байтів, поділених за 8192, якщо сторінка містить порожні проміжки. Читання цих додаткових сторінок призводить до високої активності диска, і, оскільки таблиця стає великою, має місце зростаючий вплив на продуктивність читання, особливо при скануванні таблиці.

Проблеми можуть бути і з розбивкою сторінок. Простий порада: для того, щоб зменшити розбиття сторінки, можна використати фактор заповнення, який залишає місце для нових даних. Розбиття сторінок призводить до істотного, хоча і короткочасному зменшення продуктивності, у міру того як рядки переміщаються на нову сторінку, а проте проблема в тому, що SQL Server доводиться читати більше кількість сторінок, оскільки сторінки заповнені не до кінця. Для таблиці з 1 млн. сторінок даних і 20-відсотковим коефіцієнтом заповнення результатом буде 1,2 млн. сторінок, у которцих буде додатково всього 16 Мбайт даних. Ключове питання – чи буде короткостроковий ефект від розбиття сторінки більше, ніж довготривалий ефект від читання більшої кількості сторінок? Якщо виходить, що підтримка розриву в сторінках, дає ефект коефіцієнта заповнення в 50%, це говорить про те, що підтримка високої середньої щільності сторінки так само важливо, як наявність низькою фрагментації.


Фрагментація файлів бази даних

Якщо монітор продуктивності показує надмірна кількість операцій введення / виведення, про що це говорить? І якщо FileMon показує принаймні 65,536 байт при виконанні вводу / виводу? Це означає, що файл самої бази даних фрагментований. Монітор продуктивності виводить у звіті фізичні метрики диску, не дивлячись на те що FileMon описує введення / висновок на рівні файлової системи. Щоб завантажити сторінку з даними, операційна система Windows повинна знайти зсув у файлі, щоб встановити фізичне розташування байтів на диску. Система NTFS намагається зменшити фрагментацію диска, визначаючи місце для файлу в самому великому шматку вільного простору на диску, без спроби оптимізувати таке розміщення. Якщо база даних розширювалася і стискалася регулярно (тобто використовувалася команда DBCC SHRINKDB), тоді можливо, що деякі з файлів бази даних розподілені по диску, і малоймовірно, що ці частини впорядковані, а менеджер зберігання впорядкує сторінки за номером.

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

Рішення для нових баз даних полягає в наданні з урахуванням зростання достатнього місця для створення бази даних. Якщо диск нещодавно відформатований, NTFS надасть все місце в одній безперервній області диска. Після цього можна використовувати команди DBCC і перебудовувати індекси для мінімізації фрагментації.

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


Рішення завдання

Ключові проблеми, які я виявив: велика таблиця і фрагментований файл бази даних, а також неефективна щільність заповнення сторінок. Я розглядав усі три проблеми, які мені необхідно усунути, разом, щоб вирішити проблему продуктивності бази даних. Перша: найбільші, найбільш наповнені, що використовуються для вирішення таблиці потребують індексі із зовнішнім ключем. Ця конкретна проблема справді не була викликана фрагментацією, а була пов'язана зі скануванням таблиці розміром в 2,5 Гбайт із зовнішнім ключем під час оновлення таблиці по первинному ключовому полю. Таке сканування було причиною великої кількості операцій дискового введення / виводу, так що не можна було встановити, чи існували інші проблеми з диском. Додавання індексу радикально зменшило час модернізації і час вводу / виводу операцій з диском приблизно до 80% при операції модернізації. Щоб вирішити залишилися дві проблеми, я написав збережену процедуру uspDefragTables, яку нижче ми розглянемо більш докладно.

Друга проблема, яку я виявив, полягала в тому, що таблиця була невпорядкований, тобто її дані були «складені» в тому порядку, в якому вони були вставлені. Це саме по собі не страшно; сторінки таблиці були лише трохи фрагментовані. З того моменту як дані були отримані, приблизно від 75 до 80% даних загальної кількості вставлених у всі таблиці бази даних зберігалися близько один до одного. Пам'ятайте тим не менш, що логічна дефрагментація даних у DBCC SHOWCONTIG незастосовна до неупорядкованим даними; тому такі дані з 0% фрагментації все ще можуть бути негативним фактором при формуванні запитів до даних програми.

Фактично це була така організація даних, яка при пошуку даних викликала ефект, подібний фрагментації. Найбільша таблиця містить деталізовану інформацію за різними даними. А структура невпорядкованих даних в таблиці змушує розміщувати численні детальні дані на тій же сторінці. Результуючий ефект для відновлення всіх даних для одного запиту (яких може бути кілька десятків за кілька хвилин) змушують SQL Server ігнорувати таблицю і читати кілька рядків з кожної десятої сторінки на запит. Мало того, що це закінчується великою кількістю операцій введення / виводу, це майже гарантує, що сторінки, що містять додаткові рядки, не будуть заповнені безперервно. Крім того, оскільки таблиця має тисячі операцій вставки в хвилину, будь-які додаткові дані, ймовірно, містять лише кілька рядків для будь-якого даного запиту.

Третя проблема, яка робить організацію даних менш ефективною, – це коли команда DBCC SHOWCONTIG показує, що щільність заповнення сторінки менш 50%. Мається на увазі, що для зберігання даних таблиця використовувала сторінки більше двох разів. Занадто велика кількість напівпорожніх сторінок, розкиданих по файлу бази даних, означає в реальності погану продуктивність.

Процедура, що зберігається uspDefragTables з лістингу 2 вирішує проблеми з щільністю заповнення сторінки і організацією даних. Процедура виконує ітерацію по всіх рядках таблиці, яка є результатом виведення команди DBCC SHOWCONTIG з збереженої процедури uspBuild-Fraglist, і вона шукає таблиці, які перевищують рівень фрагментації, зазначений у параметрі @ MaxFrag, як показано у фрагменті коду А лістингу 2.

Лістинг 2. Процедура, що зберігається uspDefragTables

Процедура 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>

*

*