How to boost your Adobe Campaign database performance? | DB Vacuum

adobe-campaign-how-to-boost-database-performance-vacuum-techonol

Happy New Year to Everyone out there! πŸ™‚

Prajwal Shetty

This year, I am going to try a lot of different things on this website. I hope you are all set for it. The ride will be a bit bumpy but equally exciting.

Let’s get started..

There are 2 kinds of pain:
The sort of pain that makes you strong,

or useless pain,
The sort of pain that’s only suffering.

I have no patience for useless things.

Moments like this require someone who will act, who will do the unpleasant thing, the necessary thing.

After vacuum..There…No more pain.

Kevin Spacey, House of Cards

I will split the above quote into 3 parts and correlate it to a problem we will be solving in Adobe Campaign.

Part 1

There are 2 kinds of pain:
The sort of pain that makes you strong,
or useless pain,
The sort of pain that’s only suffering.

The pain that I am referring to is the extreme slowness that you experience while working on an unoptimized database.

This is the story with almost all the products out there.If the database is not healthy, the user experience of the developers as well as the end users will be painful.

Let’s take a scenario. Six months back your company had purchased the Adobe Campaign product.You observed that the newly provisioned instance is working really fast.It can modify, select, delete the record in very less time.

Fast forward to today, a simple query on the broadLogRcp table takes 2 hours. This is painful πŸ™‚

Part 2

I have no patience for useless things.

Our time is precious and we should not let a simple database query consume it.This is the reason it is very important to keep your database in healthy condition.

Some of the minor things that you can do are:

  • Stop all the paused and unwanted started workflows (I will explain the detailed reasoning behind this in another upcoming post)
  • Disable the “Keep Interim” flag on the workflows.

The are many others as well but let’s focus on the major step that you can take. This step is called the database vacuum.

Part 3

Moments like this require someone who will act, who will do the unpleasant thing, the necessary thing.

Your database administrator is the one who has to do the unpleasant thing(vacuum the database)

There are two types of database vacuum:

Partial Vacuum: You identify the tables that have the highest index bloating and perform vacuum on this table only.

Full Vacuum: You vacuum the complete database.

Now, I have compiles a list of SQL queries that will help you understand your current health of the database. Also, this is applicable for PostgreSQL database only. I will post another article covering other databases.

Index Summary: Query to pull the number of rows, indexes, and some info about those indexes for each table. Ditch the pg_size_pretty if you’re on an ancient (<= 8.2) version)

SELECT
    pg_class.relname,
    pg_size_pretty(pg_class.reltuples::BIGINT) AS rows_in_bytes,
    pg_class.reltuples AS num_rows,
    COUNT(indexname) AS number_of_indexes,
    CASE WHEN x.is_unique = 1 THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    SUM(CASE WHEN number_of_columns = 1 THEN 1
              ELSE 0
            END) AS single_column,
    SUM(CASE WHEN number_of_columns IS NULL THEN 0
             WHEN number_of_columns = 1 THEN 0
             ELSE 1
           END) AS multi_column
FROM pg_namespace 
LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
LEFT OUTER JOIN
       (SELECT indrelid,
           MAX(CAST(indisunique AS INTEGER)) AS is_unique
       FROM pg_index
       GROUP BY indrelid) x
       ON pg_class.oid = x.indrelid
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid  )
    AS foo
    ON pg_class.relname = foo.ctablename
WHERE 
     pg_namespace.nspname='public'
AND  pg_class.relkind = 'r'
GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
ORDER BY 2;

Index size/usage statistics: Table & index sizes along which indexes are being scanned and how many tuples are fetched

SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid AND psai.schemaname = 'public' )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

Duplicate indexes: Finds multiple indexes that have the same set of columns, same opclass, expression and predicate — which make them equivalent. Usually it’s safe to drop one of them, but I give no guarantees. πŸ™‚

SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,                        (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
(array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4

FROM (SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'|| COALESCE(indexprs::text,'')||E'\n' || COALESCE(indpred::text,'')) AS KEY
FROM pg_index) sub

GROUP BY KEY HAVING COUNT(*)>1
ORDER BY SUM(pg_relation_size(idx)) DESC;

Using these queries you can decide whether you would like to go ahead with partial vacuum or a full vacuum.After the vacuum your database will process sql queries in much faster way and it will save you a lot od time and pain πŸ™‚

After vacuum…There…No more pain.

Remember that you have to perform this vacuum activity one every month to let you adobe campaign database breadth and work efficiently.If your database is on premise then it has to be done by your database administrator. On the other hand, if the instance is hosted on Adobe’s infrastructure, then you need to reach out to Adobe Campaign team to perform the vacuum.

Hope this was helpful.

Feel free to each out to me on LinkedIn, to discuss and brainstorm more.

Your Testimony matters

Required

What is your full name?
What is your email address?
What is your company name?
A headline for your testimonial.
What do you think about us?
About Prajwal Shetty 54 Articles
Prajwal Shetty is the founder of Techonol Consulting. He started Techonol with a vision to introduce the best parts of marketing automation to the brands around the globe. He is also an Adobe Certified Campaign Architect, Developer, and BP. A few words that describe him are passionate, innovative and loves to make the impossible possible using technology.