Troubleshoot High Database Load in MariaDB

Introduction

That night at 2 AM, the server monitoring alarm went off — CPU usage had spiked dramatically, but not because of heavy web traffic. After checking, the load was coming from the database. MariaDB, which should be lightweight, had become a heavyweight because of queries running endlessly without limits. I once handled a case where a single SELECT * query without a proper WHERE clause brought down an entire shared hosting server for 45 minutes. Back then I was still junior, and the lesson traumatized me to this day — always check database load first before doing anything else.

If you’ve ever experienced a server that suddenly becomes sluggish even though CPU is only at 20%, the problem is likely not the CPU but the database. High load in MariaDB is like a traffic jam on the highway — not because there are too many cars, but because one truck broke down in the middle of the road and caused everything behind it to stop. Slow or stuck queries can cause connections to pile up, and eventually your server goes down.

In this article I’ll show you the sequence of commands I always use when troubleshooting high database load in MariaDB — from the most basic to the advanced. All these commands I’ve used hundreds of times in the field and they’re proven to work. Let’s start with the first one.

Command #1: Login to MariaDB and Check Server Status

Before we dive deeper into troubleshooting, the first step I always take is logging into MariaDB and checking the overall server status. It’s like diagnosing a sick person — before examining specifics, you check their heartbeat and blood pressure first.

mysql -u root -p

After logging in, immediately run:

SHOW GLOBAL STATUS;

This command displays all MariaDB status variables — from the number of queries executed, active connections, to buffer pool usage. Don’t panic if the output is hundreds of lines. What you need to focus on initially are these three variables:

• Threads_connected — how many connections are currently active
• Threads_running — how many threads are actually executing queries right now (this is more important than Threads_connected)
• Questions — total queries executed since the server last restarted

If Threads_running is high (above 10 on a shared hosting server), that’s a red flag. Usually 3-5 is normal for small to medium servers.

Command #2: SHOW PROCESSLIST — Who’s Causing the Jam?

This is the most powerful command for troubleshooting high database load. SHOW PROCESSLIST shows all queries currently running in MariaDB — who’s running them, from where, and how long they’ve been running.

SHOW FULL PROCESSLIST;

I use FULL because without it, the Info column gets truncated at 100 characters. With FULL, you can see the complete query. The output looks roughly like this:

Id	User	Host	db	Command	Time	State	Info
14523	webapp	localhost	production	Query	847	Sorting result	SELECT * FROM orders WHERE …
14524	webapp	localhost	production	Query	234	Sending data	SELECT * FROM logs JOIN users …
14530	root	localhost	NULL	Sleep	12

What to look for:

• Time column — if any query has a Time above 60 seconds, that’s suspicious. Above 300 seconds? That’s definitely problematic.
• State column — Sending data or Sorting result lingering for a long time usually indicates a query that isn’t properly indexed.
• Info column — read the query. Look for SELECT *, LIKE '%...%', or JOIN without an effective WHERE clause.

From my experience, 80% of high database load cases can be identified directly from SHOW FULL PROCESSLIST. If you find a query with a Time above 300 seconds, that’s the primary suspect.

Command #3: Check External Connections with lsof

Sometimes the problem isn’t with the queries but with too many connections. MariaDB has a connection limit (max_connections), and if that limit is reached, new connections will be rejected. To check how many connections exist to the MariaDB port (default 3306) from outside, I use lsof:

lsof -i :3306 | grep LISTEN

This command shows all processes connected to port 3306. You can see how many connections come from a specific IP, a specific user, or a specific application.

For more specific filtering, filter by user:

lsof -i :3306 -u webapp

Or count total connections:

lsof -i :3306 | wc -l

If the count is approaching or exceeds your MariaDB max_connections (usually 151 by default), that indicates a connection leak in the application. An application that opens connections but never calls close() is a common cause of this problem.

I once handled a case where a single PHP application opened 50 database connections per request because the developer forgot to use connection pooling. The result? The server immediately hit max connections and all other users couldn’t access the database. Lesson learned: always use persistent connections or connection pooling for high-traffic applications.

Command #4: Check Open Tables with SHOW OPEN TABLES

Every time MariaDB opens a table for reading or writing, that table enters the “open tables cache.” If the cache is full, MariaDB has to close old tables before opening new ones — this adds overhead and can dramatically degrade performance.

SHOW OPEN TABLES WHERE In_use > 0;

This command shows which tables are currently locked or in use by running queries. The In_use column shows how many queries are currently using that table.

If you see a table with In_use above 5, that’s a red flag — it means many queries are accessing the same table simultaneously. This can cause lock contention, where queries wait in line to access the same table.

To check if the open tables cache is large enough:

SHOW GLOBAL VARIABLES LIKE 'table_open_cache';

On a shared hosting server with high traffic, I usually set table_open_cache to at least 2000-4000. The MariaDB default is 400, and that’s often insufficient for servers handling many tables.

Command #5: Check LVE and Resource Usage

On shared hosting servers using CloudLinux, each user has Resource Limits (LVE) that cap CPU, RAM, and I/O usage. If a particular user is consuming too many database resources, they can “eat into” other users’ allocations.

To check LVE usage:

lveinfo --usage --by-id

Or for a specific user:

lveinfo --usage --by-user webapp

The output shows:

• CPU % — percentage of CPU used
• PMEM (MB) — memory used
• IO — disk I/O usage
• EP (entries) — entry processes

If you see a specific user consistently using CPU above 80% or I/O above 50%, that could be causing the server to slow down for other users. On CloudLinux, you can adjust the LVE limits for that user:

lvectl set-user webapp --cpu=50 --pmem=512 --io=20

This command limits the webapp user to a maximum of 50% CPU, 512MB RAM, and 20 I/O. This helps prevent one user from consuming all server resources.

Command #6: Check Slow Query Log

One of the most effective ways to find problematic queries is by enabling the slow query log. This logs all queries that take longer than a certain amount of time (usually 1-2 seconds).

First, check if the slow query log is active:

SHOW GLOBAL VARIABLES LIKE 'slow_query_log';

If it’s OFF, enable it:

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

After enabling, let it run for a few minutes then check the log:

mysqldumpslow -s t -t 10 /var/lib/mysql/slow-queries.log

The mysqldumpslow command sorts slow queries by execution time and displays the top 10 slowest queries. This is extremely helpful for finding queries that need optimization.

From my experience, there’s usually a pattern: queries in the slow query log always involve JOIN between large tables without adequate indexes, or LIKE '%...%' that perform full table scans.

Command #7: Analyze Queries with EXPLAIN

After finding problematic queries from SHOW PROCESSLIST or the slow query log, the next step is to analyze those queries with EXPLAIN.

EXPLAIN SELECT * FROM orders JOIN users ON orders.user_id = users.id WHERE orders.status = 'pending';

EXPLAIN shows MariaDB’s execution plan for that query. What to look for:

• Type — if it’s ALL or index, that means a full table scan (bad). Ideally ref, eq_ref, or const.
• Key — if NULL, no index is being used (big problem).
• Rows — estimated number of rows that need to be checked. If the number is very large (millions), the query is likely slow.
• Extra — if there’s Using filesort or Using temporary, that’s a sign the query needs optimization.

Command #8: Check Unused Indexes

Sometimes the problem isn’t too few indexes but too many. Unused indexes still consume resources during INSERT/UPDATE/DELETE operations.

SELECT * FROM sys.schema_unused_indexes WHERE object_schema NOT IN ('mysql','sys','performance_schema','information_schema');

Or without the sys schema:

SELECT * FROM information_schema.statistics WHERE index_name NOT IN ('PRIMARY','PRIMARY_KEY') GROUP BY table_schema, table_name HAVING COUNT(*) > 3;

This shows tables with more than 3 indexes — typically candidates for cleanup.

Pro Tips: Commonly Forgotten

1. Restarting MariaDB is not a solution — it only delays the problem. If there’s a slow query, fix the query, don’t restart the server.
2. KILL stuck queries — if you find a query with a Time in the hundreds of seconds, don’t hesitate to KILL [ID];. Better to kill one query than have the entire server go down.
3. Monitor regularly — don’t just check when problems occur. I usually set up a cron to log Threads_running every 5 minutes, so if there’s a spike, I have historical data for analysis. If your server is on a VPS, it usually has integrated monitoring like disk I/O troubleshooting that can give you I/O usage insights too.
4. Watch aborted_connects — if the number keeps rising, there’s a connection issue that needs further investigation.

FAQ

How do I know if my MariaDB is experiencing high load?

The quickest command is SHOW GLOBAL STATUS LIKE 'Threads_running'; — if the number is above 10 on a small server, there’s likely a problem. For a fuller picture, run SHOW GLOBAL STATUS; and watch Threads_connected, Threads_running, Slow_queries, and Aborted_connects.

Is it safe to run KILL on a running query?

Yes, KILL [ID] only stops that query, not the entire connection. MariaDB will return a Query execution was interrupted error to the client, and the client will usually retry. What’s not safe is KILL [ID] on a running transaction — this can cause a time-consuming rollback.

Why doesn’t SHOW PROCESSLIST show my application’s queries?

Because the queries finished executing (too quickly). If you want to catch fast queries, enable general_log briefly (don’t leave it on too long as it can fill up the disk): SET GLOBAL general_log = 'ON'; then disable it after a few minutes.

What’s the ideal max_connections for a shared hosting server?

For shared hosting with 8-16GB RAM, I usually set max_connections between 200-300. But what’s more important is setting wait_timeout and interactive_timeout so idle connections get closed immediately. The default of 28800 seconds (8 hours) is too long — I usually set 60-120 seconds.

Related Issues

• Why DNS Changes Take Time to Propagate — often occurs alongside database load issues during server migrations
• GRUB Rescue Error After Disk Resize — disk issues that can affect database I/O

Conclusion

Troubleshooting high database load in MariaDB isn’t as complicated as you might think, as long as you know the right sequence of commands. Start with SHOW GLOBAL STATUS for an overview, SHOW FULL PROCESSLIST to identify problematic queries, lsof to check connections, SHOW OPEN TABLES to check cache, and EXPLAIN for query optimization.

The key takeaway is: don’t just blindly restart. Every problem has a root cause, and our job as engineers is to find that root cause, not just mask the symptoms. If you monitor regularly and stay proactive, your server will remain stable, God willing.

Have questions or your own database troubleshooting experience? Don’t hesitate to share in the comments — I read all of them and reply when I can. Hope this article helps!