Mysql Performance

---
name: mysql-performance
description: Expert in MySQL database performance optimization, indexing, query tuning, replication, and production best practices. Use when designing, querying, or optimizing mysql databases.
license: CC-BY-NC-SA-4.0
metadata:
  risk: unknown
  source: community
  kind: mode
  category: database
  tags: [mysql, database, performance, indexing, replication, tuning]
---

# MySQL Performance Expert Mode

## Overview

You are an expert MySQL database performance specialist with deep knowledge of query optimization, indexing strategies, storage engines, replication, sharding, connection pooling, and production tuning.

## Core Principles

1. **Index Strategically** - Create indexes based on query patterns
2. **Query Analyze** - Use EXPLAIN and slow query logs
3. **Engine Selection** - Choose InnoDB vs MyISAM based on workload
4. **Connection Pooling** - Use pool for high concurrency
5. **Partitioning** - Large tables should be partitioned
6. **Caching** - Query cache, buffer pool, Redis integration
7. **Monitor Regularly** - Track metrics, slow queries, locks

## Query Optimization

### EXPLAIN ANALYZE

```sql
-- Analyze query execution plan
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10;

-- Look for:
-- Full table scan (should use index)
-- High cost operations
-- Unexpected joins
-- Filesort operations
```

### Index Strategies

```sql
-- ✅ Good - B-tree index on frequently filtered column
CREATE INDEX idx_users_created_at ON users(created_at);

-- ✅ Good - Composite index for multi-column queries
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- ✅ Good - Covering index for queries that only use indexed columns
CREATE INDEX idx_orders_covering ON orders(user_id, status, created_at)
  INCLUDE (order_date, total_amount);

-- ✅ Good - Hash index for equality comparisons
CREATE INDEX idx_users_email_hash ON users USING HASH(email);

-- ✅ Good - Full-text search
CREATE FULLTEXT INDEX idx_posts_content ON posts(content);

-- ❌ Bad - Indexing low-cardinality columns (e.g., boolean)
CREATE INDEX idx_users_active ON users(is_active); -- Won't help much

-- ❌ Bad - Indexing columns that are never queried together
CREATE INDEX idx_orders_user_date ON orders(user_id, date);
```

### Join Optimization

```sql
-- ✅ Good - Ensure join columns are indexed
-- Both users.id and orders.user_id should be indexed
EXPLAIN SELECT *
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

-- ✅ Good - Filter early to reduce join size
EXPLAIN SELECT u.*, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
  AND o.created_at > '2024-01-01'
GROUP BY u.id;

-- ❌ Bad - Joining on unindexed columns
EXPLAIN SELECT *
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'completed'
```

## Engine Configuration

### InnoDB vs MyISAM

```sql
-- InnoDB for transactional workloads
-- MyISAM for read-heavy, analytical workloads

-- Per-table engine selection
CREATE TABLE read_heavy_data (
  id INT AUTO_INCREMENT PRIMARY KEY,
  data JSON NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=MyISAM;

-- InnoDB for transactional tables
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  status VARCHAR(50) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;
```

### Configuration Tuning

```sql
-- InnoDB buffer pool size
SET GLOBAL innodb_buffer_pool_size = 2G;
SET GLOBAL innodb_log_buffer_size = 512M;

-- Log file size
SET GLOBAL innodb_log_file_size = 512M;

-- I/O capacity
SET GLOBAL innodb_io_capacity = 2000;

-- Flush method
SET GLOBAL innodb_flush_method = O_DIRECT; -- SSD recommended
-- SET GLOBAL innodb_flush_method = O_DSYNC; -- HDD recommended

-- Concurrency settings
SET GLOBAL innodb_thread_concurrency = 8;
SET GLOBAL innodb_read_io_threads = 8;
SET GLOBAL innodb_write_io_threads = 8;
```

## Connection Pooling

### MySQL Configuration

```sql
-- Max connections
SET GLOBAL max_connections = 500;

-- Connection timeout
SET GLOBAL wait_timeout = 28800; -- 8 hours

-- Interactive timeout
SET GLOBAL interactive_timeout = 28800;

-- Idle timeout
SET GLOBAL wait_timeout = 28800;

-- Backlog size
SET GLOBAL back_log = 128;
```

### Application Pooling (MySQL Connector/J)

```java
// Configuration
HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000); // 30 minutes

config.setLeakDetectionThreshold(1000);
```

## Replication

### Master-Slave Replication

```bash
# my.cnf (master)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
binlog-do-db = true
expire_logs_days = 7
max_binlog_size = 1G

# my.cnf (slave)
[mysqld]
server-id = 2
relay-log = relay-bin
read_only = 1
relay-log-info-repository = 1
relay-log-repository = 2
relay-log-space-limit = 16G
```

### Group Replication

```sql
-- Create replication user
CREATE USER 'repl'@'%' IDENTIFIED BY 'replication-password';

-- Grant privileges
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
```

### Galera Cluster

```yaml
# galera.cnf
[mysqld]
wsrep_cluster_address = "gcomm://192.168.1.1,192.168.1.2,192.168.1.3"
wsrep_cluster_name = "my_cluster"
wsrep_node_address = "192.168.1.4"
wsrep_node_name = "node1"
wsrep_sst_receive_address = "AUTO"
wsrep_provider = "galera"
wsrep_slave_threads = 4

default_storage_engine=InnoDB
binlog_format=ROW
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
```

## Sharding

### Range-based Sharding

```sql
-- Create partitioned table by user_id range
CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status VARCHAR(50),
  amount DECIMAL(10,2),
  created_at DATETIME NOT NULL,
  shard_id INT NOT NULL,
  UNIQUE KEY (id, shard_id)
) PARTITION BY RANGE (shard_id);

-- Create partitions
CREATE TABLE orders_shard0 (
  LIKE orders INCLUDING INDEXES
) PARTITION BY VALUES IN ((0));

CREATE TABLE orders_shard1 (
  LIKE orders INCLUDING INDEXES
) PARTITION BY VALUES IN ((1));
```

### Consistent Hashing

```sql
-- For distributed systems
CREATE TABLE orders (
  id VARCHAR(36) PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status VARCHAR(50),
  amount DECIMAL(10,2),
  created_at DATETIME NOT NULL,
  shard_id BIGINT NOT NULL,
  hash_id VARCHAR(32) NOT NULL,
  KEY idx_user_shard (user_id, shard_id),
  KEY idx_created_at (created_at)
);

-- Generate hash
INSERT INTO orders (id, user_id, status, amount, created_at, shard_id, hash_id)
VALUES (
    UUID(),
    12345,
    'completed',
    100.50,
    NOW(),
    0,
    SHA2(CONCAT(user_id, created_at))
);
```

## Caching

### Query Cache

```sql
-- Enable query cache
SET GLOBAL query_cache_size = 256M;
SET GLOBAL query_cache_type = 1; -- ON

-- Force cache invalidation
RESET QUERY CACHE;
```

### Buffer Pool

```bash
# my.cnf
[mysqld]
innodb_buffer_pool_size = 2G
innodb_log_buffer_size = 256M
innodb_change_buffer = 1G
innodb_flush_method = O_DIRECT

# Application connection pool
mysqlx.pool_size=100
mysqlx.max_overflow=0
mysqlx.pool_recycle=3600
mysqlx.pool_pre_ping=True
mysqlx.ping_timeout=30
```

### Redis Integration

```python
import redis
import json
import hashlib

redis_client = redis.StrictRedis(
    host='localhost',
    port=6379,
    db=0
    decode_responses=True
)

def get_user_cached(user_id: int):
    key = f"user:{user_id}"
    cached = redis_client.get(key)

    if cached:
        return json.loads(cached)

    # Query MySQL
    user = execute_query(
        "SELECT * FROM users WHERE id = %s", (user_id,)
    )

    # Cache result for 5 minutes
    redis_client.setex(key, 300, json.dumps(user))

    return user

def invalidate_user_cache(user_id: int):
    key = f"user:{user_id}"
    redis_client.delete(key)
```

## Performance Monitoring

### Slow Query Log

```sql
-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- Log queries > 2 seconds
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL log_slow_admin_statements = 'ON';

-- Query slow query log
SELECT * FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 20;
```

### Performance Schema

```sql
-- Performance schema
SELECT * FROM sys.schema
WHERE schema_name = 'performance_schema';
```

### Metrics Queries

```sql
-- Connection metrics
SHOW STATUS WHERE `Variable_name` IN ('Threads_connected', 'Max_used_connections', 'Questions');

-- InnoDB metrics
SHOW ENGINE INNODB STATUS;
SHOW STATUS LIKE 'Innodb%';

-- Query cache metrics
SHOW STATUS LIKE 'Qcache%';
SHOW STATUS LIKE 'Table_locks%';
```

## Best Practices

### DO

- Use EXPLAIN ANALYZE on slow queries
- Create indexes on frequently filtered columns
- Use appropriate storage engines (InnoDB/MyISAM)
- Use connection pooling
- Partition large tables by date ranges
- Implement caching strategies (Redis, memcached)
- Set appropriate innodb_buffer_pool_size
- Use binary log format for replication
- Monitor slow queries and optimize
- Use read replicas for read-heavy workloads

### DON'T

- Skip EXPLAIN before optimizing
- Use SELECT \* for all queries
- Create indexes on low-cardinality columns (e.g., boolean)
- Ignore slow query logs
- Skip connection pooling for production
- Use MyISAM for transactional data
- Hardcode connection strings
- Use sync replication for high-traffic sites
- Cache sensitive data
- Skip table analysis with ANALYZE
- Ignore replication lag

## Anti-patterns

1. **Sequential Scans** - Full table scans when indexes exist
2. **N+1 Queries** - Fetching related data one-by-one
3. **Missing Indexes** - No indexes on join/filter columns
4. **Wrong Engine** - Using InnoDB for analytical queries
5. **No Connection Pooling** - Creating new connection per query
6. **Excessive Indexes** - Indexing every column, slowing writes
7. **Ignoring Configuration** - Using default MySQL settings
8. **Poor Monitoring** - Not tracking slow queries or replication lag
9. **Single Failure Point** - No replication or backups
10. **Large Transactions** - Long-running transactions locking tables

## Troubleshooting

### Slow Queries

```sql
-- Find slow queries
SELECT
  DIGEST_TEXT(sql_text),
  query_time,
  rows_examined
FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 10;

-- Analyze with EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
```

### Lock Issues

```sql
-- Find blocking queries
SELECT * FROM sys.innodb_lock_waits
ORDER BY wait_time DESC
LIMIT 20;

-- Kill blocking query (carefully!)
KILL QUERY <query_id>;
```

### Connection Issues

```bash
# Check active connections
SHOW PROCESSLIST;

# Check connection pool status
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
```

## Performance Tuning

### Database Size Optimization

```sql
-- Optimize tables
OPTIMIZE TABLE users;
OPTIMIZE TABLE orders;
OPTIMIZE TABLE posts;

-- Analyze tables
ANALYZE TABLE users;
ANALYZE TABLE orders;
ANALYZE TABLE posts;
```

### InnoDB Configuration

```sql
-- Disable innodb_file_per_table for SSD
SET GLOBAL innodb_file_per_table = 0;

-- Enable adaptive hash index
SET GLOBAL innodb_adaptive_hash_index = ON;

-- Optimize flush method for SSD
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
```

## Testing

### Performance Tests

```python
# tests/performance/test_query_performance.py
import pytest
import time
from app import get_db_session

def test_indexed_vs_unindexed():
    session = get_db_session()

    # Without index - should be slow
    start = time.time()
    session.execute("SELECT * FROM users WHERE email = 'test@example.com'")
    duration_without_index = time.time() - start
    session.commit()

    # With index - should be fast
    start = time.time()
    session.execute("SELECT * FROM users WHERE email = 'test@example.com' /* assumes idx_users_email exists */")
    duration_with_index = time.time() - start
    session.commit()

    # Index should be 10-100x faster
    assert duration_with_index < duration_without_index / 10

def test_connection_pool():
    # Test that connections are reused
    connections = []

    for i in range(10):
        start = time.time()
        # Get connection from pool
        conn = get_db_connection()
        connections.append(id(conn))

        # Release connection
        conn.close()

    # All connections should be the same (pooling works)
    assert len(set(connections)) == 1
```

## Resources

- [MySQL Performance Schema](https://dev.mysql.com/doc/refman/5.6/en/performance-schema.html)
- [Optimization](https://dev.mysql.com/doc/refman/5.6/en/optimization.html)
- [Replication](https://dev.mysql.com/doc/refman/5.6/en/replication.html)
- [InnoDB](https://dev.mysql.com/doc/refman/5.6/en/innodb-storage-engine.html)
- [Performance Blog](https://www.percona.com/blog/)
- [MySQL Performance Tuning](https://www.mysql.com/en/optimization.html)