How Monyog Helps Profile Slow Queries in MariaDB

by May 17, 2018

Categories

Tags

Administration agent-based monitoring Agentless Monitoring alert responses alert thresholds alerting Alerts Amazon Aurora Amazon EC2 Amazon RDS Amazon RDS / Aurora Amazon RDS for SQL Server Amazon Redshift Amazon S3 Amazon Web Services (AWS) Analytics application monitoring Aqua Data Studio automation availability Azure Azure SQL Database azure sql managed instance Azure VM backup Backup and recovery backup and restore backup compression backup status Backup Strategy backups big data Blocking bug fixes business architecture business data objects business intelligence business process modeling business process models capacity planning change management cloud cloud database cloud database monitoring cloud infrastructure cloud migration cloud providers Cloud Readiness Cloud Services cloud storage cloud virtual machine cloud VM clusters code completion collaboration compliance compliance audit compliance audits compliance manager compliance reporting conference configuration connect to database cpu Cross Platform custom counters Custom Views customer survey customer testimonials Dark Theme dashboards data analysis Data Analytics data architect data architecture data breaches Data Collector data governance data lakes data lineage data management data model data modeler data modeling data models data privacy data protection data security data security measures data sources data visualization data warehouse database database administration database administrator database automation database backup database backups database capacity database changes database community database connection database design database developer database developers database development database diversity Database Engine Tuning Advisor database fragmentation database GUI database IDE database indexes database inventory management database locks database management database migration database monitoring database navigation database optimization database performance Database Permissions database platforms database profiling database queries database recovery database replication database restore database schema database security database support database synchronization database tools database transactions database tuning database-as-a-service databases DB Change Manager DB Optimizer DB PowerStudio DB2 DBA DBaaS DBArtisan dBase DBMS DDL Debugging defragmentation Demo diagnostic manager diagnostics dimensional modeling disaster recovery Download drills embedded database Encryption End-user Experience entity-relationship model ER/Studio ER/Studio Data Architect ER/Studio Enterprise Team Edition events execution plans free tools galera cluster GDPR Getting Started Git GitHub Google Cloud Hadoop Healthcare high availability HIPAA Hive hybrid clouds Hyper-V IDERA IDERA ACE Index Analyzer index optimization infrastructure as a service (IaaS) infrastructure monitoring installation Integrated Development Environment interbase Inventory Manager IT infrastructure Java JD Edwards JSON licensing load test load testing logical data model macOS macros managed cloud database managed cloud databases MariaDB memory memorystorage memoryusage metadata metric baselines metric thresholds Microsoft Azure Microsoft Azure SQL Database Microsoft PowerShell Microsoft SQL Server Microsoft Windows MongoDB monitoring Monitoring Tools Monyog multiple platforms MySQL news newsletter NoSQL Notifications odbc optimization Oracle PeopleSoft performance Performance Dashboards performance metrics performance monitoring performance schema performance tuning personally identifiable information physical data model Platform platform as a service (PaaS) PostgreSQL Precise Precise for Databases Precise for Oracle Precise for SQL Server Precise Management Database (PMDB) product updates Project Migration public clouds Query Analyzer query builder query monitor query optimization query performance Query Store query tool query tuning query-level waits Rapid SQL rdbms real time monitoring Real User Monitoring recovery regulations relational databases Releases Reporting Reports repository Restore reverse engineering Roadmap sample SAP Scalability Security Policy Security Practices server monitoring Server performance server-level waits Service Level Agreement SkySQL slow query SNMP snowflake source control SQL SQL Admin Toolset SQL CM SQL code SQL coding SQL Compliance Manager SQL Defrag Manager sql development SQL Diagnostic Manager SQL Diagnostic Manager for MySQL SQL Diagnostic Manager for SQL Server SQL Diagnostic Manager Pro SQL DM SQL Doctor SQL Enterprise Job Manager SQl IM SQL Inventory Manager SQL Management Suite SQL Monitoring SQL Performance SQL Quality SQL query SQL Query Tuner SQL Safe Backup SQL script SQL Secure SQL Security Suite SQL Server sql server alert SQL Server Migration SQL Server Performance SQL Server Recommendations SQL Server Security SQL statement history SQL tuning SQL Virtual Database sqlmemory sqlserver SQLyog Storage Storage Performance structured data Subversion Support tempdb tempdb data temporal data Tips and Tricks troubleshooting universal data models universal mapping unstructured data Uptime Infrastructure Monitor user experience user permissions Virtual Machine (VM) web services webinar What-if analysis WindowsPowerShell

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.

Configuring the Slow Query Log

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:

  1. Setting the time in seconds/microseconds that define a slow query.
  2. Writing to a file or table.
  3. Providing the name of the slow query log file.
  4. Logging queries that don’t use indexes.

In Monyog, you can configure all of these settings via the ADVANCED tab of the Server Settings dialog.  It is accessible by clicking:

  1. The Servers icon (#1 in the image below).
  2. The ellipsis on the server summary box (#2 in the image below).
  3. The Edit Server item from the popup (not pictured).
  4. The ADVANCED header (underlined in blue in the image below).
  5. The MySQL Query Log item (highlighted in blue in the image below).

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.

Dashboard Metrics

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:

  • Slow log – Enabled? (Yes/no)
  • Min. execution time for a query to be considered slow, in seconds
  • No. of slow queries
  • Log queries not using indexes? (Yes/no)

Trend Values Graph

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.

Displaying Trend Values for a Specific Timeframe

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.

Query Analyzer

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.

Top Queries based on Total Time

This section of the screen displays the top queries, sorted so that the slowest query appears at the top. It includes:

  • The query statement
  • COUNT: how many times the statement appears in the log.
  • TOTAL TIME:  How much time the queries took to execute, in hh:mm:ss:ms format.
  • AVERAGE LATENCY: The average query execution time, in hh:mm:ss:ms format.
  • USER@HOST: The user and host that executed the query.

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:

  • Filtering:
    Queries can be filtered to narrow down the list to those that you are interested in.  The four filtering options are:

    • Containing
    • Not containing
    • Matching regex
    • Not matching regex

Here is a filter that restricts results to statements that contain the regex “sakila*”:

  • Sorting:
    Rows can be sorted by any column by clicking the header.  An arrow shows sorting order, i.e. ascending, descending.

  • Managing Columns:
    Individual columns may be added and removed from the query list via the Manage Columns dialog:

It’s accessible by clicking the Show/Hide Columns icon next to the Results Navigation controls:

Export as CSV

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.

Changing the Field Delimiter

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.

Filter settings

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:

  • Filter Users/Filter Hosts:
    A list of users/hosts to include or exclude from the analysis.  Both these options accept the asterisk “*” wildcard character.
  • Include Users executing the queries with Host names:
    If this option is selected it will display both the 'user' and 'host' of that particular query and it will group the query analyzer table based on 'user@host' and 'query'.
  • Read All:
    Selecting the Read All option causes the Query Analyzer to consider the whole file for analyzing. It won't consider any particular timeframe but displays all queries within the specified KB, MB or bytes size/chunk as set in the Reading limit from file option. Otherwise, it reads the last specified chunk in KB, MB or bytes set in the Reading limit from file option in the log file.  Note that it is the “smallest” of those two settings that will have effect for the analysis.  
  • Reading limit from file:
    Specifies the number of KB, MB or bytes size/chunk to read from the log file according to the Read All setting.

 

Conclusion

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:

  • The Monyog Monitors page displays a detailed display of server parameters and metrics for the General Query, Slow Query and Error logs for monitored servers.  
    It provides Trend charts that show trends in data over time.  The data timeframe may be current, historical, or a delta.
  • The Query Analyzer screen contains the Top Queries based on Total Time as well as a list of all queries using results paging.

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.