Documentation Campaign Campaign Classic v7 Documentation

[On-premise/hybrid only]{class="badge yellow" title="Applies to on-premise and hybrid deployments only"}

RDBMS Specific recommendations rdbms-specific-recommendations

Last update: Tue Sep 10 2024 00:00:00 GMT+0000 (Coordinated Universal Time)

Topics:
Monitoring

To help you set up maintenance plans, this section lists some recommendations and best practices adapted to the various RDBMS engines that Adobe Campaign supports. However, these are only recommendations. It is up to you to adapt them to your needs, in keeping with your internal procedure and constraints. Your database administrator has the responsibility to build and execute these plans.

PostgreSQL postgresql

Detecting large tables detecting-large-tables

You can add the following view to your database:

code language-none

code language-none
create or replace view uvSpace as SELECT c1.relname AS tablename, c2.relname AS indexname, c2.relpages * 8 / 1024 AS size_mbytes, c2.relfilenode AS filename, cast(0 AS bigint) AS row_count FROM pg_class c1, pg_class c2, pg_index i WHERE c1.oid = i.indrelid AND i.indexrelid = c2.oid UNION SELECT pg_class.relname AS tablename, NULL::"unknown" AS indexname, pg_class.relpages * 8 /1024 AS size_mbytes, pg_class.relfilenode AS filename, cast(pg_class.reltuples as bigint) AS row_count FROM pg_class WHERE pg_class.relkind = 'r'::"char" ORDER BY 3 DESC, 1, 2 DESC;

create or replace view uvSpace
 as
 SELECT c1.relname AS tablename, c2.relname AS indexname, c2.relpages * 8  / 1024 AS size_mbytes, c2.relfilenode AS filename, cast(0 AS bigint) AS row_count
 FROM pg_class c1, pg_class c2, pg_index i
 WHERE c1.oid = i.indrelid AND i.indexrelid = c2.oid
 UNION
 SELECT pg_class.relname AS tablename, NULL::"unknown" AS indexname, pg_class.relpages * 8  /1024  AS size_mbytes, pg_class.relfilenode AS filename, cast(pg_class.reltuples as bigint) AS row_count
 FROM pg_class
 WHERE pg_class.relkind = 'r'::"char"
 ORDER BY 3 DESC, 1, 2 DESC;

You can run this query to spot large tables and indexes:

code language-none
`SELECT * FROM uvSpace;`

Alternatively, you can run this query, for example, to see all index sizes collectively:

code language-none

code language-none
`SELECT tablename, sum(size_mbytes) AS "sizeMB_all", ( SELECT sum(size_mbytes) FROM uvspace AS uv2 WHERE INDEXNAME IS NULL AND uv1.tablename = uv2.tablename ) AS "sizeMB_data", ( SELECT sum(size_mbytes) FROM uvspace AS uv2 WHERE INDEXNAME IS NOT NULL AND uv1.tablename = uv2.tablename ) AS "sizeMB_index", ( SELECT ROW_COUNT FROM uvspace AS uv2 WHERE INDEXNAME IS NULL AND uv1.tablename = uv2.tablename ) AS ROWS FROM uvspace AS uv1 GROUP BY tablename ORDER BY 2 DESC`

SELECT
   tablename,
   sum(size_mbytes) AS "sizeMB_all",
   (
      SELECT sum(size_mbytes)
      FROM uvspace
      AS uv2
      WHERE
         INDEXNAME IS NULL
         AND uv1.tablename = uv2.tablename
   ) AS "sizeMB_data",
   (
      SELECT sum(size_mbytes)
      FROM uvspace
      AS uv2
      WHERE
         INDEXNAME IS NOT NULL
         AND uv1.tablename = uv2.tablename
   ) AS "sizeMB_index",
   (
      SELECT ROW_COUNT
      FROM uvspace
      AS uv2
      WHERE
         INDEXNAME IS NULL
         AND uv1.tablename = uv2.tablename
   ) AS ROWS FROM uvspace AS uv1
   GROUP BY tablename
   ORDER BY 2 DESC

Simple maintenance simple-maintenance

IMPORTANT

Adobe strongly suggests not to run VACUUM FULL on Campaign Adobe-hosted database setups.The maintenance suggested are a guide for ON-PREMISE installations only. For custom table implementations and schemas, use VACUUM FULL at your own risk as VACUUM - without monitoring - can exclusively lock tables causing stalled queries - and in some cases, lock up the entire database.

In PostgreSQL, you can use these typical keywords:

VACUUM (FULL, ANALYZE, VERBOSE)

To run the VACUUM operation, and analyze and time it, you can use this syntax:

\timing on
VACUUM (FULL, ANALYZE, VERBOSE) <table>;

We strongly recommend that you do not omit the ANALYZE statement. Otherwise, the vacuumed table is left with no statistics. The reason is that a new table is built, then the old one is deleted. As a result, the object ID (OID) of the table changes, but no statistics are computed. Consequently, you will immediately experience performance issues.

Here is a typical example of an SQL maintenance plan to be executed on a regular basis:

\timing on
VACUUM (FULL, ANALYZE, VERBOSE) nmsdelivery;

\timing on
VACUUM (FULL, ANALYZE, VERBOSE) nmsdeliverystat;

\timing on
VACUUM (FULL, ANALYZE, VERBOSE) xtkworkflow;

\timing on
VACUUM (FULL, ANALYZE, VERBOSE) xtkworkflowevent;

\timing on
VACUUM (FULL, ANALYZE, VERBOSE) xtkworkflowjob;

\timing on
VACUUM (FULL, ANALYZE, VERBOSE) xtkworkflowlog;

\timing on
VACUUM (FULL, ANALYZE, VERBOSE) xtkworkflowtask;

\timing on
VACUUM (FULL, ANALYZE, VERBOSE) xtkjoblog;

\timing on
VACUUM (FULL, ANALYZE, VERBOSE) xtkjob;

\timing on
VACUUM (FULL, ANALYZE, VERBOSE) nmsaddress;

\timing on
VACUUM (FULL, ANALYZE, VERBOSE) nmsdeliverypart;

\timing on
VACUUM (FULL, ANALYZE, VERBOSE) nmsmirrorpageinfo;

NOTE

Adobe recommends starting with smaller tables: this way, if the process fails on large tables (where the risk of failure is highest), at least part of the maintenance has been completed.
Adobe recommends that you add the tables that are specific to your data model, which can be subject to significant updates. This can be the case for NmsRecipient if you have large daily data replication flows.
The VACUUM statement will lock the table, which pauses some processes while maintenance is carried out.
For very large tables (typically above 5 Gb), the VACUUM FULL statement can become quite inefficient and take a very long time. Adobe does not recommend using it for the YyyNmsBroadLogXxx table.
This maintenance operation can be implemented by an Adobe Campaign workflow, using an SQL activity. For more on this, refer to this section. Make sure you schedule maintenance for a low activity time that does not collide with your backup window.

Rebuilding a database rebuilding-a-database

PostgreSQL does not provide an easy way to perform an online table rebuild since the VACUUM FULL statement locks the table, thus preventing regular production. This means that maintenance has to be performed when the table is not used. You can either:

perform maintenance when the Adobe Campaign platform is stopped,
stop the various Adobe Campaign subservices that are likely to write in the table being rebuilt (nlserver stop wfserver instance_name to stop the workflow process).

Here is an example of table defragmentation using specific functions to generate the necessary DDL. The following SQL lets you create two new functions: GenRebuildTablePart1 and GenRebuildTablePart2, which can be used to generate the necessary DDL to recreate a table.

The first function lets you create a work table (** _tmp** here) which is a copy of the original table.
The second function then deletes the original table and renames the work table and its indexes.
Using two functions instead of one means that if the first one fails, you don’t run the risk of deleting the original table.

 -- --------------------------------------------------------------------------
 -- Generate the CREATE TABLE DDL for a table
 -- --------------------------------------------------------------------------
 create or replace function GenTableDDL(text) returns text as $$
 declare
 vstrTable text;
 vrecFld RECORD;
 vstrDDL text;
 vstrFields text;
 vstrNsTable text;
 vstrTableSpace text;
 begin
 vstrTable = lower($1);

 vstrDDL = ;

 SELECT
 pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),
 pg_catalog.quote_ident(t.spcname)
 INTO
 vstrNsTable, vstrTableSpace
 FROM
 pg_namespace n, pg_class c left outer join pg_tablespace t on c.reltablespace = t.oid
 WHERE
 n.oid = c.relnamespace AND
 c.relname = vstrTable;

 vstrDDL = 'CREATE TABLE ' || vstrNsTable || '_tmp(';

 vstrFields = ;
 FOR vrecFld IN
 SELECT
 pg_catalog.quote_ident(a.attname) ||
 ' ' || t.typname ||
 case when t.typname='varchar' then '(' || cast(a.atttypmod-4 as text) || ')'
 when t.typname='numeric' then '(' || cast((a.atttypmod-4)/65536 as text) || ',' || cast((a.atttypmod-4)%65536 as text) || ')'
 else end ||
 case when a.attnotnull then ' not null' else end ||
 case when a.atthasdef then ' default '|| d.adsrc else end as DDL
 FROM
 pg_type t, pg_class c, pg_attribute a LEFT OUTER JOIN pg_attrdef d ON d.adrelid=a.attrelid and d.adnum=a.attnum
 WHERE
 a.attnum > 0 AND
 a.attrelid = c.oid AND
 t.oid = a.atttypid AND
 c.relname = vstrTable
 ORDER BY
 a.attnum
 LOOP
 IF vstrFields <> THEN
 vstrFields = vstrFields || ',' || chr(10) || ' ';
 ELSE
 vstrFields = vstrFields || chr(10) || ' ';
 END IF;
 vstrFields = vstrFields || vrecFld.DDL;
 END LOOP;

 vstrDDL = vstrDDL || vstrFields || chr(10) || ')';
 if vstrTableSpace <> then
 vstrDDL = vstrDDL || ' TABLESPACE ' || vstrTableSpace;
 end if;
 vstrDDL = vstrDDL || ';' || chr(10);

 return vstrDDL;
 END;
 $$ LANGUAGE plpgsql;

 -- --------------------------------------------------------------------------
 -- Generate the CREATE INDEX DDL for a table
 -- --------------------------------------------------------------------------
 create or replace function GenIndexDDL(text) returns text as $$
 declare
 vstrTable text;
 vrecIndex RECORD;
 vstrDDL text;
 viFld integer;
 vstrFld text;
 begin
 vstrTable = lower($1);

 vstrDDL = ;

 FOR vrecIndex IN
 SELECT
 i.indkey, i.indisunique,
 pg_catalog.quote_ident(c.relname) as tablename,
 pg_catalog.quote_ident(ic.relname) as indexname,
 pg_catalog.quote_ident(t.spcname) as tablespace
 FROM
 pg_class c, pg_index i, pg_class ic left outer join pg_tablespace t on ic.reltablespace = t.oid
 WHERE
 i.indexrelid = ic.oid AND
 i.indrelid = c.oid AND
 c.relname = vstrTable
 LOOP

  vstrDDL = vstrDDL || 'CREATE ';
  if vrecIndex.indisunique then
  vstrDDL = vstrDDL || 'UNIQUE ';
  end if;
  vstrDDL = vstrDDL ||
   'INDEX ' ||vrecIndex.indexname || '_tmp ON ' ||
   vrecIndex.tablename || '_tmp(';

  FOR viFld IN array_lower(vrecIndex.indkey, 1) .. array_upper(vrecIndex.indkey, 1) LOOP
  SELECT pg_catalog.quote_ident(a.attname) INTO vstrFld
  FROM
   pg_attribute a, pg_class c
  WHERE
   a.attnum = vrecIndex.indkey[viFld] AND
   a.attrelid = c.oid AND c.relname=vstrTable;

  vstrDDL = vstrDDL || vstrFld;
  if viFld <> array_upper(vrecIndex.indkey, 1) then
   vstrDDL = vstrDDL || ', ';
  end if;
  END LOOP;
  vstrDDL = vstrDDL || ')';

  if vrecIndex.tablespace <> then
  vstrDDL = vstrDDL || 'TABLESPACE ' || vrecIndex.tablespace;
  end if;
  vstrDDL = vstrDDL || ';' || chr(10);

 END LOOP;

 return vstrDDL;
 END;
 $$ LANGUAGE plpgsql;

 -- --------------------------------------------------------------------------
 -- Generate the ALTER INDEX RENAME for a table
 -- --------------------------------------------------------------------------
 create or replace function GenRenameIndexDDL(text) returns text as $$
 declare
 vstrTable text;
 vrecIndex RECORD;
 vstrDDL text;
 begin
 vstrTable = lower($1);

 vstrDDL = ;

 FOR vrecIndex IN
  SELECT
  pg_catalog.quote_ident(n.nspname) as namespace,
  pg_catalog.quote_ident(ic.relname) as indexname
  FROM
  pg_namespace n, pg_class c, pg_index i, pg_class ic
  WHERE
  i.indexrelid = ic.oid AND
  n.oid = ic.relnamespace AND
  i.indrelid = c.oid AND
  c.relname = vstrTable
 LOOP

  vstrDDL = vstrDDL || 'ALTER INDEX ' || vrecIndex.namespace || '.' || vrecIndex.indexname ||
    '_tmp RENAME TO ' || vrecIndex.indexname ||
    ';' || chr(10);
 END LOOP;

 return vstrDDL;
 END;
 $$ LANGUAGE plpgsql;

 -- --------------------------------------------------------------------------
 -- Build a copy of a table, with index
 -- --------------------------------------------------------------------------
 create or replace function GenRebuildTablePart1(text) returns text as $$
 declare
 vstrTable text;
 vstrTmp text;
 vstrDDL text;
 begin
 vstrTable = lower($1);

 vstrDDL = ;

 SELECT GenTableDDL(vstrTable) INTO vstrTmp;
 vstrDDL = vstrDDL|| vstrTmp || chr(10);

 vstrDDL = vstrDDL|| 'INSERT INTO ' || vstrTable || '_tmp SELECT * FROM ' || vstrTable || ';'||chr(10);
 SELECT GenIndexDDL(vstrTable) INTO vstrTmp;

 vstrDDL = vstrDDL|| vstrTmp || chr(10);
 vstrDDL = vstrDDL|| 'VACUUM ANALYSE '|| vstrTable || '_tmp;' ||chr(10);

 return vstrDDL;
 end;
 $$ LANGUAGE plpgsql;

 -- --------------------------------------------------------------------------
 -- Drop the original table and rename the copy
 -- --------------------------------------------------------------------------
 create or replace function GenRebuildTablePart2(text) returns text as $$
 declare
 vstrTable text;
 vstrTmp text;
 vstrDDL text;
 begin
 vstrTable = lower($1);

 vstrDDL = 'DROP TABLE ' || vstrTable||';'|| chr(10);
 vstrDDL = vstrDDL|| 'ALTER TABLE ' || vstrTable || '_tmp RENAME TO ' || vstrTable ||';'|| chr(10);

 SELECT GenRenameIndexDDL(vstrTable) INTO vstrTmp;
 vstrDDL = vstrDDL|| vstrTmp || chr(10);

 return vstrDDL;
 end;
 $$ LANGUAGE plpgsql;

The following example can be used in a workflow to rebuild the required tables rather than using the vacuum/rebuild command:

function sqlGetMemo(strSql)
 {
 var res = sqlSelect("s, m:memo", strSql);
 return res.s.m.toString();
 }

 function RebuildTable(strTable)
 {
 // Rebuild a table_tmp
 var strSql = sqlGetMemo("select GenRebuildTablePart1('"+strTable+"')");
 logInfo("Rebuilding table '"+strTable+"'...");
 // logInfo(strSql);
 sqlExec(strSql);

 // If fails, there is an exception thrown and so we do not delete the original table
 strSql = sqlGetMemo("select GenRebuildTablePart2('"+strTable+"')");
 logInfo("Swapping table '"+strTable+"'...");
 //logInfo(strSql);
 sqlExec(strSql);
 }

 RebuildTable('nmsrecipient');
 RebuildTable('nmsrcpgrlrel');
 // ... other tables here

Oracle oracle

Please contact your database administrator to find out about the procedures best suited to your version of Oracle.

Microsoft SQL Server microsoft-sql-server

NOTE

For Microsoft SQL Server, you can use the maintenance plan detailed on this page.

The example below concerns Microsoft SQL Server 2005. If you are using another version, contact your database administrator to find out about maintenance procedures.

First, connect to Microsoft SQL Server Management Studio, with a login with administrator rights.
Go to the Management > Maintenance Plans folder, right-click on it and choose Maintenance Plan Assistant.
Click Next when the first page comes up.
Select the type of maintenance plan you want to create (separate schedules for each task or single schedule for the whole plan), then click the Change… button.
In the Job schedule properties window, select the desired execution settings and click OK, then click Next.

Select the maintenance tasks you want to perform, then click Next.

note note
NOTE
We recommend performing at least the maintenance tasks shown below. You may also select the statistics update task, although it is already carried out by the database cleanup workflow.

In the drop-down list, select the database on which you want to run the Database Check Integrity task.
Select the database and click OK, then click Next.

Configure the maximum size allocated to your database, then click Next.

note note
NOTE
If the size of the database exceeds this threshold, the maintenance plan will try to delete unused data to free up space.

Reorganize or rebuild the index:

If the index fragmentation rate is between 10% and 40%, a reorganization is recommended.

Choose which databases and objects (tables or views) you want to reorganize, then click Next.

note note
NOTE
Depending on your configuration you can choose either the previously selected tables, or all tables in your database.

If the index fragmentation rate is higher than 40%, a rebuild is recommended.

Select the options you want to apply to the index rebuild task, then click Next.

note note
NOTE
The rebuild index process is more constricting in terms of processor use and it locks the database resources. Select the Keep index online while reindexing option if you want the index to be available during the rebuild.

Select the options you want to display in the activity report, then click Next.
Check the list of tasks configured for the maintenance plan, then click Finish.

A summary of the maintenance plan and the statuses of its various steps is displayed.
Once the maintenance plan is complete, click Close.
In the Microsoft SQL Server explorer, double-click the Management > Maintenance Plans folder.

Select the Adobe Campaign maintenance plan: the various steps are detailed in a workflow.

Note that an object has been created in the SQL Server Agent > Jobs folder. This object lets you start the maintenance plan. In our example, there is only one object since all the maintenance tasks are part of the same plan.

note important
IMPORTANT
For this object to run, the Microsoft SQL Server agent must be enabled.

Configuring a separate database for working tables

NOTE

This configuration is optional.

The WdbcOptions_TempDbName option allows you to configure a separate database for working tables on Microsoft SQL Server. This optimizes backups and replication.

This option can be used if you want the working tables (for example, the tables created during the execution of a workflow) to be created on another database.

When you set the option to “tempdb.dbo.”, the working tables are created on the default temporary database of Microsoft SQL Server. The database administrator needs to allow write access to the tempdb database.

If the option is set, it is used on all Microsoft SQL Server databases that are configured in Adobe Campaign (main database and external accounts). Note that if two external accounts share the same server, conflicts may happen (as the tempdb is unique). In the same way, if two Campaign instances use the same MSSQL server, there may be conflicts if they use the same tempdb.

recommendation-more-help

601d79c3-e613-4db3-889a-ae959cd9e3e1