Troubleshooting slow MySQL performance
The steps outlined on this article are provided AS-IS. This means we've had reports of them working for some customers — under certain circumstances — yet are not officially supported, nor can we guarantee they'll work for your specific scenario.
You may follow through and validate them on your own non-prod environments prior to production or fall back to supported alternatives if they don't work out.
We also invite you to reach out to our Community for matters that fall beyond Atlassian's scope of support!
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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.
*Except Fisheye and Crucible
Problem
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.
Diagnosis
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
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.
Cause
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.
Resolution
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/