Monitoring and profiling in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

💻 Command Output

intermediate

Identify slow queries using MySQL slow query log

You enabled the slow query log in MySQL with the following settings:

slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

What will be the output of the command below?

tail -n 5 /var/log/mysql/mysql-slow.log

A Shows the first 5 queries executed since MySQL started

B Shows the last 5 queries regardless of execution time

C Shows an error because the slow query log file path is incorrect

D Shows the last 5 slow queries that took longer than 2 seconds to execute

💡 Hint

The slow query log records queries that exceed the long_query_time threshold.

The slow query log file contains queries that took longer than the configured long_query_time (2 seconds here). The tail command shows the last 5 entries from this log.

❓ Configuration

intermediate

Configure MySQL to enable performance schema for profiling

Which configuration snippet correctly enables the Performance Schema in MySQL for detailed query profiling?

A

[mysqld]
performance_schema=TRUE

B

[mysqld]
performance_schema=ON

C

[mysqld]
performance_schema=1

D

[mysqld]
enable_performance_schema=ON

💡 Hint

Check the exact variable name and accepted values in MySQL documentation.

The correct variable to enable Performance Schema is 'performance_schema' and it accepts 'ON' or 'OFF' as values.

❓ Troubleshoot

advanced

Diagnose why slow query log is not recording queries

You enabled the slow query log with:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

But no queries appear in the slow query log file. What is the most likely cause?

A The MySQL user does not have write permission to the slow query log file location

B The long_query_time is set too low to capture any queries

C The slow query log requires a server restart to take effect

D The slow query log file path is not set, so logging is disabled

💡 Hint

Check file permissions and ownership for the log file path.

If MySQL cannot write to the slow query log file due to permission issues, no queries will be logged even if logging is enabled.

🔀 Workflow

advanced

Steps to profile a query using Performance Schema

Arrange the steps in the correct order to profile a slow query using MySQL Performance Schema.

A 1,2,3,4

B 2,1,3,4

C 1,3,2,4

D 3,1,2,4

💡 Hint

Profiling requires enabling, running, then analyzing.

First enable Performance Schema and restart MySQL. Then run the query to collect data. Next, query the Performance Schema tables. Finally, analyze the data.

✅ Best Practice

expert

Best practice for continuous MySQL query performance monitoring

Which approach is best for continuous monitoring of MySQL query performance in a production environment?

A Enable slow query log with a low long_query_time and manually analyze logs daily

B Disable all logging to maximize performance and only enable logs during incidents

C Use Performance Schema with automated tools to collect and visualize query metrics continuously

D Run EXPLAIN on all queries manually every week to check performance

💡 Hint

Consider automation and minimal manual effort for continuous monitoring.

Performance Schema combined with automated monitoring tools provides continuous, detailed insights with minimal manual work, ideal for production.