Symptoms

The rate of growth of data in your DB has drastically increased. You find this coming from the changeitem table. You use the Automation plugin in your instance.

Diagnosis

The top query will tell you the amount of data being created in the changeitem table by month. The second will give you all the data being created by everything which is not Service Desk. If you see a huge spike like the example chart bellow, then you are affected.

select date_trunc( 'month', cg.created),  sum(coalesce(pg_column_size(ci.oldstring),0)) + sum(coalesce(pg_column_size(ci.newstring),0)) as total from changegroup as cg, changeitem as ci where ci.groupid = cg.id and ci.field in (select cfname from customfield where customfieldtypekey like 'com.atlassian.servicedesk%') group by date_trunc( 'month', cg.created) order by date_trunc( 'month', cg.created)

select date_trunc( 'month', cg.created),  sum(coalesce(pg_column_size(ci.oldstring),0)) + sum(coalesce(pg_column_size(ci.newstring),0)) as total from changegroup as cg, changeitem as ci where ci.groupid = cg.id and ci.field not in (select cfname from customfield where customfieldtypekey like 'com.atlassian.servicedesk%') group by date_trunc( 'month', cg.created) order by date_trunc( 'month', cg.created)

(info) Queries are only written for PostgreSQL. If you use a different SQL server, you will need to adjust the queries for your db.

Cause

This is a bug in the combination of how the Automation plugin and JIRA Service Desk work together. Issue was tracked at:  JSD-1060 - Getting issue details... STATUS

Workaround

You can delete the bad data from the DB. We recommend performing a VACUUM / ANALYZE after the deletion completes.

delete from changegroup where id in (select id from changegroup where 
id in (select groupid from changeitem where field  in 
    (select cfname from customfield where customfieldtypekey like 'com.atlassian.servicedesk%'))
except
    (select groupid from changeitem where field not in 
    (select cfname from customfield where customfieldtypekey like 'com.atlassian.servicedesk%')));

delete from changeitem where field in (select cfname from customfield where customfieldtypekey like 'com.atlassian.servicedesk%');

(info) Queries are only written for PostgreSQL. If you use a different SQL server, you will need to adjust the queries for your db.

Resolution

Per JSD-1060 - Getting issue details... STATUS you need to upgrade to JIRA Service Desk 2.1+ to fix this issue.

Help us improve!
  • No labels