Пакетные задания, SPID/Session

[Pokersky09]

Доброго дня!

Существует проблема в нехватке количества 32 767 одновременных соединений к SQL серверу.
Обрабатываемая задача: загрузка данных из файлов csv.


Текущая конфигурация DAX:
• Ax2012 R3 (Системная версия: 6.3.1000.1928 Версия приложения: 6.3.1000.473)
• AOS x6
• SQL 2014
• Пользователей нет

Текущие настройки:
• Макс кол-во сессий: по 500 на AOS, итого 500*6 = 3000 сессий в пакетных заданиях (в настройках Администрирования Ах пишется как кол-во потоков).
• Макс кол-во подключений на SQL = не ограничено (макс системный порог 32 767)

Наблюдения:
При работе некоторых классов загрузки, кол-во соединений около 2,5 на 1 сессию. Иногда кол-во соединений достигает 80-100 на 1 поток, что приводит к превышению макс.кол-ва подключений и вылету всех AOS, с выдачей соотв. сообщений в журналы.

В любой момент времени работы пакетов, запросом sp_who на sql сервере видно что 99% SPID в состоянии sleeping_AwaitingCommand.

По завершении пакетного задания (точнее каждого Batch), подключения к sql исчезают, однако активные сессии, созданные пакетным заданием, по прежнему остаются на некоторое время, затем сами по себе исчезают.
Блокировок на sql сервере не наблюдается в любой момент.

Временным решением является сокращение кол-ва доступных потоков на АОСах до 100, в этом случае кол-во соединений остается в рамках квоты sql. Однако скорость загрузок резко ухудшается.

Алгоритм загрузки
-Обращение к таблице, для выявления существования ранее загруженной строки
-Заполнение курсора (в т.ч. обращение к справочникам для получения RecId)
-Update/Insert или пропуск строки, если существующая строка идентична.

Загрузка возможна одновременно в несколько таблиц, например SalesAgreementHeader, LogisticsLocation, LogisticsPostalAddress.

Влияние кол-ва конечных таблиц, куда идет запись, а также кол-во связанных справочников (куда идут запросы на получение RecId курсоров), не обязательно приводит к увеличению кол-ва подключений к SQL, относительно простейшей загрузки в 1 таблицу без Relation. Однако значение количества SPID/Session всегда примерно одинаково для каждого класса.

Вопросы:

1) По какому принципу формируется минимальное кол-во подключений к sql серверу, и есть ли четкая связь с элементами кода Ах?

2) Когда подключение прекращается, и в каких случаях переводится в sleeping?

3) Предусмотрены ли способы управления/контроля/ограничения максимальным кол-ом соединений (не сессий) со стороны Ах, чтобы исключить данную проблему?

4) Каков механизм снятия сессий, после завершения пакетного задания?

[gl00mie]

Посмотрите Application Object Server (AOS) configuration commands, раздел Database tuning options, там указаны описания настроек, которые можно увидеть в конфигурационной утилите сервера, в том числе:

• Leave the connection running when idle
• Maximum idle time before closing
• Maximum open cursors

А еще рассмотрите вариант обновления ядра, последняя сборка вроде - 6.3.3000.660, весьма вероятно, что в ранних сборках были какие-нибудь утечки ресурсов.

[Pokersky09]

Цитата:

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

Посмотрите Application Object Server (AOS) configuration commands, раздел Database tuning options, там указаны описания настроек, которые можно увидеть в конфигурационной утилите сервера, в том числе:

• Leave the connection running when idle
• Maximum idle time before closing
• Maximum open cursors

А еще рассмотрите вариант обновления ядра, последняя сборка вроде - 6.3.3000.660, весьма вероятно, что в ранних сборках были какие-нибудь утечки ресурсов.

Вопрос решен, помогло явное указание параметра AOS:

• Maximum idle time before closing = 0

[b_nosoff]

Цитата:

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

4) Каков механизм снятия сессий, после завершения пакетного задания?

Делайте connection.Close() перед выходом

[Pokersky09]

Выявил источник проблемы:

В DimensionStorage:SavePrivate()

 new userConnection()

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

При создании открывается новое соединение UserConnection, и почему то соединение не снимается, а остается в пуле на некоторое время (более 10минут), настройки аосов выполнили согласно описанию выше постом.

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

Пробовал добавлять в конце метода UserConnection.Finalize(), однако результатов не принесло, соединения копятся гораздо быстрее, чем снимаются. Подсмотрено в методах NumberSeq, там аналогично используются userConnection, и проблем не возникает.

/// <summary>
///    Saves the current information and retrieves the record ID of the persisted combination.
/// </summary>
/// <returns>
///    The record ID of the combination.
/// </returns>
private recId savePrivate()
{
    #OCCRetryCount
    #LedgerSHA1Hash
    DimensionSHA1Hash hash;

    DimensionAttributeLevelValue                dimAttrLevelValue;
    DimensionAttributeValueGroup                dimAttrValueGroup;
    DimensionAttributeValueGroupCombination     dimAttrValueGroupCombo;
    DimensionAttributeValueCombination          dimAttrValueCombo;
    DimensionHierarchy                          accountStructure;
    DimensionHierarchyLevel                     mainAccountSegment;
    DimensionAttributeValue                     mainAccountDimAttrValue;
    int                                         hierarchyIndex;
    int                                         segmentIndex;
    int                                         previousSegmentIndex;
    int                                         i;
    int                                         hierarchyCount;
    int                                         segmentCountForHierarchy;
    DimensionStorageSegment                     segment;
    XDSServices                                 xdsServices;
    UserConnection                              userConnection;
    LedgerDimensionBase                         savedComboId;

    if (Debug::debugMode())
    {
        Debug::assert(this.hierarchyCount() > 0);
        Debug::assert(segments != null);
        Debug::assert(totalSegmentCount > 0);
        DimensionStorage::validateCombinationIntegrity(this);
    }

    // Calculate the overall super-combo hash
    hash = this.getComboHash();

    if (hash == connull())
    {
        Debug::printDebug(strfmt('Warning: LedgerDimension of %1 is not created as no segments with DimensionAttributeValues exist (Is the backing entity instance missing?).', dimAttrValueCombo.DisplayValue));

        // Don't save combinations without level values
        initialComboId = 0;
        initialHash = connull();
        return 0;
    }
    else if (hash == initialHash)
    {
        // Nothing to do if nothing's changed
        Debug::assert(DimensionAttributeValueCombination::exist(initialComboId));
        return initialComboId;
    }

    // Turn off XDS to ensure the reference can be found.
    xdsServices = new XDSServices();
    xdsServices.setXDSState(0);

    savedComboId = DimensionStorage::getSavedComboRecIdByHash(hash);

    if (savedComboId)
    {
        initialComboId = savedComboId;
        initialHash = hash;

        return savedComboId;
    }

    // Create the main combination and group link in a separate transaction for smaller transaction scope to prevent blocking
    userConnection = new userConnection();
    userConnection.ttsBegin();
    dimAttrValueCombo.setConnection(userConnection);
    dimAttrValueGroupCombo.setConnection(userConnection);

    try
    {
        // A matching was not found, so now insert a new combination. This will link to any existing sub-groups found or new sub-groups are inserted and linked
        dimAttrValueCombo.DisplayValue = this.getComboDisplayValue();
        dimAttrValueCombo.LedgerDimensionType = ledgerDimensionType;
        dimAttrValueCombo.Hash = hash;

        // Look up the account structure and main account if applicable
        accountStructure = DimensionHierarchy::find(this.getHierarchyId(1));
        //Debug::assert(accountStructure.RecId);  // TODO: Enable once the unit test setup data correctly

        if (accountStructure && (accountStructure.StructureType == DimensionHierarchyType::AccountStructure))
        {
            // Denormalize the account structure and main account
            dimAttrValueCombo.AccountStructure = accountStructure.RecId;

            // Look up the main account segment, which uses the hierarchy/dimension attribute
            // index so this will be a cached lookup
            mainAccountSegment = DimensionHierarchyLevel::findByDimensionHierarchyAndDimAttribute(
                accountStructure.RecId,
                DimensionAttribute::getMainAccountDimensionAttribute());

            // If the hierarchy is an account structure, then the dimension hierarchy level
            // will be indexed the same in the storage segment collection as in the account
            // structure
            if (mainAccountSegment && this.segmentCount() >= mainAccountSegment.Level)
            {
                // Look up the main account recid from the sepecified DAV. This will
                // normally be a cached lookup since it was recently used to create the
                // DAV that was passed to the storage object.
                mainAccountDimAttrValue = DimensionAttributeValue::find(this.getSegment(mainAccountSegment.Level).parmDimensionAttributeValueId());
                dimAttrValueCombo.MainAccount = mainAccountDimAttrValue.EntityInstance;
            }
        }
        else if (accountStructure && (accountStructure.StructureType == DimensionHierarchyType::DefaultAccount))
        {
            // Denormalize the main account
            Debug::assert(this.segmentCount() == 1);

            // Look up the main account recid from the sepecified DAV. This will
            // normally be a cached lookup since it was recently used to create the
            // DAV that was passed to the storage object.
            mainAccountDimAttrValue = DimensionAttributeValue::find(this.getSegment(1).parmDimensionAttributeValueId());
            dimAttrValueCombo.MainAccount = mainAccountDimAttrValue.EntityInstance;
        }

        // Create the header value
        dimAttrValueCombo.insert();

        savedComboId = dimAttrValueCombo.RecId;
        initialComboId = savedComboId;
        initialHash = hash;
        hierarchyCount = this.hierarchyCount();

        for (hierarchyIndex = 1; hierarchyIndex <= hierarchyCount; hierarchyIndex++)
        {
            segmentCountForHierarchy = this.segmentCountForHierarchy(hierarchyIndex);
            hash = this.getGroupHash(hierarchyIndex);
            previousSegmentIndex = segmentIndex;

            try
            {
                // Try to find an existing value group that matches what we need for the specified group - use the default connection (as it would have been saved by another process's connection anyway)
                select firstOnly RecId from dimAttrValueGroup where dimAttrValueGroup.Hash == hash;
                if (dimAttrValueGroup.RecId)
                {
                    segmentIndex += segmentCountForHierarchy;
                }
                else
                {
                    // Create the group and levels in the same separate transaction
                    dimAttrValueGroup.setConnection(userConnection);
                    dimAttrLevelValue.setConnection(userConnection);

                    dimAttrValueGroup.DimensionHierarchy = this.getHierarchyId(hierarchyIndex);
                    dimAttrValueGroup.Hash = hash;
                    dimAttrValueGroup.Levels = segmentCountForHierarchy;
                    dimAttrValueGroup.insert();

                    for (i = 0; i < segmentCountForHierarchy; i++)
                    {
                        segmentIndex++;
                        segment = this.getSegment(segmentIndex);
                        if (!segment.isEmpty())
                        {
                            dimAttrLevelValue.DimensionAttributeValueGroup = dimAttrValueGroup.RecId;
                            dimAttrLevelValue.DimensionAttributeValue = segment.parmDimensionAttributeValueId();
                            dimAttrLevelValue.DisplayValue = segment.parmDisplayValue();
                            dimAttrLevelValue.Ordinal = i + 1;
                            dimAttrLevelValue.insert();
                        }
                    }
                }

                dimAttrValueGroupCombo.DimensionAttributeValueGroup = dimAttrValueGroup.RecId;
                dimAttrValueGroupCombo.DimensionAttributeValueCombination = savedComboId;
                dimAttrValueGroupCombo.Ordinal = hierarchyIndex;
                dimAttrValueGroupCombo.insert();
            }
            catch (Exception::DuplicateKeyException)    // Finding or creating the DAVG
            {
                if (xSession::currentRetryCount() < #RetryNum)
                {
                    segmentIndex = previousSegmentIndex;   // Revert to previous position, since insert failed
                    retry;
                }

                Debug::assert(dimAttrValueGroupCombo.RecId != 0);

                // Failed to insert a new group -- restart the transaction and try to find the full combination again (by outer catch)
                userConnection.ttsAbort();
                userConnection.ttsBegin();
                throw Exception::DuplicateKeyException;
            }
        }

        // Successfully inserted a completely new combination
        userConnection.ttsCommit();
    }
    catch (Exception::DuplicateKeyException)    // Finding or creating the DAVC
    {
        // Attempt to re-read the DAVC as another one was apparently inserted - use the default connection (as it would have been saved by another process's connection anyway)
        savedComboId = DimensionStorage::getSavedComboRecIdByHash(hash);

        if (savedComboId)
        {
            initialComboId = savedComboId;
            initialHash = hash;
        }
        else if (xSession::currentRetryCount() < #RetryNum)
        {
            // The retry will be a rare case where we didn't find it, tried to insert, got dup, tried to reread and didn't find it again so will attempt to re-insert
            retry;
        }
        else
        {
            // Record level security may have prevented the record from being found even with XDS off.
            Debug::assert(savedComboId != 0);
        }

        // Failed to insert a new combination
        userConnection.ttsAbort();
    }

    userConnection().finalize();//Добавлено для проверки

    return savedComboId;
}

Напомню, текущая задача является импорт строк LedgerJournalTrans в количестве более 50млн, кол-во строк в CustTable составляет порядка 2х Млн. Соответственно при импорте каждой строки вызывается метод

DimensionStorage::getDynamicAccount(custAccount , LedgerJournalACType::Cust);

Временным решением нашли предварительное создание аналитик, а уже после загрузку данных:

static server void DimAttributeValueCombCREATE_AXFORUM(Args _args)
{
    #define.maxRowCount(100000)
    
    int64           tick1 = WinAPI::getTickCount64();
    int             countCurrent;
    int             countCreate;
    int             countMax    = #maxRowCount;
    int             countCatch;
    CustAccount     custAccount;
    CustTable                           custTable;
    DimensionAttributeValueCombination  dimComb;

    return;//Снять в случае необходимости исполнения кода

    while select AccountNum from  custTable
    order by custTable.AccountNum asc
    notexists join RecId from dimComb
        where custTable.AccountNum == dimComb.DisplayValue
    {
        countCurrent++;
        if(countCurrent >= countMax)
            break;

        custAccount = custTable.AccountNum;

        if(custAccount)
        {
            try
            {
                DimensionStorage::getDynamicAccount(custAccount , LedgerJournalACType::Cust);
                countCreate++;
            }
            catch
            {
                    countCatch++;
            }
        }
    }

    info(strFmt('countCurrent: (%1)',   countCurrent));
    info(strFmt('countCreate: (%1)',    countCreate));
    info(strFmt('countCatch: (%1)',     countCatch));

    info(strFmt("%1", time2str(ms2TimeOfDay(WinAPI::getTickCount64() - tick1),1,1)));
}

[b_nosoff]

Немного смущает то, что в цикле по иерархиям каждый раз выполняется

    dimAttrValueGroup.setConnection(userConnection);
    dimAttrLevelValue.setConnection(userConnection);

да еще и курсор dimAttrValueGroup при первом проходе селектится в дефолтном соединении.

Попробуйте вынести эти две строчки перед циклом, и select делать для отдельной переменной (только надо не забыть при нахождении записи скопировать ее RecId в dimAttrValueGroup, она дальше используется)

И еще, попробуйте добавить

 userConnection = null;

после

userConnection.finalize();

кстати, у вас описка там (userConnection со скобками)?