[Из песочницы] (5-2) Способа перенести большую SQL таблицу
Введение
Всем привет! Это моя первая статья и пишу я ее от лица младшего инженера-разработчика на языке C#. Так что здесь не будет каких-то подробных сведений о SQL, лишь практические сведения и размышления по решению довольно не очевидной задачи, с которой мне пришлось столкнуться, для таких же новичков, как и я сам.
Сначала я опишу формулировку своей задачи в качестве примера, в котором возникает реальная необходимость переноса большой таблицы.
Итак, представим, что у вас есть web-сервис и SQL (MS-SQL) база данных с таблицей html-писем, которые ваш сервис рассылает пользователям. Письма хранятся за некоторое количество лет и удалить их нельзя, так как они нужны для сбора статистики и аналитики. Однако, с каждым годом количество писем растет, база разрастается, а места на SQL-сервере все меньше (в нашем случае еще одним фактором было восстановление базы на тестовую площадку, т.к. его время пропорционально росло) и с этим нужно что-то делать. Благо, в нашем случае есть свободный сервер с кучей свободного места (в реальности его может не быть и конечно это временное решение, но это выходит за рамки статьи). Так возникла задача по переносу большой таблицы (и говоря «большой», я имею в виду реально большую таблицу, все что я видел, пока искал похожие решения, было в районе 60–100Гб, в нашем случае таблица весила более 300 Гб).
Мы рассмотрим несколько способов решения этой задачи, но не все они будут относится к переносу вида сервер — сервер. Иногда может возникнуть необходимость переноса таблицы между базами в рамках одного сервера. Также, некоторые способы чисто теоретические, я не проверял их все на практике, однако они наверняка должны сработать.
Способ -1. Data
Как бы очевидно не звучало, но вы должны знать, какие данные собираетесь переносить. Чаще всего, данные хранятся не самым оптимальным способом, также может храниться лишняя информация. Вполне вероятно, что в вашем конкретном случае можно обойтись без переноса всех данных.
Во-первых, вероятно вам может помочь удаление какого-либо столбца, однако это блокирующая операция, а не всегда есть возможность остановить web-сервис. И на Хабре есть статья, в которой рассказывается, как это можно осуществить.
Во-вторых, не забывайте про нормализацию. Возможно какие-то данные можно перенести в словарь (в случае с письмами, можно было хранить не тела писем, а шаблоны с теми данными, которые туда вставляются), а в большой таблице хранить лишь id эти элементов, это может освободить вам кучу места.
Способ 0. SELECT INTO
Шутка =) Так вы разве что положите себе базу. Однако если речь идет о небольших размерах таблицы (тогда что вы тут делаете) можно попытаться перенести базу с помощью этой инструкции. Также, если вы располагаете тестовой базой, можно провести эксперимент, чтобы оценить общее время переноса данным методом «в лоб».
Способ 1. Backup
Самый «каноничный» способ, именно это стало решением моей задачи. Делаем бэкап базы, содержащей нашу таблицу, и восстанавливаем его на другом сервере и очищаем от всего лишнего. Далее, если есть возможность остановить web-сервис, можно его передеплоить, настроив запись в перенесенную таблицу, а старую удаляем* (тут скорее всего может возникнуть момент того, что необходимо будет писать к ней запросы с джойнами, для этого гуглите как линковать sql-серверы). Если нет такой возможности, фиксируем id последнего письма (для синхронизации), затем надо будет удалить* все перенесенные письма (писать продолжим в старую таблицу).
* Удаление отдельная тема для разговора, может показаться, что оно выполняется гораздо быстрее, чем перенос, но это не так и в общем случае советую удалять порционно.
Способ 2. MS-SQL Management Studio
Если у вас есть данная студия, можете попробовать воспользоваться встроенным инструментом для экспорта и импорта данных. Лично я прочитал на stack overflow, что это штука зависла на таблице в 60 гигов и рисковать не стал.
Способ 3. Partition
Улучшенный метод «в лоб». Идея в том, чтобы переносить данные обычным способом с таймером между итерациями. Вы разбиваете все строки на порции (например, по 100к) переносите порцию (и тут же ее можно удалять, однако не уверен, насколько это безопасно), затем засыпаете и так до победного конца. Переносить лучше с конца, чтобы не пришлось синхронизировать данные по окончанию. Способ, очевидно, очень медленный, однако таким образом вы перенесете все без остановки web-сервиса. Скорее всего это будет удобнее реализовать не SQL-скриптом, а с помощью какого-нибудь ORM.
Итог
Процесс переноса большого объема данных всегда занимает определенное количество времени, и вы должны быть к этому готовы. Не существует магического способа, который моментально бы решил вашу проблему. В каждом случае нужно отталкиваться от ваших объемов и ограничений. Если вам не подойдет ни один из способов, подумайте, нельзя ли использовать какое-либо их объединение.
Еще в конце хотелось бы добавить 2 важных замечания.
Любой процесс переноса-удаления строк в SQL логируется в transaction log для возможности все откатить в случае ошибки (я ранее предполагал, что это осуществляется только в рамках транзакции). Причем размер лога получается даже чуть больше объема данных. Убедитесь, что располагаете необходимым количеством места либо отключите логирование, однако это небезопасно.
Перед переносом нужно удостовериться, что файл данных и файл лога имеют необходимый размер, т.к. операции по их расширению тратят значительное количество времени, и сконфигурировать их соответствующим образом Таким образом вы оптимизируете перенос.
Спасибо всем прочитавшим! Буду рад любой критике, замечаниям и уточнениям. Делитесь своими способами и техниками работы с большими данными, т.к. зачастую это очень важная и нужная информация, которую не так-то просто найти.
