The article covers the key performance metrics available in MySQL that can help users improve and optimize query performance in MySQL databases. In addition, there is a brief overview of some MySQL performance monitoring tools.
MySQL is an open-source relational database that a lot of users prefer to utilize in their work and daily operations due to the variety of storage engines it supports, its flexibility and high performance, strong data security, cost-effectiveness, easy management, and reliability.
Why do you need to measure and monitor MySQL performance?
Guaranteeing smooth and effective database performance is one of the core aspects for any business and company no matter how large or small they are. Thus, getting a comprehensive insight into database uptime or load is critical in some situations and should be monitored on a regular basis.
For this reason, on the market, there are plenty of monitoring tools to track, analyze, and measure performance, and investigate its potential issues. They help you not only optimize queries but also visualize database metrics, collect statistics, and show the performance status to understand the impact on overall performance and troubleshoot possible issues.
The optimization of database performance may result in the following benefits:
- Accelerate data retrieval
- Significantly increase query performance
- Identify slow performance
- Improve user experience
- Prevent possible downtime and excessive consumption of resources
- Define the impact of database changes
- Diagnose security vulnerabilities
MySQL Database Performance Metrics
MySQL provides a bunch of useful metrics you should monitor to detect bottlenecks and analyze what queries should be optimized.
The most important database performance monitoring metrics cover the following:
- System: System resources on the server. They include CPU usage, memory usage, disk usage, network bandwidth, read/write requests, Input/Output wait statistics, average read/write time, etc.
- Query throughput: A workload of the database that can be measured as the number of queries per second. With this metric, you can analyze how your server performs and processes queries over time, as well as track the performance of specific statements (SELECT, INSERT, UPDATE, and DELETE).
- Connections: The number of open connections running simultaneously that may overload the server and slow down the performance.
- Uptime: The database startup and shutdown time on a MySQL instance.
- Threads: The number of clients currently connected.
- Response time: An average response time per query or for all queries in the database server you are connected to.
- Latency: The duration of queries or operations.
- Errors: The number of code errors that happen most frequently and cause failures.
- Queries: The number of statements most frequently executed by the server, including stored procedures.
- Questions: The number of statements sent by clients.
- Buffer pool usage: Memory usage on the server. As a rule, it is used to investigate performance issues.
Depending on the location, MySQL database performance metrics and statistics can be divided into server status variables, performance schema, and sys schema.
MySQL Server Stats
To collect server stats, you can use server status variables. They are internal counters at the session or global levels that you can view to get information about operations running on the MySQL server.
To view these variables, execute the SHOW [GLOBAL | SESSION] STATUS statement where GLOBAL returns aggregated values for all connections, and SESSION – values only for the current connection.
MySQL Performance Schema
The MySQL performance schema stores statistics about server events and query execution at a low level. Starting with MySQL v5.6.6 it is enabled by default.
MySQL displays it as the performance_schema database containing tables that you can query using SQL statements. As a result, you get information about performance that may include current events, event histories and summaries, object instances, setup data, duration of server events, status monitoring, etc.
Sys Schema in MySQL
The MySQL sys schema includes stored procedures, views, and stored functions to interpret data collected by performance schema and to check how a MySQL instance is currently running. By default, it is enabled in MySQL v5.7.7 and later. The sys schema provides information about database usage, including current connections, queries being executed, buffer size, locks, and summarizes statement activity, I/O latency, memory usage by host and users, wait stats, resources consumed by users, etc.
Monitor Queries Performance
To analyze query performance and detect long-running and resource-consuming queries, users can monitor different metrics, including CPU and memory consumption, execution time, disk activity, wait stats, the I/O cycles, etc.
In MySQL, you can identify and investigate slow query logs. For this, make sure that logs are enabled by running the following command:
MariaDB [(none)]> show global variables like 'slow%log%';Along with this, enable the slow_query_log_file variable defining the file to store slow query logs, long_query_time determining the execution time in real time and min_examined_row_limit, indicating the number of rows to be examined. All these variables can be used to search for queries that take a long time to execute and should be analyzed to optimize query performance.
Another way to detect slow MySQL queries is to execute the following command:
SHOW [FULL] PROCESSLIST;
-- or
SHOW ENGINE INNODB STATUS \GThey allow you to view active queries and to check a query status.
MySQL query logs are useful query monitoring metrics. However, they do not provide a full picture of query performance. In this case, MySQL third-party query performance tools can help at hand.
MySQL Performance Monitoring Tools
The market of MySQL performance monitoring tools offers a lot of tools to analyze and optimize query performance, for example, MySQL Enterprise Monitor, MySQL Workbench, and Query Profiler in dbForge Studio for MySQL.
MySQL Enterprise Monitor is a monitoring tool designed to keep track of MySQL real-time instances and hosts, alert users about possible issues, and notify them how they can be resolved. With the tool, you can optimize performance, be aware of releases and bug fixes, manage and avoid any issues or downtime. The Enterprise dashboard available in MySQL Enterprise Monitor helps you check execution statistics, search for the most expensive queries, observe InnoDB performance tuning metrics, identify security vulnerabilities, analyze queries visually, and view the metrics of the MySQL servers you’re running. Still, it should be noted that MySQL Enterprise Monitor depends on MySQL performance schema. Thus, for correct work, it should be enabled.
MySQL Workbench is a visual modeling tool for MySQL databases, which in addition to plenty of its useful and advanced features, provides a set of tools to view and improve database performance. The Performance dashboard can visualize the main performance metrics, such as incoming and outgoing network traffic, performance statistics, executed SQL statements, InnoDB status, including disk activity, writes, and reads. Besides, with performance reports, it is much easier to analyze MySQL database performance. To fine-tune SQL statement performance, check the explain plan.
Query Profiler available in dbForge Studio for MySQL is a MySQL query profiling and optimization tool that helps you examine and increase query performance in MySQL and MariaDB databases. With the explain plan of the MySQL performance tuning tool, you can easily understand how MySQL operations are executed, how much time it will take to execute queries, whether to use indexes to have a positive influence on performance. Query Profiler collects session statistics and displays profiling results that help you investigate possible bottlenecks and resources consumption, and decide on an efficient technique for performance optimization. For additional information about effective tips to tune performance, see MySQL Server Performance Tuning with Tips for Effective Optimization.
Conclusion
In the article, we have reviewed the main performance metrics you can use to analyze and improve MySQL query and database performance and provided a short overview of MySQL Enterprise Monitor, MySQL Workbench, and Query Profiler built into dbForge Studio for MySQL performance optimization and monitoring tools.
Download a 30-day free trial version of dbForge Studio for MySQL to evaluate the Query Profiler capabilities and get convinced that it should be purchased after the trial version expires.
