Monitoring and profiling in MySQL

Introduction

Monitoring and profiling help you see how your database works and find slow parts. This makes your database faster and more reliable.

You want to find slow queries that make your website slow.

You need to check how many connections your database has.

You want to see how much CPU or memory your database uses.

You want to understand which queries use the most resources.

You want to track errors or warnings in your database.

Syntax

MySQL

SHOW STATUS;
SHOW PROCESSLIST;
SHOW PROFILE;
SHOW PROFILES;

SHOW STATUS shows server status variables.

SHOW PROCESSLIST shows current running queries.

SHOW PROFILE shows detailed info about a query's execution.

Examples

Shows how many clients are connected to the database now.

MySQL

SHOW STATUS LIKE 'Threads_connected';

Lists all current queries running on the server.

MySQL

SHOW PROCESSLIST;

Turns on profiling, runs a query, then shows the time taken by recent queries.

MySQL

SET profiling = 1;
SELECT * FROM users WHERE id = 1;
SHOW PROFILES;

Sample Program

This example turns on profiling, runs a query that waits 1 second, then shows the profile with execution time.

MySQL

SET profiling = 1;
SELECT SLEEP(1);
SHOW PROFILES;

Important Notes

Profiling is off by default; you must enable it with SET profiling = 1;.

Profiling works per session, so it only shows queries run in the current connection.

Use SHOW PROCESSLIST to find queries that are stuck or running too long.

Summary

Monitoring shows the current state of your database server.

Profiling helps measure how long queries take to run.

Use these tools to find and fix slow or problematic queries.