This blog describes how to identify queries that cause a sudden spike in system resources as well as the user and host who executed the culprit query using the Monyog MySQL Monitor and Advisor.
How many times have you seen a system go live and perform much worse than it did in testing? There could be several reasons behind bad performance. For instance, a slow running query in MySQL can be caused by a poor database design or may be due to higher-than-normal latency in network communication. Other issues such as using too few or too many indexes may also be a factor. This blog will identify the types of poorly performing queries and outline some concrete strategies for identifying them using monitoring. Finally, some tips for improving performance will be presented.
Typically, misbehaving queries will result in two possible outcomes: high CPU usage and/or slow execution. The two issues tend to be related to some degree, because one will lead to or exacerbate the other. Depending on the root cause, the problem may be a database configuration or query issue. For instance, as MySQL databases grow in size, tables get fragmented over time. This contributes to MySQL load spikes. Protecting a server from MySQL high CPU issues requires close monitoring and periodic optimization of the database. Meanwhile, a query that requires a high degree of type conversion will also place a burden on the CPU.
Identifying the culprit requires a different approach based on the dominant outcome: high CPU usage and/or slow execution. In the next sections, we will examine how to track down both causes.
Without knowing the root cause of a slow running query, it’s difficult for a DBA to troubleshoot the problem. Therefore, the first step should be to check efficiency of all the database components before going to use a query monitor or optimizer. This check will help to understand whether the root cause of the problem is related to a query or something else. Here are a few potential causes to consider:
Using a Monitoring and Profiling tool such as Monyog will help in improving the performance issues that are related to queries. Monyog can display long running queries (Queries that holds a large number of resources) in MySQL, as well as a host of other potential issues, such as hanging threads and improper index usage – i.e. over or under utilization.
As soon as you’ve logged into Monyog, the Overview page provides a high level picture of all the selected servers registered with Monyog. Below the count of the total servers registered with Monyog, total number of disconnected servers, and servers having critical alerts and warnings, you’ll find the top 10 queries across the selected servers in Monyog, based on total execution time:
Queries with the longest execution times are positioned at the top of the list, giving you an immediate place to start looking at bottlenecks. You can click on a query to get more details. These give the list of server names on which the particular query was executed. Clicking on the server names will open the sniffer for that server with the time-range selected as the first and last seen of the query.
Beyond the Overview page, the Index Usage monitor group is good starting point for identifying the presence of table scans. You should always try to keep these as low as possible by building indexes on searchable fields.
Although this screen does not relay information about specific queries, the monitor groups in Monyog read the MySQL Slow Query log do provide that information (covered in the next section). Once you have identified the problematic queries, you can create the appropriate indexes or rewrite the queries to use indexes.
The MySQL slow query log is a log that MySQL sends slow, potentially problematic queries to. Generally the queries that are logged are those that take longer than a specified amount of time to execute or queries that do not properly hit indexes. Queries that do not use an index may not be slow if there are only a few hundred or few thousand records in the table(s) involved. But they are ‘potentially slow’ and should be identified if they access tables, which will continue to grow.
This logging functionality comes with MySQL but is turned off by default. You can check whether or not it’s turned on from the MySQL Logs screen.
The No. of Slow Queries relays how many queries are taking longer than the Min. execution time for a query to be considered slow threshold (based on the MySQL long_query_time variable). On the right, there is a chart icon that, when clicked, opens the TREND VALUES chart.
Selecting “History” from the TIMEFRAME dropdown at the top of the Monitors page allows us to group trend data by minutes, hours, days, weeks, months, or years. More precise timeframes help to more accurately tie the offending query to the slowdown event:
Accessible from the left-hand button bar (refer to the highlighted icon below), the Threads page shows you the number of threads currently being executed by MySQL fetched using the query SHOW FULL PROCESSLIST. Each query sent to MySQL is executed in a thread. The Threads feature should be used to check which queries are being executed presently. It gives you a general sense of what is keeping your server busy at that moment.
While not the best feature for monitoring queries executed over a period of time (that would be the Query Analyzer presented in the next section), it can nonetheless be employed to locate runaway processes. Under the Actions heading on the far-right, there are buttons to kill each thread:
Monyog also gives us the ability to monitor MySQL servers via Real-time monitoring. It may also provide invaluable information on poorly performing queries. To show performance metrics:
Like the Overview page, Monyog’s Query Analyzer screen also displays The Average Latency and the Total Time taken by each query to execute. In addition, you’ll find the user and host who executed the query.
You can delve deeper into a query’s mechanics via the EXPLAIN command by clicking on the query and selecting the Explain tab on the Query Details screen:
The Explain Result’s Type column describes how tables are joined. In the above Explain Result, we see a type of “ALL” in combination with an absence of keys. That indicates that a full table scan is being done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked “const”, and usually very bad in all other cases. You can usually avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.
The latest update of Monyog brings new easier ways to find problem SQL in Real-Time, including “sniffer” based Query Analyser using Performance Schema. It allows you to view the success/failure status of every query executed and also the number of ‘full table scans’ for queries in a single glance.
Monyog’s query sniffer is a functionality that records a ‘pseudo server log’ and stores it in the Monyog embedded database.
The Sniffing Mode is set on the ADVANCED tab of the server properties dialog.
With ‘Performance Schema Sniffer’ enabled on the Query Analyzer screen, you can include many additional columns to the analysis, including a count of Full Table Scans.
Here is the Query Analyzer screen in Sniffer mode with the Full Table Scan column added:
Some other ideas that may help fix CPU problems:
By using the Monyog features described in this blog, you should be able to identify the queries and/or processes that are causing system bottlenecks. You can then make the changes to improve the performance by modifying the query, Indexes and database design, based on Monyog’s Advice text.
SQL query-performance tuning is as much art as science, and is thought by some to belong to the realm of application development rather than Database Administration. Under that assumption, the goal of DBAs would be to identify those slow-running or CPU-intensive queries, gather evidence and then work with developers to find ways of improving them.