По материалам статьи Bren Newman, Xavier Schildwachter и Greg Yvkoff Transactional Replication Performance Tuning and Optimization

Эта статья посвящена повышению производительности и эффективности репликации транзакций MS SQL Server.

СОДЕРЖАНИЕ

1.	Повышение производительности репликации
2.	Повышение производительности репликации транзакций
3.	Повышение производительности при применении первоначального моментального снимка
3.1	Использование параметра -MaxBCPThread
3.2	Генерация начального снимка Shapshot Agent'ом
3.3	Применение начального снимка Distribution Agent
3.4	Использование параметра -UseInprocLoader
4.	Использование сжатых моментальных снимков
5.	Использование параллельных процессов для генерации снимка

Повышение производительности репликации

Вы можете увеличить производительность всех типов репликации, если будете следовать следующим рекомендациям:

· Оптимизируйте при разработке вашу БД и с точки зрения репликации.
· Минимизируйте распределяемое для установок 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".

[Содержание]