Monitoring MySQL Server Status and Resource Usage

Course: MySQL Training

Topic: Monitoring MySQL Server Status and Resource Usage

Course Link: MySQL Training Course – JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to MySQL Server Monitoring

MySQL server monitoring is the process of checking a database server's health, availability, performance, workload, and resource consumption. It helps database administrators identify problems such as slow queries, excessive connections, high memory usage, disk-space shortages, and transaction lock contention.

As an application grows, the number of database users, SQL queries, tables, and transactions often increases. Without monitoring, it becomes difficult to determine why an application is responding slowly or why database operations are failing.

MySQL provides several monitoring mechanisms, including status variables, system variables, Performance Schema, the sys schema, process information, and server logs. Operating-system tools provide additional information about CPU, memory, disk, and network usage.

For example, if a website becomes slow, monitoring can help determine whether the cause is an inefficient SQL query, a long-running transaction, excessive concurrent connections, or storage latency.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand the importance of MySQL server monitoring.
• Check server availability, version, and uptime.
• Inspect global and session status variables.
• Monitor database connections and query activity.
• Understand CPU, memory, disk, and network resource usage.
• Identify slow queries and possible performance bottlenecks.
• Use Performance Schema and the sys schema for diagnostics.
• Monitor InnoDB activity and transaction lock waits.
• Establish performance baselines and meaningful alerts.
• Apply monitoring best practices in production environments.

3. What Is MySQL Server Status?

MySQL server status describes the current state and activity of a database server. It includes information about connections, executed statements, server uptime, temporary tables, network traffic, and other operational counters.

MySQL exposes this information through status variables. Administrators use these variables to understand server behavior and identify unusual changes in workload.

3.1 Basic Server Information

SELECT VERSION();
SELECT NOW();
SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;

SELECT VERSION() returns the MySQL version. NOW() returns the current date and time according to the session time zone. The two SHOW statements display status information and configuration settings.

3.2 Status Variables vs. System Variables

4. Checking MySQL Server Availability

The first step in monitoring is to confirm that the MySQL server is running and responding to requests.

4.1 Check the Server Version

SELECT VERSION();

This query identifies the MySQL version and confirms that the current connection can execute SQL.

4.2 Check Server Uptime

SHOW GLOBAL STATUS LIKE 'Uptime';

The Uptime variable reports the number of seconds the server has been running. A low value may indicate a recent restart. Compare it with server logs and maintenance records before drawing conclusions.

4.3 Check Basic Query Availability

SELECT 1;

If this query succeeds, the current connection can execute a simple statement. It does not guarantee that all database operations are healthy, so further monitoring is still necessary.

4.4 Inspect Database Availability

SHOW DATABASES;
USE company_db;
SHOW TABLES;

Replace company_db with the database you want to examine. The results depend on the privileges of the connected account.

5. Understanding Global and Session Status

MySQL supports global and session-level status inspection. Global status describes activity across the server, while session status provides information about the current connection.

5.1 Global Status

SHOW GLOBAL STATUS;
SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Uptime';

Global counters are useful for identifying changes in overall workload. Many counters accumulate since server startup, so comparing readings over time is often more useful than examining a single value.

5.2 Session Status

SHOW SESSION STATUS;
SHOW SESSION STATUS LIKE 'Created_tmp%';

Session status can help investigate activity associated with the current connection. It should not be treated as a complete representation of every session on the server.

5.3 Important Status Variables

Always check the documentation for the installed MySQL version when interpreting individual variables.

6. Monitoring MySQL Connections

Applications, reporting systems, administrative tools, and background services create connections to MySQL. A large number of concurrent connections can increase resource usage and prevent new clients from connecting when the configured limit is reached.

6.1 Check Connection Usage

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

Threads_connected indicates currently open connections. Threads_running indicates threads that are not sleeping. Max_used_connections records the highest simultaneous connection count since startup, while max_connections is the configured connection limit.

6.2 Calculate Connection Utilization

Connection Utilization (%) =
(Threads_connected / max_connections) * 100

For example, if there are 80 open connections and the configured maximum is 200, the estimated utilization is 40%.

6.3 Inspect Active Sessions

SHOW FULL PROCESSLIST;

This command shows information about client sessions and their current activity. Depending on permissions, it may display the SQL statement being executed. It can help identify long-running queries, sleeping connections, and sessions waiting for resources.

6.4 Connection Monitoring Best Practices

• Track connection counts over time.
• Investigate repeated connection failures and unusual connection growth.
• Use connection pooling when appropriate for the application.
• Review application connection cleanup and timeout settings.
• Do not increase max_connections without evaluating memory and workload requirements.

7. Monitoring Query Activity

Query monitoring helps administrators understand how frequently SQL statements run and which operations consume the most database resources.

7.1 Inspect Query Counters

SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Queries';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Com_select';
SHOW GLOBAL STATUS LIKE 'Com_insert';
SHOW GLOBAL STATUS LIKE 'Com_update';
SHOW GLOBAL STATUS LIKE 'Com_delete';

These counters provide information about query and statement activity. Their definitions differ, so they should not be treated as interchangeable measures.

7.2 Understand Slow Queries

A slow query is a query that meets the configured slow-query logging criteria. Common causes include missing indexes, inefficient joins, large table scans, expensive sorting, locking, and resource contention.

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';

These statements show whether the slow query log is enabled, the configured time threshold, and the configured log file path where applicable.

7.3 Enable Slow Query Logging

SET GLOBAL slow_query_log = ON;

This changes the running server's setting when supported and permitted. The change may not persist after restart. For permanent configuration, use the supported configuration mechanism for your MySQL installation. Consider logging overhead, file growth, and operational requirements before enabling it on a busy production server.

7.4 Analyze a Query Using EXPLAIN

EXPLAIN
SELECT employee_id, employee_name
FROM employees
WHERE department = 'IT';

EXPLAIN displays the execution plan MySQL expects to use. Review table access methods, selected indexes, estimated rows, and other plan details. Actual query performance should also be measured under representative workload conditions.

8. Monitoring CPU Usage

CPU usage measures how much processor capacity is consumed by MySQL and other processes. CPU utilization may increase when many queries run simultaneously, complex calculations are performed, or large joins and sorts consume significant processing time.

8.1 Signs of CPU Pressure

• Application response times increase.
• CPU utilization remains high for extended periods.
• Many queries execute concurrently.
• Large joins, aggregations, or table scans dominate workload.
• Background jobs compete with application traffic.

8.2 Monitor CPU on Linux

top

ps -eo pid,comm,%cpu,%mem --sort=-%cpu | head

These commands help identify processes consuming CPU and memory. The exact output depends on the operating system and installed utilities.

8.3 Troubleshooting High CPU Usage

1. Determine whether CPU utilization is persistently high or temporarily elevated.
2. Identify the MySQL process and other processes competing for CPU.
3. Inspect active sessions and expensive statement patterns.
4. Use execution plans to identify inefficient access paths.
5. Review query frequency and application behavior.
6. Optimize the workload and measure the effect of each change.

High CPU usage does not automatically mean more CPU capacity is required. Inefficient queries and unnecessary repeated work may be the underlying cause.

9. Monitoring Memory Usage

MySQL uses memory for the InnoDB buffer pool, connection handling, query execution, temporary tables, and other internal operations. The operating system and other processes also require memory.

9.1 Major Memory Components

9.2 Check the InnoDB Buffer Pool Size

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

The result shows the configured buffer pool size in bytes. It does not, by itself, reveal how much operating-system memory is available or whether the buffer pool is optimally sized.

9.3 Check Operating-System Memory

free -h

On Linux, this command displays memory totals, used memory, available memory, and swap information. Interpret available memory and swap activity rather than assuming all used memory is unavailable.

9.4 Inspect MySQL Memory Instrumentation

SELECT
    EVENT_NAME,
    CURRENT_NUMBER_OF_BYTES_USED,
    HIGH_NUMBER_OF_BYTES_USED
FROM performance_schema.memory_summary_global_by_event_name
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 10;

This query lists memory instruments with large current and historical high allocations. Results depend on enabled instrumentation and server version. These figures may not represent every byte of memory consumed by the MySQL process.

10. Monitoring Disk Space and Storage I/O

MySQL requires disk space for database files, indexes, binary logs, redo and undo logs, temporary files, and backups. A full filesystem can interrupt writes, prevent log creation, or affect database availability.

10.1 Check Available Disk Space

df -h

On Linux, this command reports filesystem capacity and available space. Check the filesystems containing the MySQL data directory, temporary directory, binary logs, and backup storage.

10.2 Estimate Database Sizes

SELECT
    table_schema AS database_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2)
        AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

This query estimates table data and index sizes for each schema. It does not include every source of disk usage, such as all logs, filesystem overhead, or backup archives.

10.3 Inspect Table Sizes

SELECT
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'company_db'
ORDER BY data_length + index_length DESC;

Replace company_db with the database you want to examine. The reported sizes can be estimates depending on the storage engine and allocation behavior.

10.4 Signs of Disk I/O Problems

• Queries become slow even when CPU utilization is moderate.
• Disk latency or I/O wait increases.
• Database files and binary logs grow unexpectedly.
• Filesystem capacity approaches its limit.
• Backup and restore operations take longer than expected.

10.5 Storage Monitoring Best Practices

• Monitor free space and disk latency continuously.
• Configure alerts before filesystems reach critical capacity.
• Track binary log growth and retention policies.
• Choose storage appropriate for the expected workload.
• Test backup and restore duration against recovery objectives.
• Never delete active MySQL files manually to free space.

11. Monitoring Network Activity

MySQL clients communicate with the server through local or network connections. Network latency, bandwidth limitations, packet loss, and large query result sets can affect application response time.

11.1 Inspect Network Counters

SHOW GLOBAL STATUS LIKE 'Bytes_received';
SHOW GLOBAL STATUS LIKE 'Bytes_sent';
SHOW GLOBAL STATUS LIKE 'Connections';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';

Bytes_received and Bytes_sent are cumulative traffic counters. Collect readings at different times and calculate the difference to estimate traffic over a period. Account for restarts and counter resets when interpreting results.

11.2 Common Network-Related Symptoms

• Applications report connection timeouts.
• Large result sets consume substantial bandwidth.
• Connections are repeatedly opened and closed.
• Remote applications experience higher latency than local clients.
• Network interruptions cause client connection failures.

Investigate connection settings, network latency, query duration, result-set size, and connection pooling together. Network counters alone do not identify the root cause.

12. Using Performance Schema

Performance Schema collects runtime information about MySQL execution. It exposes statement, wait, transaction, memory, and file activity through tables in the performance_schema database.

12.1 Check Performance Schema

SHOW VARIABLES LIKE 'performance_schema';

Check whether Performance Schema is enabled. The available instrumentation and collected information depend on the server version and configuration.

12.2 Find Statements with High Total Execution Time

SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 3)
        AS total_time_seconds,
    ROUND(AVG_TIMER_WAIT / 1000000000, 3)
        AS avg_time_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

This query ranks statement patterns by accumulated execution time. The timer values are converted from picoseconds to seconds and nanoseconds to milliseconds, respectively. Results represent aggregated statistics rather than individual query executions.

12.3 Inspect File I/O Summaries

SELECT
    FILE_NAME,
    COUNT_READ,
    COUNT_WRITE,
    SUM_NUMBER_OF_BYTES_READ,
    SUM_NUMBER_OF_BYTES_WRITE
FROM performance_schema.file_summary_by_instance
ORDER BY SUM_NUMBER_OF_BYTES_READ DESC
LIMIT 10;

This query highlights files with substantial recorded read activity. Compare the results with operating-system storage metrics to investigate possible I/O bottlenecks.

12.4 Benefits of Performance Schema

• Provides detailed runtime performance information.
• Helps identify expensive statement patterns.
• Exposes waits and file I/O activity.
• Supports SQL-based diagnostics.
• Complements operating-system monitoring and server logs.

13. Using the MySQL sys Schema

The sys schema provides convenient views and procedures built on Performance Schema and related information. It helps administrators investigate common performance problems without writing every diagnostic query from scratch.

13.1 Inspect Running Sessions

SELECT *
FROM sys.processlist;

This view presents session information in a readable format. The information visible depends on the account's privileges.

13.2 Find Statements with High Latency

SELECT *
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

This view can help identify statement patterns with high accumulated latency. Review available columns for execution count, total latency, average latency, and rows examined.

13.3 Review Table I/O

SELECT *
FROM sys.schema_table_statistics
ORDER BY total_latency DESC
LIMIT 10;

This view can help identify tables with substantial recorded I/O activity and latency. A high value is a clue for further investigation, not proof that the table itself is incorrectly designed.

14. Monitoring InnoDB Activity

InnoDB is MySQL's default storage engine. It supports transactions, row-level locking, crash recovery, and other database features. Monitoring InnoDB helps administrators investigate buffer pool behavior, lock contention, and storage-related activity.

14.1 Inspect InnoDB Status

SHOW ENGINE INNODB STATUS;

This command provides a diagnostic snapshot containing transaction information, lock waits, buffer pool statistics, and other engine details. The output is extensive, so focus on sections relevant to the problem being investigated.

14.2 Monitor Buffer Pool Counters

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

These counters provide information about InnoDB buffer pool activity. Compare readings over time and interpret them alongside database size, workload, and storage metrics.

14.3 Investigate Lock Contention

SELECT *
FROM performance_schema.data_lock_waits;

This table provides information about data lock waits when available. Investigate waiting and blocking transactions before deciding whether to terminate a session. Killing a transaction may trigger a rollback and affect application behavior.

15. Establishing a Monitoring Baseline

A monitoring baseline represents normal server behavior under expected workload. It helps administrators determine whether a metric is unusually high or simply reflects normal business activity.

15.1 Important Metrics to Record

15.2 Setting Useful Alert Thresholds

Alert thresholds should reflect the workload and operational requirements of the environment. A high connection count may be normal during a scheduled batch job but concerning if it persists and coincides with failed connections.

• Alert on sustained resource pressure rather than every brief spike.
• Use different severity levels for warnings and critical conditions.
• Include the instance, timestamp, metric value, and relevant context in alerts.
• Route alerts to the team responsible for investigating the database.
• Review thresholds after significant workload or infrastructure changes.

16. Common Monitoring Problems and Troubleshooting

17. MySQL Monitoring Best Practices

1. Monitor continuously: Collect important metrics at consistent intervals appropriate to the workload.
2. Use multiple data sources: Combine MySQL status, Performance Schema, server logs, and operating-system metrics.
3. Protect monitoring credentials: Use dedicated accounts with the minimum required privileges.
4. Track historical trends: Store metrics to identify recurring patterns and gradual degradation.
5. Investigate root causes: Avoid changing server configuration based on a single metric without supporting evidence.
6. Control logging overhead: Balance diagnostic value, performance, and storage consumption.
7. Test alert delivery: Confirm that alerts reach the right people and contain actionable details.
8. Maintain backups: Monitoring does not replace tested backup and recovery procedures.
9. Review major changes: Reassess baselines after application releases, schema changes, or infrastructure upgrades.
10. Document incidents: Record symptoms, metrics, diagnosis, changes, and results.

18. Hands-On Practice Project: MySQL Server Health Report

Objective: Create a basic health report using MySQL status variables, Performance Schema, and database metadata. Perform these exercises in a development or authorized test environment.

Step 1: Check Version and Uptime

SELECT VERSION();
SHOW GLOBAL STATUS LIKE 'Uptime';

Record the version and uptime, and determine whether the server has recently restarted.

Step 2: Review Connection Usage

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

Compare the current connection count with the configured maximum and assess whether it is normal for the workload.

Step 3: Inspect Slow Query Configuration

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';

Record the logging status, threshold, and output path. Document any approved changes that would be required to enable logging.

Step 4: Review Database Sizes

SELECT
    table_schema,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2)
        AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

Identify the largest schemas and explain how their growth could be monitored over time.

Step 5: Inspect Statement Summaries

SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 3)
        AS total_time_seconds
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Identify statement patterns with high accumulated execution time. Select one for further investigation using execution plans and index analysis.

Step 6: Prepare the Health Report

• MySQL version and uptime.
• Current and maximum observed connection counts.
• Slow query logging configuration.
• Largest database schemas.
• Statement patterns with high accumulated execution time.
• CPU, memory, disk, or network concerns found at the operating-system level.
• Recommended next steps and metrics that should be monitored.

19. Interview Questions and Answers

Q1. What is MySQL server monitoring?

Answer: It is the process of observing database availability, workload, performance, resource usage, errors, and operational health.

Q2. What is the difference between status variables and system variables?

Answer: Status variables report activity or server state, while system variables describe configuration and behavior.

Q3. How can you check MySQL server uptime?

Answer: Run SHOW GLOBAL STATUS LIKE 'Uptime';.

Q4. What does Threads_connected indicate?

Answer: It reports the number of currently open connections to the MySQL server.

Q5. What is the purpose of the slow query log?

Answer: It records queries that meet the configured slow-query logging criteria and helps administrators investigate performance problems.

Q6. What is Performance Schema?

Answer: It is a MySQL feature that collects runtime instrumentation and performance data, including statement, wait, transaction, and file activity.

Q7. How do you investigate a slow SQL query?

Answer: Review its execution time, inspect the plan with EXPLAIN, check indexes and rows examined, and investigate locks and resource pressure.

Q8. Why should CPU and memory be monitored outside MySQL?

Answer: Operating-system tools show resource consumption by MySQL and other processes, providing a broader view of system health.

Q9. What is a monitoring baseline?

Answer: It is a record of normal metric behavior under expected workload, used to identify unusual changes and set useful alert thresholds.

Q10. Why is disk-space monitoring important?

Answer: A full filesystem can prevent writes, log creation, and other operations, affecting database availability and recovery.

Q11. What is the purpose of the sys schema?

Answer: It provides convenient diagnostic views and procedures based on Performance Schema and related information.

Q12. Is a high Threads_running value always a problem?

Answer: No. It depends on the workload and should be evaluated alongside latency, CPU usage, query activity, and lock waits.

20. Quick Revision Checklist

• Use SELECT VERSION() to identify the MySQL version.
• Use SHOW GLOBAL STATUS to inspect server activity.
• Compare connection counts with max_connections.
• Review slow queries and query execution plans.
• Monitor CPU, memory, disk space, disk latency, and network activity.
• Use Performance Schema for detailed runtime metrics.
• Use the sys schema for convenient diagnostic views.
• Investigate InnoDB buffer pool behavior and lock waits when relevant.
• Establish performance baselines and appropriate alerts.
• Test backups and recovery procedures regularly.

21. Conclusion

Monitoring MySQL server status and resource usage is essential for maintaining database performance, reliability, and availability. Status variables provide an overview of server activity, Performance Schema supplies detailed execution metrics, and the sys schema simplifies many diagnostic tasks. Operating-system monitoring adds important context about CPU, memory, storage, and network behavior.

Effective monitoring requires more than collecting numbers. Database administrators should establish a normal performance baseline, investigate changes using multiple sources of evidence, configure actionable alerts, and document their findings. Regular monitoring and tested recovery procedures help identify problems early and maintain reliable MySQL services.

22. Official Learning Resources

• MySQL Server Status Variables
• MySQL Performance Schema
• MySQL sys Schema
• MySQL Slow Query Log
• MySQL Optimization and Indexes
• Explore MySQL Training at JustAcademy
• Register for a MySQL Training Demo