strace's blog

Troubleshooting MariaDB and MySQL Performance Problems

Debugging

Database performance problems are never just "the database is slow." Something is causing the slowness — a bad query plan, a missing index, a lock contention, or a resource bottleneck.

Here's the diagnostic workflow I use for MariaDB and MySQL performance issues.

1. Is It Really the Database?

Before blaming the database, rule out the application layer:

# Check database responsiveness
mysqladmin -u root ping
mysqladmin -u root status

# Quick latency check
mysql -e "SELECT 1;"

# Time a query from the application
# vs. from the mysql CLI — if the CLI is fast but the app is slow,
# the problem is in the application (N+1 queries, connection pooling, etc.)

Golden rule: If SELECT 1 takes > 1ms, the database server itself has a problem (overloaded, resource contention, or network latency). If it's fast but queries are slow, it's a query/index/schema issue.

2. Enable the Slow Query Log

This is always step one:

-- Check current configuration
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';

-- Enable slow query log (runtime)
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- log queries taking > 1 second
SET GLOBAL log_queries_not_using_indexes = ON;

# Analyze the slow query log
pt-query-digest /var/lib/mysql/slow.log
# Or manually:
mysqldumpslow /var/lib/mysql/slow.log

What to look for in the digest:

  • Queries appearing most frequently (high count)
  • Queries with the highest total execution time
  • Queries not using indexes (log_queries_not_using_indexes catches these)

3. EXPLAIN — Your Best Friend

For any slow query, EXPLAIN tells you how MariaDB executes it:

EXPLAIN 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 > '2025-01-01'
GROUP BY u.id
ORDER BY order_count DESC G

Key columns to read:

  • type — ALL (full table scan) = bad, ref/eq_ref/const = good
  • rows — estimated rows examined; huge numbers mean inefficient access
  • Extra — Using filesort (sort without index), Using temporary (temp table for GROUP BY / DISTINCT) are red flags
  • key — which index is being used; NULL means no usable index

Corollary: If rows × number of queries per second exceeds your disk I/O capacity, that's your bottleneck.

4. Index Analysis

-- Check index cardinality
SHOW INDEX FROM orders;

-- Find unused indexes (MariaDB 10.5+)
SELECT * FROM information_schema.INDEX_STATISTICS;

-- Check index usage in the slow query log
-- Missing indexes often manifest as:
--   - type: ALL or type: index
--   - rows > 10000 for simple lookups

When to add an index:

  • WHERE clause columns not indexed
  • JOIN columns not indexed (foreign key columns must always be indexed)
  • ORDER BY / GROUP BY columns not covered
  • Column with high cardinality (many unique values) benefits most

When NOT to add an index:

  • Tables with frequent writes and low query volume (index maintenance overhead)
  • Low-cardinality columns (boolean, enum with 2-3 values) — the index isn't selective enough
  • Tiny tables (< 1000 rows) — a full table scan is cheaper

5. Lock Contention and Deadlocks

-- Check current locks
SHOW ENGINE INNODB STATUS G

-- Check running transactions
SELECT * FROM information_schema.INNODB_TRX G

-- Check locks held
SELECT * FROM performance_schema.data_locks;

-- Check lock waits
SELECT * FROM performance_schema.data_lock_waits;

MariaDB/MySQL deadlock info: In SHOW ENGINE INNODB STATUS, look for LATEST DETECTED DEADLOCK section. It tells you:

  1. The exact queries involved
  2. Which transaction was rolled back
  3. The lock sequence that caused the cycle

Common deadlock patterns:

  • Two tables, reverse order — transaction A locks table1→table2, B locks table2→table1. Fix: always access tables in the same order.
  • Range scan + gap lock — one transaction runs a range query, another inserts into the gap. Fix: lower isolation level or use NOWAIT.
  • Diamond dependency — three or more transactions forming a cycle.

6. performance_schema Deep Dive

-- Top queries by total latency
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- Top waits (I/O, locks, etc.)
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE SUM_TIMER_WAIT > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- File I/O by file
SELECT FILE_NAME, COUNT_READ, SUM_NUMBER_OF_BYTES_READ
FROM performance_schema.file_summary_by_instance
ORDER BY SUM_NUMBER_OF_BYTES_READ DESC
LIMIT 10;

7. Configuration Triage

-- Check key buffer pool usage
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_%';

-- If free pages < 10%, increase innodb_buffer_pool_size

-- Check query cache (deprecated in MySQL 8, still in MariaDB 10.x)
SHOW STATUS LIKE 'Qcache_%';

-- Thread pool
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';

MariaDB-specific configs to tune:

  • innodb_buffer_pool_size — should be 70-80% of available RAM for a dedicated DB server
  • innodb_log_file_size — too small causes frequent checkpoint flushing
  • max_connections — too high causes context switching thrash
  • thread_cache_size — should match expected concurrent connections

8. System Memory Monitoring

MariaDB 10.x doesn't have MySQL 8's AMM. Monitor memory:

# Per-process memory
ps -eo pid,rss,cmd | grep mariadb

# Watch for OOM kills
dmesg | grep -i 'killed process' | tail -5

Diagnostic Flow

Slow queries?
├── Run slow query log (1 second threshold)
├── EXPLAIN each slow query
│   ├── type=ALL → add index or rewrite query
│   ├── Using filesort → add covering index
│   └── rows huge but query fast → it's a cron/rare query, OK
├── Check lock contention
│   ├── SHOW ENGINE INNODB STATUS
│   └── SHOW PROCESSLIST
├── Check system resources
│   ├── iostat -x 1 (disk I/O)
│   ├── free -h (memory / swap)
│   └── top (CPU)
└── Tune config (buffer pool, log size, thread cache)

Remember: the slow query log tells you WHAT is slow. EXPLAIN tells you WHY. System metrics tell you whether it's a database problem or a hardware problem. Always check all three.