(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 для возможности все откатить в случае ошибки (я ранее предполагал, что это осуществляется только в рамках транзакции). Причем размер лога получается даже чуть больше объема данных. Убедитесь, что располагаете необходимым количеством места либо отключите логирование, однако это небезопасно.

Перед переносом нужно удостовериться, что файл данных и файл лога имеют необходимый размер, т.к. операции по их расширению тратят значительное количество времени, и сконфигурировать их соответствующим образом Таким образом вы оптимизируете перенос.
Спасибо всем прочитавшим! Буду рад любой критике, замечаниям и уточнениям. Делитесь своими способами и техниками работы с большими данными, т.к. зачастую это очень важная и нужная информация, которую не так-то просто найти.
Поделиться публикацией

Комментарии 31

    –1
    А пробовали добавить столбец с индексом и именем вроде copied (default = 0),
    постепенно копировать строки пачками по n штук как-то так:
    SELECT TOP n * FROM… WHERE copied = 0
    и потом отмечать в скопированных строках copied = 1?
      +1
      а третий способ разве не оно? Только вместо добавления столбца разбиваем уже имеющийся ключ на интервалы.
        0
        а третий способ разве не оно?
        Как я понял — там «маркером» того, что строки перенесены было их удаление из исходной таблицы после копирования. Удаление строки это намного более затратная операция по сравнению с изменением одного поля поля в строке. Учитывая, что сервер живой и там еще какие-то страсти с транзакциями-логированием удаленных строк — мне кажется мой вариант будет быстрее и не такой чувствительный для вебсервиса.
          0
          Можно не удалять, если ключ идет последовательно от 1 до 100500, то мы просто копируем записи с ключом от i*1000 до (i+1)*1000-1, запоминая на каком i мы в прошлый раз остановились. А в конце, когда все записи скопированы — truncate (или как оно в mssql называется) чтобы быстро очистить место.
      0
      Как насчет BULK INSERT?
      Она вроде как даже в лог не пишет, тоже плюс. Выгружать порциями, как в способе 3.

      А еще можно попробовать настроить репликацию…
        +1
        Вы угадали мои мысли в MySQL есть например: LOAD DATA INFILE `filePath` INTO TABLE `tableName` выполняется кратно быстрее, как сравнивать O(n^2) с O(n), но заметил, что автор упомянул пункт 2) SELECT INTO OUTFILE `filePath` если быть точным.
        +2
        Мастер экспорта-импорта работает вполне себе хорошо.
        Для разового не регулярного переноса самое то (если есть студия у вас).
        SSMS (SQL Server Management Studio это сейчас официально бесплатный инструмент от микрософта, скачать можно с сайта микрософта и установить, если какие то внутренние политики не запрещают).
        Внутри себя использует BULK INSERT и не засирает лог.
        Если опасаетесь, что на вашей большой БД подвиснет за раз, можете разбить данные, например по датам и в мастере использовать не таблицу, а запрос с условием.
        Т.е. перенесли часть данных старых, удалили часть данных старых.
        Затем снова перенесли, удалили. И так пока не выполните весь перенос.
        Только удалять старые данные лучше тоже порциями, по 10 — 100 тыс. строк, что бы лог не съел у вас место. Чем больше порция, тем больше рост лога транзакций.
        Для разовой задачи самый простой и безболезненный вариант.
        Настраивать репликацию ради разового переноса, как пишут в комментах, это очень лишнее, не стоит этого делать.
        Также можете посмотреть хелп по оператору DELETE, конструкция OUTPUT позволяет сохранить удаленные данные в целевую таблицу.
        Т.е. можно запустить цикл удаления по N записей, которые благодаря OUTPUT будут попадать в нужную вам таблицу. Позволяет это делать даже без использования ключей.
        Что бы определить были ли удалены записи в итерации и для определения выхода из цикла можно использовать глобальную переменную @@ROWCOUNT, которая показывает количество строк затронутых операцией.
        По поводу остановки не остановки веб сервиса, это другой вопрос, требующий планирования.
          0

          +1 за мастер экспорта импорта. Но он использует не bulk insert, а её другой аналог доступный только в .NET — прямая запись данных в таблицу, без всяких insert. Что-то вроде прямой передачи данных в бинарном формате. Однако, замечено на практике, что если ставить галочку на DELETE DATA BEFORE TRANSFER, и у вас осталось мало места а transaction log, то процесс все таки застревает. Возможно, для удаления он всё же используют transaction log.

            0
            Да, конечно при удалении он использует транзакцию с использованием лога транзакций, потому и лог растет в таком случае.
              0
              Очень хочется знать, как работает эта «прямая запись в таблицу» :) Можем поговорить на двоичном уровне, уровне байт, блоков?)
              Если есть кластерный индекс, любая запись должна пройти через движок БД, что бы попасть туда, куда ей нужно. А без кластерного индекса, потом будет реиндексация. Никакой «прямой записи» в принципе быть не может. Скорее всего, там оптимизация в момент записи. Просто монопольный захват таблиц или что-то в этом роде.
            0
            Я не увидел во всех способах обоснования. Да были попытки, но как сказал Сергей: Владимир не заморачивайся! При необходимости мы расширим кластер и все будет работать.

            interfax.ru ©
              0
              Мне кажется не упомянут самый надёжный способ: bcp.exe

              И очень зря.

              Та же SSMS использует под капотом именно его, а главное — это самый низкоуровневный и эффективный способ
                0
                bcp хорошо использовать для регулярных задач, если есть такая потребность.
                Разово перекинуть данные гораздо быстрее и удобнее используя интерфейс.
                  0
                  Кому как. Мне, например, быстрее и проще написать bcp с параметрами, чем тыкать «мышей» по чекбоксам.
                    0
                    Если вы скрипты bcp регулярно юзаете и у вас под рукой шаблоны, может быть.
                    Но если раз в неск. лет упала задача перекинуть таблицы из базы на другой сервер в другую базу, уверен, что писать скрипт и отлаживать вы будете дольше на порядки, чем используя мастер.
                    Любую задачу, на мой взгляд, нужно выполнять подходящим инструментом исходя из целесообразности и затрат, без фанатизма.
                  0
                  Мне тоже показалось, что игнорировать BCP в такой задаче довольно странно.
                  Я как-то переносил достаточно большие таблицы. Таблички в несколько миллионов строк копируются за пару минут.
                  0

                  Ещё как писали в похожей статье по postgre, хороший способ:
                  1) переименовываем существующую таблицу в что-нибудь, например дописываем окончание _old
                  2) Создаём новую пустую таблицу с точно такой же структурой. Структуру взять не сложно, главное не забудьте про индексы. Сделать 1 и 2 запрос можно одним запросом, поэтому ни один скрипт не прервется, и просто запишет данные а новую таблицу. Простоя не будет.
                  3) имея неменяющуюся таблицу _old, можно начать её медленно переносить. Если планируете порционно, то вообще стоит удалить индексы (кроме главного ключевого ), потому-что удаляя после запроса по 100 000 записей, будут также перестраиваться все индексы, это дорогая операция для такой толстой таблицы. Ну а я все же рекомендую переносить через мастер экспорта импорта, он не будет перестраивать индексы пока все не перенесет .

                    0
                    Тоже отличный вариант. Делали так на MySQL, когда нужно было 300гиговую таблицу перенести.
                    –1
                    Если, пока, я не заморачиваюсь над сбоями, с помощью linq2db я это дело делаю так:

                    using (var source = CreateSourceConnection())
                    using (var destination = CreateDestinationConnection())
                    {
                        // extract and transform, lazy
                        var sourceQuery = source.GetTable<SomeTable>()
                            .Select(s => new SomeDestTable{...});
                    
                        // load data by 1000 records, configurable
                        destination.GetTable<SomeDestTable>().TableName("SomeNewName")
                           .BulkCopy(sourceQuery);
                    }


                    Данные влетают в базу со скоростью мысли, при чем это могут быть любые сервера и я одновременно могу делать Transform. Такой себе ETL через ORM.

                    Если же у вас базы на том же сервере. То почему же не сделать это одним запросом.

                    using (var source = CreateSourceConnection())
                    {
                        // extract and transform, lazy
                        var sourceQuery = source.GetTable<SomeTable>()
                            .Select(s => new SomeDestTable{...});
                      
                       // appropriate INSERT INTO will be genarated
                       sourceQuery.Insert(source.GetTable<SomeDestTable>()
                            .DatabaseName("OtherDb").TableName("SomeNewName"));
                    
                    }
                      0
                      Вы забыли запрос, который отключает индексы на таблице назначения. Добавьте его скорее.
                        –1
                        Это проблема, запустить дополнительньный тюнингующий запрос? Тем более что оно варьируется от базы к базе.
                        0
                        Вы просто попробуйте на досуге перекинуть куда нибудь 500 млн. записей. Для разнообразия в БД получателе сделайте на таблице индексов несколько. И посмотрите. Возможно скорость мысли убавится.
                        Если 1000 записей залетают со скоростью мысли, нельзя это просто так экстраполировать на 100 млн. записей. Поведение будет другое.
                        0
                        А какое отношение имеет разработчик к этим переносам? Это работа DBA. Сообщаешь в IT отдел, чтобы восстановили бэкап такой-то базы на таком-то сервере. Всё, проблемы негров белых не волнуют. Нечего их работу на себя брать.
                          0
                          Иногда бывает что DBA и Developer это один и тот же человек. Особенно в маленьких компаниях.
                            0
                            В компаниях где базы данных по 300ГБ нет DBA? Ну это нонсенс какой-то. Может у них ещё и уборщицы нет? Ну а что, младший инженер по совместительству всё успеет.
                              0
                              Это не размер для баз сейчас, так базочка. Совсем не то когда окупается DBA. По крайней мере у нас так.
                                0
                                Это вообще разные профессии, какая связь с окупаемостью? По вашей логике так водитель такси должен в промежутках между поездками перебирать двигатели стоящих на ремонте камазов? Ну просто потому что так кому-то там что-то лучше окупается? Я так не думаю. Это из серии «переустанови мне виндовс, ты же программист». Нифига.
                                А окупится оно в первый же раз когда ваш сервер с критическим приложением посыпется посреди рабочего дня и младший инженер будет по гуглу и стаковерфлоу искать информацию как развернуть новый сервер и как накатить на него позапрошлогодний бэкап, который чудом где-то завалялся. И время простоя может вылиться в такие суммы, что содержание отдела айтишников покажется бесплатным.
                                  0
                                  Мы про DevOps говорим или о DBA? Хотя я уже и разницы сильно не ощущаю.
                            0
                            Да, проблемы негров белых не волнуют. До тех пор, пока это не оказывается база несколько терабайт SQL для 1С, в которой торчат онлайн 2-3 тыщ пользователей в терминале по всей России)
                            0

                            Для чего давать советы по решению задачи, которые сам не пробовал?
                            Где-то слышал/читал/увидел, сам не попробовал, но расскажу, статью напишу побольше, тема важная.

                              0
                              Во-первых, вероятно вам может помочь удаление какого-либо столбца, однако это блокирующая операция, а не всегда есть возможность остановить web-сервис. И на Хабре есть статья, в которой рассказывается, как это можно осуществить.


                              Можно было бы сразу и ссылку прикрепить?

                              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                              Самое читаемое