MS SQL on multi-processor computer

[roadman]

Программа работает с MS SQL. На однопроцессорной машине (CPU-1ГГц, 512Мб) программа (Windows Service) делит CPU с MS SQL примерно поровну 50% на 50%. Есть сервер (4CPU-2ГГц каждый, 2ГГб), на котором стоит та же самая программа и тот же самый MS SQL. Программа "кушает" 100% одного из процессоров, что очень хорошо, а вот MS SQL от 4% до 8% второго. И в целом вся система работает медленее, что, конечно, обидно. MS SQL сконфигурирован для всех 4 процессоров c приоритетом "high". Памяти свободной полно (используется где-то 20% от всей доступной оперативной).
Кто-нибудь из спецов по MS SQL 2000 (service pack последний) сталкивался с такой проблемой.

[шпиён]

1) Affinity mask не пробовали так прописать, чтоб эти процессы (SQL и Ваш сервис) не лезли друг к другу?
2) Может, тот второй процесс не только 100% процессора съедает, но и 100% шины?
3) Приоритеты у них разные?

[roadman]

1) Affinity mask не пробовали так прописать, чтоб эти процессы (SQL и Ваш сервис) не лезли друг к другу?
2) Может, тот второй процесс не только 100% процессора съедает, но и 100% шины?
3) Приоритеты у них разные?

Программа работает так:
1. берет данные в память из SQL таблиц - это работает быстро - 3-4 секунды - я спользую bulk select + custom allocators.
2. соединяется с внешним источником по TCP/IP и берет в память данные - это тоже работает быстро 1-2 секунды.
3. Делает bulk insert, построчный update и delete (commit на каждый statement). Вначале MS SQL сервер резво берет в карьер 30-40% CPU в течении 5-6 секунд, а потом 30-40 секунд "думает" 2-4% CPU, я так полагаю над индексами.
Программа при этом, ничего не делает, просто ждет. Приоритет у программы "normal".
Проясните, пожалуйста, что такое Affinity mask и как ним бороться.
С шиной не очень понятно, ведь на одно-процессорной машине последний этап вместо 30-40 секунд занимает 3-4 секунды...

[tengiz]

А почему Вам понадобилось менять приоритет по умолчанию у SQL Server? Необходимость в этом возникает исключительно редко в действительно исключительных ситуация. Судя по описанию, Ваш случай не проходит по исключительности.

Дискове подсистемы сильно разные? Если SQL Server задумавается на индексами, то тогда должна быть сильная дисковая активность, но несмотря даже на это построение индекса всё равно очень жадная до CPU операция.

Когда Вы написали про коммит после каждого стейтмента, я надеюсь Вы не имели в виду после обновления каждой строки? Иначе вся экономия за счёт использования оптимизированной встаки посредством bulk insert съедается журналированием.

[roadman]

А почему Вам понадобилось менять приоритет по умолчанию у SQL Server? Необходимость в этом возникает исключительно редко в действительно исключительных ситуация. Судя по описанию, Ваш случай не проходит по исключительности.

Дискове подсистемы сильно разные? Если SQL Server задумавается на индексами, то тогда должна быть сильная дисковая активность, но несмотря даже на это построение индекса всё равно очень жадная до CPU операция.

Когда Вы написали про коммит после каждого стейтмента, я надеюсь Вы не имели в виду после обновления каждой строки? Иначе вся экономия за счёт использования оптимизированной встаки посредством bulk insert съедается журналированием.

Менял потому, что до этого приоритет был "normal" и я просто экспериментировал. Результат такой же.
Commit относиться только к update и delete. Bulk insert идет без всякого commit, поскольку это делается автоматически в библиотеке BCP когда вызывается команда bcp_done.
На однопроцессорном компьютере это один hard drive c обычным (IDE) доступом, на сервере (4-х процессорном) это SCSI драйвер и тоже один диск.
Яркого увеличения дисковой активности не замечал. Просто MS SQL тихо и мирно потребляет 2-4% CPU в среднем и время обработки одной и той же информации увеличивается в разы.

[Dmitry67]

А на каком диске расположена tempdb ?

[roadman]

А на каком диске расположена tempdb ?

На том же, даже в той же директории.

[roadman]

А на каком диске расположена tempdb ?

На том же, даже в той же директории.

Я имел в виду 4-х процессорный сервер, а на однопроцессорной машине tempdb и newsdb расположены на разных логических дисках (правда это один физический). Дмитрий, Вы думаете это как-то может сказаться на быстродействии?

[Dmitry67]

Блин
Последний раз пишу посты из дома до работы не проснувшись
Я имел ввиду не tempdb, а log
Когда сервер быстро бежит а потом начинает тормозить очень похоже на автоматическое расширение log для операторов update/delete которые апдейтят много записей.
Последите за их размером

[tengiz]

Менял потому, что до этого приоритет был "normal" и я просто экспериментировал. Результат такой же. Commit относиться только к update и delete.

Ну так commit делается после каждой строки или нет?

Bulk insert идет без всякого commit, поскольку это делается автоматически в библиотеке BCP когда вызывается команда bcp_done.

bulk insert и bcp - это разные вещи, bcp_done и bcp API не имеют никакого отношения к bulk insert.

На однопроцессорном компьютере это один hard drive c обычным (IDE) доступом, на сервере (4-х процессорном) это SCSI драйвер и тоже один диск.

Ваша проблема может быть в именно этом - в Windows для IDE дисков по умолчанию включено кеширование записи, поэтому частые commit после каждой строки (если Вы действительно так делаете) не создают сликом большой нагрузки. Проверьте, выключено ли кеширование записи для SCSI диска на новой машине. Но если выключено, то я бы не советовал включать. Правильное решение - не делать слишком частых commit.

[Strannik223]

1) Affinity mask не пробовали так прописать, чтоб эти процессы (SQL и Ваш сервис) не лезли друг к другу?
2) Может, тот второй процесс не только 100% процессора съедает, но и 100% шины?
3) Приоритеты у них разные?

Программа работает так:
1. берет данные в память из SQL таблиц - это работает быстро - 3-4 секунды - я спользую bulk select + custom allocators.
2. соединяется с внешним источником по TCP/IP и берет в память данные - это тоже работает быстро 1-2 секунды.
3. Делает bulk insert, построчный update и delete (commit на каждый statement). Вначале MS SQL сервер резво берет в карьер 30-40% CPU в течении 5-6 секунд, а потом 30-40 секунд "думает" 2-4% CPU, я так полагаю над индексами.
Программа при этом, ничего не делает, просто ждет. Приоритет у программы "normal".
Проясните, пожалуйста, что такое Affinity mask и как ним бороться.
С шиной не очень понятно, ведь на одно-процессорной машине последний этап вместо 30-40 секунд занимает 3-4 секунды...

А не лучше ли будет взять внешние данные и вдуть их во временную таблицу использую bulk load. А потому сиквелом сделать все update & insert?

По поводу диагностики: можно законектиться к сиквел процессу отладчиком и посмотреть чем он занимается, судя по вашему описанию он будет проводить время в WaitFor[Single|Multiple]Obhect[s] Такая информация может дать дополительную пищу для размышлений

[Merle]

Ваша проблема может быть в именно этом - в Windows для IDE дисков по умолчанию включено кеширование записи,

Но сиквел же работает с файлами через FILE_FLAG_WRITE_THROUGH, и при этом все системное кеширование идет лесом, если я правильно понимаю...
FILE_FLAG_WRITE_THROUGH Instructs the system to write through any intermediate cache and go directly to disk. The system can still cache write operations, but cannot lazily flush them.

[SkyWalker]

Для системных дисков Windows отключает кэширование. Вообще очень желательно не хранить базу на системном диске. Но в Вашей ситуации надо проверить включен ли кэш на запись в самом SCSI контроллере.
В этом может быть проблема. Только обязательно проверьте есть ли батарейка в контроллере и естествеено должен быть UPS.

[YellowMan]

Так человек же писал что не видит нагрузки на диск в момент простоя...
Хорошо бы код посмотреть и планы. И блокировки в момент задумчивости.

ЗЫ
А что такое bulk select ?

[roadman]

Ну так commit делается после каждой строки или нет?
bulk insert и bcp - это разные вещи, bcp_done и bcp API не имеют никакого отношения к bulk insert.

Tengiz, я использую bulk insert не как SQL statement "bulk insert....", то есть не через внешний файл, а делая bind в памяти
bcp_bind,
bcp_sendrow,
и когда строчек набереться порядочно, то
bcp_done, поэтому bcp_done команда имеет прямое отношение к commit. Принудительно я его для bulk insert не делаю, потому как bcp библиотека этого функционала не предоставляет.

вначале выполняется bulk insert для всех новых строчек.

затем пробегаю по всем измененным строчкам в памяти и делаю update
update команда выполняется через обычный ODBC statement
"update table set .... where ....", после каждой команды выполняется commit

затем пробегаю по строчкам, помеченным как удаленные
и выполняю обычный ODBC statement
"delete from table .... where ...".
после каждой команды выполняется commit

Дмитрий размер log file почти не растет. data file в начале 4К - в конце 200М, log file в начале 1К в конце 30Кб. Log file на сервере в том же каталоге, где и все tempdb и newsdb.

Господа я уже писал, что на однопроцессорном компьютере идентичная программа при тех же самых данных работает быстрее.

Пробовал делать commit на все данные после update - иногда это 100 000 строк, вот тогда log file растёт очень быстро и всё тормозится страшным образом.

Ваша проблема может быть в именно этом - в Windows для IDE дисков по умолчанию включено кеширование записи, поэтому частые commit после каждой строки (если Вы действительно так делаете) не создают сликом большой нагрузки. Проверьте, выключено ли кеширование записи для SCSI диска на новой машине. Но если выключено, то я бы не советовал включать. Правильное решение - не делать слишком частых commit.

Идея о кешировании данных диском мне кажется очень интересной, проверю обязательно...
По поводу частых commit - то же наверное стоит попробовать, если это напрямую связано с кешированием диска. Как по вашему, господа, какое количество строк(байт?) было бы приемлемым в одном блоке транзакций 10, 100, ..., 1000 000?

[roadman]

Так человек же писал что не видит нагрузки на диск в момент простоя...
Хорошо бы код посмотреть и планы. И блокировки в момент задумчивости.

ЗЫ
А что такое bulk select ?

Это когда вы готовите буффер для bind не на одну строчку, а на 1000, скажем для примера, конфигурируя ODBC driver
SQLSetStmtAttr(m_hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (void*)m_Bulk_Size, 0);
::SQLSetStmtAttr(m_hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &row_count, 0);
Тогда select идет намного быстрее, 100Мб за 2-3 секунды, если буфер порядка 10Мб.

[tengiz]

roadman,

В SQL Server есть общепринятый жаргон, в котором bulk insert означает не что иное как bulk insert statement. Когда говорят о каком-либо способе оптимизированной массовой загрузки данных без уточнения о каком именно, то обычно говорят bulk load - что означает, что данные загружаются либо через bulk copy program (bcp), либо через bcp_api, либо через IRowsetFastLoad либо через bulk insert statement.

...поэтому bcp_done команда имеет прямое отношение к commit. Принудительно я его для bulk insert не делаю, потому как bcp библиотека этого функционала не предоставляет.

bcp_done и есть не что иное как промежуточный commit.

затем пробегаю по всем измененным строчкам в памяти и делаю update update команда выполняется через обычный ODBC statement "update table set .... where ....", после каждой команды выполняется commit

затем пробегаю по строчкам, помеченным как удаленные и выполняю обычный ODBC statement "delete from table .... where ...". после каждой команды выполняется commit

Ваша проблема именно здесь и зарыта. Такой сценарий создаёт дикую нагрузку на дисковое устройство, на котором расположен журнал, при условии, что кеширование на запись отключено. Кроме того, не стоит делать массовые изменения строк изменяя их одну за другой отдельными командами - воспользуйтесь @@rowсount для ограничения количества изменяемых отдельной комадной строк, если другого удобного способа нет (намример, изменять диапазон ключей).

По поводу частых commit - то же наверное стоит попробовать, если это напрямую связано с кешированием диска. Как по вашему, господа, какое количество строк(байт?) было бы приемлемым в одном блоке транзакций 10, 100, ..., 1000 000?

Это зависит о размера строки, начните с сотни строк, сделайте измерения, а затем увеличьте это количество, скажем, до тысячи. Как только итоговая производительнось перестанет расти или наоборот пойдёт вниз - Вы выскочили за оптимальный размер пакета.

[roadman]

В SQL Server есть общепринятый жаргон.

Мои извинения за незнания общепринятого жаргона. Теперь буду знать.

Это зависит о размера строки, начните с сотни строк, сделайте измерения, а затем увеличьте это количество, скажем, до тысячи. Как только итоговая производительнось перестанет расти или наоборот пойдёт вниз - Вы выскочили за оптимальный размер пакета.

ОК, будем экспериментировать.

Спасибо всем за brain-storm, о результатах доложу.

[tengiz]

Но сиквел же работает с файлами через FILE_FLAG_WRITE_THROUGH, и при этом все системное кеширование идет лесом, если я правильно понимаю...

Это разные кеши - есть кеш файловых систем, интегрированный в подсистему виртуальной памяти ОС, и есть кеши драйверов устройств, а также кеши самих устройств.

[roadman]

Ваша проблема может быть в именно этом - в Windows для IDE дисков по умолчанию включено кеширование записи, поэтому частые commit после каждой строки (если Вы действительно так делаете) не создают сликом большой нагрузки. Проверьте, выключено ли кеширование записи для SCSI диска на новой машине. Но если выключено, то я бы не советовал включать. Правильное решение - не делать слишком частых commit.

Only today I was able to get to the server computer and check - what's going on here.
Thanks tengiz, as Joker predicted in our private conversation your advice was the most comprehensive and precise. On a SCSI disk the write-cache was disabled and even more - I didn't find how to make it enable. And follow you guides I changed block of rows prepared for update/delete operations from 1 to 1000 and MS SQL picked up to 50% of CPU time and sometimes grabed the second free CPU. The total result - program is flying like a rocket.
Thanks again, I really appreciate ALL advices I got here.

[tengiz]

My regards to Joker