MariaDB Performance Tuning for Large Databases on Dedicated Servers

Updated on Apr 17, 2026
Mila H
14 MINS READ
Table of Contents
MariaDB Optimization for Large Databases

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:

Bash
free -h

Check CPU cores with the command below:

Bash
nproc

Check disk type and performance with the command below:

Bash
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:

Bash
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:

Bash
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:

Bash
[mysqld]# InnoDB Buffer Pool Configurationinnodb_buffer_pool_size = 24G     # Adjust based on your RAMinnodb_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:

SQL
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Also, monitor buffer pool efficiency with the following command:

SQL
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:

Bash
innodb_log_file_size = 2G      # 1/4 to 1/2 of buffer pool sizeinnodb_log_files_in_group = 2  # Default, usually sufficientinnodb_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:

Bash
innodb_flush_log_at_trx_commit = 1  # 1=ACID compliant, 2=better performance, 0=fastest but riskyinnodb_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:

Bash
sudo systemctl stop mariadbsudo 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:

Bash
innodb_io_capacity = 2000      # For SSD: 2000-4000, HDD: 200-400innodb_io_capacity_max = 4000  # Usually 2x innodb_io_capacityinnodb_read_io_threads = 8     # For read-heavy workloadsinnodb_write_io_threads = 8    # For write-heavy workloadsinnodb_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:

Bash
innodb_file_per_table = 1     # Each table gets its own .ibd fileinnodb_open_files = 4000      # Number of .ibd files kept open # Adaptive Flushinginnodb_adaptive_flushing = 1       # Enable adaptive flushing algorithminnodb_adaptive_flushing_lwm = 10  # Start flushing at 10% of max checkpoint age # Purge Configurationinnodb_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:

Bash
max_connections = 300      # Maximum simultaneous connectionsmax_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-connectionthread_pool_size = 8               # Recommended: number of CPU coresthread_pool_max_threads = 500      # Maximum threads in poolthread_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:

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

Calculate Thred Buffers with:

Bash
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:

Bash
table_open_cache = 4000        # Number of table file descriptors to keep opentable_definition_cache = 2000  # Number of table definitions to cache # File Limitsopen_files_limit = 65535       # Maximum open files (coordinate with OS limits)

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

SQL
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:

Bash
sort_buffer_size = 2M      # Buffer for ORDER BY and GROUP BY operationsread_buffer_size = 2M      # Sequential scan bufferread_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.

Bash
tmp_table_size = 64M         # Maximum size for in-memory temporary tablesmax_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:

Bash
max_allowed_packet = 256M  # Maximum packet size (important for large BLOBs)net_buffer_length = 16K    # Buffer for client/server communicationskip-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:

Bash
slow_query_log = 1                 # Enable slow query loggingslow_query_log_file = /var/log/mysql/slow-query.loglong_query_time = 2                # Log queries taking longer than 2 secondslog_queries_not_using_indexes = 1  # Log queries without index usagelog_slow_admin_statements = 1      # Log slow admin commands (ALTER, etc.) # Extended logging optionslog_slow_verbosity = query_plan  # Include query execution plan in logmin_examined_row_limit = 1000    # Only log queries examining 1000+ rows

View recent slow queries with the command below:

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

Analyze and summarize the slow query log with:

Bash
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:

Bash
performance_schema = ON # Enable key consumersperformance-schema-instrument = 'stage/%=ON'performance-schema-consumer-events-stages-current = ONperformance-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:

SQL
-- Identify slowest queriesSELECT * FROM performance_schema.events_statements_summary_by_digestWHERE SUM_TIMER_WAIT > 1000000ORDER BY SUM_TIMER_WAIT DESCLIMIT 10; -- Monitor I/O performanceSELECT * FROM performance_schema.file_summary_by_instanceORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESCLIMIT 10; -- Check table I/O waitsSELECT * FROM performance_schema.table_io_waits_summary_by_tableORDER BY SUM_TIMER_WAIT DESCLIMIT 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:

Bash
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.

Bash
[mysqld]# ===========================# BASIC SETTINGS# ===========================user = mysqldatadir = /var/lib/mysqlsocket = /var/run/mysqld/mysqld.sockpid-file = /var/run/mysqld/mysqld.pid # ===========================# INNODB SETTINGS# ===========================# Buffer Pool Configuration (70-80% of RAM for dedicated server)innodb_buffer_pool_size = 24Ginnodb_buffer_pool_instances = 8innodb_buffer_pool_chunk_size = 128M # Log Configurationinnodb_log_file_size = 2Ginnodb_log_files_in_group = 2innodb_log_buffer_size = 64Minnodb_flush_log_at_trx_commit = 1innodb_flush_method = O_DIRECT # I/O Configuration (adjust based on your storage IOPS)innodb_io_capacity = 2000innodb_io_capacity_max = 4000innodb_read_io_threads = 8innodb_write_io_threads = 8innodb_thread_concurrency = 0 # Table Configurationinnodb_file_per_table = 1innodb_open_files = 4000innodb_adaptive_flushing = 1innodb_adaptive_flushing_lwm = 10innodb_purge_threads = 4 # ===========================# CONNECTION MANAGEMENT# ===========================max_connections = 300max_connect_errors = 1000 # Thread Pool (recommended for web applications)thread_handling = pool-of-threadsthread_pool_size = 8thread_pool_max_threads = 500thread_pool_min_threads = 8 # If NOT using thread pool, configure thread cache:# thread_cache_size = 100 # ===========================# TABLE AND FILE CACHE# ===========================table_open_cache = 4000table_definition_cache = 2000open_files_limit = 65535 # ===========================# QUERY CACHE (DISABLED)# ===========================query_cache_type = 0query_cache_size = 0 # ===========================# QUERY BUFFERS# ===========================sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 4Mjoin_buffer_size = 2M # ===========================# TEMPORARY TABLES# ===========================tmp_table_size = 64Mmax_heap_table_size = 64Mtmpdir = /var/lib/mysql-tmp # ===========================# NETWORK SETTINGS# ===========================max_allowed_packet = 256Mnet_buffer_length = 16Kskip-name-resolve = 1 # ===========================# SLOW QUERY LOG# ===========================slow_query_log = 1slow_query_log_file = /var/log/mysql/slow-query.loglong_query_time = 2log_queries_not_using_indexes = 1log_slow_verbosity = query_planmin_examined_row_limit = 1000 # ===========================# PERFORMANCE SCHEMA# ===========================performance_schema = ONperformance-schema-instrument = 'stage/%=ON'performance-schema-consumer-events-stages-current = ONperformance-schema-consumer-events-statements-current = ON # ===========================# ARIA STORAGE ENGINE# ===========================aria_pagecache_buffer_size = 1Garia_sort_buffer_size = 256M # ===========================# BINARY LOGGING (for replication/backup)# ===========================server-id = 1log_bin = /var/log/mysql/mysql-binbinlog_format = ROWexpire_logs_days = 7sync_binlog = 1 # ===========================# CHARACTER SET# ===========================character-set-server = utf8mb4collation-server = utf8mb4_unicode_ci [client]socket = /var/run/mysqld/mysqld.sockdefault-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:

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

Set persistent limits in:

Bash
sudo nano /etc/security/limits.conf

Add these lines to the file:

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

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

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

Create a limits configuration file with the following command:

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

Add these LimitNOFILE settings to the file:

Bash
[Service]LimitNOFILE=102400LimitNPROC=10240

Reload systemd and restart MariaDB to apply the changes:

Bash
sudo systemctl daemon-reloadsudo systemctl restart mariadb

Check if open_files_limit was applied with:

SQL
SHOW VARIABLES LIKE 'open_files_limit';

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

Bash
# Check current I/O schedulercat /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:

Bash
sudo swapoff -a

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

Alternatively, adjust swappiness with the commands below:

Bash
sudo sysctl vm.swappiness=1echo "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:

SQL
SHOW STATUS LIKE 'Innodb_buffer_pool_pages%';

Identify memory-consuming queries:

SQL
SELECT * FROM sys.memory_global_by_current_bytesLIMIT 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:

SQL
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:

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

Check for missing indexes:

Bash
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:

Bash
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.tablesGROUP BY table_schemaORDER BY SUM(data_length + index_length) DESC;"

Check binary log size with the command below:

Bash
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.

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

InnoDB buffer pool size is the most important setting, because it determines how much data and index can stay in RAM and directly impacts disk I/O pressure.

MariaDB documentation recommends swappiness=1 and explains that 0 can increase the risk of OOM killing under strong pressure on some platforms, so 1 is a safer low value.

The slow query log is simple and immediately actionable for query tuning, while Performance Schema is deeper and better for diagnosing waits, locks, and I/O patterns.

Prev PostAutomating SSL Certificate Renewal with Certbot Cron Jobs on Linux Dedicated Servers
Next PostAffordable AI Hosting for Indie Developers
Category:Performance & Scaling
Tag:

We use cookies to run PerLod and to understand how the site is used. You can accept everything, or open Manage to turn individual categories off. Cookie policy.