Комментарии 82
А какие требования к актуальности данных на таргете?
Данные вытаскиваются для оптимизационной модели, поэтому важно иметь возможность в любой момент загрузить или наиболее актуальные, или исторические данные, использованные ранее при моделировании.
а что мешает и в ту сторону через fdw?
Иными словами, вставку строк через FDW или Linked Server лучше избегать. А если уж приперло — инкапсулировать данные в JSON/XML и вливать их по RPC.
в ответ на вопрос о переносе из ms sql в postgres. видимо кофе было мало, чтобы на ответ нажать.
Bucardo вполне бы справился с такой работой.
Вместо временной таблицы можно создать хранимую процедуру и дать доступ только к ней.
А избавится от tmp: bcp in -? Хотя, зная что это MS, наверное будет что-то вроде bcp in /dev/stdin.
insert into…
select t.g.value('./@field1', bigint),
…
select t.g.value('./@fieldN', varchar(xxx))
from @xml_body.nodes('//row') as t(d)
Во-первых, я уверен, что время формирования и парсинга XML всегда будет дольше времени формирования и парсинга текстового формата BCP. Хотя бы потому, что первый всегда больше второго.
Во-вторых, я уверен, что INSERT INTO всегда работает медленней, чем BULK INSERT
В-третьих, я уверен, что XML в БД не может быть больше 4ГБ ни при каких условиях. Что приводит и к усложнению кода, и к потере производительности.
В-четвертых, если уж таким путем идти, то JSON явно меньше размером., чем XML. Именно поэтому, если у меня есть гарантии, что объем JSON получится меньше гигабайта — его и использую. Но в рассматриваемой в статье ситуации объем точно может превышать 4 ГБ.
Кроме того, этот способ еще и универсальнее всего.
— Импорт через xml/json может быть фичей прикладного ПО, в то время как bcp — нет (как правило нет)
— bcp тупо вставляет, и логику иморта / экспорта навернуть там сложнее, чем в скрипте tsql. (Попробуйте через bcp перенести глубокую реляционную иерархию с ключами identity)
— json да, на mssql можно и через него.
— Ограничение в 2ГБ обходится дроблением. (Это еще если не вспоминать что mssql умеет в gzip)
Простите, но у меня есть все основания считать, что Вы заблуждаетесь.
При этом, Вы имеете полное право опубликовать здесь код Вашего решения, сравнить время выполнения Вашего и моего кода и разбить меня в пух и прах.
Если же Вы предлагаете менее производительное, но универсальное решение, то, простите, первый код в статье справляется с задачей намного более универсальным путем, чем Ваш. Только медленно. И статья вовсе не об универсальности, а о достижении максимальной производительности.
mssql умеет в gzip
Даже если на стороне PostgreSQL сжать в GZIP xml длиной больше 4ГБ (а это можно сделать), то как Вы его на стороне MS SQL распакуете, если DECOMPRESS() возвращает varbinary(max), длина которого лимитирована 2ГБ?
он быстрее.
я говорил что решение c xml/json блобом будет быстрее.
и говорил что оно легко становится штатной фичей ПО, в то время как bcp останется админским инструментом
кроме того, мой вариант дает готовые и целостные данные, а bcp дает полуфабрикат, который требует постобработки (выше упомянут пример импорта глубокой иерархии в которой релиционные отношения построены на идентити ключах)
и на гзипе ничего там не «умрет»
у меня строковые данные GS1 (которые as-is в xml не принимаются) вставляются как base64 значения с распаковкой прямо во время инсерта. И записей там не сто, а сотни тысяч.
я нигде не говорил, что bcp медленнее чем insert into ...select from
он быстрее.
я говорил что решение c xml/json блобом будет быстрее.
Вы не видите противоречия в своих же словах? Сначала вы говорите, что BCP быстрее, чем INSERT INTO… SELECT… FROM…, после чего сразу же говорите, что INSERT INTO… SELECT… FROM OPENJSON(...)/OPENXML(...) — вдруг быстрее BULK INSERT.
bcp дает полуфабрикат
Сам BCP действительно дает полуфабрикат. Но в описаном статье примере данные для BCP готовятся запросом в PostgreSQL, что дает уже целостное решение. Какая разница на какой стороне выполнять трансформацию данных?
Контрпример. Попробуйте источник OPENXML() из хотя бы миллиона записей указать в качестве USING в MERGE к таблице из десяти миллионов записей. Сразу заскучаете.
И в любом случае, хоть с JSON, хоть с XML, имеет всегда существенно больший размер, чем текстовый файл BCP. Следовательно, только на передаче по сети Вы проиграете.
как Вы его на стороне MS SQL распакуете, если DECOMPRESS() возвращает varbinary(max), длина которого лимитирована 2ГБ?
и на гзипе ничего там не «умрет»
Вы сильно уменьшили доверие ко всем Вашим утверждениям:
wget -c https://fias-file.nalog.ru/downloads/2021.04.16/fias_xml.zip
7za x fias_xml.zip
gzip AS_SOCRBASE_20210416_fda8d4a2-06ce-4c55-b751-c5b4dd7e7e70.XML
gzip AS_STEAD_20210416_5c40a3e2-887a-4025-a83c-cbe85b7f8e5b.XMLDROP TABLE IF EXISTS #t
CREATE TABLE #t (n int, b varbinary(max))
INSERT INTO #t (n, b)
SELECT 1, * FROM OPENROWSET(BULK N'X:\Temp\AS_SOCRBASE_20210416_fda8d4a2-06ce-4c55-b751-c5b4dd7e7e70.XML.gz', SINGLE_BLOB) rs
INSERT INTO #t (n, b)
SELECT 2, * FROM OPENROWSET(BULK N'X:\Temp\AS_STEAD_20210416_5c40a3e2-887a-4025-a83c-cbe85b7f8e5b.XML.gz', SINGLE_BLOB) rs
DECLARE @x1 xml
SELECT @x1=CONVERT(xml,DECOMPRESS(b))
FROM #t
WHERE n=1
DECLARE @x2 xml
SELECT @x2=CONVERT(xml,DECOMPRESS(b))
FROM #t
WHERE n=2
SQL Error [6365] [S0001]: An XML operation resulted an XML data type exceeding 2GB in size. Operation aborted.Это не считая того, что использование GZIP снизило производительность на порядок.
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
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 выше.
Но меня мое решение устроило — быстрее linked server в сотни раз.
А что у вас DSN=pg_sql_server?
Я использовал ODBC, 64 битные дрова, бесплатные — psqlodbc_x64
быстрее linked server в сотни раз.
А это уже странно. Если не пытаться тянуть из Linked Server данные запросом напрямую, что явно не рекомендуется, а использовать RPC (EXEC (...) AT ...), как в моем первом примере, то выигрыш меньше 20% (12328 ms против 14793 ms).
что у вас DSN=pg_sql_server?
ODBC PostgreSQL Unicode(x64) 12.02.00.00
BCP всегда все обгоняет
Не совсем. Все же данные, вместо того чтобы напрямую попасть с сервера на сервер еще приходится сначала преобразовать в текстовый вид, затем записать в файл (пусть даже и на ramfs), запустить BCP, прочитать из файла и распарсить.
С обычными постоянными таблицами то все понятно. Но в случае временных или нелогируемых таблиц — все вовсе не однозначно.
Может потому и хотят, что он работает быстро?
Во-первых, у меня сейчас нет достаточно мощного сервера, чтобы с этим драйвером поиграться.
Во-вторых, не нравится мне сочетание Open Source с проприетарным blob. В любой момент это может вылезти боком. Например, захочу на PostgreSQL 13 перейти на зоне разработки, а он его до сих пор не поддерживает.
С друго стороны. Можно было присоединить любой диск и выполнить все эти команды без линкед сервера. Т.е не понятна необходимость именно его. Все остальное отлично
Как бы bcp это отдельная утилита, которая может запускаться вне оболочки любой базы данных
А как обойтись без Linked Server я не понял. Кто «все эти команды» запустит при нажатии кнопки в вебформе, кто создаст в этом случае глобальную временную таблицу и как он узнает, что таблица уже заполнена?
Можно без словесных описаний. Просто приведите пример кода, выполняющего ровно ту же задачу, как у меня.
Запускается это не как скрипт на sql servere а как bush\shell\… скрипт в котором
1. выполняется экспорт из Postgress
как -то так
psql -U… -h 127.0.0.1 -d some_datababse -f /some/path/my_script_name.sql
2. выполняется импорт в sql
/opt/mssql-tools/bin/bcp -S destination -d DBname -U userName -P…
3. очищаете каталог
Вы что, предлагаете вместо использования ramfs, как у меня, дважды гонять эти несколько гигабайт по сети? Или наоборот, из C# на IIS по SSH ходить на сервер, где хостится PostgreSQL под выделенным для этих целей аккаунтом? В чем тогда профит и как управлять правами?
Приведите все же полностью код решения, чтобы можно было запустить его и убедиться, что Ваш вариант более производительный. Прямо по пунктам:
- Создание глобальной временной таблицы
- Загрузка данных в нее
- Вызов обработчика загруженных данных
Вы что, предлагаете вместо использования ramfs, как у меня, дважды гонять эти несколько гигабайт по сети?
а почему дважды-то?
и один раз гонять данные по сети придётся в любом случае (если только у вас pg и mssql не на одном хосте)
У нас три хоста: веб-сервер, MS SQL сервер и PostgreSQL сервер. Инициатор операции веб-сервер.
В моем примере, веб-сервер общатеся только с MS SQL. Код опубликован в статье.
Что же происходит в Вашем примере я не пойму, а код Вы не предоставляете.
Вторая проблема в том, что в обычную временную таблицу bcp записать не может. Он ее просто не увидит. Значит нужно использовать постоянную таблицу или глобальную временную.
Используйте bulk insert. Он — может.
Не берусь утверждать, что заработает, но можно сделать рамдиск и раздать его по сети через iSCSI. Диск может быть на любой из машинок.
раздать его по сети
Ключевая фраза. Файловый доступ по любой сети будет медленней, чем прямая передача по TCP в TDS и, тем более, чем локальная запись на ramfs, которая обозначает не более чем сохранение данных в кеше. Это совсем не ram disk, который требует физического копирования данных на него.
Так же следует понимать, что ramfs никто по сети не расшарит ни при каком условии, так как это легко может привести к падению сервера.
И Вы так и не ответили на вопрос об управлении правами доступа к файлам. Прямой путь через локальные права и SFTP — медленный. Через NFS/SMB и расширенные атрибуты — для ramfs не применим.
* используется специальная утилита для БД от ее же поставщика (bcp или sqlldr)
* для загружаемых данных отключается запись в лог транзакций БД (что обычно умеет делать именно эта утилита, чаще всего с помощью специальных ключей командной строки или параметров конфигов)
* отключается взаимодействие с БД через SQL, и включается режим, в котором загрузочная утилита сразу формирует из входных текстовых файлов данные во внутреннем бинарном формате БД (для sqlldr этот режим вроде бы называется «direct-режим»)
* если загружаемых данных очень много, то нужно сразу предусмотреть партиционирование (загружать по частям в отдельные партиции, после загрузки объединить их уже SQL-запросом, если необходимо)
самый быстрый способ обычно оказывается через текстовые файлы (csv или tsv)
Рискну предположить, что для bcp самым быстрым является -n (native type).
Я знаю, в статье это было оговорено. Ответ был на комментарий, который описывал решение "в принципе". А в принципе мне кажется, что используя нативные утилиты лучше начинать с нативных же форматов. Ваш же случай строится на множестве очень специфических деталей вроде наличия bcp под Linux, поддержки COPY TO PROGRAM и т.п. Вы же не использовали "специальную утилиту", как советует автор комментария.
Хотя и к вашей статье есть вопросы. Мне с трудом верится, что потенциально многопоточный бинарный SSIS, у которого под капотом есть и аналогичный bcp SqlBulkCopy, намного медленней однопоточного пайпа текстов в непонятной кодировке.
Один ваш комментарий ниже про "SSIS — выделенный хост" мне подсказываем, что вы даже не пробовали запустить dtexec CLI, который также как и bcp есть под линукс. Но я могу и ошибаться, поскольку не являюсь экспертом в этом области. Копирование больших объемов данных делаю не так часто, но с bcp, как и с dacpac/bacpac его использующим нахватался граблей, поскольку скорость не даётся бесплатно.
SSIS при коммуникации с источником данных точно так же ограничен тем же SQL, что используется при прямом обращении к этому источнику данных. Никакие специальные секретные команды ему не доступны. И ничего кроме того, что описано в статье он сделать не может. Вот только вместо быстрой COPY он будет вынужден фетчить результат SELECT, а вместо ramfs на том же хосте, что и PostgreSQL вынужден будет использовать свой локальный диск для временного файла. И никакого толку от его встроенной многопоточности Вы тут не получите. Все будет строго последовательно.
При чем тут dtexec мне вообще не понятно. SSIS — выделенный хост не потому, что его нельзя физически поставить на тот же хост, что и продуктивный SQL Server. А потому, что во-первых, это не рекомендуется самим MS, во-вторых, я в здравом уме и твердой памяти не стану ограничивать SQL серверу память и ядра ради SSIS. Просто потому, что общая производительность тогда точно упадет. А если не ограничивать в памяти SQL Server, то можно получить проблемы в SSIS из-за нехватки памяти ему.
Что касается установки SSIS на Linux, то это доступно пока скорее только для ознакомления. Слишком много ограничений для продуктивного использования.
Во-первых, ни я, ни видимо Jovanny не заметили, чтобы вы хоть что-то сказали в статье про SSIS. Возможно, это было в форме стеганографии.
Во-вторых, посмотрите исходники /src/backend/commands/copyto.c. Где вы там увидели, что COPY — это в чём-то быстрее чем обычный Sequential scan SELECTа?
В-третьих, никаких текстовых файлов там и в помине нет. Вы, видимо, не понимаете разницы между SqlBulkCopy и BULK INSERT. В качестве примера набросал простой C# скрипт использующий аналогичный подход.
using var connection = new NpgsqlConnection("…pg connection…");
connection.Open();
using var command = new NpgsqlCommand("test_tbl", connection);
command.CommandType = CommandType.TableDirect;
using var bulk = new SqlBulkCopy("…mssql connection…", SqlBulkCopyOptions.TableLock);
bulk.EnableStreaming = true;
bulk.DestinationTableName = "test_tbl";
bulk.BatchSize = 1000000;
bulk.BulkCopyTimeout = 0;
var watch = new Stopwatch();
watch.Start();
{
using var reader = command.ExecuteReader();
bulk.WriteToServer(reader);
}
watch.Stop();
Console.WriteLine(watch.Elapsed);Ни одного слова на SQL. И никаких "BULK INSERT" и этого самого SQL в профайлере. Чистый бинарный стриминг без промежуточных артефактов. Из физической таблицы в физическую перетекало где-то за 0.57+-0.02с. При этом, он работает гораздо более деликатно чем bcp.
В качестве референса, ваш EXEC с прилинкованной таблицей (тот который исполнялся CPU time = 8187 ms, elapsed time = 14793 ms) у меня даёт CPU time = 11343 ms, elapsed time = 16634 ms. То есть в целом сетап виртуалки медленнее, работает с IO на обоих концах, но делает это быстрее чем ваш вариант. При этом без всего этого колхоза с паролями и с понятным поведением, на тот случай если в таблице будет Unicode. Как вы собираетесь сращивать COPY TO, который не понимает utf-16 с bcp, который не понимает utf-8 мне пока не очень понятно. Возможно есть какой-то трюк, о котором я пока не знаю.
В-четвертых, у меня такое ощущение, что вы не понимаете разницы между SSIS и SSIS Server. SSIS — это небольшой тулкит, который идёт непосредственно с SQL Server, утилита dtexec для запуска трансформаций лежит в папочке DTS\Binn. Ведь даже простые экспорты/импорты SSMS, например, делает через SSIS. Что я, собственно, и сделал. Настроил импорт в SSMS из ODBC, сохранил в dtsx, и вызвал в консоли
PS:/> DTExec.exe /File copy.dtsx
DTExec: The package execution returned DTSER_SUCCESS (0).
Elapsed: 2.328 seconds
Для любопытства поднял размеры исходной таблицы в x10 и x100 раз.
Elapsed: 18.657 seconds
Elapsed: 179.016 seconds
И это без какого-либо тюнинга, то есть дефолтные 10K записей на батч, 3M буфер, не выключенные констрейнты, импорт в нормальную таблицу, а не временную (то есть база с Recovery Model full, а не simple как у tempdb), импорт из нормальной таблицы, а не SELECT'а, драйвер был выбран Unicode, маппинги толком не настроены. В общем где вы увидели доминирование bcp над SSIS — не понятно.
В пятых, не нашёл никаких упоминаний на https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-ssis?view=sql-server-linux-2017, что это какой-то экспериментальный билд. Собственно говоря, dtexec там с тех же времен, что и bcp.
Где вы там увидели, что COPY — это в чём-то быстрее чем обычный Sequential scan SELECTа?
COPY вообще никаких данных не передает клиенту, а SELECT — передает. При любых раскладах передача данных через TCP/IP будет медленней, чем запись в кеш, где данные и останутся при использовании ramfs, вплоть до удаления файла.
В качестве референса, ваш EXEC с прилинкованной таблицей (тот который исполнялся CPU time = 8187 ms, elapsed time = 14793 ms) у меня даёт CPU time = 11343 ms, elapsed time = 16634 ms. То есть в целом сетап виртуалки медленнее, работает с IO на обоих концах, но делает это быстрее чем ваш вариант.
Зачем брать в качестве референса пример, который не устраивал? В решении было CPU time = 0 ms, elapsed time = 881 ms.
Почему для сравнения Вы берете вообще неизвестно какую таблицу с неизвестным количеством строк, а не пример из статьи? Что с чем сравниваете?
Elapsed: 2.328 seconds
Это в разы медленней, чем 881 ms.
какой-то экспериментальный билд
Вы невнимательно читаете, то что Вам пишет оппонент.
Слишком много ограничений для продуктивного использования.
Сами почитайте.
Ни Kerberos авторизации, ни сторонних компонентов, ни каталога.
Просто модифицируйте свой код на C# так, чтобы он выполнял ровно ту же задачу, что описана в статье. Тогда сразу увидите, что он медленней варианта, предложенного в решении.
COPY вообще никаких данных не передает клиенту, а SELECT — передает.
Сам SELECT ничего не передает. Можете код посмотреть, а можете поспекулировать с командами вроде SELECT INTO.
При любых раскладах передача данных через TCP/IP будет медленней, чем запись в кеш, где данные и останутся при использовании ramfs, вплоть до удаления файла.
Конечно медленней (RDMA и мелланоксы не будем трогать). А вам рассказать, по какому протоколу bcp будет передавать данные на @@SERVERNAME из вашего примера? Только перед тем, как отправить всё по тому же TCP/IP вы решили ещё добавить сериализацию в текст (это когда timestamp, например, начинает занимать в 3 раза больше места) и десериализацию из него.
И в итоге создали узкое место — память. Вы целиком сериализуете всю таблицу и засовываете в ramfs, скармливаете батч размером 10M записей bcp, который также вынужен всё это буферизовать. Расскажите, плиз, что произойдёт когда таблица будет не мизерной как в вашем примере, а "относительно большой"?
Зачем брать в качестве референса пример, который не устраивал?
Вообще то вы его взяли в качестве референса и сравнили с ним свою bcp-реализацию. Я взял его же в качестве референса и предложил альтернативный подход, дав данные для нормализации по референсному варианту. У вас сложности с проведением простой арифметической операции? Хотите я вам помогу?
Почему для сравнения Вы берете вообще неизвестно какую таблицу с неизвестным количеством строк, а не пример из статьи? Что с чем сравниваете?
Не неизвестно какую, а SELECT INTO из вашего generate_series, один-в-один. Это вы почему-то используете в задаче передачи данных суррогаты (с кардинально отличающимся EXPLAIN), я же делал приближённый к реальности вариант.
Это в разы медленней, чем 881 ms.
Мда… из всего сказанного вы заметили только числовое значение. Давайте проведём батл в AWS/Azure/etc по копированию терабайтной таблицы с текстом в Unicode. Когда ваш вариант уйдёт на дно в первые же секунды, а SSIS всё быстро перекачает.
какой-то экспериментальный билд.
С вашим вариантом я бы не сильно привередничал. Покажите его любому DBA, у него глаза будут кровоточить. Мой же "экспериментальный" хорошо документирован у MS (https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy), где проводятся и параллели с bcp.
Вы невнимательно читаете, то что Вам пишет оппонент.
Слишком много ограничений для продуктивного использования.
Сами почитайте.
Ни Kerberos авторизации, ни сторонних компонентов, ни каталога.
Я очень внимательно читаю, просто вы пишете весьма странные вещи. Я вам говорю о простой консольной утилите dtexec, которую можно использовать вместо bcp. А вы мне про отсутствие поддержки Kerberos и даёте ссылку на статью, что де Hadoop с HDFS не поддерживаются. И одновременно сами сетуете:
Причем, bcp для Linux до сих пор не умеет авторизоваться через Kerberos. Поэтому использовать его можно только указывая кредентиалы в командной строке.
То есть он у вас для "продуктивного использования подошёл", а к другой консольной утилите повышенные требования?
Просто модифицируйте свой код на C# так, чтобы он выполнял ровно ту же задачу, что описана в статье. Тогда сразу увидите, что он медленней варианта, предложенного в решении.
Что именно эта утилита не делает? Он быстро и эффективно передаёт таблицы любых размеров, с поддержкой Unicode, с поддержкой того же Kerberos. Там где ваше решение упадёт, это продолжить работать. Или суть именно в этом, что оно должно упасть?
Сам SELECT ничего не передает.
В Вашем случае при вызове SqlBulkCopy? Ну тогда Вы вообще не понимаете, как он работает. Почитайте внимательней то, что я писал выше.
Расскажите, плиз, что произойдёт когда таблица будет не мизерной как в вашем примере, а «относительно большой»?
На данный момент в один присест передается не более 10 миллионов записей. Примерно по килобайту в текстовом представлении. Судя по тому, что я вижу:
KiB Mem: 26397264+total, 10538076 free, 70718568 used, 18271600+buff/cache
KiB Swap: 16777212 total, 16496636 free, 280576 used. 19215897+avail Mem
Запас у меня больше, чем на порядок. При меньшем запасе переключился бы с ramfs на tmpfs. Разница в их производительности — 1-2%. Несущественно.
То есть он у вас для «продуктивного использования подошёл», а к другой консольной утилите повышенные требования?Да. Это подробно было описано в статье. Невнимательно читали. Используемый SQL Server аккаунт не имеет доступа ни к одной БД, кроме tempdb. Понятно, что с такими ограничениями SSIS вообще смысла не имеет.
Там где ваше решение упадёт, это продолжить работать.С чего Вы решили, что оно упадет? bcp под Linux замечательно UTF-8 кушает.
В Вашем случае при вызове SqlBulkCopy? Ну тогда Вы вообще не понимаете, как он работает. Почитайте внимательней то, что я писал выше.
Я то как раз понимаю. Поэтому в примере и стоит TableDirect. Который в зависимости от провайдера может реализовываться по-разному. Поскольку передаёт данные протокол, а не SELECT. Я могу коннектится к No-SQL базе, я могу использовать LOB-протокол в постгресе, где не будет никаких SELECT вообще. Те же протоколы логической репликации или LISTEN/NOTIFY. Данные есть, а SELECT'ов нет. Так что у вас очень базовый, примитивный взгляд на предмет.
Запас у меня больше, чем на порядок. При меньшем запасе переключился бы с ramfs на tmpfs. Разница в их производительности — 1-2%. Несущественно.
Я вообще не понимаю, зачем использовать древний rafms, когда tempfs=ramfs+swap+куча фич. Но вот когда заканчивается ram, то картина сильно меняется. Может, конечно, в вашем мире большие данные — это 366K записей по 12 байт, что еле-еле переваливает за 4М. В моей практике это хотя бы несколько терабайт на ноду. Но уж точно это не тот объём который помещается в RAM. Замените в своём generate_series 1K на 10K, 100K, 1000K и опубликуйте результаты, как я это сделал для dtexec. Посмотрим на динамику. Делов-то, несколько ноликов добавить.
Да. Это подробно было описано в статье. Невнимательно читали.
Опять вы со своей песней. Где там в вашей статьей, что dtexec нельзя использовать как простую CLI как и bcp, дав ей те же реквизиты и tempdb, как и bcp, потому что… добавьте цитату из вашей статьи.
bcp под Linux замечательно UTF-8 кушает.
Да, мой косяк, забыл что с 2016 начал поддерживать.
Может, конечно, в вашем мире большие данные — это 366K записей по 12 байт, что еле-еле переваливает за 4М.
Давайте закончим. Вы вообще не читаете то, что я пишу:
На данный момент в один присест передается не более 10 миллионов записей. Примерно по килобайту в текстовом представлении. Судя по тому, что я вижу:
KiB Mem: 26397264+total, 10538076 free, 70718568 used, 18271600+buff/cache
KiB Swap: 16777212 total, 16496636 free, 280576 used. 19215897+avail Mem
Где там в вашей статьей, что dtexec нельзя использовать как простую CLI как и bcp, дав ей те же реквизиты и tempdb, как и bcp, потому что…
Давать права пользователю, кредентиалы которого открытым текстом видны в SQL запросе, на таблицы своей БД совершенно не хочется. Тем более на запись. Поэтому остается только вариант с глобальной временной таблицей.
Принципиальной разницы между «в SQL запросе» или «в командной строке» я не вижу.
Что толку от dtexec, если он может только писать и только в tempdb? Уже не знаю который раз говорю: не поддерживается в нем Kerberos под Linux от слова совсем!
Давайте закончим. Вы вообще не читаете то, что я пишу:
Знакомая песня. Может раз вам столько раз приходится многим об этом говорить, стоит обратить внимание на то, что и как вы пишете.
Вы видели в каком-нибудь бенчмарке тезисы вроде
На данный момент в один присест передается не более 10 миллионов записей. Примерно по килобайту в текстовом представлении.
Не более 10М — это сколько? Одна, две, сто? Так что я черпаю информацию исключительно из ваших слов, а точнее вашего примера, который выдаст 366К записей с "width 12".
Давать права пользователю, кредентиалы которого открытым текстом видны в SQL запросе, на таблицы своей БД совершенно не хочется. Тем более на запись. Поэтому остается только вариант с глобальной временной таблицей.
Пошли по кругу. Я этот абзац читал и поскольку он распространяется и на dtexec и на bcp поинтересовался, почему у вас система двойных стандартов. Ведь dtexec обладает теми же косяками, что bcp, но гораздо большими возможностями. Почему вы ему даже шанса не дали?
Мне казалось, вопросы кодирования экспорта-импорта данных давно ушли в прошлое, что есть целая куча тулзов для этого. Но я ошибался.
Поэтому задам вопрос.
А почему, например, не использовать SQL Server Integration Services?
www.cdata.com/kb/tech/postgresql-ssis-task-import-2008.rst
www.devart.com/ssis/postgresql.html
zappysys.com/blog/ssis-load-postgresql-table-data-csv-file/?gclid=Cj0KCQjwvYSEBhDjARIsAJMn0lj7HWz_EHYWouExV9OZ8uObrhrXXlL3byC154X3xtAhKsutMUgIKgQaAgMcEALw_wcB
SSIS медленней, чем описанный в статье способ.
Все равно в нем Вы будете делать те же шаги: экспортировать данные из PostgreSQL в текстовый файл и загружать их BULK INSERT из этого текстового файла в MS SQL. Но передавать по сети данные будете дважды (SSIS — выделенный хост).
Читайте:
To use the Bulk Insert task to transfer data from other database management systems (DBMSs), you must export the data from the source to a text file and then import the data from the text file into a SQL Server table or view.
Ключевая фраза
To use the Bulk Insert task to transfer data— т.е. если вы почему-то хотите использовать именно Bulk Insert Task, а не какой-то другой компонент.
А если вы хотите использовать Data Flow Task, то в качестве назначения вы можете использовать:
- ADO.NET Destination с опцией UseBulkInsertWhenPossible, которая реализуется через класс SqlBulkCopy
- OLE DB Destination с опцией Fast Load, которая использует BULK INSERT
И не надо никаких текстовых файлов .
хотите использовать именно Bulk Insert TaskЕстественно. Уже на сотнях тысяч записей он в разы быстрее любой другой альтернативы.
И не надо никаких текстовых файлов.BULK INSERT физически не может быть не из файла. Просто по его определению.
Поймите простую вещь. Никакие сервисы или классы все равно не могут использовать какие-то секретные SQL команды. Они при общении с SQL сервером так же ограничены только теми командами, которые доступны пользователю при прямом общении с SQL сервером. И если в их реализации использование временного файла скрыто от пользователя, из этого совершенно не следует, что он не используется.
– Видишь суслика?
– Нет.
– Вот и я не вижу. А он есть.
Поймите простую вещь. Никакие сервисы или классы все равно не могут использовать какие-то секретные SQL команды.
Так это вы нам рассказываете, про секретные возможности bcp. Хотя те же самые пакеты 0x7 (Bulk Load), с которыми работает он, использует и SSIS. И они прекрасно документированы в спецификации протокола TDS. И с какой стати должна возникнуть разница, а также в какой момент bcp получил монополию, — пока не объяснили.
Я утверждаю только, что если SSIS или еще что-то использует BULK LOAD, то без файла ему не обойтись.
Я понял, вы вообще не в курсе как оно работает. Так сфокусировались на BULK INSERT, что не удосужились узнать про INSERT BULK.
Да да, если посмотреть чуть внимательнее, есть и такая команда. А заглянув в спецификацию TDS, указание на которую вы проигнорировали, в п.2.2.6.1 узнаете, что есть такой Bulk Load BCP, начинающийся с команды "INSERT BULK", за которым идут метаданные и строки в виде raw data (таком же, как идут в протокол из SELECT).
Вы не можете вызвать эту команду из sqlcmd или какой-нибудь IDE, но она есть и активно используется в BCP, SSIS и т.п. Там нет никаких промежуточных файлов, можно заливать данные прямо в TDS без посредников. Вот пример как это используется в SqlBulkCopy, которую я задействовал в скрипте.
не удосужились узнать про INSERT BULK.
Вы заголовок статьи хотя бы читали? Источник — PostgreSQL. Как он Вам данные в бинарном потоке для MS SQL предоставит?
Или Вы даже не читали статью по Вашей же ссылке?
BULK
Applies to: SQL Server 2008 and later.
Used by external tools to upload a binary data stream. This option is not intended for use with tools such as SQL Server Management Studio, SQLCMD, OSQL, or data access application programming interfaces such as SQL Server Native Client.
Перечитайте внимательно статью:
To use the Bulk Insert task to transfer data from other database management systems (DBMSs), you must export the data from the source to a text file and then import the data from the text file into a SQL Server table or view.
Вы заголовок статьи хотя бы читали? Источник — PostgreSQL. Как он Вам данные в бинарном потоке для MS SQL предоставит?
Я не только прочитал, а предоставил вам и код, и инструкции, как реализовать обмен именно между Postgres и MSSQL. В .NET бинарный обмен идёт через абстракцию DbDataReader. У SSIS таких абстракций из коробки побольше и маппинги получше. Естественно он не передаёт бинарный поток напрямую, но он не будет конвертировать interger из Postgres в string, добавлять к нему разделитель, что бы потом распарсить его обратно в int SQL Server'а.
Или Вы даже не читали статью по Вашей же ссылке?
Used by external tools to upload a binary data stream. This option is not intended for use with tools such as SQL Server Management Studio, SQLCMD, OSQL, or data access application programming interfaces such as SQL Server Native Client.
Вы же понимаете, что процитированное вами я уже написал ранее:
Вы не можете вызвать эту команду из sqlcmd или какой-нибудь IDE, но она есть и активно используется в BCP, SSIS и т.п. Там нет никаких промежуточных файлов, можно заливать данные прямо в TDS без посредников.
Упоминая тот же sqlcmd, под IDE подразумевая SSMS, DataGrip и т.п., а "binary data stream" озвучив как "заливать данные прямо в TDS". Только я не могу понять, в чём загвоздка. Ваш bcp делает тоже самое. Только гоняет через текстовый формат, реконвертируя каждое значение
Перечитайте внимательно статью:
Зачем мне её перечитывать, если это ваша мания везде видеть BULK INSERT? Я его не использую. Заодно, чтобы укрепить свою уверенность, что bcp работает через BULK INSERT, попробуйте погонять его с опцией -n, когда данные идут в native-режиме, а не через csv/tsv-серилизацию. Я бы ещё предложил посмотреть профайлером и сниффером, но вряд ли у вас получится.
Вы просто логически задумайтесь над своими же определениями. Утилита bcp у вас работает на постгрес-сервере, использует под капотом BULK INSERT. А какой сервер выполняет этот BULK INSERT? Определённо @@SERVERNAME, другого просто нет. Но BULK INSERT может читать только из файла. А где этот файл на @@SERVERNAME? И в итоге вся ваша схема не сходится.
Я бы предложил от боевых действий и попытки защитить свои позиции любой ценой перейти к конструктивным. Найти реально эффективное решение. Со своей стороны стараюсь вам подкидывать конкретные варианты для экспериментов, с вашей… осталось решить как этим воспользоваться))
При любом раскладе, SSIS нужно данные с PostgreSQL как-то получить. В родном для PostgreSQL бинарном виде он тоже с ними ничего не сделает. Все равно предварительно драйвером будет выполнено преобразование в какой-то промежуточный формат. Даже если он данные засосет только в память, без создания временного файла, в любом случае, этот процесс будет медленней, чем когда PostgreSQL локально командой COPY пишет данные в ramfs. Так как на этом этапе вообще нет никакой сетевой активности. Исключительно пересылка память-память.
А раз мы начали с того, что BCP так же использует INSERT BULK, то на этом этапе разницы не заметим. Он так же локально из памяти (ramfs) возьмет исходные данные и перепакует их в двоичный поток.
Так как на этом этапе вообще нет никакой сетевой активности. Исключительно пересылка память-память.
И где это сказано? Некоторый DB-клиенты умеют видя localhost переходить на shared memory и другие IPC. COPY TO PROGRAM — это не просто память-память. Это пайп, файловая система, ACL и сериализация. Если TCP/IP — это порядка 80/1500 оверхеда, то сериализованные данные — это может быть в разы. Я не зря упоминал исходник, там не shared memory, там OpenPipeStream.
Я не говорю, что идея в корне плоха. Вполне может быть, что в Linux localhost TCP гораздо медленнее пайпов. Я знаю, что так было когда-то давно, но не следил, насколько что-либо изменилось. Для меня логичным выглядит взять и сравнить. Но с реальными конкурентами. А вы взяли самое медленное решение и на все конструктивные предложения сыпятся обвинения, что де вас не читают.
- Я как раз постил реальный код и замеры, так что с "теоретическими умозаключениями" мимо.
- Вам несколько человек предложили попробовать SSIS, тем более у вас тестовый стенд под рукой. Делов было на несколько минут. Но вместо простой манипуляции развелили демагогию с последующим подгоранием про троллинг.
- Мда, результат бенчмаркинга, как и всё у вас, просто выше всяких похвал. Что меряли, как меряли. Видимо, придётся самому нормально делать.
2. У меня не было в тот момент тестового стенда под рукой. Был только 32-х битный. А он тупо падал на таких объемах. После того, как было уже выяснено, что проблема именно в ODBC PostrgeSQL, смысла в этом не было. Ведь SSIS через этот же ODBC пойдет.
3. Приложенного DTS Вам мало?
Что именно в случае dtexec мне надо было разжевать? Какие кнопочки в визарде SSMS нажимать, чтобы получить dtsx, дублировать сюда рекомендации по настройке? А код для запуска я вам дал, в своём же бенче dtexec. Плюс дал код для эксперимента с SqlBulkCopy, который давал лучшие результаты. Но с ним вы не справились. Ведь есть риск, что не получится испортить настолько, чтобы тайминг оказался хуже вашего god-варианта.
Тестовый стенд — это тот, на котором вы проводили свои тесты. Поскольку они у вас вообще не соответствуют правилам хорошего бенчмаркинга, оставалось следовать хотя бы одному правилу — тестировать всё в одинаковых условиях. Вы начали эти тесты и логичней сравнивать другие варианты запуская в вашей же среде. Тем более, я то уже тесты провел и на более медленном сетапе скорости намного выше.
Достаточно, спасибо. Даже поверхностный просмотр говорит, что зачем-то вместо SqlNative драйвера вы стали использовать OLEDB, соединение с Postgres вообще за DNS не видно, bcp вы настраивали и сделали батч на 10М записей и сравниваете с dtexec у которого по дефолту батч в 1000 раз меньше, буфер стоит 3М, MaxConcurrentExecutables/EngineThreads не использовались от слова вообще, запускали как я понимаю на Windows. Предвосхищаю, что сейчас начнётся демагогия, мол я должен был все настройки для вашей задачи и вашей же тестовой среды подготовить. Ведь это я претендую на статью, которая громогласно заявляет о том, что нашёл самое быстрое решение. И сразу с этим соглашусь. Да, кто-то должен провести нормальные тесты.
А код для запуска я вам дал, в своём же бенче dtexec.
В упор не вижу ни в одном Вашем комментарии dtsx, которым проводилось тестирование. Если у меня глючит браузер, дайте ссылку на комментарий с полным текстом dtsx, который можно скопировать и проверить.
как я понимаю на Windows
Мне уже третий раз приходится Вам писать, что на Linux dtsexec использовать не могу по той простой причине, что Kerberos он там не поддерживает, а кредентиалы, указываемые открытым текстом использовать нельзя, так как писать приходится уже не во временную таблицу, а постоянную.
батч в 1000 раз меньше, буфер стоит 3МЯ тоже об этом писал. Если PostgreSQL использует системный кеш и памяти на его сервере доступно ~100ГБ, то MS SQL использует свой встроенный кеш. Поэтому свободной памяти на сервере с MS SQL — кот наплакал. А отбирать у сервера память для того, чтобы она использовалась раз в час SSIS я не намерен. Потому что это уже будет в ущерб производительности сервера постоянно, а не только в момент интеграции. Я и так заставил dtexeс не падать по нехватке памяти только временно остановив SQL Server Agent.
Если же тащить данные по сети не один, а два раза (используя третий хост), то смысл сравнения с BCP точно пропадает.
вместо SqlNative
С каких пор SQLNCLI11 перестал быть SqlNative?
соединение с Postgres вообще за DNS
Писал неоднократно. ODBC настроены по умолчанию. Что еще Вы хотите там увидеть?
Плюс дал код для эксперимента с SqlBulkCopy
Так мы о SSIS/dtexec речь ведем, или о какой-то внешней программе на C#? Просто если SSIS package вполне можно вызвать из T-SQL, то запихивать такое в CLR функцию мне не кажется хорошей идеей. В любом случае, на том же стенде я такое точно делать не буду. Если же речь опять о двойном копировании по сети через третий хост, то смысла что-то тестировать уже нет.
При чем тут Data Flow Task. если Bulk Insert Task существенно производительней? Название статьи помните?
Мне кажется вы сами забыли название своей статьи, которое звучит как "Как быстрее всего передавать данные". Не вставлять, а передавать. А это и есть Dataflow. У которого под капотом может быть как bulk insert, так и bulk copy, который ещё быстрее чем bulk insert.
bulk copyНет такой команды в MS SQL. Класс SqlBulkCopy использует тот же самый BULK INSERT и более производительной возможности MS SQL Server не предоставляет.
Или Вы искреннее считаете, что класс SqlBulkCopy использует при общении с SQL сервером какие-то секретные недокументированные возможности?
Не для спора, а так, просто почитать
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/bulk-copy-operations-in-sql-server
Microsoft SQL Server includes a popular command-line utility named bcp for quickly bulk copying large files into tables or views in SQL Server databases. The SqlBulkCopy class allows you to write managed code solutions that provide similar functionality. There are other ways to load data into a SQL Server table (INSERT statements, for example) but SqlBulkCopy offers a significant performance advantage over them.
The SqlBulkCopy class can be used to write data only to SQL Server tables. But the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.
that provide similar functionality
Это говорит о том, что это лишь обертка над BULK INSERT. Естественно, ничто не мешает классу использовать временные файлы для того, чтобы сначала из любого источника в этот файл данные записать, а потом, при помощи BULK INSERT эти данные вставить.
Но при этом, хоть в лоб, хоть по лбю, временный файл точно медленней, чем ramfs, где данные просто остаются в памяти (кеше) до тех пор, пока файл не будет удален.
Не верите — просто включите профайлер на SQL Server и убедитесь.
Не верите — просто включите профайлер на SQL Server и убедитесь.
Вы так часто упрекаете других в невнимательном чтении, но сами не очень следуете своим стандартам. Я вам выше уже говорил, что включал профайлер. Там нет никаких BULK INSERT. И в коде SqlBulkCopy тоже нет никаких BULK INSERT.
Но принципиально это ничего не меняет.
Как только Вы попробуете взять данные из PostgreSQL не COPY в текстовый файл, сразу же нарветесь на ту же самую проблему, с которой начиналась статья. А именно с тормозного родного ODBC.
Зачем искать, если я прямо ссылку на строчку давал с INSERT BULK))
Как только Вы попробуете взять данные из PostgreSQL не COPY в текстовый файл, сразу же нарветесь на ту же самую проблему, с которой начиналась статья. А именно с тормозного родного ODBC.
Есть такая проблема. В основном она у него связана с хэндшейками, причем будь то ODBC или managed драйвер — всё одно. Поэтому популярны всякие pgpool. На примере тех же бенчмарков с dtexec выше, если на 366K строк он давал 2.3с, то x10 уже выходило в районе 1.8с (x1), x100 — 1.7. Оверхед на старт. Аналогично с прилинкованным сервером. Дёргаешь таблицу на 100К — 7 секунд. На 1 строку — 6.5. В идеале наверно маленький демон с SqlBulkCopy + LISTEN/NOTIFY))
Вы либо не понимаете термина "троллинг", либо просто у вас подгорело от того, что плохо разбираетесь в технической стороне вопроса. Скатиться до того, чтобы называть троллингом сугубо техническую информацию, когда я вам разжевал в деталях как оно работает? Даже если бы выяснилось, что я плохо себя представляю плачевность ситуации с ODBC-драйвером, скатываться в технической дискуссии до такого… Да вы, сударь, свинья.
Выложите где-нибудь dtsx, вывод dtexec и настройки postgres. Доверия вашим тестам — ноль. Я перепроверю.
Настройки ODBC по умолчанию.
Проверять ничего не надо. Зачем Вам залазить в дерьмо и валяться в грязи общаясь со свиньей?
Я перешёл по ссылке в почте, которая отправляет сразу на комментарий. В тексте оного не было никакого намека на то, что статья обновлена. Да и обновлена она в худших традициях хабра. Здесь принято делать update-секции, а не фальсифицировать исходный текст. Мимо. Но можем свалить вину и на меня, я не против, по формальным признакам вы со всех сторон молодец.
"Длительные теоретичесие рассуждения"? Первое, что я сделал — провел тесты со своей стороны и опубликовал полученные тайминги как по dtexec, так по SqlBulkCopy. С учётом нормализации последний был быстрее вашего решения, первый — чуть медленнее, но я его вообще не настраивал. Что мне SSMS сгенерировал, то и воткнул, чтобы сформировать общую оценку. Мимо.
А вот в ответа на бенчи с мой стороны у вас полыхнуло и понеслись "длительные теоретические рассуждения". Мой самый-самый первый комментарий вам содержал удивление, что вы "не пробовали запустить dtexec CLI" и почти каждое сообщение пропитано идеей, что проще протестировать, чем болтать языком. Разжевал всё о dtexec. Что это, где лежит, как использовать. В итоге после длительного треда c вашей теоретической демагогией удалось таки прижать к стенке, но тут вы "мастерски" выкрутились… Мол герой что запустили, назло этому троллю-теоретизатору. Молодец))
На хабре ещё теплилась идея конструктивной дискуссии. И я подумал, что сообща разберёмся, почему в вашем случае SSIS работает намного медленнее чем у меня, может ли быть SqlBulkCopy и какие-то ещё способы быстрее и т.п. Но с вами это не получилось, так что да, смысла в дальнейшей полемике не вижу.
Поэтому бенчи я оформлю нормально и отдельной статьей. Мне как раз скоро надо несколько терабайт скоро перегнать, правда в обратном направлении и акцент на columnar, но затрону оба направления, в том числе попробую и ваш подход. Без иронии вам за него спасибо. У меня в голове он уже пробудил план набросать утилиту, которая будет использовать SqlBulkCopy, но в абстракцию IDataReader будет завернут пайп от постгреса, напрямую, без FS-посредников. А также попробовать какой-нибудь PL-язык, скажем Pl/Python + SQLAlchemy. Дата-саентисты часто вставляют гигантские DataFrame в самые разные СУБД, наверняка что-то должно быть.
Нет такой команды в MS SQL
Документацию прочтите. И маленький хинт для этого. T-SQL — это не весь MSSQL.
Как быстрее всего передавать данные с PostgreSQL на MS SQL