How to find the issues with most issue links or comments in the database

お困りですか?

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

コミュニティに質問

プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Fisheye および Crucible は除く

目的

It's very common to look for ticket outliers that can impact system performance such as issues with thousands of links or comments that might've been added by faulty automation or simply kept growing overtime. It is not unusual for issues with lots of associated links or comments cause threads to hang or connection to timeout before all the data is fetched.

ソリューション

We recommend testing these queries in your staging environment. They were created on PostgreSQL and may need tuning depending on your database. These will help you to identify issues that can be expensive when fetching their information from the database, so you can review them and potentially take actions to remediate the problem (e.g. deleting or archiving those issues, deleting excessive comments, links or attachments).

Issues with most comments

  • Query tested on PostgreSQL, MySQL, and Oracle. 
SELECT
	count(*),
	ji.id,
	p.pkey || '-' || ji.issuenum AS issuekey
FROM
	jiraaction ja
	JOIN jiraissue ji ON ja.issueid = ji.id
	JOIN project p ON ji.project = p.id
GROUP BY
	ji.id,
	ji.issuenum,
	p.pkey || '-' || ji.issuenum,
	p.pname
ORDER BY
	count(*)
	DESC
LIMIT 50;
  • Query tested on Microsoft SQL Server.
SELECT TOP 50
	count(*),
	ji.id,
	CONCAT(p.pkey,'-',ji.issuenum)
FROM
	jiraaction ja
	JOIN jiraissue ji ON ja.issueid = ji.id
	JOIN project p ON ji.project = p.id
GROUP BY
	ji.id,
	ji.issuenum,
	CONCAT(p.pkey,'-',ji.issuenum),
	p.pname
ORDER BY
	count(*)
	DESC;


Issues with most attachments

  • Query tested on PostgreSQL, MySQL, and Oracle.
SELECT
	issueid,
	count(*)
FROM
	fileattachment
GROUP BY
	issueid
ORDER BY
	count(issueid)
	DESC
LIMIT 5;
  • Query tested on Microsoft SQL Server.
SELECT TOP 5
	issueid,
	count(*)
FROM
	fileattachment
GROUP BY
	issueid
ORDER BY
	count(issueid)
	DESC;


  • Query tested on PostgreSQL. 
SELECT 
	count(*),
	ji.id,
	p.pkey||'-'||ji.issuenum as issuekey 
FROM 
	issuelink il
	join jiraissue ji on il.destination=ji.id
	join project p on ji.project=p.id 
GROUP BY 
	il.destination,
	ji.id,p.pkey||'-'||ji.issuenum,p.pname
ORDER BY 
	count(*) 
	DESC 
LIMIT 50;
  • Query tested on Microsoft SQL Server.
SELECT TOP 50
	count(*),
	ji.id,
	CONCAT(p.pkey,'-',ji.issuenum)
FROM 
	issuelink il
	join jiraissue ji on il.destination=ji.id
	join project p on ji.project=p.id 
GROUP BY 
	il.destination,
	ji.id,CONCAT(p.pkey,'-',ji.issuenum),p.pname
ORDER BY 
	count(*) 
	DESC;


  • Query tested on PostgreSQL. 
SELECT 
	count(*),
	ji.id,
	p.pkey||'-'||ji.issuenum as issuekey 
FROM 
	issuelink il
	join jiraissue ji on il.source=ji.id
	join project p on ji.project=p.id 
GROUP BY 
	il.source,
	ji.id,
	p.pkey||'-'||ji.issuenum,
	p.pname
ORDER BY 
	count(*) 
	DESC 
LIMIT 50;
  • Query tested on Microsoft SQL Server.
SELECT TOP 50
	count(*),
	ji.id,
	CONCAT(p.pkey,'-',ji.issuenum)
FROM 
	issuelink il
	join jiraissue ji on il.source=ji.id
	join project p on ji.project=p.id 
GROUP BY 
	il.source,
	ji.id,
	CONCAT(p.pkey,'-',ji.issuenum),
	p.pname
ORDER BY 
	count(*) 
	DESC;

Last modified on Mar 8, 2022

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

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