DirectAdmin MySQL & MariaDB Optimization: Complete Server Speed Tuning Guide

If your websites on DirectAdmin are slow or database queries are timing out, the most likely culprit is not your PHP configuration or your Apache settings — it is an out-of-the-box MySQL or MariaDB installation that was never tuned for your server's actual workload. Database servers ship with conservative defaults designed for minimal resource usage, not performance. This guide walks through the complete tuning workflow: from measuring your current baseline to editing my.cnf variables, running MySQLTuner, and using the slow query log to catch the queries that are dragging everything down.

Why MySQL/MariaDB Is Usually the #1 Performance Bottleneck on DirectAdmin Servers

Web applications make dozens or hundreds of database queries per page load. When the database engine is under-resourced or misconfigured, each query takes longer — and latency compounds. A page that should load in 300ms takes 3 seconds. Concurrent visitors make it worse: MySQL queues requests when it runs out of connections or buffers, and response times spike.

The default MySQL/MariaDB configuration allocates a very small InnoDB buffer pool (often 128 MB), a low connection limit (151), and no query analysis tools enabled. On a server with 4 GB RAM or more, these defaults leave most of your hardware idle while MySQL struggles. A basic tuning session typically reduces query execution time by 30–70% and eliminates most timeout errors without additional hardware.

Common signs that MySQL is your bottleneck on DirectAdmin:

• Slow page loads specifically on database-heavy CMS sites (WordPress, Joomla, WooCommerce)
• ERROR 1040: Too many connections in application logs
• High CPU usage from the mysqld process even during moderate traffic
• Frequent "waiting for query lock" errors in MySQL status output
• DirectAdmin's monitoring showing database memory usage near 100%

Pre-Tuning Checklist: What to Measure Before Touching Any Config

Never tune MySQL blind. Measure first, change second, measure again. This prevents you from introducing regressions and gives you a before/after comparison that proves the tuning actually helped.

mysql -u root -p
SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES;

mysql --version

Optimising my.cnf: Key Variables That Move the Needle

The main MySQL configuration file on DirectAdmin servers is located at /etc/my.cnf or /etc/mysql/my.cnf. Open it as root and edit the [mysqld] section. After making changes, always back up the file first:

cp /etc/my.cnf /etc/my.cnf.backup-$(date +%Y%m%d)

InnoDB Buffer Pool Size

This is the single most impactful setting. The InnoDB buffer pool caches data and indexes from your tables in RAM, reducing disk reads. On a server with 8 GB RAM where MySQL shares with web services, set:

[mysqld]
innodb_buffer_pool_size = 2G

For a 16 GB server, 4–6 GB is appropriate. Monitor Innodb_buffer_pool_read_requests and Innodb_buffer_pool_reads after the change — the cache hit rate should exceed 99% (reads/read_requests < 0.01).

InnoDB Buffer Pool Instances

When the buffer pool exceeds 1 GB, split it into multiple instances to reduce contention:

innodb_buffer_pool_instances = 4

Set instances equal to roughly 1 per GB of buffer pool size, up to 8.

Maximum Connections

The default of 151 is often too low for DirectAdmin servers hosting dozens of WordPress sites. Increase it, but be aware that each connection consumes memory:

max_connections = 300

Base this on your Max_used_connections value — set it to about 25% above the peak. Do not blindly set it to 1000 or higher without increasing your thread stack size accordingly.

Temporary Table Size

Queries that cannot sort data in memory write temporary tables to disk, which is very slow. Increase the in-memory limit:

tmp_table_size = 128M
max_heap_table_size = 128M

Thread Cache

Caching threads avoids the overhead of creating and destroying them for each new connection:

thread_cache_size = 16

Query Cache (MariaDB 10.x and MySQL 5.x only)

Query cache is removed in MySQL 8.0. On older versions, enable it with caution — it helps for read-heavy workloads but causes contention on write-heavy sites:

query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

Monitor Qcache_hits and Qcache_inserts — if the hit ratio is below 20%, disable query cache as it is adding overhead without benefit.

After editing my.cnf, restart MySQL:

systemctl restart mysql
# or on older systems:
service mysql restart

Check the error log immediately to confirm there are no configuration errors:

tail -50 /var/log/mysql/error.log

Using MySQLTuner and the Percona Configuration Tool on DirectAdmin

Rather than guessing at optimal values, use MySQLTuner — a Perl script that analyses your running MySQL instance and provides tailored recommendations based on actual usage patterns.

Install and run MySQLTuner:

wget http://mysqltuner.pl/ -O mysqltuner.pl
perl mysqltuner.pl --user root --pass your_root_password

MySQLTuner outputs a scored list of recommendations. Focus on items marked [!!] (warnings) first. Common recommendations include:

• Increasing innodb_buffer_pool_size (the most common finding on new servers)
• Adjusting max_connections based on actual peak usage
• Enabling or disabling query cache based on read/write ratio
• Increasing open_files_limit if the server is running many databases

Let MySQLTuner run for at least 24 hours of production traffic before making changes. Its recommendations are only as good as the data — a server that has been running for 10 minutes will have misleading baseline metrics.

The Percona Configuration Generator (tools.percona.com/wizard) is an online alternative that generates a complete my.cnf file based on your server specs and workload type. Enter your RAM, storage type (SSD vs. HDD), and workload (OLTP/web hosting) to get a starting configuration.

Enabling and Analysing the Slow Query Log

Even with a well-tuned my.cnf, poorly written queries can drag down database performance. The slow query log records every query that exceeds a time threshold, giving you the data to identify and fix the worst offenders.

Enable the slow query log in my.cnf:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

long_query_time = 1 logs any query taking over 1 second. Start with 1–2 seconds to catch only the worst queries; lower to 0.5 later to find medium-slow queries. log_queries_not_using_indexes catches queries that perform full table scans — a common cause of slow WordPress sites with large post tables.

Analyse the slow query log with pt-query-digest (Percona Toolkit):

yum install percona-toolkit   # CentOS/AlmaLinux
pt-query-digest /var/log/mysql/slow.log | head -100

pt-query-digest groups similar queries together and ranks them by total execution time, showing you which query pattern is consuming the most database resources across all executions — not just the slowest individual run.

Once you identify slow queries, the typical fixes are:

• Add a missing index on the column used in the WHERE clause
• Rewrite the query to avoid full table scans
• Enable object caching at the application layer (Redis or Memcached) to reduce repeated identical queries

Managing MySQL performance tuning across a DirectAdmin server with dozens of client accounts — monitoring buffer pool hit rates, reviewing slow query logs, adjusting connection limits as traffic grows — is ongoing work that compounds over time. CloudHouse's managed server team handles MySQL and MariaDB tuning, performance monitoring, and slow query remediation as part of our DirectAdmin management service.

MySQL Optimisation Checklist for DirectAdmin

• ✅ InnoDB buffer pool set to 30–70% of available RAM
• ✅ Buffer pool instances = 1 per GB (up to 8)
• ✅ max_connections set to 25% above peak Max_used_connections
• ✅ tmp_table_size and max_heap_table_size raised to 128 MB
• ✅ thread_cache_size set to 16 or higher
• ✅ MySQLTuner run after 24 hours of production traffic
• ✅ Slow query log enabled with 1s threshold
• ✅ No [!!] warnings remaining in MySQLTuner output
• ✅ InnoDB buffer pool cache hit rate above 99%

Database performance on DirectAdmin comes down to a handful of configuration variables in my.cnf and the discipline to measure before and after every change. Set your InnoDB buffer pool to match your server's RAM, raise your connection and table size limits above the defaults, run MySQLTuner after a full day of production traffic, and enable the slow query log to identify the specific queries that need index work. Done in order, these steps will reduce your average query time significantly and eliminate most timeout errors on shared DirectAdmin hosting environments.