Monitoring and profiling in MySQL - Mini Project: Build & Apply

Monitoring and Profiling MySQL Queries

📖 Scenario: You are a database administrator for a small online store. You want to monitor and profile the queries running on your MySQL server to find slow queries and improve performance.

🎯 Goal: Learn how to enable the slow query log, set a threshold for slow queries, and view the slow queries recorded by MySQL.

📋 What You'll Learn

Enable the slow query log in MySQL

Set the slow query time threshold to 2 seconds

Run a sample slow query

View the slow query log output

💡 Why This Matters

🌍 Real World

Monitoring slow queries helps database administrators find and fix performance bottlenecks in real applications.

💼 Career

Knowing how to enable and read MySQL slow query logs is a key skill for database administrators and DevOps engineers.

1

Enable the slow query log

Write a MySQL command to enable the slow query log by setting slow_query_log to ON.

MySQL

# Your code here

Need a hint?

Use SET GLOBAL slow_query_log = ON; to enable the slow query log.

2

Set the slow query time threshold

Write a MySQL command to set the slow query time threshold by setting long_query_time to 2 seconds.

MySQL

SET GLOBAL slow_query_log = ON;
# Your code here

Need a hint?

Use SET GLOBAL long_query_time = 2; to set the threshold to 2 seconds.

3

Run a sample slow query

Write a MySQL query that will take longer than 2 seconds to run. Use SELECT SLEEP(3); to simulate a slow query.

MySQL

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
# Your code here

Need a hint?

Use SELECT SLEEP(3); to simulate a query that runs for 3 seconds.

4

View the slow query log

Write a MySQL command to show the contents of the slow query log file. Use SHOW VARIABLES LIKE 'slow_query_log_file'; to find the file path, then use system cat <file_path> to display the log contents.

MySQL

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SELECT SLEEP(3);
# Your code here

Need a hint?

First find the slow query log file path with SHOW VARIABLES LIKE 'slow_query_log_file';. Then use system cat <file_path> to view the log.