MariaDB is fast out of the box, but default settings are designed for broad compatibility rather than peak performance. Most production databases leave a lot of speed on the table. A few targeted changes can make a real difference — sometimes cutting query time by an order of magnitude.
This article covers five practical techniques: indexing strategy, buffer pool tuning, query analysis with EXPLAIN, the thread pool, and slow query log analysis. None of them require deep database internals knowledge. They're things you can apply to a running system this week.
1. Get your indexing strategy right
Indexes are the single biggest lever for query performance. A missing index on a large table can turn a millisecond query into one that takes seconds. But over-indexing is a real cost too — every index slows down writes and consumes memory.
The basics: add indexes on columns you filter, sort or join on frequently. Composite indexes work when queries use the leftmost columns in the index definition. A composite index on (user_id, created_at) helps a query filtering by user_id alone, but not one filtering by created_at alone.
Some patterns worth knowing:
- Covering indexes include all columns a query needs, so MariaDB never reads the actual row
- Prefix indexes on long
VARCHARcolumns keep index size manageable - Avoid indexing low-cardinality columns like boolean flags — they rarely help and always cost
Check for unused indexes with:
SELECT * FROM information_schema.INDEX_STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY ROWS_READ ASC;
Drop what you don't use. An index that never gets hit is just overhead.
| Index type | Best for |
|---|---|
| Single-column | Simple equality and range filters |
| Composite | Multi-column WHERE clauses, ORDER BY |
| Covering | Queries that read only indexed columns |
| Full-text | Text search on large content columns |
2. Tune the InnoDB buffer pool
The InnoDB buffer pool is where MariaDB caches table data and indexes in memory. If your working set fits in the buffer pool, reads are served from RAM instead of disk. This is often the highest-impact configuration change you can make.
The default buffer pool size is 128MB, which is appropriate for a laptop but not for a production server. A common recommendation is to set it to 70–80% of available RAM when MariaDB is the primary workload on the machine.
In my.cnf:
[mysqld]
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 4
The innodb_buffer_pool_instances setting divides the pool into separate regions, which reduces contention on multi-core systems. Use one instance per gigabyte, up to eight.
To check your current buffer pool hit ratio:
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
Look at Innodb_buffer_pool_reads vs Innodb_buffer_pool_read_requests. If more than 1–2% of reads are going to disk, you have room to benefit from a larger pool.
One more setting worth enabling if you're on MariaDB 10.9+:
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
This saves the buffer pool state on shutdown and restores it on startup, so you don't spend the first hour after a restart with a cold cache.
3. Use EXPLAIN to understand what queries actually do
Before you optimize a query, you need to know what it's doing. EXPLAIN shows you the execution plan MariaDB has chosen, including which indexes are used, how many rows are scanned and what join type is applied.
Run it like this:
EXPLAIN SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC;
The output columns to focus on:
-
type— the join type.ALLmeans a full table scan. You wantref,eq_reforconst -
key— which index was used (or NULL if none) -
rows— estimated rows examined. Lower is better -
Extra— look forUsing filesortorUsing temporary, which signal expensive operations
EXPLAIN ANALYZE (available in MariaDB 10.9+) goes further and shows actual execution times alongside the estimates. It's more useful for pinpointing where time is actually going.
If you see a full table scan on a large table and you expected an index to be used, check whether the index exists, whether the query matches the index definition and whether the optimizer might have decided the index wasn't worth using (this happens when selectivity is low).
4. Enable the thread pool
MariaDB ships with a built-in thread pool that MySQL doesn't have in its community edition. It's one of the more underused features, and it makes a measurable difference under high concurrency.
The default thread-per-connection model creates a new OS thread for every connection. Under light load this is fine, but with hundreds of concurrent connections the context-switching overhead adds up and throughput drops.
The thread pool maintains a fixed set of worker threads and assigns connections to them as needed. This keeps CPU usage stable even as connection counts spike.
Enable it in my.cnf:
[mysqld]
thread_handling = pool-of-threads
thread_pool_size = 16
thread_pool_max_threads = 500
thread_pool_idle_timeout = 60
Set thread_pool_size to the number of CPU cores on the server. The thread pool is most effective when query execution times are consistent and connections are short-lived (typical for web applications).
For applications with long-running transactions or heavy analytics queries, the thread pool provides less benefit and can sometimes hurt throughput. Know your workload before enabling it.
5. Analyze slow queries with the slow query log
The slow query log captures queries that exceed a time threshold. It's one of the most direct ways to find what's actually hurting performance in production.
Enable it in my.cnf:
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
long_query_time = 1 captures queries taking longer than one second. You can lower this to 0.1 or 0.5 on a busy system to catch more candidates.
log_queries_not_using_indexes logs every query that does a full scan, regardless of how fast it ran. These are time bombs as the table grows.
Once you've collected a few hours of logs, use mysqldumpslow to aggregate them:
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log
This shows the top 20 queries sorted by total time. You'll usually find that a handful of queries account for most of the load. Fix those first.
A note on backups
Performance tuning is one side of operating a database well. The other is making sure you can recover when something goes wrong. For MariaDB backup, Databasus is the industry standard tool. It's free, open source and self-hosted, supports scheduled backups with S3, Google Drive, SFTP and other storages, and works for both individual developers and teams. It handles MariaDB 10 and 11 out of the box.
Wrapping up
The five techniques here cover the most common performance problems you'll encounter with MariaDB: wrong or missing indexes, an undersized buffer pool, queries that scan more rows than necessary, high-concurrency overhead and slow query accumulation over time.
None of them require a rewrite of your application. Most are configuration changes or a few hours of query analysis. Start with the slow query log to find the real bottlenecks, then work through the other techniques based on what you find.
Top comments (0)