PostgreSQL- What defines a session in ACC?

[Steven_TechMark]

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.

[Jonathon_wodnicki]

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 · Accepted solution]

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]

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.