Optimizing MariaDB Performance for WordPress

Introduction

This guide will help you optimize MariaDB performance for WordPress installations running on LEMP stacks (Linux, Nginx, MariaDB, PHP). Using custom configuration files, these optimizations are tailored for typical VPS setups with different CPU and RAM specifications.

Understanding MariaDB Configuration

MariaDB’s configuration on Debian-based systems follows a specific hierarchy:

/etc/mysql/
├── mariadb.conf.d/
│   ├── 50-server.cnf
│   ├── 50-client.cnf
│   └── 60-custom.cnf  # Your custom configurations
└── my.cnf

Custom Configuration Best Practices

Create your custom configuration file:

touch /etc/mysql/mariadb.conf.d/60-custom.cnf

This file loads after the default configurations, ensuring your settings take precedence.

Why Use 60-custom.cnf?

MariaDB reads configuration files in alphabetical order from several directories. Files are read in the following order:

By naming our file 60-custom.cnf, we ensure it’s read after the default configurations (which typically start with 50) but before any user-specific configurations (which might start with 70 or higher). This allows our optimizations to:

• Override default settings without modifying original configuration files
• Maintain better organization and documentation
• Easily revert changes by removing a single file
• Keep customizations separate from MariaDB package updates

Prerequisites

1. A running LEMP stack with MariaDB installed.
2. Root access to your server.
3. Basic understanding of your server specifications (RAM, CPU cores, and storage type).
4. Monitoring tools to analyze system metrics.

Step 1: Gather System Information

Collecting system metrics will guide us in tailoring configurations to fit available resources.

# Check CPU cores
nproc

# Check available RAM
free -h

# Check storage type (SSD or HDD)
lsblk -d -o name,rota  # Output: 0 for SSD, 1 for HDD

Step 2: Create the Configuration File

Create the custom configuration file for MariaDB.

vi /etc/mysql/mariadb.conf.d/60-custom.cnf

Add the following basic structure:

[mysqld]
# Custom configurations will go here

Step 3: Configuration Examples for Different VPS Setups

1. Configuration for 2-Core, 4GB RAM VPS

[mysqld]
# SQL Mode
sql_mode = "NO_ENGINE_SUBSTITUTION"

# InnoDB Settings
innodb_buffer_pool_size = 1536M               # 25-40% of RAM for InnoDB buffer pool
innodb_buffer_pool_instances = 2              # Match core count for smaller VPS
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
innodb_read_io_threads = 2                    # Match 2 cores
innodb_write_io_threads = 2
innodb_file_per_table = 1

# Flush and Logging Settings
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
sync_binlog = 100
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M

# Thread Settings
thread_handling = pool-of-threads
thread_pool_size = 2
thread_cache_size = 32

# Table Cache Settings
table_open_cache = 2048
table_definition_cache = 1024
table_open_cache_instances = 2

# Memory Settings
join_buffer_size = 2M
sort_buffer_size = 4M
read_rnd_buffer_size = 1M
tmp_table_size = 32M
max_heap_table_size = 32M

# Connection Settings
max_connections = 100

# Query Cache
query_cache_type = 0
query_cache_size = 0

# Performance Schema
performance_schema = OFF

# Additional Optimizations
innodb_change_buffering = all
innodb_checksum_algorithm = crc32
innodb_log_compressed_pages = OFF
max_allowed_packet = 32M

2. Configuration for 4-Core, 8GB RAM VPS

[mysqld]
# SQL Mode
sql_mode = "NO_ENGINE_SUBSTITUTION"

# InnoDB Settings
innodb_buffer_pool_size = 4G                 # ~50% of RAM for InnoDB buffer pool
innodb_buffer_pool_instances = 4             # Match 4 cores
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_file_per_table = 1

# Flush and Logging Settings
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
sync_binlog = 100
innodb_log_file_size = 384M
innodb_log_buffer_size = 24M

# Thread Settings
thread_handling = pool-of-threads
thread_pool_size = 4
thread_cache_size = 48

# Table Cache Settings
table_open_cache = 4096
table_definition_cache = 2048
table_open_cache_instances = 4

# Memory Settings
join_buffer_size = 4M
sort_buffer_size = 6M
read_rnd_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M

# Connection Settings
max_connections = 150

# Query Cache
query_cache_type = 0
query_cache_size = 0

# Performance Schema
performance_schema = ON

# Additional Optimizations
innodb_change_buffering = all
innodb_checksum_algorithm = crc32
innodb_log_compressed_pages = OFF
max_allowed_packet = 64M

3. Configuration for 8-Core, 16GB RAM VPS

[mysqld]
# SQL Mode
sql_mode = "NO_ENGINE_SUBSTITUTION"

# InnoDB Settings
innodb_buffer_pool_size = 8G                # ~75% of RAM for InnoDB buffer pool
innodb_buffer_pool_instances = 8             # One instance per GB of buffer pool
innodb_io_capacity = 3000
innodb_io_capacity_max = 6000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_file_per_table = 1

# Flush and Logging Settings
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
sync_binlog = 100
innodb_log_file_size = 512M
innodb_log_buffer_size = 32M

# Thread Settings
thread_handling = pool-of-threads
thread_pool_size = 8
thread_cache_size = 64

# Table Cache Settings
table_open_cache = 8192
table_definition_cache = 4096
table_open_cache_instances = 8

# Memory Settings
join_buffer_size = 4M
sort_buffer_size = 8M
read_rnd_buffer_size = 2M
tmp_table_size = 256M
max_heap_table_size = 256M

# Connection Settings
max_connections = 300

# Query Cache
query_cache_type = 0
query_cache_size = 0

# Performance Schema
performance_schema = ON

# Additional Optimizations
innodb_change_buffering = all
innodb_checksum_algorithm = crc32
innodb_log_compressed_pages = OFF
max_allowed_packet = 64M

Explanation of Key Directives

Below is an explanation of the directives used, their purposes, and the reasoning behind each value:

• innodb_buffer_pool_size: The innodb_buffer_pool_size directive allocates memory for caching InnoDB data and indexes, which helps reduce disk I/O by keeping frequently accessed data in memory. This setting is critical for MariaDB performance, especially on servers running dynamic applications like WordPress.
  • Recommended Allocation: Adjust innodb_buffer_pool_size to approximately 60-70% of total available RAM on systems with adequate resources. However, for smaller systems with limited RAM (e.g., 2-4 GB), it’s better to start with 25-40% to avoid starving other essential LEMP services like FastCGI and Redis cache. Monitoring overall memory usage is recommended to ensure this setting optimally balances MariaDB performance with the needs of other services.
  • Fractional Values Caution: MariaDB may fail to start if innodb_buffer_pool_size is set to fractional values (e.g., 1.5G). Use precise values in megabytes (e.g., 1536M) to prevent parsing issues and ensure reliable startup.
  • This approach allows for effective use of available memory without compromising other stack components. Adjust as needed based on performance monitoring and memory demands.
• innodb_io_capacity and innodb_io_capacity_max: These determine the number of IOPS (input/output operations per second) that InnoDB will allow. Higher values for SSDs to maximize I/O efficiency.
• innodb_flush_log_at_trx_commit: Setting this to 2 reduces write frequency for faster performance at a minor data-loss risk in crashes, suitable for web applications where slight risk is acceptable.
• thread_handling and thread_pool_size: Configures thread management based on CPU cores. pool-of-threads manages connections more efficiently in high-load environments.
• table_open_cache and table_definition_cache: Sets the number of open tables allowed, optimizing memory usage by setting values based on server capabilities.
• tmp_table_size and max_heap_table_size: Limits temporary table sizes in RAM for efficient handling of transient data, minimizing reliance on disk for faster operations.
• query_cache_type and query_cache_size: Set to 0 as Redis cache is enabled.
• innodb_flush_method: Set to O_DIRECT to prevent double-buffering, which is beneficial on servers with SSD storage.
• sync_binlog: Setting sync_binlog = 100 is a balanced choice that syncs the binary log every 100 transactions, preserving write performance while retaining recovery points.

Step 4: Apply Changes

# Test configuration
mysqld --verbose --help | grep -A 1 "Default options"
mariadb --verbose --help | grep -A 1 "Default options"

# Restart MariaDB
systemctl restart mariadb

# Verify service status
systemctl status mariadb

Step 5: Monitor Performance

Monitor your database performance using:

-- Check global status variables
SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW GLOBAL STATUS LIKE 'Created_tmp_%';
SHOW GLOBAL STATUS LIKE 'Handler_%';

-- Check current settings
SHOW VARIABLES LIKE 'innodb_io%';
SHOW VARIABLES LIKE 'thread%';
SHOW VARIABLES LIKE 'table_open%';

Step 6: Fine-Tuning

Monitor your WordPress site’s performance for at least a week. Key metrics to watch:

1. Slow Query Log entries
2. CPU usage
3. Memory usage
4. Disk I/O
5. Connection count

Tips for Limited Resource VPS (4GB RAM)

• Keep performance_schema = OFF
• Minimize buffer sizes
• Monitor swap usage (free -h)
• Consider enabling slow query log temporarily to identify issues:

  SET GLOBAL slow_query_log = 'ON';
  SET GLOBAL long_query_time = 2;

Common Issues and Solutions

1. High CPU Usage
   • Decrease thread_pool_size
   • Enable slow query log to identify problematic queries
2. Memory Issues
   • Reduce buffer sizes
   • Lower max_connections
   • Consider disabling performance_schema on lower RAM systems
3. Disk I/O Problems
   • Adjust innodb_io_capacity
   • Enable innodb_file_per_table
   • Consider faster storage

Security Considerations

1. Always backup your database before making configuration changes
2. Keep the MariaDB configuration file permissions restricted:

   chmod 644 /etc/mysql/mariadb.conf.d/60-custom.cnf
   chown root:root /etc/mysql/mariadb.conf.d/60-custom.cnf

Conclusion

This configuration guide provides optimization templates for common VPS configurations running WordPress on LEMP stacks. Remember that database optimization is an iterative process – monitor your site’s performance and adjust settings accordingly. Regular maintenance and updates are crucial for maintaining optimal performance.