How to Fix MySQL Slow Queries on DirectAdmin Servers (Complete 2026 Guide)

Log in to your server via SSH and connect to MySQL as root. Run the following commands to see whether slow query logging is already active:

mysql -u root -p
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';

If slow_query_log returns OFF, the log is disabled and you won't have any historical data to analyse. The long_query_time variable defines the threshold in seconds — queries exceeding this value are written to the log. A common starting value is 1 (one second).

On DirectAdmin servers, the MySQL configuration file is typically located at /etc/my.cnf or /etc/mysql/my.cnf. Open the file and add or update the following block under the [mysqld] section:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-queries.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100

The option log_queries_not_using_indexes = 1 is especially valuable — it catches queries that run fast on small tables today but will destroy performance as data grows. The min_examined_row_limit = 100 prevents trivial single-row lookups from flooding the log.

Make sure the log directory exists and has correct ownership, then restart MySQL:

mkdir -p /var/log/mysql
chown mysql:mysql /var/log/mysql
service mysqld restart   # or: systemctl restart mysqld

Without restarting MySQL you can also enable the slow query log dynamically for a running session:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-queries.log';

Then confirm the status:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW STATUS LIKE 'Slow_queries';

The SHOW STATUS LIKE 'Slow_queries' counter tells you how many slow queries have been recorded since the last MySQL restart. If the number climbs quickly, you have an active performance problem.

To see what MySQL is doing at this exact moment:

SHOW FULL PROCESSLIST;

Look for rows with a high Time value or a State of Locked, Copying to tmp table, or Sorting result — these are your immediate culprits. You can kill a runaway query with:

KILL QUERY <process_id>;

MySQL ships with mysqldumpslow, a built-in utility that groups similar queries and reports totals. Run it against your slow query log:

mysqldumpslow -s t -t 20 /var/log/mysql/slow-queries.log

Flags explained: -s t sorts by total query time (use -s c to sort by count, -s l for lock time); -t 20 shows the top 20 queries. The output replaces variable values with N and S placeholders so identical queries with different parameters are grouped together.

pt-query-digest from Percona Toolkit provides far richer reporting — response time histograms, fingerprinting, and profile tables. Install it on DirectAdmin servers:

# On CentOS/AlmaLinux
yum install -y percona-toolkit

# On Debian/Ubuntu
apt-get install -y percona-toolkit

# Or download directly
wget https://percona.com/get/pt-query-digest
chmod +x pt-query-digest

pt-query-digest /var/log/mysql/slow-queries.log > /tmp/query_report.txt
cat /tmp/query_report.txt | less

The report opens with a Profile table sorted by response time, showing each query's rank, total time, percentage of total load, count, and average time. Below the profile, each query gets a detailed block including min/max/mean/95th-percentile response times.

Focus your optimization effort on queries that appear in the top 5 by total time. Typically these are:

• Full table scans on large tables (Using filesort or Using temporary in EXPLAIN)
• Queries joining tables without indexed foreign keys
• SELECT * queries that return far more data than the application needs
• Queries running inside loops — the same query executed hundreds of times per request

For real-time analysis without writing to a log file, you can tap the MySQL general query log or use tcpdump:

tcpdump -s 65535 -x -nn -q -tttt -i eth0 -c 1000 port 3306 > /tmp/mysql_tcpdump.txt
pt-query-digest --type tcpdump /tmp/mysql_tcpdump.txt

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl --user root --pass <your_root_password>

The script connects to MySQL, reads dozens of status variables and configuration settings, then produces a colour-coded report. Green checkmarks mean the setting is healthy; red exclamation points are recommendations you should act on.

• General Statistics — uptime, total queries, slow queries, thread count
• Storage Engine Statistics — InnoDB vs MyISAM usage, fragmented tables
• Security Recommendations — anonymous accounts, weak passwords
• Performance Metrics — key buffer efficiency, InnoDB buffer pool usage, query cache hit rate
• Recommendations — the most actionable section, listing exact my.cnf changes to make

• InnoDB buffer pool too small — the most frequent finding on servers with ≥ 4 GB RAM. Covered in the next section.
• Query cache disabled or sized wrong — on MySQL 5.7 and earlier, the query cache can help read-heavy workloads. On MySQL 8.0, the query cache was removed entirely.
• Too many open files — increase open_files_limit in my.cnf if mysqltuner flags this.
• Thread cache hit rate low — increase thread_cache_size to reduce thread creation overhead.
• High temporary table creation — consider increasing tmp_table_size and max_heap_table_size.

For any recommendation mysqltuner makes, you can dig deeper with SHOW STATUS. For example, to understand temporary table behaviour:

SHOW STATUS LIKE 'Created_tmp%';

If Created_tmp_disk_tables is more than 25% of Created_tmp_tables, your queries are creating temporary tables that overflow to disk — a significant performance drain.

A common rule of thumb for dedicated database servers is to allocate 70–80% of total RAM to the InnoDB buffer pool. On a server shared with a web stack (Apache, PHP), aim for 50–60%. First, check your current setting and the size of all InnoDB data:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

SELECT
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE engine = 'InnoDB';

If the total InnoDB data size exceeds your current buffer pool size, you're almost certainly hitting disk on every cache miss.

Open /etc/my.cnf and update or add these settings under [mysqld]:

[mysqld]
innodb_buffer_pool_size = 2G          # Adjust based on available RAM
innodb_buffer_pool_instances = 4      # One instance per 1 GB of buffer pool
innodb_buffer_pool_chunk_size = 128M  # Default; only change if pool size > 1G
innodb_log_file_size = 512M           # 25% of buffer pool size
innodb_flush_log_at_trx_commit = 2    # 1=safest, 2=balanced, 0=fastest
innodb_flush_method = O_DIRECT        # Bypass OS cache for InnoDB data files
innodb_read_io_threads = 4
innodb_write_io_threads = 4

On MySQL 5.7 and later, you can resize the buffer pool online:

SET GLOBAL innodb_buffer_pool_size = 2147483648;  -- 2 GB in bytes

Monitor the resize progress:

SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

After applying changes, check the buffer pool hit rate to confirm improvement:

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

Calculate hit rate as: 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests). A healthy server should have a hit rate above 99%. If it's lower, the buffer pool is still too small or you have an inefficient query pattern fetching more data than necessary.

• innodb_io_capacity — set to the IOPS capability of your storage (e.g., 200 for SATA HDD, 1000 for SSD, 10000 for NVMe). This tells InnoDB how aggressively to flush dirty pages.
• innodb_file_per_table = 1 — stores each table in its own .ibd file, making it easier to reclaim space after large deletes.
• innodb_stats_on_metadata = 0 — prevents InnoDB from updating statistics on every SHOW TABLE STATUS or INFORMATION_SCHEMA query, which can cause lock contention on busy servers.

For any query identified in your slow query log, prefix it with EXPLAIN to see how MySQL plans to execute it:

EXPLAIN SELECT u.id, u.email, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
AND o.status = 'pending'\G

The \G formats the output vertically for readability. Focus on these columns:

• type — ALL means a full table scan (worst). Aim for ref, eq_ref, or const.
• rows — MySQL's estimate of rows examined. Large numbers signal inefficiency.
• Extra — watch for Using filesort, Using temporary, and Using join buffer — all indicate sub-optimal execution.
• key — which index is being used. NULL here means no index is in play.

Query the information schema to list tables with no indexes:

SELECT t.table_schema, t.table_name, t.table_rows
FROM information_schema.tables t
LEFT JOIN information_schema.statistics s
  ON t.table_schema = s.table_schema
  AND t.table_name = s.table_name
WHERE t.table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
  AND s.index_name IS NULL
  AND t.table_type = 'BASE TABLE'
ORDER BY t.table_rows DESC;

Once you've identified a column that should be indexed, add it with ALTER TABLE:

-- Single column index
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

-- Composite index for multi-column WHERE clauses
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- Covering index (includes all columns needed by the query)
ALTER TABLE orders ADD INDEX idx_covering (user_id, status, total, created_at);

For very large tables, use ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE to avoid locking the table during index creation on MySQL 5.6+:

ALTER TABLE orders
ADD INDEX idx_user_id (user_id),
ALGORITHM=INPLACE,
LOCK=NONE;

MySQL's Performance Schema tracks which indexes are actually being used and which are redundant. Enable it and check for unused indexes:

-- Enable performance schema (in my.cnf: performance_schema = ON)

SELECT object_schema, object_name, index_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema NOT IN ('mysql','performance_schema','information_schema','sys')
  AND index_name IS NOT NULL
  AND count_star = 0
ORDER BY object_schema, object_name;

Indexes with count_star = 0 have never been used since the last server restart. Consider dropping them to reduce write overhead.

Beyond indexing, query structure itself can cause slowness:

• Avoid SELECT * — fetch only the columns you need. This reduces data transfer and enables covering indexes.
• Use LIMIT on large result sets — paginate results instead of fetching thousands of rows at once.
• Avoid functions on indexed columns in WHERE clauses — WHERE YEAR(created_at) = 2025 prevents index use; rewrite as WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31'.
• Replace correlated subqueries with JOINs — correlated subqueries execute once per row of the outer query.
• Use query caching at the application layer — for read-heavy data that changes infrequently, cache results in Redis or Memcached rather than hitting MySQL repeatedly.

After heavy INSERT/DELETE activity, InnoDB tables can become fragmented and statistics can drift from reality. Refresh them:

-- Update statistics for the query optimizer
ANALYZE TABLE orders;

-- Rebuild the table to reclaim space and defragment
OPTIMIZE TABLE orders;

For automated maintenance on DirectAdmin, add a cron job that runs mysqlcheck during off-peak hours:

0 3 * * 0 /usr/bin/mysqlcheck -u root -p<password> --all-databases --optimize >> /var/log/mysqlcheck.log 2>&1

If you find that MySQL optimization is taking more time than you can spare, our team at CloudHouse offers managed server management that covers proactive MySQL tuning, slow query elimination, and ongoing performance monitoring.