Show MySQL Performance — Variables, Status & Monitoring

Introduction

MySQL performance tuning and monitoring are critical for maintaining healthy, responsive database systems. This guide covers the essential commands and concepts for monitoring MySQL performance in real-time, understanding configuration variables, and diagnosing bottlenecks.

Core Concepts & Terminology 📚

Key Terms

Variables (Settings): Configuration parameters that control MySQL server behavior, buffer sizes, timeouts, and resource allocation.
Status Counters: Real-time metrics showing current database activity (queries executed, connections, cache hits, etc.).
Session Scope: Variables/status metrics for a single client connection.
Global Scope: Variables/status metrics for the entire MySQL server instance.

Understanding the Scope

Global variables apply server-wide and persist across all connections. Session variables are connection-specific and may differ per client. Understanding scope is vital when tuning:

┌─────────────────────────────────────┐
│       MySQL Server Instance          │
├─────────────────────────────────────┤
│  Global Variables & Status Counters  │
│                                      │
│  ┌────────────┐  ┌────────────┐    │
│  │ Connection │  │ Connection │    │
│  │ Session #1 │  │ Session #2 │    │
│  │(Local Vars)│  │(Local Vars)│    │
│  └────────────┘  └────────────┘    │
│                                      │
└─────────────────────────────────────┘

Core Abbreviations & Key Metrics 🔑

Abbreviation	Full Name	What It Means
InnoDB	The default storage engine	Provides ACID transactions, row-level locking, and crash recovery
ACID	Atomicity, Consistency, Isolation, Durability	Guarantees reliable database transactions
QPS	Queries Per Second	Number of queries processed per second; key performance metric
TPS	Transactions Per Second	Number of completed transactions per second
IOP	I/O Operations Per Second	Disk read/write rate; affects storage performance
Slow Query Log	Log of queries exceeding `long_query_time`	Helps identify performance bottlenecks
Query Cache	In-memory cache for query results (MySQL ≤ 5.7)	Speeds up identical queries; removed in MySQL 8.0+
Buffer Pool	InnoDB’s in-memory cache for data pages	Huge performance impact; should be 50–80% of system RAM

SHOW VARIABLES — Understanding Configuration 🔧

Basic Syntax

# Show all variables (default: session-only in some MySQL versions)
SHOW [GLOBAL | SESSION] VARIABLES;

# Show variables matching a pattern
SHOW VARIABLES LIKE 'innodb%';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

# Show specific variable
SELECT @@global.max_connections;
SELECT @@session.sql_mode;

Practical Examples: Critical Performance Variables

1. Max Connections

-- View current limit
SHOW VARIABLES LIKE 'max_connections';

-- Result might be:
-- Variable_name: max_connections
-- Value: 151

-- Increase limit (session requires SUPER privilege)
SET GLOBAL max_connections = 1000;

-- Verify change (persists until restart; add to my.cnf for permanent change)
-- Add to /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf:
-- [mysqld]
-- max_connections = 1000

Why it matters: Determines how many simultaneous client connections are allowed. Insufficient limit = rejected connection attempts; excessive setting = wasted memory.

2. InnoDB Buffer Pool Size

-- Check current buffer pool size
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Optimal value is 50–80% of system RAM
-- For a 32GB server, set to ~24GB
SET GLOBAL innodb_buffer_pool_size = 26843545600;  -- 25GB in bytes

-- View how it affects memory usage
SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS buffer_pool_gb;

Why it matters: Larger buffer pool = more data cached in RAM = fewer disk reads = faster queries. This is the single most impactful tuning setting for InnoDB.

3. Query Timeout Settings

-- Interactive client timeout (seconds, default 28800 = 8 hours)
SHOW VARIABLES LIKE 'interactive_timeout';
SET GLOBAL interactive_timeout = 600;  -- 10 minutes

-- Connection timeout without activity (seconds)
SHOW VARIABLES LIKE 'wait_timeout';
SET GLOBAL wait_timeout = 600;

-- TCP timeout (prevents zombie connections)
SHOW VARIABLES LIKE 'net_read_timeout';
SET GLOBAL net_read_timeout = 120;  -- 2 minutes

4. Slow Query Log

-- Enable slow query logging
SET GLOBAL slow_query_log = 'ON';

-- Define what counts as "slow" (seconds; default 10)
SET GLOBAL long_query_time = 2;

-- Specify log file location
SHOW VARIABLES LIKE 'slow_query_log_file';
-- File: /var/log/mysql/slow.log

Persistent configuration in /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
slow_query_log = 1
long_query_time = 2
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 1

SHOW STATUS — Real-Time Performance Metrics 📊

Basic Syntax

-- Show all session status
SHOW STATUS;
-- or explicitly
SHOW SESSION STATUS;

-- Show global status (cumulative across entire server)
SHOW GLOBAL STATUS;

-- Filter by pattern
SHOW GLOBAL STATUS LIKE 'Innodb%';
SHOW GLOBAL STATUS WHERE variable_name = 'Questions';

Key Performance Metrics & Interpretation

1. Queries & Throughput

-- Number of SQL statements executed
SHOW GLOBAL STATUS LIKE 'Questions';

-- Break it down: SELECT, INSERT, UPDATE, DELETE counts
SHOW GLOBAL STATUS LIKE 'Com_%';
-- Result example:
-- Com_select: 523400
-- Com_insert: 12300
-- Com_update: 8900
-- Com_delete: 2100

-- Calculate QPS (Queries Per Second) over time:
-- 1. Get Questions count
SELECT @@global.Questions AS q1;
-- 2. Sleep 10 seconds
-- SELECT SLEEP(10);
-- 3. Get Questions count again
SELECT @@global.Questions AS q2;
-- QPS = (q2 - q1) / 10

-- Or use a simple script to monitor
SHOW GLOBAL STATUS LIKE 'Uptime';  -- Server running time in seconds

2. Connection Metrics

-- Current active connections
SHOW GLOBAL STATUS LIKE 'Threads_connected';

-- Total connections since startup
SHOW GLOBAL STATUS LIKE 'Connections';

-- Connection errors (refused, aborted)
SHOW GLOBAL STATUS LIKE 'Connection%';
-- Aborted_clients: clients that disconnected abnormally
-- Aborted_connects: refused connections

3. Cache Performance (Buffer Pool & Query Cache)

-- InnoDB Buffer Pool efficiency
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
-- Example:
-- Innodb_buffer_pool_reads: 1250 (disk page loads)
-- Innodb_buffer_pool_read_requests: 98500 (cache lookups)
-- Hit Ratio = (1 - reads / read_requests) * 100
--           = (1 - 1250 / 98500) * 100 ≈ 98.7%

-- Query cache hits (if using MySQL ≤ 5.7)
SHOW GLOBAL STATUS LIKE 'Qcache%';
-- Qcache_hits: queries served from cache
-- Qcache_inserts: queries added to cache
-- Qcache_not_cached: queries that couldn't be cached

4. InnoDB Transaction & Lock Metrics

-- Rows read/modified
SHOW GLOBAL STATUS LIKE 'Innodb_rows%';
-- Innodb_rows_read: rows examined
-- Innodb_rows_inserted, updated, deleted: writes

-- Transactions committed/rolled back
SHOW GLOBAL STATUS LIKE 'Innodb_trx%';
-- Innodb_trx_commit, rollback, abort

-- Lock waits (high values = contention)
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';
-- Innodb_row_lock_waits: transactions waiting on row locks
-- Innodb_row_lock_time: total wait time in milliseconds

5. Slow Query Metrics

-- Queries that exceeded long_query_time
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- Analyze slow log
-- Linux/Mac command:
-- mysqldumpslow -s c -t 10 /var/log/mysql/slow.log  # Top 10 slow queries

Real-World Monitoring Script 🎯

Create a simple monitoring script to diagnose issues:

-- FILE: mysql_performance_snapshot.sql
-- Run this to get a performance snapshot

SELECT '=== UPTIME & CONNECTIONS ===' AS metric;
SELECT 
  @@global.version AS mysql_version,
  ROUND((@@global.uptime / 3600), 2) AS uptime_hours,
  @@global.max_connections AS max_connections,
  (SELECT COUNT(*) FROM information_schema.processlist) AS current_connections;

SELECT '=== QPS & THROUGHPUT ===' AS metric;
SELECT 
  @@global.Questions AS total_questions,
  ROUND(@@global.Questions / @@global.uptime, 2) AS avg_qps,
  (SELECT SUM(VARIABLE_VALUE) FROM performance_schema.global_status 
   WHERE VARIABLE_NAME LIKE 'Com_%') AS total_commands;

SELECT '=== INNODB BUFFER POOL ===' AS metric;
SELECT 
  ROUND(@@global.innodb_buffer_pool_size / 1024 / 1024 / 1024, 2) AS pool_size_gb,
  (SELECT SUM(VARIABLE_VALUE) FROM performance_schema.global_status 
   WHERE VARIABLE_NAME IN ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests')) 
   AS pool_requests,
  (SELECT SUM(VARIABLE_VALUE) FROM performance_schema.global_status 
   WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') AS disk_reads;

SELECT '=== LOCK CONTENTION ===' AS metric;
SELECT 
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status 
   WHERE VARIABLE_NAME = 'Innodb_row_lock_waits') AS row_lock_waits,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status 
   WHERE VARIABLE_NAME = 'Innodb_row_lock_time') AS total_lock_wait_ms;

SELECT '=== SLOW QUERIES ===' AS metric;
SELECT 
  @@global.slow_query_log AS slow_query_enabled,
  @@global.long_query_time AS slow_query_threshold_sec,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status 
   WHERE VARIABLE_NAME = 'Slow_queries') AS slow_query_count;

Usage:

mysql -u root -p < mysql_performance_snapshot.sql

Deployment Architecture for Monitoring 🏗️

Typical monitoring setup:

┌─────────────────┐
│  Monitoring App │   (Prometheus, Datadog, New Relic)
│  (Metrics,      │
│   Alerting)     │
└────────┬────────┘
         │ polls SHOW STATUS every 60s
         │
┌────────▼─────────────────┐
│   MySQL Server Instance  │
│   ├─ SHOW VARIABLES      │
│   ├─ SHOW STATUS         │
│   ├─ Performance Schema  │
│   └─ Slow Query Log      │
└──────────────────────────┘
         │
┌────────▼────────────┐
│   Grafana Dashboard │   (visualize trends)
│   (trends, graphs)  │
└─────────────────────┘

Common Pitfalls & Best Practices ⚠️

Pitfalls to Avoid

Problem	Cause	Solution
High memory usage	Buffer pool too large (> 80% RAM)	Set to 50–80% of available RAM; monitor OOM killer
Connection pool exhaustion	`max_connections` too low	Increase gradually; monitor with `SHOW PROCESSLIST`
Slow queries pile up	Missing indexes, full table scans	Enable slow log; use `EXPLAIN` to optimize queries
Lock contention	High concurrent writes to same rows	Redesign transactions; use row-level locking wisely
Stale persistent connections	Clients don’t reconnect after DB restart	Use connection pooling (PgBouncer, ProxySQL)
Unreliable timeout values	Firewall/network resets connections	Set `wait_timeout` conservatively (600–900 sec)

Best Practices ✅

Baseline & Monitor Regularly
- Capture SHOW STATUS at low-traffic times as a baseline
- Compare against baseline to spot anomalies
- Monitor buffer pool hit ratio (should be >99%)

Enable Slow Query Log in Production (with caution)

SET GLOBAL long_query_time = 1;  -- or 2–5 sec depending on workload
SET GLOBAL log_queries_not_using_indexes = 1;

Tune Buffer Pool for Your Hardware

# Rough calculation
# 32GB RAM → set buffer pool to ~24–26GB
total_ram_gb=32
buffer_pool_gb=$((total_ram_gb * 3 / 4))
echo "Recommended buffer pool: ${buffer_pool_gb}GB"

Use Connection Pooling
- Every new connection to MySQL requires handshake overhead
- Use ProxySQL, PgBouncer, or application-level pooling
- Reduces load; improves response time

Monitor in Real Time with mysqladmin

# Monitor STATUS every 5 seconds, 10 iterations
mysqladmin -u root -p extended-status -i5 -c10

# Monitor processlist for long-running queries
mysqladmin -u root -p processlist

Use Information Schema & Performance Schema

-- Find queries currently running
SELECT * FROM information_schema.processlist 
WHERE state != '' AND time > 60;  -- Queries running > 60 sec

-- Find most executed statements (MySQL 5.7+)
SELECT * FROM performance_schema.events_statements_summary_by_digest 
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

Pros & Cons: MySQL vs. Alternative Databases 🔄

MySQL Strengths ✅

Advantage	Benefit
ACID Compliance (InnoDB)	Reliable transactions; data integrity
Simple to tune	Fewer knobs than PostgreSQL; easier baseline
Mature ecosystem	Rich monitoring tools; vast documentation
Write scalability (with sharding)	Horizontal scaling possible with planning
Cost	Open-source; lower operational cost

MySQL Weaknesses ❌

Limitation	Trade-off
Read-only scaling limited	Replica lag; not true read distribution
Query optimizer inconsistent	Needs manual index hints for complex queries
Full-text search weak	Inferior to Elasticsearch; use external tools
JSON support basic	Not comparable to MongoDB or PostgreSQL
Locking overhead	Row locks can cause contention under high concurrency

When to Choose Alternatives

Scenario	Better Alternative
Complex JSON documents, flexible schema	MongoDB (NoSQL; document store)
Advanced analytics, complex queries	PostgreSQL (more powerful optimizer; JSONB, arrays)
Time-series metrics	InfluxDB / Prometheus (optimized for time-series)
Extreme write throughput, no ACID needed	Cassandra / DynamoDB (highly distributed)
Full-text search, log analytics	Elasticsearch (search-focused)
Real-time cache, low-latency	Redis (in-memory; not persistent)

Useful Resources & Tools 📖

Official Documentation

Performance Tuning Guides

“High Performance MySQL” (3rd Edition) — Baron Schwartz, Peter Zaitsev, Vadim Tkachenko — Comprehensive MySQL tuning bible
Percona Blog: MySQL Performance Tuning
MySQL Tuning Primer Script — Auto-generates recommendations based on SHOW STATUS

Monitoring Tools

Prometheus + Grafana — Open-source metrics + visualization
- MySQLd Exporter — Export MySQL metrics to Prometheus
Percona Monitoring & Management (PMM) — Free, open-source MySQL-specific monitoring
New Relic, Datadog, Splunk — Commercial APM solutions with MySQL support

Query Optimization Tools

Percona Toolkit — pt-query-digest to analyze slow logs
```
pt-query-digest /var/log/mysql/slow.log
```
MySQL Explain Analyzer — Visual EXPLAIN output parser
SolarWinds DPA — Advanced query execution plans

Alternative Monitoring Approaches 🔧

1. Performance Schema (MySQL 5.7+)

More detailed than SHOW STATUS; lower overhead:

-- Enable Performance Schema
SELECT @@global.performance_schema;  -- Must be ON

-- Find top time-consuming queries
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

-- Find hottest tables
SELECT * FROM performance_schema.table_io_waits_summary_by_table
ORDER BY SUM_TIMER_WAIT DESC LIMIT 5;

2. Replication Monitoring

If using MySQL replication:

-- Check replication lag (seconds)
SHOW SLAVE STATUS\G
-- Look for Seconds_Behind_Master

-- Master perspective
SHOW MASTER STATUS\G

3. InnoDB-Specific Metrics

-- View InnoDB engine status (very detailed)
SHOW ENGINE INNODB STATUS\G
-- Contains: locks, transactions, buffer pool, I/O stats

Troubleshooting Scenario: High Load Investigation 🔍

Situation: Production MySQL server suddenly becomes slow.

Step-by-step diagnosis:

# 1. Check overall resource usage
SHOW GLOBAL STATUS LIKE 'Threads_connected';  # How many connections?

# 2. Identify slow queries
SHOW GLOBAL STATUS LIKE 'Slow_queries';  # How many slow queries since startup?
mysqldumpslow -s c -t 5 /var/log/mysql/slow.log  # Top 5 slow queries

# 3. Check buffer pool efficiency
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';  # Hit ratio < 95%?

# 4. Detect lock contention
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_waits';  # Spikes?

# 5. Find current long-running queries
SELECT * FROM information_schema.processlist WHERE TIME > 300;  # > 5 min

# 6. Check disk I/O
# (System level, not MySQL)
iostat -x 1  # Watch for iowait spikes

# 7. Kill problematic queries if needed
KILL QUERY <process_id>;  # Kill single query
KILL <process_id>;  # Kill entire connection

Summary

Use SHOW VARIABLES to understand and tune MySQL configuration, and SHOW STATUS to monitor real-time performance. Key metrics to watch:

QPS & throughput (Questions, Threads_connected)
Cache efficiency (Innodb_buffer_pool hit ratio > 99%)
Lock contention (Innodb_row_lock_waits)
Connection health (Connections, Aborted_connects)

Combine with slow query log, Performance Schema, and external monitoring for comprehensive insights. Regular baselining and proactive tuning prevent most production issues.

Introduction

Core Concepts & Terminology 📚

Key Terms

Understanding the Scope

Core Abbreviations & Key Metrics 🔑

SHOW VARIABLES — Understanding Configuration 🔧

Basic Syntax

Practical Examples: Critical Performance Variables

1. Max Connections

2. InnoDB Buffer Pool Size

3. Query Timeout Settings

4. Slow Query Log

SHOW STATUS — Real-Time Performance Metrics 📊

Basic Syntax

Key Performance Metrics & Interpretation

1. Queries & Throughput

2. Connection Metrics

3. Cache Performance (Buffer Pool & Query Cache)

4. InnoDB Transaction & Lock Metrics

5. Slow Query Metrics

Real-World Monitoring Script 🎯

Deployment Architecture for Monitoring 🏗️

Common Pitfalls & Best Practices ⚠️

Pitfalls to Avoid

Best Practices ✅

Pros & Cons: MySQL vs. Alternative Databases 🔄

MySQL Strengths ✅

MySQL Weaknesses ❌

When to Choose Alternatives

Useful Resources & Tools 📖

Official Documentation

Performance Tuning Guides

Monitoring Tools

Query Optimization Tools

Alternative Monitoring Approaches 🔧

1. Performance Schema (MySQL 5.7+)

2. Replication Monitoring

3. InnoDB-Specific Metrics

Troubleshooting Scenario: High Load Investigation 🔍

Summary

Comments