Monitor MySQL Slow Queries with the Slow Query Log

Monitor MySQL Slow Queries with the Slow Query Log

Monitor MySQL slow queries to identify performance bottlenecks and optimize database operations. This guide shows you how to track slow query count, execution times, and set up automated monitoring with Zuzia.app.

For comprehensive database monitoring, see Database Performance Monitoring. For troubleshooting database issues, see Database Connection Timeout Errors.

Why Monitor MySQL Slow Queries

Slow queries can cause application timeouts, poor user experience, and database performance degradation. Monitoring slow queries helps you identify performance bottlenecks, optimize database operations, and prevent issues before they impact users.

Method 1: Enable MySQL Slow Query Log

Enable Slow Query Logging

# Connect to MySQL
mysql -u root -p

# Enable slow query log
SET GLOBAL slow_query_log = 'ON';

# Set slow query threshold (queries taking longer than 2 seconds)
SET GLOBAL long_query_time = 2;

# Verify slow query log is enabled
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

Check Slow Query Log Location

# Find slow query log file location
mysql -u root -p -e "SHOW VARIABLES LIKE 'slow_query_log_file';"

# View slow query log
sudo tail -f /var/log/mysql/slow-query.log

Method 2: Monitor Slow Queries from MySQL

Check Slow Query Count

# Check total slow queries
mysql -u root -p -e "SHOW STATUS LIKE 'Slow_queries';"

# Check slow queries per hour
mysql -u root -p -e "SHOW STATUS LIKE 'Slow_queries';" | grep -o '[0-9]*'

View Recent Slow Queries

# View slow queries from slow log table (if enabled)
mysql -u root -p -e "SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;"

Analyze Slow Query Patterns

# Count slow queries by user
mysql -u root -p -e "SELECT user_host, COUNT(*) as slow_count FROM mysql.slow_log GROUP BY user_host ORDER BY slow_count DESC;"

# Find most frequent slow queries
mysql -u root -p -e "SELECT sql_text, COUNT(*) as count FROM mysql.slow_log GROUP BY sql_text ORDER BY count DESC LIMIT 10;"

Method 3: Automated Slow Query Monitoring with Zuzia.app

Manual checks work while you are looking. To keep running them without you, Zuzia uses its agent and scheduled tasks.

How it works in Zuzia

• Install the Zuzia agent on the server with one command. CPU, RAM, disk, load and network metrics start reporting on their own, with alerts on a threshold, on drift from the server's normal, or on a disk fill-up forecast.
• Save the commands from this guide as a scheduled task and choose how often it runs, from every minute to once a year. The agent runs it on every host you pick.
• If a command fails, you get an alert right away by email, Slack, Discord, push (through your Pushover account) or SMS (through your Vonage account).
• The output of every run is kept, so you can look back at what changed. On paid plans, AI can read the output and alert you only when it spots a problem.

Setting it up

1. Add the server in the Zuzia dashboard and install the agent.
2. Create a scheduled task with the check commands below, then choose the hosts and how often it runs.
3. Choose your notification channels. Turn on maintenance mode during planned work so it doesn't trigger alerts.

Best Practices for Slow Query Monitoring

1. Set Appropriate Thresholds

• Set long_query_time based on your application requirements
• Typical values: 1-2 seconds for web applications
• Lower thresholds (0.5 seconds) for high-performance applications

2. Review Slow Queries Regularly

• Review slow query log weekly
• Identify patterns in slow queries
• Optimize frequently slow queries
• Add indexes where needed

3. Monitor Slow Query Trends

• Track slow query count over time
• Identify peak times for slow queries
• Correlate slow queries with application activity
• Plan optimizations based on trends

4. Optimize Slow Queries

• Add indexes to frequently queried columns
• Rewrite inefficient queries
• Use query caching where appropriate
• Optimize database schema if needed

Troubleshooting High Slow Query Count

Step 1: Identify Problematic Queries

# View recent slow queries
sudo tail -100 /var/log/mysql/slow-query.log

# Find most frequent slow queries
mysql -u root -p -e "SELECT sql_text, COUNT(*) as count FROM mysql.slow_log GROUP BY sql_text ORDER BY count DESC LIMIT 10;"

Step 2: Analyze Query Performance

# Explain slow query execution plan
mysql -u root -p -e "EXPLAIN SELECT ... FROM ... WHERE ...;"

# Check table indexes
mysql -u root -p -e "SHOW INDEXES FROM table_name;"

Step 3: Optimize Queries

• Add missing indexes
• Rewrite inefficient queries
• Use appropriate JOIN types
• Limit result sets when possible

FAQ: Common Questions About MySQL Slow Query Monitoring

What is considered a slow query?

Slow queries depend on your application requirements. Generally, queries taking longer than 1-2 seconds are considered slow. Set long_query_time based on your performance requirements.

How often should I check slow queries?

For production databases, continuous automated monitoring is essential. Zuzia.app can check slow query count every few minutes, alerting you when slow queries increase significantly.

Can slow queries impact server performance?

Yes, slow queries can consume excessive CPU and memory, block other queries, and cause database performance degradation. Monitor and optimize slow queries proactively.

How do I optimize slow queries?

Optimize slow queries by adding indexes to frequently queried columns, rewriting inefficient queries, using query caching, and optimizing database schema based on query patterns.

Related guides, recipes, and problems

• Related guides

  • guides--monitoring-database-performance-mysql-postgresql.md
  • guides--server-performance-monitoring-best-practices.md

• Related recipes

  • recipes--check-postgresql-active-connections.md
  • recipes--monitor-database-connection-pool-usage.md
  • recipes--monitor-mysql-database-size-growth.md

• Related problems

  • problems--database-connection-timeout-errors.md
  • problems--server-slow-performance-issues.md