Clean up extra Service Management data in the db

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

This article requires fixes

This article has been Flagged for fixing. Use caution when using it and fix it if you have Publisher rights.

症状

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.

診断

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 Management. 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.

原因

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

回避策

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.

ソリューション

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

最終更新日 2020 年 11 月 23 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.