Database CPU 100% Incident

Symptoms

Indicators:
├── CPU usage > 90%
├── Query response time > 1s
├── Connection count high
├── Slow query log growing
├── Application timeouts
└── User-facing errors

Impact:
├── Slow page loads
├── Checkout failures
├── Search timeouts
└── Admin panel unresponsive

Detection

# Monitor MySQL CPU
mysqladmin status
SHOW PROCESSLIST;
SHOW GLOBAL STATUS LIKE 'Threads_running';

# Check slow queries
SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';

# Check InnoDB status
SHOW ENGINE INNODB STATUS;

Common Causes

1. Missing indexes
2. Full table scans
3. Lock contention
4. Too many connections
5. Large JOINs
6. Suboptimal queries
7. Statistics outdated
8. Buffer pool too small

Step 1: Identify Problem Queries

-- Find running queries
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

-- Find slow queries
SELECT * FROM mysql.slow_log
WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY query_time DESC
LIMIT 10;

-- Find queries using full scan
SELECT * FROM sys.statements_with_full_table_scans
ORDER BY no_index_used_count DESC
LIMIT 10;

Step 2: Analyze Query Performance

-- Analyze specific query
EXPLAIN SELECT * FROM catalog_product_entity
WHERE sku = '24-WB04';

-- Check index usage
SHOW INDEX FROM catalog_product_entity;

-- Check query execution plan
EXPLAIN ANALYZE SELECT * FROM catalog_product_entity
JOIN catalog_product_entity_varchar ON ...
WHERE catalog_product_entity_varchar.value LIKE '%wireless%';

Step 3: Check System Resources

# Check MySQL process
ps aux | grep mysql

# Check connections
mysql -e "SHOW STATUS LIKE 'Threads_connected';"

# Check buffer pool
mysql -e "SHOW STATUS LIKE 'Innodb_buffer_pool%';"

# Check disk I/O
iostat -x 1 5

# Check memory
free -m

Immediate Actions

-- Kill long-running queries
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.processlist
WHERE command != 'Sleep'
  AND time > 60
  AND user = 'magento';

-- Kill specific query
KILL QUERY 12345;

-- Kill connection
KILL 12345;

-- Increase max connections (temporary)
SET GLOBAL max_connections = 200;

-- Optimize table
OPTIMIZE TABLE catalog_product_entity;

Query Optimization

-- Add missing index
ALTER TABLE catalog_product_entity
ADD INDEX idx_sku (sku);

-- Add composite index
ALTER TABLE catalog_product_entity_varchar
ADD INDEX idx_attr_entity (attribute_id, entity_id, store_id);

-- Rewrite slow query
-- Before: Full table scan
SELECT * FROM catalog_product_entity
WHERE name LIKE '%wireless%';

-- After: Use full-text search
SELECT * FROM catalog_product_entity
WHERE MATCH(name) AGAINST('wireless' IN BOOLEAN MODE);

System Tuning

-- Increase buffer pool size
SET GLOBAL innodb_buffer_pool_size = 4294967296; -- 4GB

-- Optimize query cache (MySQL 5.7)
SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_size = 67108864; -- 64MB

-- Optimize thread cache
SET GLOBAL thread_cache_size = 16;

-- Optimize table open cache
SET GLOBAL table_open_cache = 2000;

Scale Up

# Add read replica
mysql -e "CHANGE MASTER TO MASTER_HOST='replica1';"

# Switch reads to replica
# Update application config to use read replica

# Vertical scaling
# Increase CPU cores, RAM

Monitoring Setup

// Monitor these metrics
$metrics = [
    'mysql_cpu_usage' => [
        'warning' => 70,
        'critical' => 90
    ],
    'mysql_slow_queries' => [
        'warning' => 10,
        'critical' => 50
    ],
    'mysql_connections' => [
        'warning' => 100,
        'critical' => 150
    ],
    'mysql_query_time' => [
        'warning' => 1000, // ms
        'critical' => 5000
    ]
];

// Alert configuration
$alerts = [
    'database_cpu' => [
        'metric' => 'mysql_cpu_usage',
        'threshold' => 90,
        'duration' => '5m',
        'action' => 'page_on_call'
    ]
];

Query Review Process

// Require EXPLAIN for all queries
public function reviewQuery($query)
{
    $explain = $this->db->fetchRow('EXPLAIN ' . $query);
    
    if ($explain['type'] === 'ALL') {
        throw new \Exception('Full table scan detected');
    }
    
    if ($explain['key'] === null) {
        throw new \Exception('No index used');
    }
    
    if ($explain['rows'] > 10000) {
        throw new \Exception('Query returns too many rows');
    }
}

Capacity Planning

// Monitor growth
$growth = [
    'table_sizes' => $this->getTableSizes(),
    'index_sizes' => $this->getIndexSizes(),
    'query_volume' => $this->getQueryVolume(),
    'connection_count' => $this->getConnectionCount()
];

// Plan scaling
if ($growth['table_sizes']['catalog_product_entity'] > 1000000) {
    $this->plan->addTask('Shard catalog_product_entity');
}

if ($growth['connection_count'] > 100) {
    $this->plan->addTask('Add connection pooling');
}