Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 36
Хотел спросить, что будет, когда превысите максимальную длину запроса.
Отвечаю сам: Нет лимита. ( There has been no hard-wired upper limit for several releases now. )
Отвечаю сам: Нет лимита. ( There has been no hard-wired upper limit for several releases now. )
Интересный способ связывать MS SQL и Postgres, не знал про него.
Но в остальном как-то маловато для статьи на Хабре.
Но в остальном как-то маловато для статьи на Хабре.
Метод правильный — выводы ошибочные.
Основная разница между 1 и 2-2.5 вариантами — колличество соединений.
В первом случае у вас перед вставкой каждой строки открывается соединение, после вставки — закрывается. Вот в этом и кроется «тормоз» так как сама процедура установления соединения достаточно медленная.
Ну а последний вариант — это уже не совсем «SQL». Это больше похоже на «блочную работу» по типу как у MySqlDump. И этот метод станет не таким эффективным если размер «блока» будет мегабайт 100-1000. (из-за большого времени на физический транспорт) А вот если сформировать «блок» в виде текста, заархивировать, передать мультипоточным протоколом, а принимающая сторона распакует и выполнит на «принимающем» сервере — это будет куда более эффективный метод.
Главный «враг» во всем этом это Ваши 16мс задержки между хостами.
Основная разница между 1 и 2-2.5 вариантами — колличество соединений.
В первом случае у вас перед вставкой каждой строки открывается соединение, после вставки — закрывается. Вот в этом и кроется «тормоз» так как сама процедура установления соединения достаточно медленная.
Ну а последний вариант — это уже не совсем «SQL». Это больше похоже на «блочную работу» по типу как у MySqlDump. И этот метод станет не таким эффективным если размер «блока» будет мегабайт 100-1000. (из-за большого времени на физический транспорт) А вот если сформировать «блок» в виде текста, заархивировать, передать мультипоточным протоколом, а принимающая сторона распакует и выполнит на «принимающем» сервере — это будет куда более эффективный метод.
Главный «враг» во всем этом это Ваши 16мс задержки между хостами.
начну с конца. про главного врага и да и нет. я специально писал про удалённый сервер, но в реальности ситуация с локальным точно такая же. мне кажется, главный враг — это ожидание подтверждения транзакции со стороны mssq. про количество соединений, если честно не догадался проверить, вы утверждаете, что в одной транзакции mssql каждый раз открывает новое соединение?
блоки, которые сейчас используются в бою, это примерно 30Мб раз в час, на сотню узлов PostgeSQL, и примерно 150Мб два раза в сутки, на ту же сотню хостов.
способ с архивацией — передачей — распаковкой хорош, но хотелось обойтись без ещё одной детальки, которая может сломаться.
блоки, которые сейчас используются в бою, это примерно 30Мб раз в час, на сотню узлов PostgeSQL, и примерно 150Мб два раза в сутки, на ту же сотню хостов.
способ с архивацией — передачей — распаковкой хорош, но хотелось обойтись без ещё одной детальки, которая может сломаться.
https://habrahabr.ru/post/305982/#comment_9707862
Не знаю как насчет удаленной БД, но и на локальной вставка большого количества записей по одной будет занимать немалое время. А вариант с одним запросом INSERT, вставляющим тысячу строк за один раз — я полагаю, ни для кого ни секрет.
главное не в INSERT с большим количеством строк за раз, а, всё-таки, удалённое исполнение этого запроса.
Все же не соглашусь :) Удаленное выполнение запроса в Вашем примере увеличивает эффективность всего в 10 раз, в то время как вставка многих записей одним INSERT увеличивает эффективность в 80 раз (ориентировался по приведенным оценкам времени выполнения). Так что пунктом 2 следовало бы описать вставку многих записей одним запросом, а пунктом 2.5 — удаленное выполнение.
Не пробовали вариант с INSERT… SELECT…?
Должно быстро отработать и нет необходимости формирования запроса «вручную».
Должно быстро отработать и нет необходимости формирования запроса «вручную».
Эта штука точно сработает внутри одного сервера. Как она сработает со «связанными» — предсказать сложно. Надо проверять ;)
попробовал, результат такой же, как и в первом случае 2 минуты 49 секунд на 1000 строк.
insert into RemotePG...RemoteTable (RecordID, RecordName)
select top 1000 RecordID, RecordName
from LocalTable with(nolock)
мне кажется даже если это сработает — то в реальной жизни такой поход упрется в максимальную длину запроса, и это случится на живом сервере в самый не подходящий момент. К тому страдает читаемость кода и логов. Спасибо за статью!
Насколько оперативно новые записи из MSSQL должны появляться в Postgres? Если раз в день/часс, то почему бы не попробовать написать ETL и грузить с помощью SSIS?
есть несколько блоков данных, условно медленные и быстрые, быстрые — раз в час, медленные два раза в сутки. проблема в том, что хостов, на которых эти данные нужны, больше сотни. таблиц, которые таким образом синхронизируются примерно два десятка.
а чем SSIS пакет будет принципиально отличаться? когда-то давно пробовал SSIS с единственным mysql, скорость была похожа, как если бы я просто исполнял запросы из консоли.
а чем SSIS пакет будет принципиально отличаться? когда-то давно пробовал SSIS с единственным mysql, скорость была похожа, как если бы я просто исполнял запросы из консоли.
Ну формально SSIS как и раз и создан под задачи, которые вы описываете — мигрировать данные между системами. Он по дефолту заточен под большие объемы, использует пакетные операции и т.п. Конкретный адаптер\драйвер для системы (в вашем случае — Postgres) обычно тоже можно затюнить под конкретную задачу\профиль данных (размер блока и т.п.).
Соответственно пишите ETL (если вы не трансформируете данные, то это вообще можно мастером сделать), настраиваете два джоба (часовой и полусуточный) и готово.
Конкретных бенчмарков к сожалению не могу предоставить. Попробуйте на одной таблице, в теории должно быть как минимум не хуже вашего решения.
Соответственно пишите ETL (если вы не трансформируете данные, то это вообще можно мастером сделать), настраиваете два джоба (часовой и полусуточный) и готово.
Конкретных бенчмарков к сожалению не могу предоставить. Попробуйте на одной таблице, в теории должно быть как минимум не хуже вашего решения.
у меня сложилось некоторое предубеждение против SSIS, уж больно он капризный. но вы меня убедили, возьму себя в руки и попробую на какой-нибудь большой таблице. отпишусь по результатам.
попробовал, цедит в час по чайной ложке: за 15 минут около 9к. записей. при этом в свойствах data transformation вроде бы прописано, что 1000 записей за раз, в 100 потоков. плюс, SSIS не видит источников данных из 64-битного драйвера, что неудобно, потому что у меня все источники заведены там.
Тоже столкнулся с такой задачей. Данные на линкед сервер шли очень медленно. Таблица с 65 млн записей скопировалась бы по расчетам только через 300 суток непрерывной записи. Решение нашли тоже через SSIS. В access базу пишутся через ssis пакет по 1 млн данные (если больше аксесс тупить начинает), затем другой ssis выбирает из него данные в другой сервер (но уже напрямую).
Очень странно. У нас SSIS перегружает Oracle > MS SQL по 4 млн.записей в минуту, причем это ограничено в основном скоростью работы view на стороне Oracle (из физических таблиц — 5+ млн.записей в минуту). Таблицы не очень широкие, атрибутов по 10-20.
общее с комментарием выше – таргет сервер mssql, видимо дело в этом.
А какие версии у Postgres/MSSQL? Подниму у себя, погоняю. Непонятно, где затык. Скорость вашего метода 2.5 имхо в том, что данные одной строкой передаются, по сути файлом. Можно попробовать вставить ssisом из csv.
версии более-менее всё равно, 9.5 Postgres, и любой выше 2000 у MSSQL. главное в скорости, что MSSQL не следит за подтверждением транзакции на удалённом сервере. отправил, там что-то исполнилось, здесь пришло «запрос исполнился».
csv вряд ли даст какой-нибудь прирост, но появится лишний шаг (или два), которые тоже могу сломаться. и проблема, мне кажется, не в том откуда выбирать, выбирает быстро, вставляет медленно.
csv вряд ли даст какой-нибудь прирост, но появится лишний шаг (или два), которые тоже могу сломаться. и проблема, мне кажется, не в том откуда выбирать, выбирает быстро, вставляет медленно.
В mssql есть прекрасный и быстрый инструмент для импорта- экспорта. За давностью лет могу ошибиться в названиях, но если в enterprise manager зайти в раздел packages — то это самое оно.
Быстро
Любые odbc источники
Можно планировать запуск
Можно строить сложные последовательности обработки
И НЕ НАДО ДУМАТЬ ОБ ЭКРАНИРОВАНИИ ДАННЫХ!!!
Это был mssql 2000. Вряд ли сегодня стало много хуже.
Быстро
Любые odbc источники
Можно планировать запуск
Можно строить сложные последовательности обработки
И НЕ НАДО ДУМАТЬ ОБ ЭКРАНИРОВАНИИ ДАННЫХ!!!
Это был mssql 2000. Вряд ли сегодня стало много хуже.
кстати, мы использовали это для систематической синхронизации двух баз данных управленческого учёта в процессе постепенного перехода на новое ПО: старой, несвязной, с двоящимися записями на карат aka db2 и новой на MSSQL. Абсолютно разные структуры таблиц, проверки, устранение дублирования, обеспечение связности данных… При этом визуальное наглядное представление и автоматическая работа. На тот момент кроме MS никто, кажется, такого полноценного инструмента не имел.
Целиковый запрос м.б. и побыстрее, но экранирование данных игнорировать нельзя.
Целиковый запрос м.б. и побыстрее, но экранирование данных игнорировать нельзя.
А вы не пробовали то же самое хотя бы для теста сделать с помощью Foreign data wrapper?
Насколько всё будет хуже/лучше для простого COPY из внешних таблиц?
Насколько всё будет хуже/лучше для простого COPY из внешних таблиц?
Talend — возможно лучшее решение, чем SSIS для данной задачи/ Есть бесплатный www.talend.com/download
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Ускоряем вставку данных из MSSQL на удалённый PostgreSQL в 800 раз