Home / Database / Fix MySQL Database Slow Performance

Database

Fix MySQL Database Slow Performance

Fix MySQL database slow performance issues. Diagnose slow queries, optimize indexes, configure buffer pool, and improve MySQL performance.

Published: Apr 27, 20269 min readBy FixWikiHub Editorial Team

Abstract illustration for a troubleshooting knowledge base category.

# Fix MySQL Database Slow Performance

Your MySQL database is running slow. Queries take too long, applications timeout, and users complain about performance. Let's diagnose and fix MySQL performance issues systematically.

Introduction

This article covers troubleshooting steps and solutions for Fix MySQL Database Slow Performance. The error typically occurs in production environments and can cause service disruptions if not addressed promptly.

Symptoms

Common error messages include:

```bash # Check MySQL status mysqladmin -u root -p status

# Check process list mysql -e "SHOW PROCESSLIST"

# Check full process list mysql -e "SHOW FULL PROCESSLIST" ```

```bash # Check InnoDB status mysql -e "SHOW ENGINE INNODB STATUS\G"

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

# Check slow queries mysql -e "SHOW STATUS LIKE 'Slow_queries'" ```

```bash # Check CPU top -p $(pgrep mysql)

# Check memory free -h

# Check disk I/O iostat -x 1 10

# Check disk space df -h /var/lib/mysql ```

Common Causes

Configuration misconfiguration
Missing or incorrect credentials
Network connectivity issues
Version compatibility problems
Resource exhaustion or limits
Permission or access denied

Step-by-Step Fix

Check MySQL status:

```bash # Check MySQL status mysqladmin -u root -p status

# Check process list mysql -e "SHOW PROCESSLIST"

# Check full process list mysql -e "SHOW FULL PROCESSLIST" ```

Check performance metrics:

```bash # Check InnoDB status mysql -e "SHOW ENGINE INNODB STATUS\G"

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

# Check slow queries mysql -e "SHOW STATUS LIKE 'Slow_queries'" ```

Check system resources:

```bash # Check CPU top -p $(pgrep mysql)

# Check memory free -h

# Check disk I/O iostat -x 1 10

# Check disk space df -h /var/lib/mysql ```

Common Causes and Solutions

Cause 1: Missing Indexes

bash

# Slow queries due to full table scans

Diagnosis:

```bash # Enable slow query log mysql -e "SET GLOBAL slow_query_log = 'ON'" mysql -e "SET GLOBAL long_query_time = 2"

# Check slow queries mysql -e "SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10"

# Analyze query with EXPLAIN mysql -e "EXPLAIN SELECT * FROM users WHERE email = 'user@example.com'" ```

Solution:

```sql -- Check existing indexes SHOW INDEX FROM users;

-- Add index for frequently queried columns CREATE INDEX idx_users_email ON users(email);

-- Add composite index for multiple columns CREATE INDEX idx_users_status_created ON users(status, created_at);

-- Analyze table to update statistics ANALYZE TABLE users;

-- Force index usage if needed SELECT * FROM users FORCE INDEX (idx_users_email) WHERE email = 'user@example.com'; ```

Cause 2: Inefficient Queries

bash

# Queries using filesort, temporary tables

Diagnosis:

```sql -- Use EXPLAIN to analyze EXPLAIN SELECT u.*, o.* FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 'active';

-- Check for filesort and temporary tables EXPLAIN SELECT * FROM users ORDER BY created_at DESC LIMIT 1000; ```

Solution:

```sql -- Add index for ORDER BY CREATE INDEX idx_users_created ON users(created_at);

-- Optimize JOIN queries -- Ensure join columns are indexed CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Avoid SELECT * SELECT id, name, email FROM users WHERE status = 'active';

-- Use LIMIT SELECT * FROM orders WHERE user_id = 123 LIMIT 100;

-- Optimize subqueries -- Bad: Subquery in WHERE SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 'pending');

-- Better: JOIN SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 'pending'; ```

Cause 3: Buffer Pool Too Small

bash

# Low buffer pool hit rate

Diagnosis:

```sql -- Check buffer pool hit rate SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

-- Calculate hit rate -- Hit rate = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) SELECT (1 - (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') / (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')) * 100 AS buffer_pool_hit_rate; ```

Solution:

```bash # Increase buffer pool size (should be 70-80% of available RAM) # In /etc/mysql/mysql.conf.d/mysqld.cnf [mysqld] innodb_buffer_pool_size = 4G innodb_buffer_pool_instances = 4

# Restart MySQL systemctl restart mysql ```

Cause 4: Connection Pool Issues

bash

# Too many connections, connection exhaustion

Diagnosis:

```sql -- Check current connections SHOW STATUS LIKE 'Threads_connected'; SHOW STATUS LIKE 'Max_used_connections'; SHOW VARIABLES LIKE 'max_connections';

-- Check connection details SELECT user, host, db, command, time, state FROM information_schema.processlist; ```

Solution:

```sql -- Increase max connections SET GLOBAL max_connections = 500;

-- Or in my.cnf [mysqld] max_connections = 500 wait_timeout = 600 interactive_timeout = 600

-- Implement connection pooling in application ```

```python # Python connection pooling with SQLAlchemy from sqlalchemy import create_engine

engine = create_engine( 'mysql+pymysql://user:pass@host/db', pool_size=10, max_overflow=20, pool_recycle=3600 ) ```

Cause 5: Table Locking Issues

bash

# Lock wait timeout exceeded

Diagnosis:

```sql -- Check locks SELECT * FROM information_schema.INNODB_LOCKS; SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- Check transactions SELECT * FROM information_schema.INNODB_TRX;

-- Check lock status SHOW ENGINE INNODB STATUS\G ```

Solution:

```sql -- Kill long-running transactions SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE time > 60 AND command = 'Sleep';

-- Optimize queries to reduce lock time -- Use smaller transactions START TRANSACTION; UPDATE users SET status = 'active' WHERE id = 1; COMMIT;

-- Use optimistic locking UPDATE products SET stock = stock - 1, version = version + 1 WHERE id = 1 AND version = 5; ```

Cause 6: Disk I/O Bottleneck

bash

# High disk I/O wait

Diagnosis:

```bash # Check disk I/O iostat -x 1

# Check MySQL disk usage mysql -e "SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM information_schema.tables GROUP BY table_schema" ```

Solution:

```bash # In my.cnf [mysqld] # Use faster storage innodb_flush_method = O_DIRECT

# Increase I/O capacity innodb_io_capacity = 2000 innodb_io_capacity_max = 4000

# Reduce flush frequency innodb_flush_log_at_trx_commit = 2 # Less safe but faster

# Use SSD # Move MySQL data to SSD ```

Cause 7: Memory Issues

bash

# Out of memory, swapping

Diagnosis:

```bash # Check memory usage free -h cat /proc/meminfo | grep -i mysql

# Check swap usage swapon -s ```

Solution:

```bash # In my.cnf [mysqld] # Total memory = buffer_pool + key_buffer + max_connections * (read_buffer + sort_buffer) innodb_buffer_pool_size = 4G key_buffer_size = 64M read_buffer_size = 1M sort_buffer_size = 1M max_connections = 200

# Disable swap for MySQL (if enough RAM) echo 0 > /proc/sys/vm/swappiness ```

Cause 8: Slow Replication

bash

# Replication lag

Diagnosis:

```sql -- On replica SHOW SLAVE STATUS\G

-- Check seconds behind master SELECT Seconds_Behind_Master FROM information_schema.PROCESSLIST WHERE COMMAND = 'Slave_SQL'; ```

Solution:

```sql -- Enable parallel replication STOP SLAVE; SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK'; SET GLOBAL slave_parallel_workers = 4; START SLAVE;

-- Optimize binary log -- On master SET GLOBAL binlog_format = 'ROW'; SET GLOBAL sync_binlog = 0; # Faster but less safe ```

Performance Optimization Configuration

```ini # /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld] # Basic settings max_connections = 500 wait_timeout = 600 interactive_timeout = 600

# InnoDB settings innodb_buffer_pool_size = 4G innodb_buffer_pool_instances = 4 innodb_log_file_size = 512M innodb_log_buffer_size = 64M innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT innodb_file_per_table = 1

# Query cache (MySQL 5.7 and earlier) query_cache_type = 0 query_cache_size = 0

# Slow query log slow_query_log = 1 long_query_time = 2 slow_query_log_file = /var/log/mysql/slow.log

# Binary log log_bin = mysql-bin binlog_format = ROW expire_logs_days = 7

# I/O settings innodb_io_capacity = 2000 innodb_io_capacity_max = 4000

# Thread settings thread_cache_size = 100 innodb_thread_concurrency = 0 ```

Verification

```bash # Test query performance mysql -e "SELECT BENCHMARK(1000000, 1+1)"

# Check buffer pool hit rate mysql -e "SHOW STATUS LIKE 'Innodb_buffer_pool_read%'"

# Run mysqltuner mysqltuner

# Run pt-query-digest pt-query-digest /var/log/mysql/slow.log

# Benchmark with sysbench sysbench oltp_read_write --tables=10 --table-size=100000 --threads=4 --mysql-host=localhost --mysql-user=root --mysql-password=password run ```

Monitoring Queries

```sql -- Top 10 slow queries SELECT query_time, sql_text FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

-- Tables without indexes SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema') AND table_name NOT IN (SELECT table_name FROM information_schema.statistics);

-- Unused indexes SELECT * FROM sys.schema_unused_indexes;

-- Redundant indexes SELECT * FROM sys.schema_redundant_indexes;

-- Table sizes SELECT table_schema, table_name, ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb FROM information_schema.tables WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema') ORDER BY size_mb DESC LIMIT 20; ```

Prevention

1.[ ] Check slow query log for problematic queries
2.[ ] Analyze queries with EXPLAIN
3.[ ] Add appropriate indexes
4.[ ] Configure buffer pool size (70-80% of RAM)
5.[ ] Check connection pool settings
6.[ ] Monitor disk I/O
7.[ ] Check for table locks
8.[ ] Review replication lag (if applicable)
9.[ ] Update table statistics with ANALYZE TABLE
10.[ ] Use monitoring tools (mysqltuner, pt-query-digest)

[Database troubleshooting: Fix Backup Exclusive Lock Table Production Writes ](backup-exclusive-lock-table-production-writes)
[Fix Connection Pool Leak Application Not Closing Issue in Database](connection-pool-leak-application-not-closing)
[Fix Connection Reset Idle Timeout Firewall Issue in Database](connection-reset-idle-timeout-firewall)
[Fix Connection Reset Idle Timeout Serverless Database Issue in Database](connection-reset-idle-timeout-serverless-database)
[Fix Connection String Encoding Special Characters Issue in Database](connection-string-encoding-special-characters)

Was this guide helpful?

Related search paths

People also search for

If the symptom is close but not identical, these search paths usually surface the right neighboring fixes faster than scrolling the full archive.

MySQL Database Slow Performance MySQL Database Slow Performance Database MySQL Database Slow Performance troubleshooting MySQL Database Slow Performance fix Fix MySQL database slow performance issues Database Fix MySQL database slow performance issues

Explore Related Topics

Browse Guides from Other Categories

Discover troubleshooting guides from related categories to expand your knowledge.

FAQ

Database Troubleshooting FAQs

Common questions about troubleshooting and preventing similar issues

How do I know if this database-errors troubleshooting guide applies to my situation?

This guide is designed for database-errors issues. If you're experiencing similar symptoms described in the article, follow the step-by-step instructions. Start with the most common causes and work through the diagnostic process.

Is it safe to follow these database-errors troubleshooting steps?

Yes, all steps are designed to be safe and non-destructive. We recommend creating backups before making significant changes and testing each step before proceeding to the next.

How long does it typically take to resolve this type of database-errors issue?

Most database-errors issues can be resolved within 30 minutes to 2 hours, depending on the complexity and root cause. Follow the troubleshooting flow to identify and fix the problem efficiently.

How can I prevent this database-errors issue from happening again?

Regular maintenance, monitoring, and following best practices for database-errors configuration can help prevent recurrence. Consider implementing automated checks and alerts for early detection.

Written by

FixWikiHub Editorial Team

Our editorial team consists of experienced DevOps engineers, systems administrators, and cloud architects with hands-on experience in production environments across AWS, Azure, GCP, and on-premises infrastructure.

Every guide undergoes technical review for accuracy and is updated when software versions, commands, or best practices change.

Last updated: Apr 27, 2026

About our team

Important Notice

Disclaimer & Safety Guidelines

The troubleshooting steps in this guide are provided for educational and informational purposes. Before applying any changes to production systems:

Test in a staging environment first — Always verify commands and configurations in a non-production environment before deploying to live systems.
Create backups — Ensure you have current backups of databases, configurations, and critical files before making changes.
Understand the impact — Review how each step may affect your specific environment, dependencies, and users.
Consult official documentation — This guide supplements, but does not replace, official vendor documentation and best practices.

FixWikiHub is not responsible for any damages arising from the use of this content. See our Terms of Use for more information.

Resources

Official Documentation & Further Reading

For authoritative information, consult the official documentation for the technologies discussed in this guide. Our troubleshooting content supplements, but does not replace, vendor documentation.

AWS Documentation — Official Amazon Web Services guides and API references
Kubernetes Documentation — Official Kubernetes documentation
Nginx Documentation — Official Nginx web server documentation
Apache Documentation — Official Apache HTTP Server documentation
Docker Documentation — Official Docker container documentation

Fix MySQL Database Slow Performance

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Common Causes and Solutions

Cause 1: Missing Indexes

Cause 2: Inefficient Queries

Cause 3: Buffer Pool Too Small

Cause 4: Connection Pool Issues

Cause 5: Table Locking Issues

Cause 6: Disk I/O Bottleneck

Cause 7: Memory Issues

Cause 8: Slow Replication

Performance Optimization Configuration

Verification

Monitoring Queries

Prevention

People also search for

Browse Guides from Other Categories

WordPress

SSL

DNS

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading

Fix MySQL Database Slow Performance

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Common Causes and Solutions

Cause 1: Missing Indexes

Cause 2: Inefficient Queries

Cause 3: Buffer Pool Too Small

Cause 4: Connection Pool Issues

Cause 5: Table Locking Issues

Cause 6: Disk I/O Bottleneck

Cause 7: Memory Issues

Cause 8: Slow Replication

Performance Optimization Configuration

Verification

Monitoring Queries

Prevention

Related Articles

People also search for

Share this guide

More Database Troubleshooting Guides

Browse Guides from Other Categories

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading