Использование copy-on-write на стендах разработки и тестирования с базами данных SQL Server

В мире кровавого энтерпрайзу есть некоторое количество проектов-мамонтов. Они большие, у них базы данных на SQL Server, в этих базах тысячи и дестяки тысяч объектов, миллионы строк кода T-SQL, огромная вариативность данных, всё хрупкое, неидемпотентное, недетерминированное и фигово документированное. Короче, как писал Roy Osherove в своей The art of unit-testing:

Finally, as a friend once said, a good bottle of vodka never hurts when dealing with legacy code.

В вольном переводе "Да там без поллитры не разберёшься!"

И вот у этих проектов есть беда — большие контуры тестирования и разработки, часто так или иначе модифицированные и уменьшенные копии основного продуктового контура. Да-да-да, тут сразу поналетят умные да в белой одежде и начнут объяснять, что надо писать тестовые наборы данных (а кто спорит?), что тестовый контур должен быть небольшим (а кто спорит?), что код должен быть переносимым между СУБД (спасибо, Кэп!), что всё было бы лучше, если бы проект переписали N лет назад (ха-ха) и прочие "станьте ёжиками" и "пусть едят пирожные". Нет, дорогие мои. Просто представьте, что у вас есть БД SQL Server с 25К объектов (таблиц и ХП) и миллионами строк запросов, и часть объектов создана с SET ANSI NULLS ON, а часть с SET ANSI NULLS OFF. И точно известно, что в части запросов эта разница используется. И БД на дестяки ТиБ. И однодневный простой системы стоит больше, чем квартиры всех разработчиков, которые за последние 20 лет трогали этот код (из которых, кстати, сейчас работает только 7 последних самураев). Одно это может не давать перейти с SQL Server 2008 R2 на что-то более свежее пару лет.

Не надо думать, что на таких проектах работают тупые люди, которые не хотят разрабатывать качественно и быстро. Хотят. Но есть проблемы, одной из которых является размер, нет РАЗМЕР тестовых сред. Полноценный прогон самых необходимых тестов занимает несколько часов. Разработческая среда занимает сотни ГиБ (и безопасники не дают её тащить на машину разработчиков!), а тестовая — больше. Всё что могли разумного с ней для облегчения уже сделали (блобы вычищены, данные урезаны, сжатие на уровне страниц включено, разработчик использует database snapshots и т.д.). Если хотим, чтобы тесты не "моргали", то, конечно, среда должна быть в некотором детерминированном и консистентном состоянии — то есть свежеподнятая. Хотим чаще делать тесты, хотим больше автоматизировать и так далее… Но — РАЗМЕР. Да, кстати, модному менеджеру, который пришёл и говорит: "А давайте разработчиков пересадим в облако", — ему просто сказали, сколько будет стоить хранение сотен ТиБ и их заливка в облаке (и сколько времени будет развёртываться контур). Больше не возвращался. Наверное денег ищет.

Так что же делать? Заворачиваемся в простыню и ползём к кладбищу? НЕТ. Не таков путь самурая. Абзацем выше, я упоминал снимки базы данных (database snapshots), это такая странная недофича SQL Server (уже не помню, в 2005 или в 2008 она появилась). Доступна до версии 2016 была только в Enterprise и Develop редакции (а потом с ограничениями). В общем, при помощи возможностей NTFS, SQL Server умеет делать доступную только для чтения "копию" базы данных. Но копирования в момент создания не происходит, вместо этого создаётся "разреженный" файл и потом происходит копирование страниц при последующей записи в основную БД (Copy on write — CoW — вот почему на картинке корова). Почему "недофича"? Ну просто с этими снэпшотами куча ограничений. И ограничения на редакцию сервера, и производительность основной БД падает, и возврат к моментальному снимку возможен, только если он один, и размеры на диске смотреть неудобно, и с разными другими фичами можно нарваться на сложности. Кому интересно — читайте документацию. Я могу себе представить использование такого в продуктовом контуре, но в каких-то граничных случаях — разве что для каких-то массовых ETL, требующих консистентности или чего-то подобного, но и в этом случае есть идеи получше, например, уровень изоляции snapshot или AlwaysOn реплику использовать. А вот в разработке снимки проявляют себя прекрасно. Разворачиваем среду, создаём снимок, развлекаемся, если что-то не так, то быстро возвращаем "как было". Создание снимка занимает секунды, возвращение к снимку — от силы минуты. Всё, задача решена, можем расходиться? Не спешите. Нам всё еще нужны десятки или сотни сред. И чем больше, тем лучше. А петабайтное хранилище всё еще бизнес не купил разработчикам. И всё еще развёртывание нового контура — десятки минут и часы.

Картинка из документации database snapshots

Но, к счастью, для тех, у кого SQL Serer 2017 или 2019 выход есть. Не всем, но многим подойдёт. SQL Server с версии 2017 может быть развёрнут на linux. В продуктиве SQL Server скорее всего на Linux развёртывать не будут, уж слишком много нюансов. Но мы же разработчики. Нам плевать уже даже на вторую девятку в непрерывности. У нас сервер всё равно не такой быстрый, как в проде (лишь бы планы запросов более-менее воспроизводились), и мы готовы к экспериментам. Мы же разработчики! Но чем же нам поможет Linux? А в нём есть файловые системы (ФС), которые уже неплохо умеют в CoW. В данной статье я опишу XFS и BTRFS.

Итак, уточним задачу. Мы попытаемся создать стенд MS SQL Server, на котором будут располагаться шаблоны достаточно больших тестовых баз данных, так, чтобы можно было быстро (секунды) создавать тестовые базы из шаблона, да еще и так, чтобы созданная тестовая БД занимала ровно столько места, сколько в ней произошло изменений за счёт CoW.

Далее я предполагаю, что у вас есть тестовый стенд, который не жалко, на котором нет даже баз разработки, на данной машине у вас полные права (почти вся работа с ФС и установка ПО требует root-доступа), и в целом вы с linux немного знакомы, команды du/df/lsblk и другие не пугают, многие моменты, которые "известны всем" или "легко гуглятся" я не буду разжёвывать. Команды, требующие root-доступа в тексте статьи используют sudo, чтобы удобнее было выполнять в установке "по умолчанию".

SQL Server на Linux официально развёртывается на трёх дистрибутивах: RHEL, Ubuntu и Suse. Ну и в docker-контейнерах, но в данном случае они не подошли. На Ubuntu всё нижеперечисленное работает, но меня тут попросили развернуть на Oracle Linux (OL), поэтому сейчас стенд развернул на нём. OL — это почти-почти клон RHEL.

Установка нам достаточна самая минимальная, ничего кроме сети, SSH и утилит работы с ФС нам не надо. Ставить можете любым подходящим способом: взять готовую виртуальную машину у админов, развернуть через какой-нибудь cloud-init (ссылка для RHEL) или kickstart или чем там у вас принято разворачивать, а можете воспользоваться ручной установкой в текстовом режиме (не рекомендую "графический" режим — он вам на этой машине никогда больше не понадобится, и ставить на ВМ в маленьком разрешении просто неудобно). Диски можете размечать как привычнее: я использовал раздел ext4 для корня /, раздел SWAP и XFS-раздел для /home — просто потому что привычнее. Для экспериментов мы будем монтировать отдельные разделы, не забудьте оставить для них место, если экспериментируете на реальной железке.
Отмечу ещё, что базовые репозитории OL достаточно скудны, поэтому может потребоваться подключить EPEL (Extra Packages for Enterprise Linux). Мне, например, они потребовались для установки пакетов для распаковки 7z-файлов, ну и htop оттуда заодно поставил. Для данной статьи ставил OL 8.6 со всеми апдейтами.

Базовая установка SQL Server очень простая. Строчка за строчкой из мануала — только внимательнее с версиями SQL Server: по умолчанию уже открывается статья про SQL Server 2022. Но если у вас есть дополнительные требования, например, если вам нужна аутентификация AD — что типично для энтерпрайза, то придётся глубже погрузиться в документацию. Ну и, конечно, не забываем про настройку collation, ключей запуска, флагов трассировки (если вам нужны), настройку tempdb (опять же — если надо)

Теперь про ФС. Официально SQL Server поддерживает ext4 и XFS, причем, насколько я знаю, ext4 не поддерживает CoW. ZFS, извините, не пробовал, но вот беглые эксперименты с BTRFS показывают, что с BTRFS всё тоже получается. BTRFS специфичная ФС, в ней CoW по умолчанию — в том числе из-за этого, даже при правильном её использовании, она медленнее XFS во многих тестах. Может и надёжность какая-то не такая, но в данной задаче нам ни производительности, ни супернадёжности вроде не надо, поэтому опишу плюсы и минусы для обеих ФС.

Итак, считаем, что SQL Server установился по умолчанию в /opt/mssql, базы данных по умолчанию лежат в /var/opt/mssql/data, служба mssql-server.service работает, SSMS по обычному порту 1433 подключается, начинаем развлекаться.

Важное предупреждение: ниже приводятся команды для моего демонстрационного стенда. Они даны только для примера. Они могут у вас не работать. Многие команды могут быть разрушительны для той ОС в которой они запускаются (и вообще почти все с sudo). Не запускайте их, если не понимаете, что произойдёт. Не запускайте их вне стенда, который не жалко потерять или легко восстановить. Если вы работаете по ssh, не забудьте проверить, на каком хосте вы выполняете команды.

XFS

Кратко об XFS. Весьма почтенная добротная ФС, созданная в 1993 году, пришедшая на Linux в 2001 году, долгое время развивалась непойми как. 10 лет назад, кажется, только OpenSuse из более-менее популярных дистрибутивов использовал её по умолчанию (для /home). В 2014-2016 году получила заряд бодрости (коммиты не анализировал, но, скорее всего, связано с тем, что Red Hat начал её предлагать) и вскоре завезли нужную нам в данном упражнении CoW.

Подготовка раздела

Пусть для данной ФС у нас есть сырое блочное устройсто sdb. Разметим его. Лично я предпочитаю parted, потому что в скриптах удобно использовать, но это вкусовщина:

sudo parted --script /dev/sdb -- \
    unit MiB \
    mklabel gpt \
    mkpart primary xfs 1MiB 24GiB \
    name 1 sqlxfs \
    print

Внутри parted мы выполнили команды:

• unit MiB — чтобы вывод был в круглых по основанию 2 мебибайтах
• mklabel gpt — создали таблицу разделов GPT (в 2022 году не вижу смысла использовать таблицу разделов msdos)
• mkpart primary xfs 1MiB 24GiB — создали раздел почти на 24 ГиБ. Впереди оставили 1 МиБ на выравнивание. Обратите внимание, что параметр xfs не создаёт ФС.
• name 1 sqlxfs — обозвали раздел
• print — полюбовались на результат

Создадим ФС:

sudo mkfs.xfs /dev/sdb1 -f -L sqlxfs

Вывод этой команды примерно такой:

meta-data=/dev/sdb1              isize=512    agcount=4, agsize=1572800 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=1        finobt=1, sparse=1, rmapbt=0
         =                       reflink=1
data     =                       bsize=4096   blocks=6291200, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0, ftype=1
log      =internal log           bsize=4096   blocks=25600, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0

Имеет смысл обратить внимание на то, что reflink=1. Размер блока по умолчанию 4 КиБ: теоретически, наверное, лучше поставить 8 КиБ для выравнивания по страницам SQL Server, но в документации сказано "XFS on Linux currently only supports pagesize or smaller blocks" и я забил.

Сначала примонтируем "куда попало" и сделаем структуру:

# предположим, что уже есть директория /tmp/mnt/xfs 
sudo mount /dev/sdb1 /tmp/mnt/xfs/ -o noatime
sudo mkdir /tmp/mnt/xfs/{template,work}
sudo chown -R mssql:mssql /tmp/mnt/xfs
sudo umount /tmp/mnt/xfs

• noatime — опция которая запрещает при каждом обращении к файлам писать время последнего доступа. В данном случае это не обязательно, но вообще это лучше делать по соображениям производительности.
• Создаём 2 папки template и work (вы, конечно же можете создать совершенно свою структуру папок)
• Созданную структуру "отдаём" пользователю и одноименной группе mssql. По умолчанию права к файлам СУБД имеет только сама СУБД. Такой ручной chown при дальнейшем использовании лучше пересмотреть, но для демо — сойдёт.
• Размонтируем раздел

Теперь примонтируем "куда надо" и постоянно. Это уже нужно прописывать в /etc/fstab и, конечно же лучше по UUID, а не по имени /dev/sdb1 диска. UUID cмотрим в sudo blkid. Если выполнить не от рута, то скорее всего вы вообще не увидите /dev/sdb1. То же самое можно посмотреть командой lsblk --output NAME,UUID /dev/sdb. Полученный UUID вставляем в fstab как-то так:

#... какие-то строчки выше ...
UUID=25d2312b-4ccf-4e4d-b680-78cde1d83cd6 /var/opt/mssql/data/xfs   xfs     defaults,noatime   0  0

Не забудьте создать упомянутую /var/opt/mssql/data/xfs и проверить/установить права mssql к ней. Перезагружаемся, проверяем командой mount, что раздел смонтировался куда нужно и права mssql на месте. Можно приступить к развёртыванию баз данных.

Создание файлов БД

В качестве шаблона в статье я использую тестовые базы многоуважаемого Брента Озара — они доступны публично и имеют достаточный объём. Очевидно, что в вашем уютненьком энтерпрайзе вы найдёте свои тестовые БД для развёртывания.

Предполагается что команды ниже выполняются в домашнем каталоге пользователя. Скачиваем архив:

curl -o StackOverflow2010.7z https://downloads.brentozar.com/StackOverflow2010.7z

Распаковываем (вот тут-то и понадобился пакет p7zip из EPEL):

7za x StackOverflow2010.7z

Получили файлики:

• Readme_2010.txt — нам больше не понадобится
• StackOverflow2010.mdf — файл данных
• StackOverflow2010_log.ldf — файл журналов транзакций

Напомню, что база данных MS SQL Server всегда состоит не меньше, чем из двух файлов. Должен быть один или больше файлов данных: первый обычно с расширением mdf, второй и последующие ndf, но это лишь соглашение — это файлы в котором хранятся данные в страницах по 8 КиБ. И должен быть один или больше (редко-редко больше одного) файл с журналами транзакций, обычно с расширением ldf. Оба вида файлов являются обязательными. Журналы транзакций — это не "какие-то логи", а существенный элемент целостности базы данных и часто важный фактор, ограничивающий производительность. В других СУБД этот механизм называют WAL (write-ahead log). Для целостности данных WAL/ldf даже важнее, чем файлы данных, "выкинуть" WAL/ldf нельзя. Все операции с файлами БД нужно рассматривать в контексте целостности и консистентности файлов данных и файлов WAL. Я понимаю, что это сказано уже 100500 раз, и те, кому действительно нужна эта статья, должны (MUST в терминах RFC-2119) знать настолько базовые моменты работы SQL Server, но всё равно, считаю важным напомнить, в частности, потому что мы собираемся напрямую манипулировать файлами.

Дальше мне не очень удобно использовать "родное" имя файлов, поэтому переименую:

# StackOverflow2010.mdf -> sof.mdf
mv StackOverflow2010.mdf sof.mdf
# StackOverflow2010_log.ldf -> sof_log.ldf
mv StackOverflow2010_log.ldf sof_log.ldf

Создаём папку /var/opt/mssql/data/xfs/template/sof, копируем туда файлы и устанавливаем владельцем mssql:

sudo mkdir -p /var/opt/mssql/data/xfs/template/sof
sudo cp {sof.mdf,sof_log.ldf} /var/opt/mssql/data/xfs/template/sof
sudo chown -R mssql:mssql /var/opt/mssql/data/xfs/template/sof

Эти бесконечные chown-ы были связаны с тем, что работаем мы из-под обычного пользователя, копируем через sudo от имени root, а SQL Server работает от имени пользователя mssql и каталог /var/opt/mssql/data/ доступен только ему. Для того чтобы посмотреть права и владельца файлов можно использовать ls -l, и хотя в большинстве современных дистрибутивов для ls -l есть команда-алиас ll, но в OL такой алиас есть у "обычного" пользователя, но нет у root.

sudo ls -la /var/opt/mssql/data/xfs/template/sof

Замечаем, кстати, что копировались файлы неторопливо, со скоростью обычного копирования.

Делаем БД-шаблон и клоны БД

Часть инструкций этого раздела выполняется на стороне SQL Server. Если вы ультра-фанат CLI, и при установке SQL Server установили command-line tools, то, конечно, вы и дальше можете выполнять команды только в SSH-консоли. Ну а мы, изнеженные комфортом, откроем SSMS и будем выполнять команды там.

Подключаем БД:

/* SQL Server */
USE [master]
GO
CREATE DATABASE [template_xfs_sof] ON 
( FILENAME = N'/var/opt/mssql/data/xfs/template/sof/sof.mdf' ),
( FILENAME = N'/var/opt/mssql/data/xfs/template/sof/sof_log.ldf' )
FOR ATTACH
GO

У меня подключение тестовой БД с конвертацией из старого формата хранения заняло 5 секунд.

На нашем демо-стенде на этом подключение шаблона в общем-то закончено. В реальной жизни свежеподключенную базу, принесённую "откуда-то" придётся немного причесать. Может какие-то объекты пересоздать (синонимы или ХП), может какие-то данные изменить, выполнить какие-то миграции до "нужной" версии, переключить режим совместимости, установить модель восстановления. Такие действия, конечно, должны быть автоматизированы и сильно зависят от конкретной вашей БД.

После того как база подготовлена к клонированию, переводим её в оффлайн режим:

/* SQL Server */
USE [master]
GO
ALTER DATABASE [template_xfs_sof] SET OFFLINE
GO

Это важный момент. В Windows файлы открытой БД SQL Server даже с правами администратора не получится скопировать обычными средствами. А чтобы файлы были закрыты, нужно либо перевести базу в offline (её регистрация в master сохраняется), либо выполнить detach, либо завершить работу SQL Server. Перевод базы в offline в этом смысле наименее "инвазивный". Впрочем, с точки зрения файлов БД это всё практически идентичная процедура: завершить соединения, отменить открытые транзакции, сбросить грязные страницы в файл данных. Отдельно отмечу, что многие внешние системы резервного копирования (внешние — то есть не сам SQL Server) для получения снимка файлов в Windows используют особый Volume Shadow copy Service (VSS), который мало того, что позволяет доступ к этим данным, так еще и обеспечивает, чтобы файлы данных и журналов транзакций получались "на один момент", что важно для восстановления. Естественно, это требует поддержки VSS от SQL Server. Кому интересно — подробности в документации.

Но в linux пользователь root не просто "администратор". Он часто может даже то, что нельзя. Например, читать или копировать эксклюзивно захваченный файл, в частности скопировать файлы базы данных во время работы. А вот получить гарантии, что потом эти файлы не мусор — тут сложнее. Во-первых, если копировать файлы по одному, даже если бы это были атомарные операции, а это не всегда так, то файл данных не будет соответствовать журналу транзакций. А это может значить, что у вас есть 2 больших и ненужных файла. Во-вторых, за консистентность файлов отвечает SQL Server и не стоит без необходимости ему мешать. Да, вам может повезти и файлы можно будет подключить снова. А может и не повезти.

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

/* SQL Server */
USE [master]
GO
ALTER DATABASE [template_xfs_sof] SET ONLINE
GO

Ну и собственно самое вкусное. Когда база в режиме OFFLINE вы можете просто "скопировать" файлы моментальным снимком (в рамках одной файловой системы, которая поддерживает такую функцию, конечно):

sudo cp --recursive --reflink=always --preserve=all /var/opt/mssql/data/xfs/template/sof /var/opt/mssql/data/xfs/work/sof1

Кратко по параметрам:

• --recursive — копировать папку целиком рекурсивно (но копирует по одному файлу, насколько я понял вывод strace)
• --reflink=always — тот самый "волшебный" параметр, который заставляет копировать "без копирования", создавая "снимок" исходного файла
• --preserve=all — скопировать права и остальные атрибуты файла (ну наконец-то без дурацкого chown!)
• /var/opt/mssql/data/xfs/template/sof — что копируем
• /var/opt/mssql/data/xfs/work/sof1 — куда копируем

Замечаем, что копировалось всё моментально. Проверяем, что всё прошло, как ожидалось:

sudo ls -la /var/opt/mssql/data/xfs/work/sof1

Любуемся выводом:

drwxr-xr-x. 2 mssql mssql         40 Oct 13 06:18 .
drwxr-xr-x. 3 mssql mssql         18 Oct 13 12:26 ..
-rw-r--r--. 1 mssql mssql 9248833536 Oct 13 11:54 sof.mdf
-rw-r--r--. 1 mssql mssql  268312576 Oct 13 11:54 sof_log.ldf

Прицепляем базу данных:

/* SQL Server */
USE [master]
GO
CREATE DATABASE [work_xfs_sof_1] ON 
( FILENAME = N'/var/opt/mssql/data/xfs/work/sof1/sof.mdf' ),
( FILENAME = N'/var/opt/mssql/data/xfs/work/sof1/sof_log.ldf' )
FOR ATTACH
GO

В моей установке только один пользователь SQL Server (sa), поэтому права внутри базы данным мне не интересны, а в реальности вам скорее всего придётся сделать какой-нибудь sp_changedbowner и/или другие манипуляции для подготовки стенда к работе. Но это вы уж сами разберётесь.

Для большей наглядности эксперимента делаем еще пару копий:

sudo cp --recursive --reflink=always --preserve=all /var/opt/mssql/data/xfs/template/sof /var/opt/mssql/data/xfs/work/sof2
sudo cp --recursive --reflink=always --preserve=all /var/opt/mssql/data/xfs/template/sof /var/opt/mssql/data/xfs/work/sof3

И аналогично подключаем файлы в БД [work_xfs_sof_2] и [work_xfs_sof_3].

Данные отлично читаются:

/* SQL Server */
USE [master];

SELECT * FROM [work_xfs_sof_1].[dbo].[Users] u WHERE u.Id = 4;
SELECT * FROM [work_xfs_sof_2].[dbo].[Users] u WHERE u.Id = 4;
SELECT * FROM [work_xfs_sof_3].[dbo].[Users] u WHERE u.Id = 4;

Проверяем, что данные независимо меняются:

UPDATE [work_xfs_sof_1].[dbo].[Users] SET Reputation = Reputation + 1 WHERE Id = 4; 
UPDATE [work_xfs_sof_2].[dbo].[Users] SET Reputation = Reputation + 2 WHERE Id = 4; 
UPDATE [work_xfs_sof_3].[dbo].[Users] SET Reputation = Reputation + 3 WHERE Id = 4; 

SELECT * FROM [work_xfs_sof_1].[dbo].[Users] u WHERE u.Id = 4;
SELECT * FROM [work_xfs_sof_2].[dbo].[Users] u WHERE u.Id = 4;
SELECT * FROM [work_xfs_sof_3].[dbo].[Users] u WHERE u.Id = 4;

Единственный момент, что простое сложение размеров файлов теперь не работает. То есть у команд du и df теперь дебет с кредитом не сводится. Сразу скажу, что прям-таки хорошего решения я не знаю. В этой статье есть пример, как детально анализировать такие файлы при помощи xfs_bmap, но как-то это хлопотно. В любом случае df показывает свободное место, на это можно ориентироваться.

BTRFS

BTRFS тоже интересная файловая система. Она достаточно молодая, развивается с 2007 года. CoW у неё "в крови" — для нашей задачи это плюс, но в целом это подкладывает некоторое количество граблей в работу с ней. Плюс это еще и весьма многофункциональная ФС, кроме CoW, это ещё и подтома (subvolumes), это компрессия на лету (только не включайте её на файлах БД!), это возможности RAID (которые тоже нам сейчас не нужны), и еще гора всякого. Издалека по фичастости напоминает ZFS, но какое-то всё "самобытное". Короче, свистелки и гуделки на все вкусы — и всё еще разрабатываются новые — поэтому некоторые особенности лучше пока не использовать, пока не утряслись. Где-то это к месту, где-то мешает. Попробуем разобраться хотя бы с моментальными снимками подтомов.

Подтома — ликбез

В принципе BTRFS можно использовать таким же образом, как и XFS — всё тот же cp --reflink будет работать. Но у BTRFS есть очень важная штука — подтома. Они являются ключевым понятием для BTRFS в части монтирования и механизма моментальных снимков.

Подтом — это целостное и независимое (для механизма моментальных снимков) дерево папок и файлов. Конечно, файлы этих деревьев лежат вперемешку на одном и том же блочном устройстве, тут нет никакой магии. Если где-то внутри подтома "находится" другой подтом, то это чем-то похоже на примонтированный отдельно подтом — в том смысле, что моментальный снимок "корневого" подтома будет содержать лишь ссылку на "вложенный". Самое важное, что подтома можно (почти) моментально скопировать и отдельно монтировать. Исторически из распространённых дистрибутивов первым начал использовать BTRFS с подтомами OpenSUSE для механизма аналогичного "точкам восстановления" Windows. Чаще всего это используется примерно так: в ФС создаётся 2 подтома "нижнего уровня" для корня (например /@root) и для "хомяка" (пусть /@home). Также создаётся папка для моментальных снимков корня, и отдельные подтома для информации, которая не должна попадать в снимок (в ОС это обычно где-то в /var, соответственно в нашей ФС это подтома где-то в /@root/var/...). В fstab монтируются, соответственно, /@root в / и /@home в /home. После этого на события установки и удаления пакетов вешаются хуки с вызовом специальной программы (часто это Timeshift или snapper) или тупо самописные скрипты, которые просто делают моментальный снимок при каждом таком событии. Причём этот путь протоптан в той или иной степени, кажется, уже для всех распространённых дистрибутивов. Кому интересна практика для ArchLinux, поройтесь в видео Ermanno Ferrari на его канале (он, правда, взял паузу в выпусках видео). Или в установке "из коробки" OpenSUSE и документации к этому, хм, самобытному дистрибутиву. Этот механизм не заменяет бэкапов, конечно же, но отлично выручает в случаях экспериментов с ОС.

Часто механизм подтомов и снимков BTRFS сравнивают с "похожими" механизмами LVM. Не буду вдаваться сильно в подробности, но "это другое": ФС делает снимки на уровне файлов и небольшим размером блока, подтом обычно не ограничен в размере (только размером всей ФС) и снимки в LVM совершенно по-другому хранят разницу.

Еще раз обращу внимание, что BTRFS использует CoW постоянно по умолчанию, что просто рушит производительность интенсивного изменения больших файлов и увеличивает фрагментацию (образы ВМ, базы данных, сложные большие файлы) и поэтому те файлы и папки, которые так меняются, стоит помечать упомянутым chattr +C /dir/file. Причём атрибут этот надо ставить на директорию до создания файлов или на пустой файл (подробнее на ArchWiki — и вообще на ArchWiki весьма полезное практическое описание). Отмечу, установка этого флага отключает механизм контрольных сумм для этого файла и может изменить атомарность некоторых операций.

Для нас главная прелесть механизма подтомов именно в мгновенном создании "копии" (snapshot) всего подтома целиком. В отличие от cp --reflink это происходит не по одному файлику, а для всего подтома. Для одной БД из двух файлов разница не особо критична, но если подтом содержит много файлов, то разница существенна. Снимки (snapshot) бывают как доступные только для чтения, так и доступные на чтение-запись — нам нужны вторые, конечно. Снимки не рекурсивные — если создаётся снимок подтома, в котором внутри есть подтома, то вложенные подтома не дублируются. "Мгновенность" создания снимков ограничена тем, что перед его созданием ФС требует завершения уже выполненных отложенных операций записи.

Омрачает всё это только то, что Microsoft официально BTRFS не поддерживает — если вы столкнётесь с проблемами, вендор вам не поможет (ох, горе-горе для стенда разработки).

Подготовка раздела

На этот раз наше сырое блочное устройсто sdс. Размечать будем практически также, как и XFS:

sudo parted --script /dev/sdс -- \
    unit MiB \
    mklabel gpt \
    mkpart primary btrfs 1MiB 24GiB \
    name 1 sqlbtrfs \
    print

Ключи те же, значения те же. Создадим ФС:

sudo mkfs.btrfs /dev/sdс1 --force --label sqlbtrfs

Монтируем для создания структуры:

sudo mkdir /tmp/mnt/btrfs
sudo mount /dev/sdс1 -o subvolid=0, noatime /tmp/mnt/btrfs

Ключ -o subvolid=0 нужен как раз, чтобы прицепить "самый корневой" раздел.

Создаём структуру:

sudo btrfs subvolume create /tmp/mnt/btrfs/data
sudo btrfs subvolume create /tmp/mnt/btrfs/data/template
sudo btrfs subvolume create /tmp/mnt/btrfs/data/work

Тут я создал подтом data, затем "как бы в нём" (на самом деле считайте это просто точкой монтирования) подтома template и work. При этом дальше мы будем создавать еще подтома под каждую БД, поэтому могли обойтись и просто директориями, но мне так удобнее.

Назначаем подтом по умолчанию:

sudo btrfs subvolume set-default /tmp/mnt/btrfs/data/

Подтом по умолчанию мы назначили только для того, чтобы не указывать его в fstab.

Размонтируем:

sudo umount /tmp/mnt/btrfs

Создаём папку /var/opt/mssql/data/btrfs и прописываем в fstab по UUID:

# ... в последней строчке что-то такое
UUID=cdd007bb-938d-4d6c-88e6-1cf28e7b84d8 /var/opt/mssql/data/btrfs btrfs   defaults,noatime   0  0

Перегружаемся, проверяем, что в /var/opt/mssql/data/btrfs появились template и work и смотрим, что все подтома на месте:

sudo btrfs subvolume list /var/opt/mssql/data/btrfs/

Вывод примерно такой (ID могут отличаться):

ID 257 gen 30 top level 5 path data
ID 258 gen 470 top level 257 path template
ID 259 gen 358 top level 257 path work

Напоследок меняем владельца:

sudo chown -R mssql:mssql /var/opt/mssql/data/btrfs

Создание файлов БД и подтома для "размножения"

Считаем, что файлы базы для копирования у нас уже есть (см. выше в разделе про XFS). Создадим подтом для этой базы, копируем файлы, назначим владельца:

# создание подтома
sudo btrfs subvolume create /var/opt/mssql/data/btrfs/template/sof
# установка атрибута, запрещающего CoW
sudo chattr +C /var/opt/mssql/data/btrfs/template/sof
# копируем файлы
sudo cp {sof.mdf,sof_log.ldf} /var/opt/mssql/data/btrfs/template/sof
# отдаём всё mssql
sudo chown -R mssql:mssql /var/opt/mssql/data/btrfs/template/sof

Несмотря на то, что мы поставили атрибут, запрещающий CoW, механизм моментальных снимков будет нормально работать. Просто если данные файла есть лишь в одном подтоме, то CoW не будет использоваться. Посмотреть состояние атрибута С можно командой lsattr:

sudo lsattr /var/opt/mssql/data/btrfs/template/sof

Базу подключаем точно также, как в случае XFS:

/* SQL Server */
USE [master]
GO
CREATE DATABASE [template_btrfs_sof] ON 
( FILENAME = N'/var/opt/mssql/data/btrfs/template/sof/sof.mdf' ),
( FILENAME = N'/var/opt/mssql/data/btrfs/template/sof/sof_log.ldf' )
FOR ATTACH
GO

После подключения, как-то готовим БД и затем отпускам файлы:

/* SQL Server */
USE [master]
GO
ALTER DATABASE [template_btrfs_sof] SET OFFLINE
GO

Клонирование и использование клонов

Создать моментальный снимок подтома просто:

sudo btrfs subvolume snapshot /var/opt/mssql/data/btrfs/template/sof /var/opt/mssql/data/btrfs/work/sof1 

Права mssql выдались сами.

Аналогично XFS создадим еще 2 снимка:

sudo btrfs subvolume snapshot /var/opt/mssql/data/btrfs/template/sof /var/opt/mssql/data/btrfs/work/sof2 
sudo btrfs subvolume snapshot /var/opt/mssql/data/btrfs/template/sof /var/opt/mssql/data/btrfs/work/sof3 

Подключаем первую БД:

/* SQL Server */
USE [master]
GO
CREATE DATABASE [work_btrfs_sof_1] ON 
( FILENAME = N'/var/opt/mssql/data/btrfs/work/sof1/sof.mdf' ),
( FILENAME = N'/var/opt/mssql/data/btrfs/work/sof1/sof_log.ldf' )
FOR ATTACH
GO

Вторую и третью аналогично в work_btrfs_sof_2 work_btrfs_sof_3.
Проверяем чтение данных:

/* SQL Server */
USE [master];

SELECT * FROM [work_btrfs_sof_1].[dbo].[Users] u WHERE u.Id = 4;
SELECT * FROM [work_btrfs_sof_2].[dbo].[Users] u WHERE u.Id = 4;
SELECT * FROM [work_btrfs_sof_3].[dbo].[Users] u WHERE u.Id = 4;

И модификацию:

UPDATE [work_btrfs_sof_1].[dbo].[Users] SET Reputation = Reputation + 1 WHERE Id = 4; 
UPDATE [work_btrfs_sof_2].[dbo].[Users] SET Reputation = Reputation + 2 WHERE Id = 4; 
UPDATE [work_btrfs_sof_3].[dbo].[Users] SET Reputation = Reputation + 3 WHERE Id = 4; 

SELECT * FROM [work_btrfs_sof_1].[dbo].[Users] u WHERE u.Id = 4;
SELECT * FROM [work_btrfs_sof_2].[dbo].[Users] u WHERE u.Id = 4;
SELECT * FROM [work_btrfs_sof_3].[dbo].[Users] u WHERE u.Id = 4;

Но теперь, в отличие от XFS, мы можем посмотреть использование дисков:

sudo btrfs filesystem du /var/opt/mssql/data/btrfs

Несколько нагляднее, чем в XFS, хотя тоже "есть пространство для улучшения":

     Total   Exclusive  Set shared  Filename
   8.61GiB       0.00B           -  /var/opt/mssql/data/btrfs/template/sof/sof.mdf
 255.88MiB       0.00B           -  /var/opt/mssql/data/btrfs/template/sof/sof_log.ldf
   8.86GiB       0.00B           -  /var/opt/mssql/data/btrfs/template/sof
   8.86GiB       0.00B           -  /var/opt/mssql/data/btrfs/template
   8.61GiB    56.00KiB           -  /var/opt/mssql/data/btrfs/work/sof1/sof.mdf
 255.88MiB    67.76MiB           -  /var/opt/mssql/data/btrfs/work/sof1/sof_log.ldf
   8.86GiB    67.82MiB           -  /var/opt/mssql/data/btrfs/work/sof1
   8.61GiB    56.00KiB           -  /var/opt/mssql/data/btrfs/work/sof2/sof.mdf
 255.88MiB    67.76MiB           -  /var/opt/mssql/data/btrfs/work/sof2/sof_log.ldf
   8.86GiB    67.82MiB           -  /var/opt/mssql/data/btrfs/work/sof2
   8.61GiB    56.00KiB           -  /var/opt/mssql/data/btrfs/work/sof3/sof.mdf
 255.88MiB    67.76MiB           -  /var/opt/mssql/data/btrfs/work/sof3/sof_log.ldf
   8.86GiB    67.82MiB           -  /var/opt/mssql/data/btrfs/work/sof3
  26.59GiB   203.45MiB           -  /var/opt/mssql/data/btrfs/work
  35.45GiB   203.45MiB     8.86GiB  /var/opt/mssql/data/btrfs

Также можно посмотреть на btrfs filesystem df, btrfs filesystem usage и вообще почитать документацию btrfs.

А ещё можно попробовать делать снимки прямо во время работы! Теперь у нас нет (по крайней мере не должно быть) неатомарности копирования файла данных (mdf) и журнала транзакций (ldf), поэтому в большинстве случаев подключение файлов "горячей" копии должно пройти успешно. Предположим, на базе [work_btrfs_sof_1] идёт у нас многочасовой многоэтапный тест — хочется, чтобы к некоторым состояниям можно было вернуться впоследствии. Такой тест я имитировал кучей больших и маленьких UPDATE. Параллельно выполнению запускал команды в консоли:

 sudo btrfs subvolume snapshot /var/opt/mssql/data/btrfs/work/sof1 /var/opt/mssql/data/btrfs/work/sof1_x

Вместо последнего x — номера снепшотов 1..6. При подключении, как и ожидается, происходит откат незавершённых транзакций, поэтому если в тесте происходит огромное обновление, а снимок сделан прямо перед коммитом, то подключение будет о-о-очень долгим. У меня на подключенных БД успешно прошёл dbcc checkdb, база восстановилась работоспособной. В конце концов, мы не на продуктиве, а даже 8 работоспособных снепшотов из 10 могут помочь разобрать сложный тест.

Кстати, маленький хинт. Для анализа разницы в данных двух таблиц удобно использовать EXCEPT:

/* SQL Server */
USE [master];

/* В одну сторону */
SELECT * FROM [work_btrfs_sof_1].[dbo].[Users] u
EXCEPT
SELECT * FROM [snp_btrfs_sof_1_5].[dbo].[Users] u;

/* В другую сторону */
SELECT * FROM [snp_btrfs_sof_1_5].[dbo].[Users] u
EXCEPT
SELECT * FROM [work_btrfs_sof_1].[dbo].[Users] u;

Часто такой вариант удобнее соединения выборок (join), потому что выборка может быть не уникальной или ключ уникальности из большого числа столбцов.

После использования подтома можно удалять:

 sudo btrfs subvolume delete /var/opt/mssql/data/btrfs/work/sof1_x

Выводы и сравнение решений

Всё, что сделано — это лишь демонстрация идеи, с которой можно экспериментировать и начать писать скрипты автоматизации развёртывания тестовых сред (в вашем любимом CI/CD тулинге, например). Как говорится в некоторых учебниках и лекциях "это предлагается читателю в качестве несложного самостоятельного упражнения". Ну и не забывайте, что CoW всё-таки заметно влияет на производительность.

Что получается по разнице ФС:

Тесты производительности я не делал, потому что мне лень они сильно зависят от конкретного сценария использования. Я бы ожидал падения в 2-2,5 раза на операциях записи в файлы, но надо помнить, что даже в очень активно используемых на запись реляционных OLTP системах количество операций записи составляет 5-10% от общего количества.

Помимо прямой выгоды в дисковом пространстве, предложенная система решает еще несколько важнейших задач:

1. Сильно сокращает время подготовки и объём тестового стенда. Настолько, что под каждый тест можно делать большой стенд
2. Позволяет неплохо автоматизировать тесты и CI/CD
3. Позволяет делать много стендов и точек восстановления, что может помочь разобрать сложный кейс

Мысли вслух, предупреждения и предостережения

Небольшие заметки на полях:

• Никто не мешает применить ту же технику к другим видам тестовых сред или к другим задачам. В частности, на просторах Интернета я встречал эксперименты с PostgreSQL. Честно говоря, даже не понимаю, почему в pg клонирование таблиц и БД в сам движок не встроено.
• Эта техника может быть очень полезна коллективам разработчиков корпоративных систем на платформе 1С: Предприятие. Собственно, этот пример сделан специально для нашей команды 1Сников с большим количеством разработчиков и контуров.
• "Все трюки выполнены профессионалами дилетантом не пытайтесь повторить это самостоятельно". Но не забывайте о технике безопасности — не тащите эту поделуху в ответственный продуктив. (Про дилетанта я соврал :) )
• Все события и обстоятельства почти вымышлены и любые совпадения почти случайны.
• Спасибо тем, кто помогал в вычитывании и написании статьи.