Однажды мне потребовалось забирать регулярно относительно большие объемы данных в MS SQL из PostgreSQL. Неожиданно выяснилось, что самый очевидный способ, через Linked Server на родные ODBC к PostgreSQL, очень медленный.
История вопроса
На этапе прототипирования все было хорошо. Просто потому, что протипировалось всего несколько тысяч записей. Как только перешли к разработке, сразу возникло подозрение, что с производительностью что-то не то:SET STATISTICS TIME ON
DECLARE
@sql_str nvarchar(max)
DROP TABLE IF EXISTS #t
CREATE TABLE #t (
N int,
T datetime
)
SELECT @sql_str='
SELECT N, T
FROM generate_series(1,1000,1) N
CROSS JOIN generate_series($$2020-01-01$$::timestamp,
$$2020-12-31$$::timestamp, $$1 day$$::interval) T'
INSERT #t (N, T)
EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES
Такой простейший пример выборки всего 366 тысяч записей оказался жутко медленным:
SQL Server Execution Times:
CPU time = 8187 ms, elapsed time = 14793 ms.
Решение
В первую очередь, захотелось исключить самый подозрительный элемент - ODBC. К тому времени MS уже предоставлял утилиту bcp для Linux. Поэтому bcp был установлен на сервер, где работал PostgreSQL и проведен следующий тест:SET STATISTICS TIME ON
DECLARE
@sql_str nvarchar(max),
@proxy_account sysname='proxy_account',
@proxy_password sysname='111111'
DROP TABLE IF EXISTS ##t
CREATE TABLE ##t (
N int,
T datetime
)
SELECT @sql_str='
COPY (
SELECT N, T
FROM generate_series(1,1000,1) N
CROSS JOIN generate_series($$2020-01-01$$::timestamp,
$$2020-12-31$$::timestamp, $$1 day$$::interval) T )
TO PROGRAM $pgm$ tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); '
+'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##t'' '
+'in $tmp_file -S '+REPLACE(@@SERVERNAME,'','\')
+' -U '+@proxy_account+' -P '''
+@proxy_password+''' -c -b 10000000 -a 65535; '
+'rm $tmp_file $pgm$ NULL $nil$$nil$;'
EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES
Результат сразу порадовал, причем сильно:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 881 ms.
Реализация
Не сложно заметить, что такой подход требует явного указания логина и пароля. Причем, bcp для Linux до сих пор не умеет авторизоваться через Kerberos. Поэтому использовать его можно только указывая кредентиалы в командной строке.Вторая проблема в том, что в обычную временную таблицу bcp записать не может. Он ее просто не увидит. Значит нужно использовать постоянную таблицу или глобальную временную.
Давать права пользователю, кредентиалы которого открытым текстом видны в SQL запросе, на таблицы своей БД совершенно не хочется. Тем более на запись. Поэтому остается только вариант с глобальной временной таблицей.
В связи с тем, что процессы на сервере могут запускаться асинхронно и одновременно, использовать фиксированное имя глобальной временной таблицы опасно. Но тут нас опять спасает динамический SQL.
Итоговое решение следующее:
DECLARE
@sql_str nvarchar(max),
@proxy_account sysname='proxy_account',
@proxy_password sysname='111111'
SELECT @sql_str='
DROP TABLE IF EXISTS ##proxy_table_'+CONVERT(nvarchar(max),@@SPID)+'
CREATE TABLE ##proxy_table_'+CONVERT(nvarchar(max),@@SPID)+' (
N int,
T datetime
)'
EXEC (@sql_str)
SELECT @sql_str='
COPY (
SELECT N, T
FROM generate_series(1,1000,1) N
CROSS JOIN generate_series($$2020-01-01$$::timestamp,
$$2020-12-31$$::timestamp, $$1 day$$::interval) T )
TO PROGRAM $pgm$ tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); '
+'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##proxy_table_'''
+CONVERT(nvarchar(max),@@SPID)+' '
+'in $tmp_file -S '+REPLACE(@@SERVERNAME,'\','\\')
+' -U '+@proxy_account+' -P '''
+@proxy_password+''' -c -b 10000000 -a 65535; '
+'rm $tmp_file $pgm$ NULL $nil$$nil$;'
EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES
Пояснения
В PostgreSQL команда COPY может писать в файл или на стандартный ввод вызываемой ей программы. В данном случае вместо программы использован скрипт на sh. Вывод COPY, поступающий на стандартный ввод, записывается во временный файл с уникальным именем, формируемым mktemp. К сожалению, bcp не умеет читать данные со стандартного ввода, поэтому приходится ему создавать файл.Для совместимости формата, формируемого командой COPY и формата, ожидаемого bcp, обязательно следует указывать в COPY параметр NULL $nil$$nil$
Остальные параметры bcp:
- -c - символьный формат, так как бинарный формат PostgreSQL не совместим с бинарным форматом MS SQL и мы вынуждены использовать только символьный;
- -b - количество записей, вставляемых одной транзакцией. В моей конфигурации десять миллионов оказалось оптимальным значением. В иной конфигурации это число, скорее, может потребоваться уменьшить, чем увеличить;
- -a - размер пакета. В нашем случае лучше указывать сразу максимальный. Если сервер не поддерживает указанную длину пакета, то просто будет использована максимальная длина пакета, поддерживаемая сервером.
Теги:
Хабы:
Реклама
ЧИТАЮТ СЕЙЧАС
- ДИТ Москвы, коронобесие, большие данные: преступление и наказание
18,9k67 - Visual Studio 2022
3,3k10 - Текущее положение дел по распознаванию лиц и камерам наблюдений в Москве и мире
10,1k58 - Спутниковый интернет Starlink продолжает развиваться: в этом году он станет мобильным и покроет почти всю планету
9,7k25 - НАСА показало потоки марсианской пыли во время полета вертолета «Индженьюити»
3,2k3 - [Личный опыт] Жизнь за Великой китайской стеной: как в Китае дела с IT, цензурой и интеграцией в общество
1,5k2 - Сеть настроили и полетели
Мегапост
Редакторский дайджест
Присылаем лучшие статьи раз в месяц- +9
- 11
- 2,2k
- 18
- Поделиться
11,2
Карма
7,9
Рейтинг
Вадим Петряев ptr128
Архитектор ИС
ПОХОЖИЕ ПУБЛИКАЦИИ
- 24 июня 2020 в 12:54
T-SQL. Формирование XML со списком значений
54,4k189 - 12 ноября 2019 в 23:48
Бэкап файловых и SQL баз 1С (в облако и с шифрованием)
615,3k6310 - 12 августа 2019 в 10:11
Почему не SQL?
8261,7k417178
КУРСЫ
-
SUSE Linux Enterprise Server 12 – Безопасность
11 мая 2021 1 неделя 30 000 Сетевая Академия ЛАНИТ -
Oracle, PL/SQL. Продвинутый курс
17 июня 2021 27 700 Luxoft Training -
SUSE Linux Enterprise Server 15 - Администрирование
5 июля 2021 1 неделя 50 000 Сетевая Академия ЛАНИТ -
SUSE Linux Enterprise Server 15 - Продвинутое администрирование
12 июля 2021 1 неделя 50 000 Сетевая Академия ЛАНИТ -
Administrator Linux.Basic
24 апреля 2021 55 000 OTUS
Реклама
МИНУТОЧКУ ВНИМАНИЯ
Комментарии 18
-
x893вчера в 23:25
Странно, что начиналось не с «байт состоит из 8 битов».
-
Harliffсегодня в 00:29
А какие требования к актуальности данных на таргете?
-
ptr128сегодня в 00:39
По расписанию раз в час или по нажатию на кнопки в веб-форме. Суммарный объем данных не большой (суммарно до 10 миллионов строк из нескольких десятков таблиц). За минуту всегда проходит, включая фиксацию запрошенных данных в архиве на стороне PostgreSQL.
Данные вытаскиваются для оптимизационной модели, поэтому важно иметь возможность в любой момент загрузить или наиболее актуальные, или исторические данные, использованные ранее при моделировании.
-
-
Pridachin_N_Lсегодня в 07:13
Благодарю за реализацию. А можно разложить эту методику, только в обратном направлении, из MS SQL -> PostgreSQL.
-
ptr128сегодня в 08:40
В обратном порядке, через TDS_FDW проблем с производительностью не возникает, поэтому подобные извращения не требуются.
-
hazgсегодня в 07:19
pgloader.readthedocs.io/en/latest/ref/mssql.html
в ответ на вопрос о переносе из ms sql в postgres. видимо кофе было мало, чтобы на ответ нажать.
-
tzlomсегодня в 07:34
Bucardo вполне бы справился с такой работой.
Вместо временной таблицы можно создать хранимую процедуру и дать доступ только к ней.
-
ptr128сегодня в 08:37
Во-первых, я не понял, с каких пор Bucardo стал поддерживать репликацию между MS SQL и PostgreSQL. Можете ссылочку дать?
Во-вторых, не понял смысл хранимки. BULK INSERT может быть только из файла. А остальные варианты вставки строк в MS SQL всегда медленней, чем BULK INSERT.
-
terrapodсегодня в 08:26
А избавится от tmp: bcp in -? Хотя, зная что это MS, наверное будет что-то вроде bcp in /dev/stdin.
spounge-bobсегодня в 08:40
быстрее всего и универсальнее всего данные в MSSQL и Oracle можно закинуть через xml
insert into…
select t.g.value('./@field1', bigint),
…
select t.g.value('./@fieldN', varchar(xxx))
from @xml_body.nodes('//row') as t(d)
-
ptr128сегодня в 08:51
Точно не быстрее, а медленней. Можете убедиться сами.
Во-первых, я уверен, что время формирования и парсинга XML всегда будет дольше времени формирования и парсинга текстового формата BCP. Хотя бы потому, что первый всегда больше второго.
Во-вторых, я уверен, что INSERT INTO всегда работает медленней, чем BULK INSERT
В-третьих, я уверен, что XML в БД не может быть больше 4ГБ ни при каких условиях. Что приводит и к усложнению кода, и к потере производительности.
В-четвертых, если уж таким путем идти, то JSON явно меньше размером., чем XML. Именно поэтому, если у меня есть гарантии, что объем JSON получится меньше гигабайта — его и использую. Но в рассматриваемой в статье ситуации объем точно может превышать 4 ГБ.
-
-
Tzimieсегодня в 10:58
Я столкнулся с теми же тормозами когда читал данные с VMware — раньше там был MS SQL, потом они перешли на Postgre. Вначале использовал Linked servers, но это жуткие тормоза. Остановился на следующем решении (пример чтения статистик):
set @sql='select D.SAMPLE_TIME,D.STAT_VALUE from vc.vpxv_hist_stat_daily D
where STAT_GROUP=''cpu'' and STAT_NAME=''usage'' and ENTITY='''+@vm+''''
set @sql=replace(@sql,'''','''''')
set @outer='insert into _cpustats select SAMPLE_TIME,STAT_VALUE FROM OPENROWSET(''MSDASQL'', ''DSN='+@dsn+''','''+@sql+' '') AS a;'
truncate table _cpustats
exec(@outer)
Пароли скрыты в ODBC
-
ptr128сегодня в 11:38
Так тоже пробовал. Те же яйца, но вид сбоку:
SET STATISTICS TIME ON
DECLARE
@sql_str nvarchar(max)
DROP TABLE IF EXISTS #t
CREATE TABLE #t (
N int,
T datetime
)
SELECT @sql_str='
SELECT N, T
FROM generate_series(1,1000,1) N
CROSS JOIN generate_series($$2020-01-01$$::timestamp,
$$2020-12-31$$::timestamp,$$1 day$$::interval) T'
SELECT @sql_str='
INSERT #t (N, T)
SELECT N, T
FROM OPENROWSET(''MSDASQL'', ''DSN=pg_sql_server'', '''
+@sql_str+''') AS O'
EXEC (@sql_str)
SQL Server Execution Times:
CPU time = 5500 ms, elapsed time = 12328 ms.
Это против 881 ms через BCP выше.
-
Источник статьи: https://habr.com/ru/post/553472/