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

Откуда: Berlin
Сообщений: 185
alexboesky
bcp схавает все запросы даже если будет много дублей?

да, но можно ведь сделать импорт во временную таблицу и там найти дубли, обработать их и сделать вставку в целевую таблицу.
все равно будет быстрее, чем вставлять по одной.
2 мар 12, 19:12    [12185898]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexboesky
Member

Откуда: Киев
Сообщений: 40
про IGNORE_DUP_KEY сегодня уже дошло... пришел к выводу что лучший способ - поставить IGNORE_DUP_KEY, и юзать sqlbulkcopy класс, в нем обрабатывать все строки перед вставкой... т.к. обрезать длину и т.д.... либо на PHP оформить красиво базу и скормить bcp..... просто я боюсь что с ними тоже потеряется скорость после 70лямов строк...((
3 мар 12, 01:05    [12187041]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
squid
Member

Откуда: LA
Сообщений: 590
alexboesky,

Я бы попробовал
1. Сформировать текстовый файл
2. Использовать связку OPENROWSET(BULK .... http://msdn.microsoft.com/ru-ru/library/ms190312.aspx
3. как уже сказали, дабы исключить дубликаты юзать опцию индекса IGNORE_DUP_KEY

плюсы - BCP который и предназначен для массовой загрузки, с пиминальным протоколированием.


С другой стороны попробуйте в существующем рещении использовать не хардкод
insert into plain_text([pass]) values('$pass')
а параметризированный вызов, с помощью sp_executesql - уменьшите нагрузку парсер и на кеш планов.
3 мар 12, 15:15    [12188224]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexboesky
Member

Откуда: Киев
Сообщений: 40
спасибо всем большое! сегодня буду тестить :)
3 мар 12, 17:09    [12188616]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31956
alexboesky
просто я боюсь что с ними тоже потеряется скорость после 70лямов строк...((
Да, она будет теряться из за того, что по любому характер доступа к диску становится случайным.

Но всё таки в целом будет намного быстрее.

Если загрузка разовая (или периодическая с загрузкой 10% и более от размера таблицы), то можно строить индекс на pass после загрузки. Тогда скорость будет максимальной и время загрузки будет линейно зависить от размера (там правда ещё одна засада - будет падение, когда индекс перестанет влезать в память).
3 мар 12, 20:59    [12189171]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31956
alexeyvg
Если загрузка разовая (или периодическая с загрузкой 10% и более от размера таблицы), то можно строить индекс на pass после загрузки. Тогда скорость будет максимальной и время загрузки будет линейно зависить от размера (там правда ещё одна засада - будет падение, когда индекс перестанет влезать в память).
Конечно, тогда pass будет неуникальным... И придётся делать дополнительную переливку данных. Тут нужно пробовать, что быстрее. Может, не так и страшна это случайное обновление индексных сраниц. По любому, выйгрыш от bulk insert будет в сотни раз.
3 мар 12, 21:03    [12189177]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Да даже без балка вставка в таблицу без рандомного индекса будет в разы шустрее.
3 мар 12, 21:21    [12189228]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
squid
Member

Откуда: LA
Сообщений: 590
Гавриленко Сергей Алексеевич
Да даже без балка вставка в таблицу без рандомного индекса будет в разы шустрее.


Прошу прощение. Что за термин такой - "рандомный индекс" ?! ;)

Проверил на своей мини сервере. SQL 2008 R2 10.5.2500. Pentium DC 3Ghz 4 GB, по сиквел отдано строго 2гига озу. Базу полнотью положил на Raid1 2*scsi 10k.

Я смог залить 10 млн записей за 92 сек с использованием BCP. т.е. более 100 тыс записей/сек

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

при таком раскладе
1. цикл, в цикле ставка одной записи 1200 записей/сек Top wait - LogWriter (99%)
2. транзакция цикл, в цикле ставка одной записи , коммит. 1200 записей/сек. Top wait - IO_Completion (99%)

Есть смысл оптимизировать эти подходы, дабы приблизится к BULK ? по моему очевидно, это подход тупиковый.


Итак, топик стартеру, пара стратегичеких советов.

1. Формирование файла на C# на 10 млн строк - 2-3 сек. размер файла ~80мб
2. Используйте BULK
в моем случай это
insert into dbo.plain_text  with (tablock)
 (pass)
Select * from openrowset(Bulk 'C:\hash_values.csv', FORMATFILE='C:\hash_values.fmt', ROWS_PER_BATCH = 1000000  ) as T


где, with (tablock) хинт позволяющий серверу (только начиная 2008) ипользовать инсерт с минимальным протоколированием. Это снижает нагрузку IO примерно вдвое. В моем случае во времени выигрыш дает тоже вдвое.
ROWS_PER_BATCH - я сходу поставил 1 к 10 от объема. влияет на производительность, думаю можно найти значение оптимальней.

ENJOY
3 мар 12, 23:24    [12189755]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
squid
Гавриленко Сергей Алексеевич
Да даже без балка вставка в таблицу без рандомного индекса будет в разы шустрее.


Прошу прощение. Что за термин такой - "рандомный индекс" ?! ;)
Не монотонно возрастающий. Т.е., когда таблица реально большая, в самом плохом случае, каждый ключ будет ложиться в отдельную страницу. Это не комильфо: даже если от сплитов подстраховаться филлфактором, этистраницы как бы надо отыскать и с диска поднять.

По одной записи хуже всего. Пачкой будет быстрее. Пачкой балком или с минимальным протоколированием - еще быстрее.

Сообщение было отредактировано: 4 мар 12, 01:23
3 мар 12, 23:39    [12189812]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
squid
Member

Откуда: LA
Сообщений: 590
неверно указал факты:

автор
2. транзакция цикл, в цикле ставка одной записи , коммит. 4000 записей/сек. Top wait - IO_Completion (99%)

Да и еще покольку IO самое узкое место в данной задаче, то разнесение таблицы, лога по разным дискам будет давать бенефиты.

да и загрузилоь 70 млн записей за 13 мин.
3 мар 12, 23:43    [12189824]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
squid
Есть смысл оптимизировать эти подходы, дабы приблизится к BULK ? по моему очевидно, это подход тупиковый.
Балк не всегда удобен. Например, когда надо вставить не все, а только то, чего нет.

Сообщение было отредактировано: 3 мар 12, 23:47
3 мар 12, 23:44    [12189827]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31956
squid
Гавриленко Сергей Алексеевич
Да даже без балка вставка в таблицу без рандомного индекса будет в разы шустрее.


Прошу прощение. Что за термин такой - "рандомный индекс" ?! ;)

Проверил на своей мини сервере. SQL 2008 R2 10.5.2500. Pentium DC 3Ghz 4 GB, по сиквел отдано строго 2гига озу. Базу полнотью положил на Raid1 2*scsi 10k.

Я смог залить 10 млн записей за 92 сек с использованием BCP. т.е. более 100 тыс записей/сек
Просто у ТС там поле с уникальным индексом.

Вы сделайте поле с ГУИД и индекс на него.

Через некоторое время индекс будет занимать много страниц, и обновление индекса при записи превратится в обновление случайного набора страниц (т.е. при вставке очередных 100000 строк будет обновляться 100000 случайных страниц индекса, частично со сплитом, хотя это тут уже неважно)

И вот эти обновления страниц по количеству вставленных строк по всему диску и станут проблемой - если простенький рейд на несколько дисков позволяет писать последовательно гигабайт в секунду, то при случайной записи будет уже 5 мегабайт, т.е. скорость снизится в 200 раз.

Это мы и наблюдаем у ТС, только ещё усугублённое отдельными транзакциями на каждую строку.
4 мар 12, 01:20    [12190137]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexboesky
Member

Откуда: Киев
Сообщений: 40
squid,

insert into dbo.plain_text  with (tablock)
 (pass)
Select * from openrowset(Bulk 'C:\passwords\ResultFiles\0...9999999\xaa', FORMATFILE='C:\temp_format.frm', ROWS_PER_BATCH = 1000000  ) as T


спасибо, это гараздо быстрее чем на пхп инсертить по одной строкой.

(3000000 row(s) affected) за 00:01:16

как вам удалось 10 лямов за 92 секунды вставить? или bcp быстрее чем openrowset+bulk?
у меня 3 ляма вставило за 1мин 16сек, причем конфигурация системы - 16гб озу и 8ядерный проц i7 3.4ghz
4 мар 12, 03:30    [12190342]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexboesky
Member

Откуда: Киев
Сообщений: 40
видимо мало памяти было изначально выделено... на этот раз 3 ляма за 26 сек загрузило....
4 мар 12, 04:15    [12190361]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
squid
Member

Откуда: LA
Сообщений: 590
Гавриленко Сергей Алексеевич
squid
Есть смысл оптимизировать эти подходы, дабы приблизится к BULK ? по моему очевидно, это подход тупиковый.
Балк не всегда удобен. Например, когда надо вставить не все, а только то, чего нет.


Как раз удобство конструкции SELECT .... OPENROWSET(BULK....) позволяет это сделать. Ничто не мешает Вам фильтровать выборку.
4 мар 12, 13:28    [12190824]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
squid
Member

Откуда: LA
Сообщений: 590
alexboesky
squid,

insert into dbo.plain_text  with (tablock)
 (pass)
Select * from openrowset(Bulk 'C:\passwords\ResultFiles\0...9999999\xaa', FORMATFILE='C:\temp_format.frm', ROWS_PER_BATCH = 1000000  ) as T


спасибо, это гараздо быстрее чем на пхп инсертить по одной строкой.

(3000000 row(s) affected) за 00:01:16

как вам удалось 10 лямов за 92 секунды вставить? или bcp быстрее чем openrowset+bulk?
у меня 3 ляма вставило за 1мин 16сек, причем конфигурация системы - 16гб озу и 8ядерный проц i7 3.4ghz


Тут 2 фокуса.
1. Это with (tablock) который даст на 2008 сервере прирост, за счет отключения протоколирования в лог. Данные льются сразу в страницы данных. Какая у Вас версия сиквела ?
2. Количество ЦПУ, и их частота в данном случае почти ничего не решают. горлышко - дисковая подсистема. Вы о не ничего не сказали. В моем случае это постоянная запись на уровне 20 мб/сек при 120-150 IOPSах.



запустил еще раз вставку 70 млн. отпишусь.
4 мар 12, 13:36    [12190861]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
squid
Member

Откуда: LA
Сообщений: 590
Итак, вставка 70 лямов - теже 13 мин. разброс +1 минута
скорость заливки практически линейная.


alexeyvg
squid
пропущено...
Прошу прощение. Что за термин такой - "рандомный индекс" ?! ;)

Проверил на своей мини сервере. SQL 2008 R2 10.5.2500. Pentium DC 3Ghz 4 GB, по сиквел отдано строго 2гига озу. Базу полнотью положил на Raid1 2*scsi 10k.

Я смог залить 10 млн записей за 92 сек с использованием BCP. т.е. более 100 тыс записей/сек
Просто у ТС там поле с уникальным индексом.

Вы сделайте поле с ГУИД и индекс на него.

Через некоторое время индекс будет занимать много страниц, и обновление индекса при записи превратится в обновление случайного набора страниц (т.е. при вставке очередных 100000 строк будет обновляться 100000 случайных страниц индекса, частично со сплитом, хотя это тут уже неважно)

И вот эти обновления страниц по количеству вставленных строк по всему диску и станут проблемой - если простенький рейд на несколько дисков позволяет писать последовательно гигабайт в секунду, то при случайной записи будет уже 5 мегабайт, т.е. скорость снизится в 200 раз.

Это мы и наблюдаем у ТС, только ещё усугублённое отдельными транзакциями на каждую строку.



По поводу рамномного индекса полная чушь. Я не зря задал вопрос о терминологии.
100% поведение индекса определяется самими данными. Или, простите мой скарказм - существует DDL CREATE RANDOM INDEX ?


Далее, теория теорией, но GUID не так страшен. на эту тему достаточно статей. Расчепление страниц, вставка в произвольное место.. звучит ужасно для неподготовленного. Но мы то знаем :)
Вы не учитываете простой факт. При при каждом расчеплении выделяется новая страница с разделением записей. В результате каждая страница полупустая. И факт наступления следующего расчепления, увы наступает не сразу после следующей же вставки.
Более того при интенсивном инсерте последовательных значений в кластерный индекс Вы столкнетесь с другой проблемой - Блокировки одной и той же страницы. https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=916768&hl= что совсем нехорошо.


Далее, специально для Вас заменил определение ID на GUID с DF newid()

вставка 10 млн записей - 2 мин 15 сек (1 мин 32 сек)
Вставка 70 млн записей 17 мин 34 сек. (было 13 мин)

мой RAID пишет макс со скоростью 25 мб.сек. Увы контроллер фаст скази версии2 и имплементации 97 года. Ни о каких гигах в секунду речи и быть не может!

конечное увеличение времени на запрос я обосновываю на 90% - с увеличением объема записываемых данных.


Замечания ?
4 мар 12, 17:27    [12191457]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
squid
Замечания ?
10 млрд. сделайте тест. А потом и про "чушь" поговрить можно.
4 мар 12, 17:36    [12191472]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
squid
Member

Откуда: LA
Сообщений: 590
Гавриленко Сергей Алексеевич
squid
Замечания ?
10 млрд. сделайте тест. А потом и про "чушь" поговрить можно.


Сделаю.
А Вы готовы что либо сделать ?
4 мар 12, 17:39    [12191478]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
squid
А Вы готовы что либо сделать ?
А надо?

Вам про Фому, а вы про Ерему. Не чушь это. Расщепленние страниц - это стремление к ФФ = 50%, т.е. к тому, чтобы данные занимали в два раза больше, чем надо (адскую фрагментацию, для простоты, не рассматриваем). Т.е. любое i/o надо умножать на два. И в кеше в два раза меньше записей при том же его объеме.

Но. Изначально вопрос был не в этом, а в том, что индекс с случайно поступающими ключами с ростом количества данных требует модификации все большего числа страниц. Т.е. количество страниц, в которые записи попадут, стремится к количеству вставляемых записей.

А балком, ясен пень, быстрее, чем по одной записи. Кто ж спорит.

З.Ы. И, кстати, чтобы вставлять в кластерный с минимальным протоколированием в непустую таблицу с некластерным индексом, нужен еще флаг 610.
4 мар 12, 17:54    [12191502]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31956
squid
По поводу рамномного индекса полная чушь. Я не зря задал вопрос о терминологии.
100% поведение индекса определяется самими данными. Или, простите мой скарказм - существует DDL CREATE RANDOM INDEX ?
Вы прочитайте мой пост сначала, перед ответом. Если есть несогласие с каким-то звеном в логической цепочке, то скажите.

По моему, я всё подсчитал правильно и количество обновляемых страниц на диске всегда будет равно количеству вставленных строк.
squid
Расчепление страниц, вставка в произвольное место.. звучит ужасно для неподготовленного. Но мы то знаем :)
Вы не учитываете простой факт. При при каждом расчеплении выделяется новая страница с разделением записей. В результате каждая страница полупустая. И факт наступления следующего расчепления, увы наступает не сразу после следующей же вставки.
Совершенно верно, про это я и написал в своём сообщении - расщепление страниц в данном случае роли не играет. Не пойму, с чем вы спорите? :-)
4 мар 12, 19:28    [12191726]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexboesky
Member

Откуда: Киев
Сообщений: 40
мне и 3 ляма в 3 минуты это счастье)) вроде всё круто, запустил скрипт который загружает балком по 3 ляма... все большие файлы разбил на 3 ляма строк...

столкнулся с другой проблемой... пхп-скрипт цыклом выполняет для каждого файла(3ляма) отдельный запрос, меня всё устраивает, но sqlserver занимает память не освобождая ее после предыдущего запроса... щас почти 8гб занимает, а 20 минут придется перезагружать sqlserver и снова запускать скрипт... возможно ли как-то сказать sqlserver'у освободить память, не перезагружая его руками?
4 мар 12, 21:32    [12192237]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexboesky
Member

Откуда: Киев
Сообщений: 40
*"...а через 20 минут..."
4 мар 12, 21:35    [12192254]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexboesky
Member

Откуда: Киев
Сообщений: 40
http://xip.su/lol.png
4 мар 12, 21:46    [12192285]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexboesky
Member

Откуда: Киев
Сообщений: 40
решил проблему)) просто в настройках инстанса указал лимит 8гб, после чего sqlserver освободил всё кроме 8гб))
4 мар 12, 22:09    [12192361]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить