SHOW STATUS - MySQL

Overview

The SHOW STATUS command in MySQL displays the current status and values of system variables, connection information, and thread statistics. It is primarily used for debugging and troubleshooting MySQL performance issues.

Syntax

SHOW STATUS [VARIABLES | SESSION | GLOBAL | ALL]
[LIKE 'pattern']
[WHERE condition]

Options/Flags

VARIABLES

  • VARIABLES: Shows the status of all MySQL system variables.

SESSION

  • SESSION: Shows the status of variables specific to the current session.

GLOBAL

  • GLOBAL: Shows the status of global variables that apply to all sessions.

ALL

  • ALL: Shows the status of all variables across all levels (system, session, and global).

LIKE

  • LIKE: Filters the output to show only variables that match the specified pattern.

WHERE

  • WHERE: Filters the output based on a specified condition.

Examples

Display All System Variables

SHOW VARIABLES;

Display Session-Specific Status

SHOW SESSION STATUS LIKE 'Threads%';

Filter Output By Condition

SHOW GLOBAL STATUS WHERE Variable_name = 'Slow_queries';

Common Issues

Incomplete Output

  • Ensure that the mysql.status table exists in the performance schema. If not, run CREATE TABLE mysql.status (disabled SMALLINT) to create it.

Missing Permission

  • The SHOW STATUS command requires the SUPER or PROCESS privilege. Grant the necessary privilege to the user if needed.

Integration

Combining with Slow Query Log

  • Use SHOW STATUS WHERE Variable_name = 'Slow_queries' to check if the slow query log is enabled.

Analyzing Thread Pool Size

  • Use SHOW STATUS LIKE 'Threads%' to monitor the current and maximum number of threads in the thread pool.