When troubleshooting MySQL performance issues such as CPU saturation, several administrative techniques can help identify and resolve problems efficiently. This guide coveers practical commands for process control, capacity analysis, and schema management.

Bulk Connection Termination

When specific clients generate excessive connections, individual KILL commands become impractical. Use this dynamic approach:

-- Generate termination statements for connections from a problematic subnet
SET @host_pattern = '10.50.12.%';

SELECT CONCAT('KILL ',id,';') AS kill_statement
FROM information_schema.processlist
WHERE host LIKE @host_pattern
INTO OUTFILE '/tmp/batch_terminate.sql';

SOURCE /tmp/batch_terminate.sql;

Cross-Database Storage Analysis

Monitor storage distribution across all schemas to identify capacity bottlenecks:

-- Storage overview across all databases
SELECT 
    table_schema AS database_name,
    FORMAT(SUM(table_rows), 0) AS total_rows,
    FORMAT(SUM(data_length) / 1024 / 1024, 2) AS data_mb,
    FORMAT(SUM(index_length) / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

Granular Table-Level Metrics

Identify individual tables consuming the most resources:

-- Detailed breakdown per table
SELECT 
    table_schema AS db_name,
    table_name AS table_name,
    table_rows AS estimated_rows,
    ROUND(data_length / POWER(1024, 2), 2) AS data_size_mb,
    ROUND(index_length / POWER(1024, 2), 2) AS index_size_mb
FROM information_schema.tables
ORDER BY data_length DESC, index_length DESC
LIMIT 50;

Focused Schema Examination

Analyze a specific database's footprint, for example the system schema:

-- Capacity summary for a single database
SELECT 
    table_schema AS schema_name,
    SUM(table_rows) AS row_count,
    ROUND(SUM(data_length) / (1024*1024), 2) AS total_data_mb,
    ROUND(SUM(index_length) / (1024*1024), 2) AS total_index_mb
FROM information_schema.tables
WHERE table_schema = 'sys';

Table-Level Deep Dive

Examine every table within a particular schema:

-- All tables in mysql system database
SELECT 
    table_name AS name,
    table_rows AS rows_estimate,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'mysql'
ORDER BY data_length DESC, index_length DESC;

Efficient Table Duplication

Create backups before schema changes or data migrations:

-- Safe table backup within a transaction
START TRANSACTION;

CREATE TABLE IF NOT EXISTS users_backup LIKE users;

INSERT INTO users_backup
SELECT * FROM users;

COMMIT;

Row Count Estimation Techniques

Get quick estimates or precise counts depending on your needs:

-- Fast approximation from metadata
SELECT 
    TABLE_NAME AS name,
    TABLE_ROWS AS estimated_rows
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'warehouse';

-- Precise row count with COUNT()
SELECT 
    'inventory' AS table_name,
    COUNT(*) AS exact_rows
FROM warehouse.inventory;

Command-line alternative:

# Display table statistics including row counts
mysqlshow --count --status warehouse

Schema Modification for Primary Keys

Add an auto-incrementing primary key to existing tables:

-- Add surrogate auto-increment key to existing table
ALTER TABLE access_log
ADD COLUMN entry_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;