Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
Dzianis
Member

Откуда:
Сообщений: 76
Есть ли альтернатива утилите BCP по перегону таблиц в файлы?

В задаче для внешней системы приходится ежесуточно выгружать данные из таблиц в текстовые файлы для внешней системы.
Задача работает уже лет 5. Некоторые таблицы за это время разрослись до 60-90 млн записей.
Выгрузка идет с помощью BCP
По монитору производительности вижу, что запись в текстовый файл идет со скоростью 7-10 мбайт в секунду. Не выше. При этом чтение файлов данных прыгает до 70-120 мбайт, потом прекращается.
возможности дисковой системы до 150 мбайт в легкую. Ощущение что сервер быстро читает следующую часть данных, а утилита медленно пишет данные в тестовый файл.

сервер sql2014.
Таблицы выгружаются в чистом виде (то есть не результат джойна или фильтра). Памяти достаточно.

Есть ли более быстрые утилиты по экспорту?
3 июн 17, 19:22    [20537197]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Dzianis
Есть ли более быстрые утилиты по экспорту?
Быстрее bcp не найдёте.

А как выгружается, там просто выгрузка из таблицы, или сложный запрос? Сортировки есть? Затыка не видите при записи в файл - например, большая очередь к диску, большие задержки?
3 июн 17, 20:19    [20537266]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
Dzianis
Member

Откуда:
Сообщений: 76
alexeyvg

А как выгружается, там просто выгрузка из таблицы, или сложный запрос? Сортировки есть? Затыка не видите при записи в файл - например, большая очередь к диску, большие задержки?


Просто выгрузка большой таблицы без сортировки.
максимум это будут конвертации полей, допустим nvarchar в varchar или datetime в date.

С очередью диска всё ок. Часть времени выгрузка идет в те моменты, когда сервер больше ничем и не занят.
при этом, файлы базы данных на одном контролере. Каталог выгрузки на другом.
3 июн 17, 20:35    [20537293]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
Dzianis
Member

Откуда:
Сообщений: 76
если не смогу найти альтернативы, то на следующей неделе попробую просто запускать несколько экземпляров BCP для выгрузки разных частей таблицы с последующей текстовой склейкой.
3 июн 17, 20:45    [20537303]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Dzianis
Просто выгрузка большой таблицы без сортировки.
максимум это будут конвертации полей, допустим nvarchar в varchar или datetime в date.

С очередью диска всё ок. Часть времени выгрузка идет в те моменты, когда сервер больше ничем и не занят.
при этом, файлы базы данных на одном контролере. Каталог выгрузки на другом.
Посмотрел - у меня скорость на такой же операции 11 мб/сек. Посмотрю в понедельник, какая там загрузка дисков...
4 июн 17, 02:18    [20537619]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
Dzianis
Member

Откуда:
Сообщений: 76
alexeyvg
Посмотрел - у меня скорость на такой же операции 11 мб/сек. Посмотрю в понедельник, какая там загрузка дисков...


Примерно о том и речь. 11 мб/сек по странному маловато.
4 июн 17, 08:57    [20537701]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Dzianis
alexeyvg
Посмотрел - у меня скорость на такой же операции 11 мб/сек. Посмотрю в понедельник, какая там загрузка дисков...
Примерно о том и речь. 11 мб/сек по странному маловато.
Посмотрел подробнее - запись в файл 11 мб/сек, чтение из файла данных базы 5 мб,сек, очередь к диску 0.03-0.1, BCP загружает процессор на 2%
4 июн 17, 10:06    [20537743]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
Rankatan
Member

Откуда:
Сообщений: 250
Ssis как альтернатива bcp
4 июн 17, 10:58    [20537767]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
aleks2
Guest
Rankatan
Ssis как альтернатива bcp

Бред. SSIS - монстр.
А монстры быстрыми не бывают.

ЗЫ. Тредстартеру. Есть FileTable - сам экспортируй. Если такой прыткий.

ЗЗЫ. Тока дурни выгружают все. Умные выгружают тока новое.
4 июн 17, 11:01    [20537773]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
Dzianis
Member

Откуда:
Сообщений: 76
aleks2
ЗЗЫ. Тока дурни выгружают все. Умные выгружают тока новое.


спасибо кэп )
есть бизнес процесс, работающий кучу лет.
Так как поломать принципиальную схему за пару дней не получится, то вот пока решаю озвученную задачу.
4 июн 17, 12:16    [20537826]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
архивариус
Member

Откуда:
Сообщений: 150
1. If your I/O system is fast, consider using multiple bulk insert operations in parallel. A single bulk operation will only fully utilize one CPU core. As we will see in the other scenarios, utilizing multiple, concurrent bulk streams is the key to scaling bulk loading.
2. тамже пишут SSIS быстрее чем bcp
3.
Optimizing Bulk Import Performance
http://msdn.microsoft.com/en-us/library/ms190421(v=sql.105).aspx

The Data Loading Performance Guide
http://technet.microsoft.com/en-us/library/dd425070(SQL.100).aspx

We Loaded 1TB in 30 Minutes with SSIS, and So Can You
http://msdn.microsoft.com/en-us/library/dd537533(v=sql.100).aspx

К сообщению приложен файл. Размер - 5Kb
4 июн 17, 15:11    [20537985]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Rankatan
Ssis как альтернатива bcp
Я пробовал разные варианты, быстрее не получается.

архивариус,
Спасибо, хорошая подборка.
Я подозревал, что дело в одном потоке, но как то это странно, ни одно ядро одним BCP не загружается хотя бы процентов на 20.
Неоптимальненько :-(
Попробую выгружать в N потоков... Хотя конечно геморройно, лучше бы это сделали разработчики BCP :-)
Мне вот только критерий разделения неудобно делать, буду шаманить...
4 июн 17, 19:01    [20538234]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7780
Скорее всего, ограничение на один поток. Выгрузка в несколько поможет.
Особенно жестко идет ограничение на виртуалке.
5 июн 17, 11:19    [20539507]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Попробовал запустить в 8 потоков.
В одном потоке выгружалось 11 мб/сек, в 8 выгружается по 600-800 кб/сек на поток, то есть общая скорость упала.
Очередь к диску возросла с 0.1 до 2
:-(
5 июн 17, 15:32    [20540682]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7780
alexeyvg,

А сколько ядер задействовано? Впечатление, что через одно все потоки пытается пропихнуть.
5 июн 17, 16:32    [20540916]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
aleks2
Guest
alexeyvg
Попробовал запустить в 8 потоков.
В одном потоке выгружалось 11 мб/сек, в 8 выгружается по 600-800 кб/сек на поток, то есть общая скорость упала.
Очередь к диску возросла с 0.1 до 2
:-(


1. Из одной таблицы?
2. Через TCP/IP или shared memory?
3. На один и тот же диск все 8 потоков?
5 июн 17, 17:51    [20541160]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36989
alexeyvg
Попробовал запустить в 8 потоков.
В одном потоке выгружалось 11 мб/сек, в 8 выгружается по 600-800 кб/сек на поток, то есть общая скорость упала.
Очередь к диску возросла с 0.1 до 2
:-(
Параллелить имеет смысл только если один поток и производительность одного ядра являетются узким местом.
5 июн 17, 17:54    [20541164]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Владислав Колосов
А сколько ядер задействовано? Впечатление, что через одно все потоки пытается пропихнуть.
В смысле? Запустил 8 процессов Windows с bcp. Наверное, он их нормально распределяет по сокетам и ядрам.

aleks2
1. Из одной таблицы?
2. Через TCP/IP или shared memory?
3. На один и тот же диск все 8 потоков?

1. Да.
2. Не знаю, как там bsc работает... Думаю, через shared memory
3. Да, на один диск, и более того, файл с таблицей источником (точнее, её секцией) тоже на этом диске.
Гавриленко Сергей Алексеевич
Параллелить имеет смысл только если один поток и производительность одного ядра являетются узким местом.
Совершенно верно.

Ну вот, я таким образом гадаю, где же узкое место :-)

У сиквела это иногда трудно понять, совершенно необъяснимое поведение.

Какие ресурсы сервер может ожидать, что бы считать блок за блоком таблицу, расшифровать слоты в блоках, и записать это последовательно в файл?

2 потока последовательного доступа.

Правда, работает с одним диском, однако я ожидаю, что это (2 конкурентных последовательных доступа) замедлит скорость пусть раза в 3, ну пусть даже в 10, но не в 100 же?

И это подтверждается очередью к диску 0.01-0.1.
Раз очереди нет, значит, диск - не узкое место?
Ну и раз ни одно ядро не занято больше чем на 10%, то это тоже не узкое место?

В общем, непонятно.
6 июн 17, 02:03    [20541887]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
Dzianis
Member

Откуда:
Сообщений: 76
alexeyvg
подтверждается очередью к диску 0.01-0.1.
Раз очереди нет, значит, диск - не узкое место?
Ну и раз ни одно ядро не занято больше чем на 10%, то это тоже не узкое место?

В общем, непонятно.


Эх.
Сидел на новом сервере. Тестировал.

BCP:
- таблица в 5 легких колонок, 88 млн записей. выгружается 8 минут. Скорость 17-19 мб.
- эта ж таблица. Выгружается сразу в bcp 4 файла (то есть 4 копии таблицы). 8 минут на все 4 потока для получения 4х файлов. Скорость 18-20 мб. Все таки даные таблицы уже закешированы.
- эта ж таблица. Выгрузка в 4 файла по PK (то есть 1/4 таблицы в каждый файл). скорость 17-18 мб. Время 3 минуты на все 4 потока.
- эта ж таблица. продублирована 4 раза, Pk дефрагментированы. Выгрузка в 4 bcp. То есть один экз таблицы = 1 файл. Скорость 15-18. В среднем те же 8 минут.

Длина очереди диска не понялась выше 0.135
Итого по процессору ограничений нет. По диску нет. По памяти нет.
Просто особенности алгоритма утилиты.
7 июн 17, 13:18    [20546668]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Dzianis
Эх.
Сидел на новом сервере. Тестировал.

BCP:
- таблица в 5 легких колонок, 88 млн записей. выгружается 8 минут. Скорость 17-19 мб.
- эта ж таблица. Выгружается сразу в bcp 4 файла (то есть 4 копии таблицы). 8 минут на все 4 потока для получения 4х файлов. Скорость 18-20 мб. Все таки даные таблицы уже закешированы.
- эта ж таблица. Выгрузка в 4 файла по PK (то есть 1/4 таблицы в каждый файл). скорость 17-18 мб. Время 3 минуты на все 4 потока.
- эта ж таблица. продублирована 4 раза, Pk дефрагментированы. Выгрузка в 4 bcp. То есть один экз таблицы = 1 файл. Скорость 15-18. В среднем те же 8 минут.

Длина очереди диска не понялась выше 0.135
Итого по процессору ограничений нет. По диску нет. По памяти нет.
Просто особенности алгоритма утилиты.
Я тоже поковырялся... Конечно, база и файл уже на разных дисках.
Это определяется получением данных, кривой код в драйвере доступа. Запись в файл не ограничивает.
Не в состоянии оно передавать данные с сервера на клиент с бОльшей скоростью. Несмотря на то, что ресурсы не заняты, т.е. ни одно ядро не загружено, и очередь диска не превышает 0.1. Но всё равно выгружает медленно.
Планы ковыряния ещё есть, буду дальше копать, стало интересно :-)
7 июн 17, 13:27    [20546692]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
komrad
Member

Откуда:
Сообщений: 5245
сделал тест на вскидку

таблица 216млн записей, 40ГБ данных
данные и файл на разных дисках

выгрузилась за ~17минут, средняя скорость записи ~52МБ/сек, нагрузка цпу 3%
режим выгрузки - один поток, всё по умолчанию, native mode, подключение по shared memory

bcp
...
1000 rows successfully bulk-copied to host-file. Total received: 216010000
1000 rows successfully bulk-copied to host-file. Total received: 216011000

216011852 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1070042 Average : (201872.31 rows per sec.)
7 июн 17, 15:08    [20547213]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
Dzianis
Member

Откуда:
Сообщений: 76
Dzianis
BCP:
- таблица в 5 легких колонок, 88 млн записей. выгружается 8 минут. Скорость 17-19 мб.
- эта ж таблица. Выгружается сразу в bcp 4 файла (то есть 4 копии таблицы). 8 минут на все 4 потока для получения 4х файлов. Скорость 18-20 мб. Все таки даные таблицы уже закешированы.
- эта ж таблица. Выгрузка в 4 файла по PK (то есть 1/4 таблицы в каждый файл). скорость 17-18 мб. Время 3 минуты на все 4 потока.
- эта ж таблица. продублирована 4 раза, Pk дефрагментированы. Выгрузка в 4 bcp. То есть один экз таблицы = 1 файл. Скорость 15-18. В среднем те же 8 минут.


Тест 1 и 4 повторены через SSIS
- таблица в 5 легких колонок, 88 млн записей. Скорость 18-23 мб.
- эта ж таблица. продублирована 4 раза, Pk дефрагментированы. Выгрузка в 4 пакета ssis. То есть один экз таблицы = 1 файл. Скорость 18-19.

Субьективно быстрее на 10%. Это скорее погрешность в оценке, чем реальное отличие.
7 июн 17, 16:00    [20547496]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
komrad
Member

Откуда:
Сообщений: 5245
Dzianis,

без обид, но тест странный - вес данных не указан, скорость почему-то просто в МБ, формат данных не указан.
7 июн 17, 16:04    [20547508]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
Dzianis
Member

Откуда:
Сообщений: 76
komrad,

сравнительная характеристика.
понятно что скорость может отличаться от железа к железу у разных пользователей.

Таблица по sp_spaceused data:6435112 KB index_size:10416 KB
88 млн записей. по 8 колонок. Формат тестовый файл (ключ -с)
" скорость в МБ" - так написал.
7 июн 17, 16:30    [20547619]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли альтернатива утилите BCP по перегону таблиц в файлы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
komrad
Dzianis,

без обид, но тест странный - вес данных не указан, скорость почему-то просто в МБ, формат данных не указан.
У Dzianis указано, что 5 лёгких колонок.
А у вас какая таблица? Судя по всему, записей мало, но широких? Запись 185 байт?

У меня узкая, 3 поля int и одно float

Проблема как раз возникает для маленьких записей. Плохой механизм получения следующий записи, где то там idle вставлено.
7 июн 17, 17:39    [20547965]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить