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.
Kevin Spacey, House of Cards
After vacuum..There…No more pain.
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.
