Monitoring and profiling in MySQL - Step-by-Step Execution

Concept Flow - Monitoring and profiling

Start Monitoring

↓

Collect Metrics

↓

Analyze Performance

↓

Identify Bottlenecks

↓

Profile Queries

↓

Optimize Queries

↓

Repeat Cycle

This flow shows how monitoring collects data, profiling analyzes queries, and optimization improves performance in a cycle.

Execution Sample

MySQL

SHOW STATUS LIKE 'Threads_connected';
SET profiling = 1;
SELECT * FROM employees WHERE id = 10;
SHOW PROFILE FOR QUERY 1;
SET profiling = 0;

These commands show current active connections and detailed profiling of a specific query.

Execution Table

Step	Command	Action	Result
1	SHOW STATUS LIKE 'Threads_connected';	Check active connections	Threads_connected: 5
2	SET profiling = 1;	Enable profiling	Profiling enabled
3	SELECT * FROM employees WHERE id = 10;	Run query to profile	Query executed, profiling data collected
4	SHOW PROFILE FOR QUERY 1;	Display query profile	Detailed timing of query phases shown
5	SET profiling = 0;	Disable profiling	Profiling disabled

💡 Profiling disabled after query analysis to avoid overhead

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Threads_connected	unknown	5	5	5	5	5
profiling	OFF	OFF	ON	ON	ON	OFF
Query 1 Profile Data	none	none	none	collected	displayed	displayed

Key Moments - 3 Insights

Why do we enable profiling only before running the query?

Profiling must be enabled before the query runs to collect timing data, as shown in step 2 enabling profiling before step 3 query execution.

What does 'Threads_connected' tell us?

'Threads_connected' shows how many clients are currently connected to the MySQL server, useful for monitoring load (step 1).

Why disable profiling after analysis?

Profiling adds overhead, so it is disabled after analysis to keep performance normal, as in step 5.

Visual Quiz - 3 Questions

Look at the execution table, what is the value of 'Threads_connected' after step 1?

A 5

B 0

C 10

D Unknown

💡 Hint

Check the 'Result' column in row for step 1 in the execution table.

At which step is profiling enabled?

A Step 3

B Step 1

C Step 2

D Step 5

💡 Hint

Look at the 'Action' column for enabling profiling in the execution table.

If profiling was not disabled at step 5, what would happen?

A No effect on performance

B Server performance could degrade

C Profiling data would be lost

D Queries would run faster

💡 Hint

Refer to the key moment explaining why profiling is disabled after analysis.

Concept Snapshot

Monitoring and profiling in MySQL:
- Use SHOW STATUS to monitor server metrics.
- Enable profiling with SET profiling = 1 before running queries.
- Use SHOW PROFILE FOR QUERY n to see detailed query execution steps.
- Disable profiling after analysis to avoid performance impact.
- Repeat monitoring and profiling to optimize database performance.

Full Transcript

Monitoring and profiling in MySQL involves checking server status metrics like active connections using SHOW STATUS. Profiling is enabled with SET profiling = 1 before running a query to collect detailed timing data. After executing the query, SHOW PROFILE FOR QUERY n displays the breakdown of query execution phases. Profiling is then disabled to prevent overhead. This cycle helps identify slow queries and optimize them for better performance.

Practice

1. What is the main purpose of SHOW PROCESSLIST in MySQL monitoring?

easy

A. To display currently running queries and their status

B. To list all database users

C. To show the database schema

D. To backup the database

Solution

1. Step 1: Understand the command purpose

   SHOW PROCESSLIST lists all active threads and queries running on the MySQL server.

2. Step 2: Identify what it displays

   It shows query IDs, user, host, command, time, and status of each running query.

3. Final Answer:

   To display currently running queries and their status -> Option A

4. Quick Check:

   SHOW PROCESSLIST = Running queries info [OK]

Hint: SHOW PROCESSLIST shows active queries and their states [OK]

Common Mistakes:

• Confusing it with user listing commands
• Thinking it shows database structure
• Assuming it performs backups

2. Which of the following is the correct syntax to enable the MySQL slow query log?

easy

A. SET GLOBAL slow_query_log = ON;

B. SET GLOBAL slow_query_log = 1;

C. ENABLE slow_query_log;

D. START slow_query_log;

Solution

1. Step 1: Recall the correct syntax for enabling slow query log

   MySQL uses numeric values 0 (off) and 1 (on) for boolean system variables.

2. Step 2: Check each option

   SET GLOBAL slow_query_log = 1; uses SET GLOBAL slow_query_log = 1; which is correct syntax to enable the slow query log.

3. Final Answer:

   SET GLOBAL slow_query_log = 1; -> Option B

4. Quick Check:

   Enable slow query log with 1, not ON [OK]

Hint: Use 1 or 0 to enable/disable slow query log [OK]

Common Mistakes:

• Using ON instead of 1
• Using ENABLE or START commands which don't exist
• Confusing session and global variables

3. Given the query:

SHOW STATUS LIKE 'Slow_queries';

What does the output number represent?

medium

A. The maximum time a query took to execute

B. The total number of queries executed since server start

C. The number of currently running slow queries

D. The number of queries that took longer than the slow query time threshold

Solution

1. Step 1: Understand the status variable 'Slow_queries'

   This variable counts how many queries exceeded the slow query time limit since the server started.

2. Step 2: Interpret the output meaning

   The number is cumulative, showing total slow queries, not current or max time.

3. Final Answer:

   The number of queries that took longer than the slow query time threshold -> Option D

4. Quick Check:

   Slow_queries = count of slow queries [OK]

Hint: Slow_queries counts total slow queries since server start [OK]

Common Mistakes:

• Thinking it shows current running slow queries
• Confusing it with total queries count
• Assuming it shows max query time

4. You run SHOW PROFILE; but get an error saying profiling is disabled. What should you do to fix this?

medium

A. Run SET profiling = 1; before running queries

B. Restart MySQL server to enable profiling

C. Run ENABLE PROFILING; command

D. Profiling is deprecated and cannot be enabled

Solution

1. Step 1: Understand profiling status

   Profiling is off by default and must be enabled per session with SET profiling = 1;.

2. Step 2: Identify correct enabling method

   Run SET profiling = 1; before running queries correctly enables profiling for the current session before running queries.

3. Final Answer:

   Run SET profiling = 1; before running queries -> Option A

4. Quick Check:

   Enable profiling with SET profiling = 1 [OK]

Hint: Enable profiling per session with SET profiling = 1 [OK]

Common Mistakes:

• Trying to enable profiling globally or via restart
• Using ENABLE PROFILING command which doesn't exist
• Assuming profiling is permanently on

5. You want to find the top 3 slowest queries executed on your MySQL server using the slow query log. Which approach is best?

hard

A. Run SHOW PROCESSLIST and sort by longest running queries

B. Use EXPLAIN on all queries to find slow ones

C. Enable slow query log, then query the mysql.slow_log table ordering by query_time DESC LIMIT 3

D. Restart MySQL with debug mode to log slow queries

Solution

1. Step 1: Enable slow query log and log to table

   Set slow_query_log = 1 and log_output = 'TABLE' to log slow queries into mysql.slow_log table.

2. Step 2: Query the slow_log table for top slow queries

   Run SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 3; to get the slowest queries.

3. Final Answer:

   Enable slow query log, then query the mysql.slow_log table ordering by query_time DESC LIMIT 3 -> Option C

4. Quick Check:

   Slow query log table + ORDER BY query_time DESC = top slow queries [OK]

Hint: Query slow_log table ordered by query_time DESC for slowest queries [OK]

Common Mistakes:

• Using SHOW PROCESSLIST which shows only current queries
• Using EXPLAIN which analyzes query plan, not runtime
• Restarting server unnecessarily for logging