Troubleshooting slow MySQL performance

お困りですか?

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

コミュニティに質問

本記事で説明している手順は、現時点でのものとなります。そのため、一部のお客様で特定の状況下で動作したという報告がありますが、正式にサポートされているわけではなく、お客様の特定のシナリオで動作することを保証するものではありません。

本番環境での実施の前に一通り非本番環境で検証し、成功しなかった場合にはサポートされている代替案にフォール バックしてください。

また、アトラシアン サポートのサポート対象外のご質問の場合には、Community もご活用ください。

プラットフォームについて: 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 は除く


問題

If you see poor performance on a system where MySQL is being used as the backend for an Atlassian application, there is an easy way to confirm this.

診断

Getting the data

  • Check if the MySQL slow query log is enabled. If so, skip to the Converting data into information section.
  • Shut down the Atlassian applications which use the suspected MySQL server.
  • Enable the MySQL slow query log. You can find instructions on how to do this at: Enabling MySQL slow query logs
  • Restart MySQL after doing this.
  • Start your Atlassian application back up.
  • Wait till your Atlassian application has gone through a period of poor behavior.

Converting data into information

  • MySQL provides us with a tool call mysqldumpslow which can be used to analyze the log file.
  • The following syntax will show you the top 10 queries sorted by average query time:

    mysqldumpslow -t 10 mysql-slow-query.log > mysqldumpslow.out

    (info) Remember to update the file names and paths to suit your environment.

  • This is a sample output:

    Count: 109  Time=56.73s (6183s)  Lock=0.00s (0s)  Rows=3990419.2 (434955691), jiradbuser[jiradbuser]@localhost
      SELECT ID, ISSUE, CUSTOMFIELD, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE FROM customfieldvalue
  • The important thing to look at here is the Count and the Time. The Count is the number of times this query ran within your log set. The Time is an average amount of time for each of those queries runs to complete. With the number in parentheses, in this case 6138s, being the total (Count * Time) amount of time spent on running this query.

原因

The MySQL server can't handle the load being placed on it. This could be due to resource contention, or because it has not been appropriately tuned.

ソリューション

Option 1: If the MySQL server is sharing resources with the Atlassian application, move MySQL onto its own server.

Option 2: Check your MySQL configuration and see if you can tune it to improve performance. We have seen drastically improved DB performance when cache sizes are increased. Identifying the specific values that are right for you requires that you consult a DBA. You can also read up on the topic at: https://www.mysql.com/why-mysql/performance/

最終更新日: 2023 年 2 月 1 日

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

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