PostgreSQL- What defines a session in ACC? | Community
Skip to main content
Steven_TechMark
Steven_TechMark
Level 2
April 25, 2019
Solved

PostgreSQL- What defines a session in ACC?

  • April 25, 2019
  • 3 replies
  • 2315 views

Hi,

While our DB has many tables that are using their own custom sequence, xtknewid is still being used by a select few large tables. What I've found is that while there is a fair few new records inserted, it pales in comparison to the rate of which xtknewid is increasing (eg. if 2 million new records are inserted using xtknewid in total, the sequences still goes up by 20 million or so).

This leaves the most likely conclusion that the cache value of xtknewid is contributing to this growth (it is currently set to 30) and decreasing it to a smaller number will solve this problem. What I want to find out is what is a session in the context of Postgres inside ACC? At the moment, the cache is set to create 30 new records inside the DB per session.

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Steven_TechMark

    HI Jon,

    Thanks for the answer. It will indeed most likely be the workflow logs table is contributing largely to this (I've since checked and it is outputting ~200-300K logs per day). I think your last point about the raw activity storage may also be the case as the workflows on the instance are using quite a widespread amount of SQL script to log data into various tables. The cache may be going hand-in-hand with the workflow log session created I think.

    Delivery/ broadlog-wise it looks ok as we are using the OOTB sequence nmsBroadlogId for these.

    The cache can be configured when using SQL script to configure the sequence cursor set inside the DB.

    ALTER SEQUENCE xtknewid .... CACHE 30 CYCLE;

    When using this for xtknewid previously we set it to 30 (which I think is the default for it regardless OOTB) alongside the CYCLE option that allow for the sequence values to be recycled.

    You could also set this when creating the custom sequence:

    SELECT CreateSequenceIfNecessary('NewSequence', '<cursor_value>', '<cache_value>');

    ....

    There is also also script available to check the information for a current sequence, including the cursor value and the cache value:

    'var cnx = application.getConnection()

    var seqname = "xtknewid";

    var request = "SELECT * FROM " + seqname + ";" ;

    var response = cnx.query( request );

    var result = "";

    for each (var row in response)

    logInfo("sequence: " + row[0] + " | last_value: " + row[1] + " | start_value: " + row[2] + " | increment by: " + row[3] + " | max value: " + row[4] + " | min value: " + row[5] + " | cache value: " + row[6])'

    We were able to confirm that cache is 30 for xtkNewId using this script.

    The above is explained in more detail here Adobe Campaign: Sequences Exhaustion - Adobe Campaign Classic​ (from 15:00 onwards and 4:00 onwards respectively). For the full scripts, I would contact Customer Care (Adobe Support) for these (I'm not certain if I'm able to share these publicly) or message a member of staff here who I am sure will be happy to provide them for you.

    Many thanks,

    Steven

    3 replies

    Jonathon_wodnicki
    Community Advisor
    Jonathon_wodnickiCommunity Advisor
    Community Advisor
    April 25, 2019

    Hi,

    Session-wise:

    Operator sessions are tracked in xtk:sessionInfo, which doesn't touch XtkNewId.

    DB sessions, sprocs, triggers, internal maintenance scripts, etc also don't touch XtkNewId.

    Workflow-wise:

    Workflow audit log uses XtkNewId in older builds. To remediate:

    • Reduce the amount of logging in the workflow
    • Reduce the frequency of execution of the workflow
    • Use a dedicated sequence for the table(s) being inserted into
    • Use a dedicated sequence for xtk:workflowLog
    • Avoid storing raw activity data in Adobe Campaign's db. If external delivery logs are being stored in custom tables, consider using the ootb ones instead.

    Out of curiosity, what is 'cache value of xtknewid' and where is it set to 30?

    Thanks,

    -Jon

      Steven_TechMark
      Steven_TechMarkAuthorAccepted solution
      Level 2
      April 30, 2019

      HI Jon,

      Thanks for the answer. It will indeed most likely be the workflow logs table is contributing largely to this (I've since checked and it is outputting ~200-300K logs per day). I think your last point about the raw activity storage may also be the case as the workflows on the instance are using quite a widespread amount of SQL script to log data into various tables. The cache may be going hand-in-hand with the workflow log session created I think.

      Delivery/ broadlog-wise it looks ok as we are using the OOTB sequence nmsBroadlogId for these.

      The cache can be configured when using SQL script to configure the sequence cursor set inside the DB.

      ALTER SEQUENCE xtknewid .... CACHE 30 CYCLE;

      When using this for xtknewid previously we set it to 30 (which I think is the default for it regardless OOTB) alongside the CYCLE option that allow for the sequence values to be recycled.

      You could also set this when creating the custom sequence:

      SELECT CreateSequenceIfNecessary('NewSequence', '<cursor_value>', '<cache_value>');

      ....

      There is also also script available to check the information for a current sequence, including the cursor value and the cache value:

      'var cnx = application.getConnection()

      var seqname = "xtknewid";

      var request = "SELECT * FROM " + seqname + ";" ;

      var response = cnx.query( request );

      var result = "";

      for each (var row in response)

      logInfo("sequence: " + row[0] + " | last_value: " + row[1] + " | start_value: " + row[2] + " | increment by: " + row[3] + " | max value: " + row[4] + " | min value: " + row[5] + " | cache value: " + row[6])'

      We were able to confirm that cache is 30 for xtkNewId using this script.

      The above is explained in more detail here Adobe Campaign: Sequences Exhaustion - Adobe Campaign Classic​ (from 15:00 onwards and 4:00 onwards respectively). For the full scripts, I would contact Customer Care (Adobe Support) for these (I'm not certain if I'm able to share these publicly) or message a member of staff here who I am sure will be happy to provide them for you.

      Many thanks,

      Steven

        Jonathon_wodnicki
        Community Advisor
        Jonathon_wodnickiCommunity Advisor
        Community Advisor
        April 30, 2019

        Oh you went much deeper on this, thanks, good to know.

        I've seen Adobe's sequence exhaustion doc and package, sequences are a scarce resource everyone finds out about when it's too late.

          Terms & ConditionsAccessibility statement

          Loading footer...