Короткі рекомендації по настройці і оптимізації реплікації транзакцій, Мова запитів SQL, Бази даних, статті

За матеріалами статті Bren Newman, Xavier Schildwachter і Greg Yvkoff


Transactional Replication Performance Tuning and Optimization

Ця стаття присвячена підвищенню продуктивності та ефективності реплікації транзакцій MS SQL Server.

Підвищення продуктивності реплікації

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

· Оптимізуйте при розробці вашу БД і з точки зору реплікації.
· Мінімізуйте розподіляється для установок SQL Server кількість пам’яті.
· Використовуйте виділені диски для розміщення журналів транзакцій.
· Збільште розмір пам’яті для серверів, задіяних в реплікації.
· Використовуйте мультипроцесорні системи.
· Публікуйте тільки необхідні дані.
· Запускайте Shapshot Agent тільки при необхідності і не під час пікового навантаження.
· Не зберігайте файли моментальних знімків і файли журналів транзакцій на одному диску.
· Використовуйте єдиний шлях для зберігання файлів моментальних знімків.
· Розгляньте можливість використання стиснення файлів моментальних знімків.
· Розгляньте можливість використання pull і anonymous підписок.
· Проаналізуйте можливість використання параметра-UseInprocloader для Distribution Agent

Підвищення продуктивності реплікації транзакцій

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

· Використовуйте постійно запущений Distribution Agent замість запуску агента за розкладом.
· Розмістити дистрибутора на окремому сервері.
· Збільште кількість пам’яті на дистриб’юторові.
· Використовуйте збережені процедури при маніпулюванні великими обсягами даних в реплікації.
· Збільшення значення параметра-ReadBatchSize для Log Reader Agent.
· Скоротіть час зберігання історії і час завершення підписки.
· Використовуйте свої збережені процедури для операторів типу insert, delete, update на передплатника.
· Використовуйте горизонтальні фільтри.

Підвищення продуктивності при застосуванні початкового моментального знімку

Застосування початкового знімка може займати досить-таки значна кількість часу, особливо якщо ви переносите великий обсяг даних по мережі або у вас низька якість з’єднання. Наступні приклади показують збільшення продуктивності Shapshot-агента при оптимізації параметра-MaxBCPThread і при використання параметра-UseInprocLoader

Використання параметра-MaxBCPThread

В реплікації транзакцій, параметр-MaxBCPThread може бути застосований як до Shapshot-агенту, так і до Distribution-агенту. Даний параметр вказує кількість паралельно виконуваних операцій bulk-copy. Максимальна кількість потоків і ODBC-з’єднання, які можуть бути виконані одночасно – це і є значення параметра-MaxBCPThread.
Параметр – MaxBCPThread повинен мати значення більше нуля і може бути не лімітований за значенням. За замовчуванням, використовується значення рівне 1. Коли цей параметр використовується у Shapshot-агента, він впливає на час генерації файлу моментального знімка. Якщо цей параметр використовується у Distribution-агента, він впливає на час застосування змін на передплатника.
Так як Shapshot-агент виробляє масове копіювання всіх даних зазначеної публікації, він записує повну публікацію по зазначеному шляху. Отже, “швидка” дискова підсистема дозволить швидко зчитувати і записувати дані на диск, зменшуючи час формування файлу моментального знімка. Це також застосовно для Distribution-агента, який “застосовує” знімок на передплатника. У тестових прикладах, які будуть показані нижче, використовувалися різні диски для зберігання файлів журналу транзакцій і зберігання файлів моментальних знімків.
Виграш у продуктивності при використанні параметра MaxBCPThread також залежить від кількості процесорів сервера. Установка високих значень для MaxBCPThread може сильно завантажити систему, так як система повинні витрачати дуже багато ресурсів для управління процесами. Використання кількості потоків, більшого ніж кількість статтею в небудь публікації (природно треба вибрати будь середнє значення) не надасть вам додаткових переваг. У представленому нижче прикладі, публікація має сем статей, загальний розмір яких 228 мегабайт.

Публікація № 1

Articles Total rows Reserved size (KB) Index size (KB)
CUSTOMER 120,000 19,984 4.032
PAYMENT 120,000 11,280 2,848
ORDERS 374,000 82,208 22,416
NAMES 120,000 7,056 32
CUSTOMER_HISTORY 120,000 23,744 64
PAYMENT_HISTORY 120,000 8,448 64
ORDERS_HISTORY 374,000 75,376 192
TOTAL 1,348,000 228,096 29,648

Генерація початкового знімка Shapshot Agent’ом

Наступні дані були отримані на двухпроцессорной сервері: 450 Mhz Xeon, RAM 256Mb. При встановленому значення параметра-MaxBCPThreads в “7”, час генерації знімка в 1.6 рази відбувається швидше, ніж коли даний параметр має значення рівне “1”. На однопроцесорній машині, використання значення “7” для параметра-MaxBCPThreads прискорило генерацію знімка в 1.27 рази щодо результату для значення рівного “1”. Установка значення “7” на однопроцесорній системі не дає істотних переваг, і тільки більше завантажує процесор.

Процесор MaxBCPThreads = 1 MaxBCPThreads = 3 MaxBCPThreads = 7
2 процесора 122 сек 84 сек 76 сек
1 процесор 122 сек 96 сек 96 сек

Застосування початкового знімка Distribution Agent

Наступні дані показують, що на двухпроцессорной машині, при використання значення “7” для параметра-MaxBCPThreads, застосування початкового знімка відбувається в 1.3 рази швидше, ніж при використанні значення рівне “1”. На однопроцесорній машині, CPU є, скажімо так, “вузьким місцем” і зміна даного значення не дасть приросту в продуктивності. При використання значення “7” для параметра-MaxBCPThreads, застосування знімка відбувається в 1.3 рази швидше, ніж при використанні значення рівного “1”. Використання двухпроцессорной машини, безсумнівно, дає більше приріст продуктивності, ніж використання однопроцесорній машини. Застосування знімка відбувається в 1.75 разів швидше, ніж на однопроцесорній машині.

Процесор MaxBCPThreads = 1 MaxBCPThreads = 3 MaxBCPThreads = 7
2 процесора 120 сек 98 сек 92 сек / td>
1 процесор 148 сек 144 сек 144 сек

Використання параметра-UseInprocLoader

Даний параметр може бути використаний Distribution-агентом під час застосування знімка на передплатника. Коли використовується вказаний параметр, Distribution-агент буде використовувати BULK INSERT операції, що зменшує час, необхідне для застосування первинного знімка на передплатника. Для збільшення продуктивності реплікації в Надалі використовуйте параметр-UseInprocLoader спільно з параметром-MaxBCPThread. Наступний приклад показує публікацію, що включає в себе 10 статей загальним обсягом 46 мегабайт.

Публікація № 2

Articles Total rows Reserved size (KB) Index size (KB)
CUSTOMER 60,000 7,944 1,968
PAYMENT 60,000 5,640 1,424
ORDERS 187,000 29,896 11,144
NAMES 5,765 328 16
PRODUCTS 10,000 904 264
INTERESTED_IN 6,000 1,216 752
STATE 200 64 48
SHIPPERS 51 40 32
SHIP_TYPE 11 40 32
REGION 2 40 32
TOTAL 329,029 46,112 15,712

Коли ви використовуєте тільки параметр-UseInprocLoader, знімок застосовується в 1.4 рази швидше, ніж без використання даного параметра. Коли-UseInprocLoader використовується спільно з параметром-MaxBCPThread до встановленого значенням рівним “5”, знімок застосовується в 2.1 рази швидше.

Час застосування знімка на передплатника

Без параметрів -UseInprocLoader -UseInprocLoader, -MaxBCPThread = 5
36 секунд 25 секунд 17 секунд

В останньому прикладі ви можете спостерігати явне збільшення продуктивності. За замовчуванням, даний параметр не використовується, тому що він негативно впливає на кількість вільної пам’яті на видавця і пропускну здатність мережі. Для початку я б рекомендував використовувати даний параметр для абонентів з невеликим кол-вом публікацій і деякий час поспостерігати за роботою сервера. Я використав цей параметр для абонентів з 2-ма публікаціями по 3 таблиці в одній публікації і з 1 таблицею в другій публікації. При цьому кількість появи інтернет-замовлень за хвилину для нашої системи збільшилася приблизно в 3-3.5 рази. Тобто, якщо раніше час появи замовлення в системі йшло зі швидкістю 1 замовлення в 2 хвилини (причому за так і не з’ясованим причин), то на даний момент це відбувається зі швидкістю 2-3 замовлення в 1 хвилину.
Для установки даного параметра в Enterprise Manager виберіть необхідного Distribution-агента, і у властивостях агента на вкладці Step виберіть крок “Run agent” і додайте параметр-UseInprocLoader.

Використання стиснутих миттєвих знімків

Використання даної опції рекомендується, коли ви використовуєте pull або віддаленого push передплатника. Ця опція також надає додаткові можливості, коли ви використовуєте для розміщення снапшотов FTP. Стискання файлів моментальних знімків в зазначеному для зберігання знімків шляху може зменшувати розмір дискового простору, необхідного для зберігання файлів знімків і, в деяких випадках, може збільшити продуктивність, коли є необхідність передати файл знімка по лінії з низькою якістю зв’язку. Однак, стиснення файлів знімків вимагає додаткової витрати ресурсів системи при створенні і застосуванні файлу моментального знімка. А це, відповідно, може призвести до збільшення часу генерації та застосування знімка. У публікації № 2, склад якої був приведений вище, Shapshot-агент створює 20 файлів, включаючи файли зі схемами даних і файли даних, загальний розмір яких складе 130 мегабайт. Якщо Ви використовуєте стиснення зазначених файлів, Shapshot-агент створить. cab файл розміром близько 65 мегабайт, фактично передплатнику потрібно завантажити вдвічі менший файл. Незважаючи на це, для зберігання стиснутих файлів знімків потрібно більше місця на диску. Стислі знімки можуть займати більше місця на дистриб’юторові (зберігається і стислий знімок, і звичайний знімок), також час генерації файлу знімку збільшується приблизно в 4.5 рази в порівнянні з часом, необхідним для генерації нестисненого файлу знімку, тому що воно витрачається на стиснення файлу знімка.

Використання паралельних процесів для генерації знімку

Коли Ви використовуєте установки за замовчуванням для генерації файлу знімка, SQL Server накладає поділювану блокування на всі таблиці, включені в статтю для реплікації. Це запобігає зміні даних під час створення файлу знімка. При паралельних процесах генерації знімку (доступно тільки для реплікації транзакцій) також відбувається накладення розділяється блокування, але на нетривалий час, тобто всі користувачі можуть спокійно продовжувати працювати з даними.
Коли Ви створюєте нову публікацію, використовуючи реплікацію транзакцій і вказуєте, що всі передплатники будуть працювати під управління SQL Server 7.0 або SQL Server 2000, тільки тоді можливо буде використовувати паралельні процеси для генерації файлів знімка.
Після старту реплікації, Shapshot-агент накладає поділювану блокування на публіціруемие таблиці. Накладення блокування дозволяє запобігти зміні даних, помічених для генерації знімка. В цей час відбувається запис в журнал транзакцій часу початку генерації знімка. Після закінчення генерації знімка, блоковані ресурси звільняються і користувачі можуть модифікувати дані. Тривалість блокування, природно, залежить від кількості даних, необхідних для копіювання.
Після закінчення генерації знімка, відбувається другий запис в журнал, що показує час закінчення генерації знімка. Будь-які транзакції, що змінили дані в публіціруемих таблицях і успішно завершені в зазначений проміжок часу, зчитуються Log Reader і записуються в базу розподілу. Коли знімок застосовується на передплатника, Distribution-агент спочатку застосовує файли знімку (схема і дані). Потім, для узгодження даних кожної завершеної транзакції, йде перевірка, застосовувалася дана транзакція на передплатника. Під час процесу узгодження даних, таблиці на Передплатники блокуються, транзакції очікують звільнення блокування і будуть застосовані на Передплатники тільки після звільнення таблиць.
Незважаючи на наявність паралельних процесів генерації створення знімків, додаткові операції введення-виведення при записи на диск файлів знімків можуть істотно знизити продуктивність. Тому, якщо це можливо, визначте час для створення знімка під час невеликої активності сервера.
Для SQL Server 2000 використання паралельних процесів генерації файлів знімків не рекомендується, якщо публіціруемая таблиця має унікальний індекс без первинного ключа або кластерного. Якщо зміни даних зачіпають кластерний ключ, коли почався паралельний процес генерації знімка, реплікація може обвалитися з помилкою “duplicate key”.

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


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

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

Ваш отзыв

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

*

*