Troubleshooting slow MySQL performance
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
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/