MariaDB Performance Tuning for Large Databases on Dedicated Servers

Running MariaDB at scale on a dedicated server isn’t just about installing the service and increasing RAM; it’s about aligning the database engine’s behavior with your hardware and workload. MariaDB Optimization for Large Databases requires careful tuning of numerous configuration parameters.

In this guide from PerLod Hosting, we want to explore the key configurations that matter most in real production environments, including memory allocation, disk I/O and durability settings, concurrency and connection handling, and practical monitoring to validate improvements.

At the end of this guide, you will have a stable and high-performance MariaDB configuration that can handle heavy workloads.

Prerequisites for MariaDB Optimization for Large Databases

Before starting MariaDB Optimization for Large Databases, it’s important to confirm the environment is ready. Make sure the following requirements are met before starting:

• A dedicated Linux server such as Ubuntu, CentOS, RHEL, or AlmaLinux.
• MariaDB 10.4 or newer is installed.
• Root or sudo privileges to edit configuration files and restart services.
• At least 16GB RAM. 32GB+ is strongly recommended for large databases.
• SSD storage to reduce latency and handle heavy I/O efficiently.
• Basic database administration knowledge.

Also, you must check your server’s available resources to calculate optimal configuration values.

Check total RAM with the command below:

free -h

Check CPU cores with the command below:

nproc

Check disk type and performance with the command below:

lsblk -d -o name,rota,model

The rota=0 means SSD, and rota=1 means HDD.

Check current MariaDB resource limits by using the following commands:

mysql -u root -p -e "SHOW VARIABLES LIKE '%buffer%';"
mysql -u root -p -e "SHOW VARIABLES LIKE '%connections%';"

Once you are done with these requirements, proceed to the following steps to explore the MariaDB configuration parameters.

Core MariaDB InnoDB Configuration Parameters

Core InnoDB settings determine how MariaDB uses memory, handles disk I/O, and maintains durability under heavy load. Since InnoDB is the default and most common storage engine for large production databases, tuning these parameters usually provides the best performance on your dedicated server.

InnoDB Buffer Pool Size

The innodb_buffer_pool_size is the most essential parameter for InnoDB performance. It caches data and indexes in memory, which reduces disk I/O operations by keeping datasets in RAM.​

Recommended Configuration for InnoDB Buffer Pool Size:

• Allocate 70-80% of total RAM on dedicated database servers.
• For a 32GB server: Set to 24-26GB.
• For shared servers: Reduce to 50-60% to leave memory for OS and other applications.

Edit the MariaDB configuration file with the command below:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

# Or on some systems, you can use:
sudo nano /etc/my.cnf

In the file, add or modify these settings under the [mysqld] section:

[mysqld]
# InnoDB Buffer Pool Configuration
innodb_buffer_pool_size = 24G     # Adjust based on your RAM
innodb_buffer_pool_instances = 8  # 1 instance per GB, max 64

# Buffer pool chunk size (must divide buffer pool size evenly)
innodb_buffer_pool_chunk_size = 128M

Once you are done, you can verify the current buffer pool size with the command below:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Also, monitor buffer pool efficiency with the following command:

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

InnoDB Log Configuration

Every change is recorded in InnoDB redo logs first, so the database can recover cleanly after a crash and can batch writes more efficiently instead of constantly forcing random disk I/O.

For write-heavy workloads, it is important to configure this setting correctly.

InnoDB Log File Configuration:

innodb_log_file_size = 2G      # 1/4 to 1/2 of buffer pool size
innodb_log_files_in_group = 2  # Default, usually sufficient
innodb_log_buffer_size = 64M   # Large buffer reduces disk I/O for transactions

• innodb_log_file_size: Size of each redo log file; larger values improve write performance but increase recovery time after crashes. Set to at least 1/4 of the buffer pool size or equivalent to one hour of peak writes​.

• innodb_log_buffer_size: Memory buffer for log writes; 64MB is recommended to handle large transactions without frequent disk writes

Log flushing behavior:

innodb_flush_log_at_trx_commit = 1  # 1=ACID compliant, 2=better performance, 0=fastest but risky
innodb_flush_method = O_DIRECT      # Prevents double buffering on Linux

innodb_flush_log_at_trx_commit: Controls durability vs. performance:​

• 1 (default): Full ACID compliance, flushes to disk on every commit (safest, slower).
• 2: Writes to the OS buffer on commit, flushes every second (balanced, risk of 1 second data loss on OS crash).
• 0: Writes and flushes every second only (fastest, risk of 1 second data loss on MySQL crash).

innodb_flush_method: O_DIRECT bypasses the OS file cache, preventing double buffering and reducing memory pressure. Recommended for most systems with sufficient RAM

Warning: Changing innodb_log_file_size requires stopping MariaDB and removing old log files:

sudo systemctl stop mariadb
sudo rm /var/lib/mysql/ib_logfile*
sudo systemctl start mariadb

InnoDB I/O Configuration

You must configure I/O capacity based on your storage type to optimize background operations.

InnoDB I/O Capacity Settings:

innodb_io_capacity = 2000      # For SSD: 2000-4000, HDD: 200-400
innodb_io_capacity_max = 4000  # Usually 2x innodb_io_capacity
innodb_read_io_threads = 8     # For read-heavy workloads
innodb_write_io_threads = 8    # For write-heavy workloads
innodb_thread_concurrency = 0  # 0=unlimited (recommended for modern CPUs)

Settings Explanation:

innodb_io_capacity: Defines the IOPS available for background tasks, such as flushing dirty pages. Set based on storage:

• SSD/NVMe: 2000-4000
• RAID SSD: 4000-10000
• HDD: 200-400

innodb_io_capacity_max: Maximum IOPS during adaptive flushing when the system is behind. Typically 2x the base capacity.

innodb_read_io_threads / innodb_write_io_threads: Number of threads for async I/O operations; increase for high concurrency workloads.

innodb_thread_concurrency: Limits active threads inside InnoDB; 0 (unlimited) works best on modern multi-core systems.

Warning: Setting innodb_io_capacity_max too high can degrade performance by causing excessive flushing and write amplification on SSDs.

InnoDB Table Configuration

InnoDB table configuration controls how MariaDB stores your tables on disk and how it handles background tasks.

These settings become especially important when you have many large tables, frequent writes, and steady UPDATE/DELETE activity, because they directly affect disk usage, I/O stability, and long-term performance.

Table and Tablespace Settings:

innodb_file_per_table = 1     # Each table gets its own .ibd file
innodb_open_files = 4000      # Number of .ibd files kept open

# Adaptive Flushing
innodb_adaptive_flushing = 1       # Enable adaptive flushing algorithm
innodb_adaptive_flushing_lwm = 10  # Start flushing at 10% of max checkpoint age

# Purge Configuration
innodb_purge_threads = 4      # Threads for cleaning old row versions

Connection and Thread Management in MariaDB

Connection and thread settings control how MariaDB handles concurrent users without running out of memory or CPU, which is especially essential on large databases where traffic spikes can quickly overwhelm the server.

Each client connection has overhead, so setting a realistic max_connections and avoiding inefficient “one thread per connection” behavior can prevent resource exhaustion and keep latency stable under load.

Connection Settings:

max_connections = 300      # Maximum simultaneous connections
max_connect_errors = 1000  # Prevent connection blocking from failed attempts

# Thread Pool (Recommended for web applications)
thread_handling = pool-of-threads  # Use thread pool instead of one-thread-per-connection
thread_pool_size = 8               # Recommended: number of CPU cores
thread_pool_max_threads = 500      # Maximum threads in pool
thread_pool_min_threads = 8        # Minimum threads to keep alive

# Thread Cache (if NOT using thread pool)
thread_cache_size = 100            # Recommended 50-100 for connection-intensive workloads

Calculation Formula for max_connections is like this:

max_connections = (Available RAM - Global Buffers) / Thread Buffers

Calculate Thred Buffers with:

Thread Buffers = sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size

Table and File Management in MariaDB

Table and file caches help MariaDB avoid repeatedly opening the same table files and reloading table metadata, which becomes a real bottleneck when you have lots of tables or high concurrency.

With the right cache sizing, MariaDB spends less time on filesystem operations and more time actually executing queries, which brings better performance under load.

Table Cache Configuration:

table_open_cache = 4000        # Number of table file descriptors to keep open
table_definition_cache = 2000  # Number of table definitions to cache

# File Limits
open_files_limit = 65535       # Maximum open files (coordinate with OS limits)

To check the status, you can run the commands below:

SHOW GLOBAL STATUS LIKE 'Opened_tables';
SHOW GLOBAL STATUS LIKE 'Open_tables';

If Opened_tables keeps increasing quickly over time, MariaDB is reopening tables too often, typically a sign that table_open_cache is too low.

You can increase the cache, as long as the OS file limits support it.

MariaDB Performance and Optimization Settings

Performance and optimization settings help MariaDB stay fast and stable under real traffic.

Query buffers

These buffers are often allocated per connection, so the goal is to keep them large enough to help common queries but not so large that many concurrent sessions eat all RAM.

Per-Connection Buffer Sizes:

sort_buffer_size = 2M      # Buffer for ORDER BY and GROUP BY operations
read_buffer_size = 2M      # Sequential scan buffer
read_rnd_buffer_size = 4M  # Random read buffer (used after sorting)
join_buffer_size = 2M      # Buffer for table joins without indexes

Temporary Tables: The temporary tables decide when MariaDB can keep internal temporary tables in memory versus spilling them to disk, which is usually much slower.

tmp_table_size = 64M         # Maximum size for in-memory temporary tables
max_heap_table_size = 64M    # Maximum size for MEMORY tables (should match tmp_table_size)
tmpdir = /var/lib/mysql-tmp  # Directory for temporary files (consider using SSD or tmpfs)

Network and Packet Configuration

This protects the server and clients by defining the maximum size of a single packet. Also, enabling skip-name-resolve can speed up connection handling by avoiding DNS lookups. Only use IP addresses in GRANT statements when enabled​.

Network Settings:

max_allowed_packet = 256M  # Maximum packet size (important for large BLOBs)
net_buffer_length = 16K    # Buffer for client/server communication
skip-name-resolve = 1      # Disable DNS lookups (improves connection speed)

Slow Query Logging

The slow query log is a straightforward way to discover which queries actually waste time in production.

Slow Query Log Configuration:

slow_query_log = 1                 # Enable slow query logging
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2                # Log queries taking longer than 2 seconds
log_queries_not_using_indexes = 1  # Log queries without index usage
log_slow_admin_statements = 1      # Log slow admin commands (ALTER, etc.)

# Extended logging options
log_slow_verbosity = query_plan  # Include query execution plan in log
min_examined_row_limit = 1000    # Only log queries examining 1000+ rows

View recent slow queries with the command below:

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

Analyze and summarize the slow query log with:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

Performance Schema

Performance Schema provides deeper and structured performance data, which helps discover bottlenecks that don’t display clearly in basic logs.

Performance Schema Configuration:

performance_schema = ON

# Enable key consumers
performance-schema-instrument = 'stage/%=ON'
performance-schema-consumer-events-stages-current = ON
performance-schema-consumer-events-statements-current = ON

Note: Performance Schema adds slight overhead; enable carefully in production or use only during troubleshooting.

Useful Performance Schema Queries:

-- Identify slowest queries
SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_TIMER_WAIT > 1000000
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- Monitor I/O performance
SELECT * FROM performance_schema.file_summary_by_instance
ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
LIMIT 10;

-- Check table I/O waits
SELECT * FROM performance_schema.table_io_waits_summary_by_table
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Aria Storage Engine Configuration in MariaDB

Aria is an internal storage engine that MariaDB commonly uses for system-related tables and certain temporary or work tables, so its settings can still affect overall stability and background performance even if your main application tables are InnoDB.

Aria Storage Engine Settings:

aria_pagecache_buffer_size = 1G  # Cache for Aria tables (similar to key_buffer_size for MyISAM)
aria_sort_buffer_size = 256M     # Buffer for Aria index operations

Production-Ready MariaDB Configuration Template (32GB RAM with SSD)

Here is a practical and complete MariaDB configuration template optimized for a dedicated server with 32GB RAM and SSD storage.

It groups the most important settings into a single config.

[mysqld]
# ===========================
# BASIC SETTINGS
# ===========================
user = mysql
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid

# ===========================
# INNODB SETTINGS
# ===========================
# Buffer Pool Configuration (70-80% of RAM for dedicated server)
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_chunk_size = 128M

# Log Configuration
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# I/O Configuration (adjust based on your storage IOPS)
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 0

# Table Configuration
innodb_file_per_table = 1
innodb_open_files = 4000
innodb_adaptive_flushing = 1
innodb_adaptive_flushing_lwm = 10
innodb_purge_threads = 4

# ===========================
# CONNECTION MANAGEMENT
# ===========================
max_connections = 300
max_connect_errors = 1000

# Thread Pool (recommended for web applications)
thread_handling = pool-of-threads
thread_pool_size = 8
thread_pool_max_threads = 500
thread_pool_min_threads = 8

# If NOT using thread pool, configure thread cache:
# thread_cache_size = 100

# ===========================
# TABLE AND FILE CACHE
# ===========================
table_open_cache = 4000
table_definition_cache = 2000
open_files_limit = 65535

# ===========================
# QUERY CACHE (DISABLED)
# ===========================
query_cache_type = 0
query_cache_size = 0

# ===========================
# QUERY BUFFERS
# ===========================
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
join_buffer_size = 2M

# ===========================
# TEMPORARY TABLES
# ===========================
tmp_table_size = 64M
max_heap_table_size = 64M
tmpdir = /var/lib/mysql-tmp

# ===========================
# NETWORK SETTINGS
# ===========================
max_allowed_packet = 256M
net_buffer_length = 16K
skip-name-resolve = 1

# ===========================
# SLOW QUERY LOG
# ===========================
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_slow_verbosity = query_plan
min_examined_row_limit = 1000

# ===========================
# PERFORMANCE SCHEMA
# ===========================
performance_schema = ON
performance-schema-instrument = 'stage/%=ON'
performance-schema-consumer-events-stages-current = ON
performance-schema-consumer-events-statements-current = ON

# ===========================
# ARIA STORAGE ENGINE
# ===========================
aria_pagecache_buffer_size = 1G
aria_sort_buffer_size = 256M

# ===========================
# BINARY LOGGING (for replication/backup)
# ===========================
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7
sync_binlog = 1

# ===========================
# CHARACTER SET
# ===========================
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
socket = /var/run/mysqld/mysqld.sock
default-character-set = utf8mb4

System-Level Configuration for MariaDB Optimization

System-level tuning is the part most people skip, but it has a huge impact on MariaDB stability and throughput on dedicated servers.

Even with a perfect my.cnf, the OS can still throttle MariaDB, waste performance, and create extra I/O overhead, so these settings make sure MariaDB can actually use the resources you’re paying for.

1. MariaDB Operating System Limits: MariaDB performance depends on OS-level resource limits, particularly open file limits.

Check current limits with:

ulimit -n                  # Current open files limit for user
cat /proc/sys/fs/file-max  # System-wide maximum

Set persistent limits in:

sudo nano /etc/security/limits.conf

Add these lines to the file:

mysql soft nofile 102400
mysql hard nofile 102400
root soft nofile 102400
root hard nofile 102400

2. Systemd Service Configuration for MariaDB: For systems using systemd, create a systemd override directory with the command below:

sudo mkdir -p /etc/systemd/system/mariadb.service.d

Create a limits configuration file with the following command:

sudo nano /etc/systemd/system/mariadb.service.d/limits.conf

Add these LimitNOFILE settings to the file:

[Service]
LimitNOFILE=102400
LimitNPROC=10240

Reload systemd and restart MariaDB to apply the changes:

sudo systemctl daemon-reload
sudo systemctl restart mariadb

Check if open_files_limit was applied with:

SHOW VARIABLES LIKE 'open_files_limit';

3. Filesystem Optimization: Configure the I/O scheduler for database workloads with the commands below:

# Check current I/O scheduler
cat /sys/block/sda/queue/scheduler

# Set deadline scheduler (recommended for databases on SSD)
echo deadline | sudo tee /sys/block/sda/queue/scheduler

# Make permanent (add to /etc/rc.local or systemd service)

Note: For SSDs, consider deadline or noop schedulers; for HDDs, use deadline.

4. Disable Swap for Database: Swapping database pages to disk degrades performance.

Disable swap temporarily with the command below:

sudo swapoff -a

To make it permanent, open /etc/fstab file and comment out the swap line.

Alternatively, adjust swappiness with the commands below:

sudo sysctl vm.swappiness=1
echo "vm.swappiness=1" | sudo tee -a /etc/sysctl.conf

Troubleshoot Common Issues in MariaDB Configuration for Large Databases

Here are some common MariaDB issues and their solutions:

Issue 1. High Memory Usage

Check buffer pool usage:

SHOW STATUS LIKE 'Innodb_buffer_pool_pages%';

Identify memory-consuming queries:

SELECT * FROM sys.memory_global_by_current_bytes
LIMIT 10;

The solution is to reduce innodb_buffer_pool_size or per-connection buffers.

Issue 2. Too Many Connections Error

Check current and max connections with the commands below:

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

To fix the too many connections error, increase max_connections or investigate connection leaks in the application.

Issue 3. Slow Query Performance

Analyze the slow query log with the command below:

pt-query-digest /var/log/mysql/slow-query.log

Check for missing indexes:

sudo mysqlcheck -u root -p --check --all-databases

To resolve this, add indexes, optimize queries, and increase relevant buffers.

Issue 4. Disk Space Issues

Check database sizes with:

sudo mysql -u root -p -e "
SELECT table_schema AS 'Database',
  ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS 'Size (GB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;"

Check binary log size with the command below:

sudo du -sh /var/log/mysql/mysql-bin.*

The solution is to purge old binary logs, optimize or compress tables, and adjust expire_logs_days.

FAQs

Conclusion

MariaDB optimization for large databases is about matching the database engine’s caching, I/O, and concurrency behavior to your real hardware limits and workload patterns. We hope you enjoy the optimization tips discussed in this guide.

Subscribe to our X and Facebook channels to get the latest updates and articles.

For further reading:

Bare metal load balancer on Kubernetes with MetalLB

Optimizing GPU Performance Using Tensor Cores