Производительность

[Def]

Добрый день!
интерсено узнать мнение специалистов которые работают с достаточно крупными базами

Имеется
DAX4 SP2
SQL 2008 SP1
База 300ГБ корзина Xyratex F6412
Сервер Win2008 8 проц
64 гб ОП
Нагрузка 100 чел
Много складских документов, закупки, продажи, производство немного
складских аналитик 5ть 90% позиций работают 3 мя

Заведены 2 Фирмы
Обьединены в виртуальную компанию
в SQL 2008 стоят крыжы автоперсчет автосбор статистики

Наблюдается странное поведение
1. выключаем index hint в AOS, запускаем update statistics
некотрое время работает замечательно, затем даже выборка остатков findsum начинает работать оочень медленно даже по одной номенклатуре
делаем update statistics, снова работает замечательно

2. включаем index hint в AOS, запускаем update statistics
работает замечательно до и после

вопрос
1. почему SQL так сильно лажает с выбором оптимального плана
2. Первый вариант сделан по рекомендациям, почему тупит?
3. У кого база еще больше, что Вы делаете для обеспечения стабильной производительности?

[mazzy]

Цитата:

Сообщение от Def

вопрос
1. почему SQL так сильно лажает с выбором оптимального плана
2. Первый вариант сделан по рекомендациям, почему тупит?
3. У кого база еще больше, что Вы делаете для обеспечения стабильной производительности?

1. потому что оптимальный план выбирается на основе статистики
2. потому что статистику надо периодически обновлять
У вас, скорее всего, выключен автоапдейт статистики (и это правильно), но в этом случае надо добавлять maintenance plan, который периодически ее обновляет
3. я щас глюпый вещь спрошу. только не сердитесь пожалуйста.
60гб - это только данные? или вместе с логом? сколько весит лог? периодический бэкап делается? recovery mode = full?

можете привести скриншот отчета Disk Usage by Table?

[mazzy]

Цитата:

Сообщение от Def

в SQL 2008 стоят крыжы автоперсчет автосбор статистики

Ой, извините. Пропустил.

[Zabr]

Цитата:

Сообщение от mazzy

60гб - это только данные? или вместе с логом?

У него база 300 Гб. А 64 - это он про оперативку написал.

Def, у нас тоже AX4SP2, база 320 Гб, из них 20 Гб пустые. Правда, на SQL 2005. Средне-ежедневная пиковая загрузка - 110 юзеров. Работают 2 АОС (в кластере с распределением нагрузки между собой). Index Hint на АОСах выключены. С производительностью все нормально. Основных направлений работы - два: выявление программных косяков (в том числе в стандартной Аксапте, в том числе поиски без индексов, или по не полным индексам, или по неподходящим инлдексам) + периодическая чистка базы от старых ненужных данных (ежедневная и ежемесячная).

[Vadik]

Опция Auto update statistics не гарантирует оптимальных или актуальных статистик - она гарантирует их периодическое обновление при превышении числом модификаций порогового значения. Если хотите гарантированно иметь свежие статистики - обновляйте их регулярно, соответствующие задачи и опции есть в maintenance plan-ах и визардах

[fed]

Рискну предположить следующее:
У вас очень неравномерно распределены остатки по номенклатуре. Допустим есть парочка номенклатур, для которых включен номер партии, соответственно на них приходиться процентов 80 от таблицы inventSum.
Затем вспоминаем, что начиная с версии SQL 2005, SQL использует так называемый parameter sniffing. Под этим заковыристым словом подразумевается механизм, при котором план исполнения параметризованного запроса генерируется для первого встреченного набора параметров запроса, и затем повторно используется для всех остальных значений параметров.
Теперь представим себе что сегодня у вашего сервера неудачный день, и первый пришедший запрос, запрашивает остатки по той самой парочке номенклатур. Сервер, поднапрягшись, генерирует план с full scan и parallelism (поскольку для той номенклатуры, которая эдак процентов 40-50 от inventSum занимает это и вправду нормально). Затем приходят нормальные легкие запросы. Но план исполнения-то сохранен. Значит сервер даже простенькие запросы из серии "Просумировать 5 записей" исполняет через full scan и parallelism.
Потом, после того как вы статистику пересобираете, сервер сбрасывает сохраненный план исполнения (поскольку он это делает каждый раз когда меняется статистика по некой таблице/индексу и данный сохраненный план эту таблицу использует).

Чего в такой ситуации делать:
1. Можно попробовать залезть в inventSum.findSumJoin() и вручную засунуть туда хинт forceLiterals, чтобы параметризация не использовалась и план запроса каждый раз перегенерировался бы. Правда - в этом случае у вас весьма нехило возрастет нагрузка на процессор сервера БД. Так что если вы этим советом попробуете воспользоваться, помониторьте нагрузку на сервер денька 2-3. Может оказаться что лекарство будет тяжелее болезни.
2. Вместо долгого и мучительного сбора статистики при торможении, можно просто отдать SQL-серверу простую комманду DBCC FREEPROCCACHE. Эта комманда заставит сервер забыть все сохраненные планы исполнения запросов и сгенерировать, при надобности, заново. Нагрузка на процессор сервера возрастет, но не очень сильно и только на следующие минут 10-15.

В общем - попробуйте насчет DBCC Freeproccache. Если оно от торможения поможет - значит моя гипотеза верна. Если не поможет - значит у вас и вправду статистика как-то неимоверно быстро устаревает и кроме ее регулярного сбора вас ничто не спасет.

[Def]

всем спасибо за обильные комментарии

уточнения, забыл про второй выделнный файл итого

1. база 550Гб без лога
2. таблицы inventtrans, prodbom, имеют Page Compression
2. index hint до перехода на sql2008 были выключены счас пришлось включить
3. снимаем стаитстику смотрим добавлеям хинты в код или реорганизуем существующие у таблиц (не нравится мне это)
4. 4 аоса с балансировкой
5. recovery model FULL
6. кто нибудь практикует свертку inventtrans ledgertrans за далекие периоды (счвертка в прмяом смысле слова с заменой на к примеру журналы проводка который ставит остаток на начало какого то периода)
7. скрин Disk Usage by Table приложу не могу здоровая pdf не лезет во вложение

Спасибо fed за конкретизацию мысли я об этом догадывался но никак не мог формализовать в словоформе, я не сторонник прописывания в запросы индехов ибо индехи создаю на этапе создания таблицы и продумываю какие выборки будут по этой таблице производится, по этой причине никогда не углублялся в познание SQL статистики, профан.

[Zabr]

Цитата:

Сообщение от Def

база 550Гб без лога

Можно пару вопросов?
1) Сколько лет работает Аксапта?
2) Почему при 100 пользователях у вас аж 4 АОСа ? по-моему, это много. В какой момент ставили 2-й, 3-й, 4-ай АОСы, то есть по какому критерию определяли, что их пора добавлять? у нас на 110 одновременных пользователей - 2 АОСа, и рассчитываю что их хватит до 160-180 пользователей.

[gl00mie]

Цитата:

Сообщение от fed

начиная с версии SQL 2005, SQL использует так называемый parameter sniffing. Чего в такой ситуации делать:

Можно еще поставить обновление на Ms SQL 2008 и отключить parameter sniffing, см. axperf: Important SQL Server Change! - Parameter Sniffing and Query Plan Caching

Цитата:

Сообщение от Def

скрин Disk Usage by Table приложу не могу здоровая pdf не лезет во вложение

Не надо здоровый pdf - есть скрипт, который выдаст обычный текстовый отчет, из которого можно показать тут какой-нить top 20 таблиц.

Цитата:

Сообщение от Zabr

Почему при 100 пользователях у вас аж 4 АОСа?

На самом деле, пользователи - ленивы и не любопытны медлительны и редко когда могут загрузить АОС и СУБД так, как их может загрузить какой-нить пакетник сопоставления или обмен с внешней системой, накидывающей заказы пачками. Так что, может, там основную нагрузку вовсе и не пользователи создают.

[Def]

Цитата:

Сообщение от Zabr

Можно пару вопросов?
1) Сколько лет работает Аксапта?
2) Почему при 100 пользователях у вас аж 4 АОСа ? по-моему, это много. В какой момент ставили 2-й, 3-й, 4-ай АОСы, то есть по какому критерию определяли, что их пора добавлять? у нас на 110 одновременных пользователей - 2 АОСа, и рассчитываю что их хватит до 160-180 пользователей.

рабоатет с 2005 года
лицензий 300 на них и расчитывали 4 аоса
просто временно работает вместо 300 чел 100 чел
действительно оснвная нагрузка создается пакетниками которые и делают основную тяжелую работу по производству по планированию по разноске

table_name row_count table_size data_space_used idx_space_used unused_space
INVENTTRANS 63858530 150592936 24709000 110640000 15243936
LEDGERTRANS 17039073 93479104 37290672 52150808 4037624
PRODJOURNALBOM 33219296 27138360 20507392 6592104 38864
PURCHLINE 4784237 12069224 8190040 3814552 64632
INVENTJOURNALTRANS 12431286 11841728 8399032 3384528 58168
PRODCALCTRANS 13963100 9336000 6312288 2995680 28032
BOMCALCTRANS 10659228 6583264 5975584 587320 20360
PRODBOM 10819244 6549064 1123304 5272120 153640
VENDINVOICETRANS 3982171 6132728 5100112 1019912 12704
INVENTSUMDATETRANS 9546332 3549528 3472680 72408 4440
INVENTREPORTDIMHISTORY 20401593 2648248 2575952 54744 17552
INVENTJOURNALTABLE 2566915 2301200 1570640 709320 21240
SYSTRACETABLESQL 492340 2257232 2253984 2608 640
INVENTDIM 3214504 1770592 422072 1347152 1368
PRODTABLE 803396 1756224 1074032 621408 60784
PRODJOURNALTABLE 2944891 1714992 1139464 566176 9352
INVENTSUM 3195279 1543904 1119256 424008 640
PRODJOURNALPROD 2090276 1456568 942600 502472 11496
PRODTABLEJOUR 3217955 1398800 701936 689488 7376
XREFREFERENCES 4438386 989304 374536 614128 640
PURCHLINEDELETE 377676 715408 606368 84848 24192
VENDPACKINGSLIPTRANS 883102 700968 542792 157880 296
TRANSACTIONLOG 2660296 428160 419936 3728 4496
SYSTRACETABLESQLEXECPLAN 476423 412640 410992 440 1208
BOM 153860 395072 263576 121872 9624
INVENTTABLE 61808 336824 176896 143600 16328
SALESLINE 139124 300176 192080 96504 11592
INVENTJOURNALREPORTTABLE_RU 973543 296608 125296 165080 6232
PRICEDISCTABLE 174663 274032 110912 158888 4232
LEDGERSUMTMP 826345 263712 211688 49328 2696
ADDRESSZIPCODE 547995 240416 104448 135648 320
CUSTINVOICETRANS 140238 220648 179688 36360 4600
VENDINVOICEJOUR 133588 216816 156600 51584 8632
PRINTJOBPAGES 86448 199120 191328 4408 3384
JOURNALERROR 100128 188960 161456 9832 17672
INVENTTABLEMODULE 370884 185504 181720 2344 1440
SYSUSERLOG 586620 183704 72400 107952 3352
XREFPATHS 413452 175864 104856 70448 560

[kornix]

добрый день!
Насколько я помню AOS 4-ки не может утилизировать больше 2Гб памяти (связано видимо с особенностями реализации). По идее, узкие места по производительности можно выявить с помощью нагрузочного тестирования.. Можно попробовать взять стандартный AxBenchMark, немного изменить под себя и запустить. Процесс интересный, полезный, графики и отчеты красивые получаются
Проект можно взять здесь

[mazzy]

Цитата:

Сообщение от Def

table_name row_count table_size data_space_used idx_space_used unused_space
INVENTTRANS
LEDGERTRANS
PRODJOURNALBOM
PURCHLINE
INVENTJOURNALTRANS
PRODCALCTRANS
BOMCALCTRANS
PRODBOM
VENDINVOICETRANS
INVENTSUMDATETRANS
INVENTREPORTDIMHISTORY
INVENTJOURNALTABLE
SYSTRACETABLESQL
INVENTDIM
PRODTABLE
PRODJOURNALTABLE
INVENTSUM
PRODJOURNALPROD
PRODTABLEJOUR
XREFREFERENCES
PURCHLINEDELETE
VENDPACKINGSLIPTRANS
TRANSACTIONLOG
SYSTRACETABLESQLEXECPLAN
...
SYSUSERLOG
XREFPATHS

ну... особо лишнего у вас в базе нет.
можно безболезненно почистить systracelog и SYSUSERLOG
также скорее всего можно удалить удаленные закзаы типа (PURCHLINEDELETE)

дальше нужно смотреть на ваше приложение.
но в стандартном приложении все журналы/заказы - черновики (ищите на форуме обсуждалось неоднократно)
черновики в стандартном приложении можно удалять.
но журналы нельзя удалять в локализованном приложении с запущенной книгой продаж и покупок, а также если у вас "странно" написаны отчеты.

но в целом - вполне правильное распределение таблиц.

немного странно, что у вас в этом списке нет inventSettlement.
у вас, скорее всего, сильно переписано закрытие или вы его не используете.
(обычно в InventSettlement можно абсолютно безболезненно удалять записи, у которых cancelled = Yes)

[mazzy]

Цитата:

Сообщение от Def

table_name row_count table_size data_space_used idx_space_used unused_space
INVENTTRANS 63858530 150592936 24709000 110640000 15243936
LEDGERTRANS 17039073 93479104 37290672 52150808 4037624

кстати, если загрузить ваши данные и проанализировать.
то сразу видно, что у LedgerTrans и InventTrans (особенно у InventTrans) индексы занимают гораздо большее место, чем сами данные.

Это значит, что, скорее всего, на поддержку индексов система тратит неадекватно большое время (при записи и обновлении). А к trans таблицам обновление/добавление применяется очень часто.

кроме того, судя по тому, что у InventTrans неиспользуемое место почти сравнялось с местом под данные, можно сделать предположение, что вы давно не проводили реорганизацию данных в SQL. следовательно, скорее всего, данные сильно фрагментированы. Следовательно, SQL выполняет гораздо больше операций чтения/записи с диском.

Скорее всего, стоит еще раз подумать над индексами (особенно у InventTrans)
и провести реорганизацию данных (хотя бы один раз)

[cryomice]

Тоже подключусь к дискуссии.

По поводу отношения размера таблицы к размер индексов, для inventtrans и prodbom включена компрессия на таблицу, но на индексы компрессия не включена, отсюда такая разница в занимаемом месте. Без сжатия inventtrans занимает порядка 120ГБ. Отношение изменится, но все равно будет в районе 1, так что по всем вами указанным таблицам необходимо пересмотреть индексы.

Цитата:

Сообщение от Def

2. таблицы inventtrans, prodbom, имеют Page Compression

[mazzy]

спасибо. отлично.
тогда, судя по таблицам, особо лишнего в таблицах нет. похоже таблицы уже подчистили.

если думать об уменьшении размера, то только удаление журналов.
но с удалением журналов нужно быть предельно осторожным.

[Evgeniy2020]

При таких объемах, АОС и MS SQL естественно должны быть 64 битными.
и для того чтобы ОЗУ эффективно обрабатывать свыше 4 ГБ
(а не через PAE).

а вообще надеюсь что сеть у вас хотя бы Gigabit ethernet.

а вообще конечно здорово было бы, исторические данные поместить
в другой сервер, скажем те данные 2010 - 3 года, то есть до 2008 года
наверно желательно поместить в другую историческую базу.
ну и для отчетов за весь период дописать обращение к историческим данным.

можно еще технический аудит заказать из МС кажется,
но это не дешево.

для таких объемов даже элементарные join будут громадными, по выборке,
просто не будут в оперативку помещаться.

размеры индексов возможно гововорят о не малом их числе.
я лично не сторонник отключать auto update statistics
так как в прошлом у нас так одна база настолько замедлилась,
что мы не смогли даже понять где получились тормоза.

если было бы запасное оборудование, то можно было бы перекачать чистые данные,
и заново построить индексы на запасном оборудовании.

но все же наверно есть смысл свыше 3 лет данные наверно перенести в другой сервер,
или хотя бы в другую базу. так как индексы за весь период данных много занимают.

[mazzy]

Цитата:

Сообщение от Evgeniy2020

можно еще технический аудит заказать из МС кажется,
но это не дешево.

Цитата:

Сообщение от Evgeniy2020

для таких объемов даже элементарные join будут громадными, по выборке,
просто не будут в оперативку помещаться.

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

В стандартных отчетах будут использоваться гораздо меньшие выборки.
http://axapta.mazzy.ru/lib/inventsumdate/

Цитата:

Сообщение от Evgeniy2020

но все же наверно есть смысл свыше 3 лет данные наверно перенести в другой сервер,
или хотя бы в другую базу. так как индексы за весь период данных много занимают.

не, это плохой совет.

дело в том, что стандартная международная Аксапта изначально писалась под Оракл, в котором очень давно была возможность сегментировать данные по датам. И предполагалось, что старые данные просто будут вынесены в отдельный сегмент и размещены на отдельном диске.

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

И, соответственно, все отчеты писались так, чтобы с как можно меньшей вероятностью дергать старые данные (хотя такие отчеты и были конечно).

Но в результате такого подхода в Аксапте нет инструментов архивирования, переноса в другие таблицы и/или на другие сервера. Мало того, вся функциональность построена так, что в системе будут присутствовать все данные

Поэтому совет "перенести на другой сервер" - скорее всего, очень дорого обойдется предприятию.

==============
Да, Майкрософт готовил функционал архивирования в Акс6. но толком так и не смог его отладить. Поэтому будьте осторожны с таким советом. Особенно для ax4.

[Evgeniy2020]

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

в крайнем случае можно было бы посчитать сальдо 3 года назад,
и сооотвественно создать архивную базу а в новой от сальдо 3 ех летней давности, за то база будет на 70% меньше в среднем.

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

но это конечно помогло бы кардинально.

[mazzy]

Цитата:

Сообщение от Evgeniy2020

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

в крайнем случае можно было бы посчитать сальдо 3 года назад

архивная база - это не только сальдо.
придется что-то делать с сопоставлениями.
сопоставлений в аксапте - много.

это и сопоставления проводок клиентов (CustSettlement), и проводок поставщиков (VendSettlement). Это и складские сопоставления приходов и расходов (InventSettlement). и так далее.

От сопоставлений зависят курсовые/суммовые. себестоимость и куча всего.

в результате, какую бы дату архивирования ни выбрать, необходимо очень тщательно переработать сопоставления. А от результатов одних сопоставлений зависят другие. В общем случае, корректно разорвать непрерывные данные очень, ОЧЕНЬ сложно.

Разве что в каком-нибудь вырожденном случае

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

[_scorp_]

Цитата:

Сообщение от Evgeniy2020

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

Очень популярный способ в 1С Если мне не изменяет память, в 1С v7.7 была даже штатная операция закрытия базы для типовых, т.к. с какого то момента база 1С-ка не могла уже эффективно работать с большими таблицами.