Обсудить
бизнес-задачи
бизнес-задачи
Повышение отказоустойчивости КХД за счет оптимизации бэкапа в SQL Server
блог о bi, №1 в рунете
Резервное копирование (бэкап, backup) – это процесс создания дублирующей копии данных на отдельном носителе (локальном или удаленном) для предотвращения потери исходных данных и обеспечения возможности их быстрого восстановления в случае непредвиденных ситуаций, таких как повреждение или утрата оригинальных данных.
Методы резервного копирования
1. Полный бэкап (Full)
Полное резервное копирование делает копию всей базы данных, включая все объекты и данные системных таблиц.
Восстановление базы по созданной резервной копии происходит быстро, так как бэкап не зависит от других бэкапов.
Однако, создание такой полной копии занимает много времени и много дискового пространства. Для больших баз данных может потребоваться, кроме полных резервных копий, создавать также и дифференциальные резервные копии баз данных.
Восстановление базы по созданной резервной копии происходит быстро, так как бэкап не зависит от других бэкапов.
Однако, создание такой полной копии занимает много времени и много дискового пространства. Для больших баз данных может потребоваться, кроме полных резервных копий, создавать также и дифференциальные резервные копии баз данных.
2. Дифференциальный бэкап (Differential)
Дифференциальное или разностное резервное копирование подразумевает создание копии только тех данных, которые изменились с момента последней полной резервной копии. Этот тип бэкапа используется в сочетании с полной резервной копией, так как для восстановления из дифференциальной копии необходима полная резервная копия. Из-за этого процесс восстановления происходит медленнее, чем при использовании только полной резервной копии. Однако дифференциальное копирование более оптимизировано с точки зрения использования памяти и времени.
При выборе метода резервного копирования необходимо учитывать объем изменений в данных. Если ежедневный оборот данных довольно высок, то дифференциальные копии могут оказаться неудобными, так как они будут занимать значительный объем памяти.
При выборе метода резервного копирования необходимо учитывать объем изменений в данных. Если ежедневный оборот данных довольно высок, то дифференциальные копии могут оказаться неудобными, так как они будут занимать значительный объем памяти.
Создание бэкапа
1. Через среду SQL Server Management Studio
Для создания бэкапа необходимо выбрать в обозревателе баз данных нужную базу, нажать на правую кнопку мыши, выбрать Tasks -> Back Up. Появится следующее окно с настройками для бэкапа:
2
1
3
Рисунок 1. Настройки для резервного копирования базы данных General,
где 1 – наименование базы, 2 – способ бэкапирования, 3 – путь к бэкапу
где 1 – наименование базы, 2 – способ бэкапирования, 3 – путь к бэкапу
1
2
Рисунок 2. Настройки для резервного копирования базы данных вкладка Backup Options,
где 1 – наименование бэкапа, 2 – сжатие (да, нет, по умолчанию)
где 1 – наименование бэкапа, 2 – сжатие (да, нет, по умолчанию)
2. Через скрипт
Такие же настройки для бэкпирования можно указать через скрипт:
BACKUP DATABASE [test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\TestDB.bak' WITH NAME = N'TestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;Дифференциальная резервная копия:
BACKUP DATABASE [test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\TestDB.bak' WITH DIFFERENTIAL;Параметры перечисляются запятой.
Параметры для оптимизации бэкапа
Следующие параметры настройки резервного копирования, которые влияют на оптимизацию бэкапирования:
1. BLOCKSIZE = {blocksize | @ blocksize_variable}
Параметр определяет размер физического блока данных, используемого при создании резервной копии (в байтах). Поддерживаются следующие размеры блока: 512, 1024, 2048, 4096, 8192, 16384, 32768 и 65536 байт (64 КБ). По умолчанию, размер блока равен 512 байт, но рекомендуется выбирать от 4 КБ, так как маленький размер блока влечет за собой множество блоков данных, из-за чего замедляется процесс создания бэкапа и восстановления базы. Слишком большой размер блока также может привести к снижению производительности, так как в случае ошибки при создании резервной копии придется повторять бэкапирование всего блока.
2. BUFFERCOUNT = {buffercount | @ buffercount_variable}
Параметр задает общее количество буферов ввода-вывода, которые будут использоваться во время операции резервного копирования. Можно указать любое целое положительное значение. Однако, слишком большое число буферов может привести к ошибке нехватки оперативной памяти. Это связано с тем, что чрезмерное использование виртуального адресного пространства процессом Sqlservr.exe может занимать больше доступной памяти.
Параметр BUFFERCOUNT не имеет конкретных ограничений на допустимые значения. Тем не менее, разработчики SQL Server предлагают использовать следующую формулу для расчета рекомендуемого значения:
(NUMBEROFBACKUPDEVICES * GETSUGGESTEDIODEPTH) + NUMBEROFBACKUPDEVICES + (2 * DATABASEDEVICECOUNT)
Где:
- GetSuggestedIoDepth - показатель производительности дисков. Для обычных жестких дисков ПК он по умолчанию равен 3, а для виртуальных дисков (VDI) варьируется от 1 до 4.
- NumberofBackupDevices - количество устройств, используемых для резервного копирования. SQL Server поддерживает до 64 устройств.
- DatabaseDeviceCount - количество файлов базы данных и файлов журнала транзакций. Для его нахождения можно воспользоваться следующим скриптом:
Параметр BUFFERCOUNT не имеет конкретных ограничений на допустимые значения. Тем не менее, разработчики SQL Server предлагают использовать следующую формулу для расчета рекомендуемого значения:
(NUMBEROFBACKUPDEVICES * GETSUGGESTEDIODEPTH) + NUMBEROFBACKUPDEVICES + (2 * DATABASEDEVICECOUNT)
Где:
- GetSuggestedIoDepth - показатель производительности дисков. Для обычных жестких дисков ПК он по умолчанию равен 3, а для виртуальных дисков (VDI) варьируется от 1 до 4.
- NumberofBackupDevices - количество устройств, используемых для резервного копирования. SQL Server поддерживает до 64 устройств.
- DatabaseDeviceCount - количество файлов базы данных и файлов журнала транзакций. Для его нахождения можно воспользоваться следующим скриптом:
SELECT COUNT(*) AS DatabaseDeviceCount
FROM sys.master_files
WHERE database_id = DB_ID();3. MAXTRANSFERSIZE = {maxtransfersize | @ maxtransfersize_variable}
Параметр указывает наибольший объем пакета данных в байтах для обмена данными между SQL Server и носителем резервного копирования. Поддерживаются значения, кратные 65 536 байтам (64 КБ), вплоть до 4 194 304 байт (4 МБ).
Общий объем пространства, используемого буферами, определяется по следующей формуле:
BUFFERCOUNT * MAXTRANSFERSIZE
Общий объем пространства, используемого буферами, определяется по следующей формуле:
BUFFERCOUNT * MAXTRANSFERSIZE
Демонстрация влияния параметров буферизации на производительность резервного копирования
Для демонстрации влияния зависимости времени выполнения резервного копирования от параметров буферизации было проведено тестирование на примере базы данных объемом 18 ГБ.
Были протестированы различные вариации значений параметров для выявления наиболее оптимальных:
На рисунке 3 представлена зависимость создания бэкапа от параметра BLOCKSIZE. Из графика видно, что наиболее оптимальными значениями параметра BLOCKSIZE оказались значения 8192, 16384, 65536 байт.
Были протестированы различные вариации значений параметров для выявления наиболее оптимальных:
- Размер блока (BLOCKSIZE) от 4096 до 65 536 байт.
- Количество буферов (BUFFERCOUNT) от 7 (рассчитанное для минимального подходящего значения тестируемой базы) до 224 (рассчитанное для максимального подходящего значения тестируемой базы) (см. формулу выше).
- Максимальный размер пакета данных (MAXTRANSFERSIZE) от 524288 (512 КБ) до 4194304 (4 МБ).
На рисунке 3 представлена зависимость создания бэкапа от параметра BLOCKSIZE. Из графика видно, что наиболее оптимальными значениями параметра BLOCKSIZE оказались значения 8192, 16384, 65536 байт.
Рисунок 3. Зависимость времени создания бэкапа от параметра BLOCKSIZE
На рисунке 4 представлена зависимость создания бэкапа от параметра BUFFERCOUNT. Из графика видно, что наиболее оптимальными значениями параметра BUFFERCOUNT оказались значения 28, 56.
Рисунок 4. Зависимость времени создания бэкапа от параметра BUFFERCOUNT
На рисунке 5 представлена зависимость создания бэкапа от параметра MAXTRANSFERSIZE. Из графика видно, что наиболее оптимальным значением параметра MAXTRANSFERSIZE оказалось 512 КБ.
Рисунок 5. Зависимость времени создания бэкапа от параметра MAXTRANSFERSIZE
До изменения параметров время создания бэкапа с параметрами по умолчанию составляло 59 секунд. Мы смогли найти такую комбинацию параметров бэкапирования (таблица 1), при котором время сократилось на 23 секунд (на 40 %).
Таблица 1. Подобранная комбинация параметров
Время создания бэкапа
BLOCKSIZE
BUFFERCOUNT
MAXTRANSFERSIZE
36
4096
7
524288
В таблице 2 приведен реальный случай оптимизации бэкапирования путем изменнеия параметров, база объемом 78 ГБ.
Таблица 2. Временные замеры операции бэкапирования
Тип параметров буферизации бэкапирования
Время выполнение в сек. (1 запуск)
Время выполнение в сек. (2 запуск)
Время выполнение в сек. (3 запуск)
Настроенные параметры:
- BLOCKSIZE = 16384,
- BUFFERCOUNT = 224,
- MAXTRANSFERSIZE = 4194304
51
53
50
Параметры по умолчанию
130
127
132
Вывод: при правильной настройке параметров буферизации время бэкапирования уменьшается до 2.5 раз.