MariaDB came into being the day that Oracle announced the purchase of Sun in 2010. In order to keep it free under the GNU GPL, Michael Widenius forked MySQL and took several MySQL developers with him in the process. Since then, MariaDB has been a drop-in replacement for MySQL, albeit with more features and better performance.
In the Improve MariaDB Performance using Query Profiling blog, we learned some useful techniques for tracking and analyzing slow queries using a couple of MariaDB server’s built-in tools: the Slow Query Log and the Performance Schema.
The Slow Query Log records queries that are deemed to be slow and potentially problematic, that is, queries that take longer than the long_query_time global system variable value to run.
The Performance Schema is a storage engine that contains a database called performance_schema, which in turn consists of a number of tables. It may be utilized to view raw data in the summary views as well as review performance over time.
Both of the above tools come with their own pros and cons. For example, the slow query log is easy to work with and may be viewed with any text editor. The Performance Schema tables may be queried with regular SQL statements for a wide range of performance information. At the same time, both tools tend to produce a wealth of data that can be a burden to wade through.
That’s where a professional monitoring tool add tremendous value.
More than a Real-time monitoring tool, Monyog features RDS OS and file-based log monitoring, including the General Query, Slow Query and Error logs in a single view. It also lets you view RDS OS metrics like CPU Utilization, RAM usage etc. using the CloudWatch API.
In MariaDB, as in MySQL, the Slow Query Log is disabled by default. It must be enabled by setting the slow_query_log global system variable to 1. There are a few other system variables for:
In Monyog, you can configure all of these settings via the ADVANCED tab of the Server Settings dialog. It is accessible by clicking:
The ADVANCED tab of the MySQL Query Log item contains settings for the General Query, Slow Query and Error logs.
The Server Settings dialog also allows us to apply the Slow Query Log settings to the current server or to all servers with tags same as the current server.
Clicking the SAVE button closes the dialog and persists the Slow Log settings.
The Dashboard displays a set of charts so that DBAs can easily understand the complete security, availability, and performance picture of all their MySQL servers in one place. Monyog ships with a default dashboard called “Performance metrics”, but DBAs can create their own set of charts for database and OS specific metrics for one or more servers. These include query performance metrics such as Queries Executed, Statements, and Query Cache Efficiency.
All charts and graphs displayed on the Dashboard can be exported in PDF/JPG/PNG formats. To export a chart, click the download icon and select your preferred file format from the drop-down context menu.
Viewing MySQL Logs Details
The Monyog Monitors page displays a detailed display of server parameters and metrics. Clicking the MySQL Logs item under the MONITOR GROUP header brings up details about the General Query, Slow Query (highlighted with a red box in the image below) and Error logs for monitored servers.
Slow Query information includes:
A graph or chart is a visual information graphic representation of tabular numeric data. Graphs are often used to make it easier to understand large quantities of data and the relationship between different parts of the data. Graphs can usually be read more quickly than the raw data that they come from.
One type of chart is called a Trend chart or run chart. It’s utilized to show trends in data over time. Due to data fluctuations, single point measurements can be misleading. Displaying data over time increases understanding of the real performance, particularly with regard to an established target or goal.
Clicking on a Trend value graph icon in the No. of slow queries row will display a graph, depicting query performance over time.
Following is an example of a trend chart for the Master server:
The SERVERS legend lists all of the servers from the SQL Logs screen. Each is assigned its own color for easy identification in the graph. Servers whose trend values do not appear in the graph are “greyed out”. Clicking a Server toggles its inclusion in the graph, thus saving having to return to the SQL Logs screen to select or deselect it. For example, the above graph was produced by clicking the Trend value graph icon in the Master column of the Monitors table. Hence, the other three servers are greyed out in the legend. Clicking any of these Servers will add it to the graph, while clicking the Master server will remove it from the graph.
Hovering the mouse over the graph line will display the details for that point on the graph:
Clicking anywhere outside of the graph dialog closes the dialog.
All charts and graphs displayed by the Monitors can be exported in CSV format. To export a chart select the option from the drop-down context menu.
The trend graphs explored above presents the current trend data. In Monyog Professional, Enterprise and Ultimate editions, you can also select a specific time-frame for which to include in the graph by choosing the History item from the TIMEFRAME dropdown.
This will display an additional dropdown for selecting the timeframe range. It contains a number of intervals such as “Today”, “Yesterday”, and “Last 2 Days” as well as start and end fields to set a custom range. Clicking on either custom range fields presents a calendar widget for choosing an exact date and time.
Now, clicking on the ‘Trend Graph’ icon in the No. of slow queries row displays the trends graph.
Along with the graph, the Historical Trend Graph also shows the monitor values for each server in tabular form underneath the graph. You can enable the option Show Only Changed Values to restrict values to those before and after changes.
Displaying Delta Results
The third type of Time Frame, Delta, displays results based on data for the period between the last data collection and the collection before that. This setting can help give you a better idea of the current situation, and how much it differs from the ‘average’ or ‘normal’ situation.
In the ‘Query Analyzer’ tab select which of the MySQL servers you want and the type of log (including the Slow Query log) you want to analyze. Next click the Analyze button to begin the analysis.
After a few seconds an analysis result like the following will appear:
The Query Analyzer screen is divided into 2 parts: the top half of the screen contains the Top Queries based on Total Time while the bottom half shows all of the queries using results paging.
This section of the screen displays the top queries, sorted so that the slowest query appears at the top. It includes:
Each statement is presented as a bar chart at the very top of the query data whereby each query is represented using a unique color. Each query’s Total Execution Time appears from left-to-right so that the slowest would be displayed at the far left. The bar chart helps to quickly assess how slow each of the slowest queries compares to the slowest. In the image above, we can see that the slowest query was several magnitudes slower than all of the other slow queries times combined!
Clicking on a row brings up the Query Details. This includes additional information such as the query’s first and last seen date & times, its Max Time, Rows Sent, and Rows Examined:
This is also true of the Queries section.
Queries
The Queries section provides a more complete list of analyzed queries. In addition to having the ability to navigate through all of the queries via paging, it also features:
Here is a filter that restricts results to statements that contain the regex “sakila*”:
It’s accessible by clicking the Show/Hide Columns icon next to the Results Navigation controls:
To the immediate left of the Show/Hide Columns icon, the Export as CSV icon saves the query data to a Comma Separated Values (.csv) file.
CSV files may be read by applications like MS Excel.
The option to define the field delimiter is provided because some localized Windows programs that use the comma (,) as a decimal sign will require a semicolon (;) as field separator. This includes Microsoft Office programs like Excel and Access. On Linux, the situation is less uniform but some localized applications such as OpenOffice Calc (spreadsheet app) requires a semicolon (;) as the field separator.
Users can change the CSV export settings by using General > CSV EXPORT from the Settings screen.
The Query Analyzer offers a few options specific to the Slow Query Log. These are accessible by clicking the Settings icon (highlighted in red below).
Options include:
Both of the Slow Query Log and Performance Schema come with their own pros and cons. Whereas the slow query log is easy to work with and may be viewed with any text editor, the Performance Schema tables may be queried with regular SQL statements for a wide range of performance information. At the same time, both tools tend to produce a wealth of data that can be a burden to wade through.
That’s where a professional monitoring tool like Monyog can add tremendous value. Specifically:
Query profiling is a useful technique for analyzing the overall performance of a database. Employing Monyog to monitor the MariaDB Slow Query Log and the Performance Schema is one of the most efficient ways to do that.
