How to find the issues with most issue links or comments in the database
プラットフォームについて: Data Center のみ。 - This article only applies to Atlassian apps on the Data Center プラットフォーム。
この KB は Data Center バージョンの製品用に作成されています。Data Center 固有ではない機能の Data Center KB は、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。 Server* 製品のサポートは 2024 年 2 月 15 日に終了しました。Server 製品を実行している場合は、 アトラシアン Server サポート終了 のお知らせにアクセスして、移行オプションを確認してください。
*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(*),
p.pkey || '-' || ji.issuenum AS issuekey
FROM
fileattachment fa
JOIN jiraissue ji ON fa.issueid = ji.id
JOIN project p ON ji.project = p.id
GROUP BY
issueid,
ji.issuenum,
p.pkey || '-' || ji.issuenum,
p.pname
ORDER BY
count(issueid)
DESC
LIMIT 50;Query tested on Microsoft SQL Server.
SELECT TOP 50
issueid,
count(*),
CONCAT(p.pkey,'-',ji.issuenum)
FROM
fileattachment
JOIN jiraissue ji ON fa.issueid = ji.id
JOIN project p ON ji.project = p.id
GROUP BY
issueid,
ji.issuenum,
CONCAT(p.pkey,'-',ji.issuenum),
p.pname
ORDER BY
count(issueid)
DESC;Issues with most INWARD links
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;Issues with most OUTWARD links
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;この内容はお役に立ちましたか?