Optimizing MariaDB for Performance and Stability

Optimizing MariaDB is essential to get optimum performance.  MariaDB is a powerful relational database management system, but without proper tuning, it can suffer from slow performance, high memory usage, and even crashes. This article provides a comprehensive guide to optimizing MariaDB based on your server’s workload, particularly when hosting multiple WordPress sites. The recommendations here are backed by real-world data analysis and proven best practices.

Final Optimization Script

#!/bin/bash
# MariaDB Tuning Script
# This script updates MariaDB configuration based on collected data.

CONFIG_FILE=”/etc/mysql/my.cnf”
BACKUP_FILE=”/etc/mysql/my.cnf.backup.$(date +%Y%m%d%H%M%S)”

# Backup existing configuration
cp “$CONFIG_FILE” “$BACKUP_FILE”
echo “Backup of my.cnf created at $BACKUP_FILE”

# Apply optimized settings
cat <<EOL >> “$CONFIG_FILE”
[mysqld]
# Memory Optimization
innodb_buffer_pool_size=1G # 50-75% of available RAM; increased from 128M
innodb_log_file_size=256M # Between 128M-512M, adjusted for crash recovery
innodb_flush_log_at_trx_commit=2 # Reduces disk IO; possible values: 0, 1, 2
innodb_flush_method=O_DIRECT # Avoids double buffering
innodb_io_capacity=500 # Increased from 200 for better disk performance

# Connection and Cache Tuning
max_connections=200 # Based on workload; default was 151
thread_cache_size=64 # Number of reusable threads; improved performance
query_cache_size=16M # Query caching; range: 0-64M
query_cache_limit=1M # Max query size in cache; range: 256K-4M

# Temporary Tables & Sort Buffers
tmp_table_size=64M # Increased from 16M; range: 32M-128M
max_heap_table_size=64M # Matches tmp_table_size; range: 32M-128M
sort_buffer_size=4M # Default 2M; range: 2M-8M
join_buffer_size=8M # Improved for large joins; range: 4M-16M

# Logging and Slow Queries
slow_query_log=ON # Enables slow query logging
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=2 # Log queries taking longer than 2 seconds; range: 1-10s

# Table Open Cache
table_open_cache=4000 # Increased from 6000; prevents excessive table opening
open_files_limit=65535 # Increased for handling large number of files

# Performance Schema
enable-performance-schema=0 # Disabling for reducing memory overhead

EOL

echo “Optimized MariaDB settings applied. Restarting MariaDB…”
systemctl restart mariadb

if systemctl status mariadb | grep -q “active (running)”; then
echo “MariaDB restarted successfully with optimized settings.”
else
echo “MariaDB restart failed. Check logs for issues.”
fi

Why Optimizing MariaDB Matters

Performance tuning in MariaDB is essential because:

• Faster Queries: Optimizing buffer sizes and cache ensures quick data retrieval.
• Reduced Crashes: Prevents memory exhaustion issues that cause frequent crashes.
• Efficient Resource Utilization: Proper tuning helps MariaDB make the best use of available CPU, RAM, and disk I/O.

Key Changes in the Optimization Script – MariaDB performance tuning

Below, we explain each setting adjusted in our tuning script, why it was changed, and the recommended value ranges.

1. Memory Optimization

innodb_buffer_pool_size

• Change: Increased from 128M to 1G
• Reason: The InnoDB buffer pool caches frequently accessed data and indexes. Since MariaDB mainly uses InnoDB storage, increasing this value improves performance.
• Recommended Range: 50% to 75% of total RAM

innodb_log_file_size

• Change: Increased from 100M to 256M
• Reason: Affects transaction log storage and crash recovery. Larger log file sizes help manage high transaction volumes efficiently.
• Recommended Range: 128M to 512M

innodb_flush_log_at_trx_commit

• Change: Set to 2
• Reason: Reduces disk I/O while maintaining reasonable data safety. A setting of 1 ensures full ACID compliance but can slow down writes.
• Recommended Values: 0 (fastest but unsafe), 1 (safe but slow), 2 (good balance)

innodb_flush_method

• Change: Set to O_DIRECT
• Reason: Prevents double-buffering, improving disk performance.
• Recommended Values: O_DIRECT (for SSDs), fsync (for HDDs)

innodb_io_capacity

• Change: Increased from 200 to 500
• Reason: Defines how many I/O operations InnoDB performs per second. Higher values improve disk throughput.
• Recommended Range: 200 to 2000 (higher for SSDs)

2. Connection and Cache Tuning

max_connections

• Change: Increased from 151 to 200
• Reason: Ensures more concurrent users can access the database without connection failures.
• Recommended Range: 100 to 500 (depends on server resources)

thread_cache_size

• Change: Set to 64
• Reason: Helps MariaDB reuse threads, reducing CPU overhead from frequent thread creation.
• Recommended Range: 32 to 128

query_cache_size

• Change: Set to 16M
• Reason: Enables caching of frequent queries to reduce execution time.
• Recommended Range: 0 (if using modern caching methods) to 64M

query_cache_limit

• Change: Set to 1M
• Reason: Prevents very large queries from consuming excessive cache space.
• Recommended Range: 256K to 4M

3. Temporary Tables & Sort Buffers

tmp_table_size & max_heap_table_size

• Change: Increased from 16M to 64M
• Reason: Reduces the need for disk-based temporary tables, speeding up complex queries.
• Recommended Range: 32M to 128M

sort_buffer_size

• Change: Increased from 2M to 4M
• Reason: Helps improve sorting efficiency in queries.
• Recommended Range: 2M to 8M

join_buffer_size

• Change: Increased from 4M to 8M
• Reason: Reduces query execution time for complex joins.
• Recommended Range: 4M to 16M

4. Logging and Slow Queries

slow_query_log

• Change: Enabled
• Reason: Helps identify slow queries affecting performance.
• Recommended Value: ON

long_query_time

• Change: Reduced from 10s to 2s
• Reason: Captures more slow queries for analysis and optimization.
• Recommended Range: 1s to 10s

5. Table Open Cache

table_open_cache

• Change: Increased from 6000 to 4000
• Reason: Helps MariaDB store more table structures in memory, reducing overhead (part of MariaDB best practices).
• Recommended Range: 2000 to 10000

open_files_limit

• Change: Increased to 65535
• Reason: Allows MariaDB to open more file descriptors, preventing crashes under high load.
• Recommended Range: 50000 to 100000

6. Performance Schema

enable-performance-schema

• Change: Disabled
• Reason: Reduces memory overhead, improving performance.
• Recommended Value: OFF (for most setups)

Conclusion – Optimize MySQL for WordPress

By applying these optimizations, MariaDB will be better suited to handle high-traffic WordPress sites efficiently. Improve MariaDB speed also. Performance improvements will be noticeable, especially in query execution time and system stability. If your workload changes, revisit these settings periodically to fine-tune them accordingly.