MySQL 8.0 Performance Tuning: From 1K to 10K QPS

Our MySQL 5.7 database was hitting limits. Slow queries, DDL downtime, 1K QPS max.

Upgraded to MySQL 8.0 and optimized. Query performance 10x, instant DDL, 10K QPS. Here’s how.

MySQL 8.0 New Features

Key Improvements:

Window functions
CTEs (Common Table Expressions)
Invisible indexes
Instant DDL
Descending indexes
JSON improvements

Window Functions

Before (MySQL 5.7):

-- Complex self-join for ranking
SELECT u1.user_id, u1.score,
  (SELECT COUNT(*) FROM users u2 WHERE u2.score > u1.score) + 1 AS rank
FROM users u1
ORDER BY score DESC;

-- Slow: 5s for 100K users

After (MySQL 8.0):

-- Window function
SELECT user_id, score,
  RANK() OVER (ORDER BY score DESC) AS rank
FROM users;

-- Fast: 500ms for 100K users (10x faster)

Common Table Expressions (CTEs)

-- Recursive CTE for hierarchical data
WITH RECURSIVE employee_hierarchy AS (
  -- Base case
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- Recursive case
  SELECT e.id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy
ORDER BY level, name;

-- Complex analytics with multiple CTEs
WITH 
  monthly_sales AS (
    SELECT 
      DATE_FORMAT(order_date, '%Y-%m') AS month,
      SUM(total) AS sales
    FROM orders
    GROUP BY month
  ),
  sales_growth AS (
    SELECT 
      month,
      sales,
      LAG(sales) OVER (ORDER BY month) AS prev_month_sales,
      (sales - LAG(sales) OVER (ORDER BY month)) / LAG(sales) OVER (ORDER BY month) * 100 AS growth_pct
    FROM monthly_sales
  )
SELECT * FROM sales_growth
WHERE growth_pct > 10;

Invisible Indexes

-- Test index without dropping it
ALTER TABLE users ALTER INDEX idx_email INVISIBLE;

-- Check query performance
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- If performance is bad, make it visible again
ALTER TABLE users ALTER INDEX idx_email VISIBLE;

-- If performance is fine, drop it
DROP INDEX idx_email ON users;

Instant DDL

-- Add column instantly (no table copy)
ALTER TABLE users ADD COLUMN last_login DATETIME, ALGORITHM=INSTANT;

-- Before MySQL 8.0: 30 minutes for 10M rows
-- MySQL 8.0: <1 second

Descending Indexes

-- Create descending index
CREATE INDEX idx_created_desc ON posts (created_at DESC);

-- Query uses index efficiently
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10;

-- Before: Filesort
-- After: Using index

JSON Improvements

-- JSON table function
SELECT jt.*
FROM users,
JSON_TABLE(
  preferences,
  '$[*]' COLUMNS(
    category VARCHAR(50) PATH '$.category',
    value VARCHAR(100) PATH '$.value'
  )
) AS jt;

-- JSON aggregation
SELECT 
  user_id,
  JSON_ARRAYAGG(
    JSON_OBJECT('product', product_id, 'quantity', quantity)
  ) AS cart_items
FROM cart
GROUP BY user_id;

Performance Optimization

-- Analyze table
ANALYZE TABLE users;

-- Optimize table
OPTIMIZE TABLE users;

-- Check index usage
SELECT 
  table_name,
  index_name,
  cardinality,
  seq_in_index
FROM information_schema.statistics
WHERE table_schema = 'mydb'
ORDER BY table_name, index_name, seq_in_index;

-- Find unused indexes
SELECT 
  object_schema,
  object_name,
  index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
ORDER BY object_schema, object_name;

Query Optimization

-- Use EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2020-01-01'
GROUP BY u.id
HAVING order_count > 5;

-- Optimize with covering index
CREATE INDEX idx_user_orders ON orders (user_id, id);
CREATE INDEX idx_user_created ON users (created_at, id, name);

Configuration Tuning

# my.cnf
[mysqld]
# InnoDB settings
innodb_buffer_pool_size = 16G
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Query cache (disabled in 8.0)
# query_cache_type = 0

# Connection settings
max_connections = 500
max_connect_errors = 1000000

# Performance schema
performance_schema = ON
performance_schema_instrument = 'wait/%=ON'

# Slow query log
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1

Monitoring

import mysql.connector
from prometheus_client import Gauge, Counter

# Metrics
db_connections = Gauge('mysql_connections', 'Active connections')
db_queries = Counter('mysql_queries_total', 'Total queries')
db_slow_queries = Counter('mysql_slow_queries_total', 'Slow queries')

def monitor_mysql():
    """Monitor MySQL metrics."""
    conn = mysql.connector.connect(
        host='localhost',
        user='monitor',
        password='password'
    )
    cursor = conn.cursor(dictionary=True)
    
    # Connections
    cursor.execute("SHOW STATUS LIKE 'Threads_connected'")
    result = cursor.fetchone()
    db_connections.set(int(result['Value']))
    
    # Queries
    cursor.execute("SHOW STATUS LIKE 'Questions'")
    result = cursor.fetchone()
    db_queries.inc(int(result['Value']))
    
    # Slow queries
    cursor.execute("SHOW STATUS LIKE 'Slow_queries'")
    result = cursor.fetchone()
    db_slow_queries.inc(int(result['Value']))
    
    cursor.close()
    conn.close()

Results

Performance:

Metric	MySQL 5.7	MySQL 8.0	Improvement
QPS	1K	10K	10x
Window queries	5s	500ms	10x
DDL time	30min	<1s	1800x
Complex queries	10s	1s	10x

Features:

Window functions: ✅
CTEs: ✅
Instant DDL: ✅
Invisible indexes: ✅

Resource Usage:

CPU: -30%
Memory: Same
Disk I/O: -40%

Lessons Learned

Window functions powerful: 10x faster
Instant DDL game-changer: No downtime
CTEs improve readability: Easier to maintain
Invisible indexes useful: Safe testing
Upgrade worth it: Massive gains

Conclusion

MySQL 8.0 transformed our database performance. QPS 1K → 10K, instant DDL, 10x query performance.

Key takeaways:

QPS: 1K → 10K (10x)
Window queries: 5s → 500ms (10x)
DDL time: 30min → <1s (1800x)
CPU usage: -30%
Disk I/O: -40%

Upgrade to MySQL 8.0. Performance gains are real.

Table of Contents