🗄️ MariaDB Database Optimization Guide

Professional Server Administration & Database Tuning

angelscript

📋 Introduction

This comprehensive guide covers MariaDB database optimization for WordPress hosting environments. While WordPress optimization plugins focus on individual site databases, this guide addresses system-level MariaDB optimization for improved efficiency and performance across all databases.

🎯 Key Objectives:
  • Optimize MariaDB database management system performance
  • Configure InnoDB buffer pool and log buffer settings
  • Use MySQLTuner for performance analysis and recommendations
  • Monitor and manage database memory usage
  • Implement proper maintenance schedules

🔧 MySQLTuner Overview

MySQLTuner is a powerful script that analyzes your MariaDB configuration and provides recommendations for optimal performance and stability. Proper usage is critical for maintaining a healthy database environment.

⚠️ Important Guidelines:
  • Frequency: Run MySQLTuner once every 60-90 days maximum
  • Traffic Requirement: Ensure your sites have active traffic for accurate recommendations
  • Implementation: Add variables one at a time and restart MariaDB after each change
  • Support: Consult the course Q&A for advice before implementing recommendations

Running MySQLTuner

Navigate to the MySQLTuner directory and execute the script with sudo privileges:

cd ~/MySQLTuner/
ls -l
sudo ./mysqltuner

⚙️ InnoDB Configuration

Understanding InnoDB Components

InnoDB is the default storage engine for MariaDB and MySQL. Two critical parameters affect its performance: the log buffer size and the buffer pool size.

InnoDB Memory Architecture

Application
Data Requests
→
Buffer Pool
Cached Data & Indexes
→
Log Buffer
Transaction Changes
→
Disk
Persistent Storage

1. InnoDB Log Buffer Size

The InnoDB log buffer temporarily stores transaction data before writing it to disk log files. Increasing this buffer reduces disk write frequency and improves write performance.

Default Value: 16MB
Recommended Value: 32MB
Purpose: Caches data changes before flushing to InnoDB log files

Check Current Log Buffer Size

First, log in to MariaDB and check the current setting:

sudo mariadb
SHOW VARIABLES LIKE 'innodb_log_buffer_size';
exit;

Configure Log Buffer Size

Edit the main MariaDB configuration file:

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

Add the following directive in the InnoDB section (typically after innodb_log_file_size):

innodb_log_buffer_size = 32M

Save the file (Ctrl+O, Enter, Ctrl+X) and restart MariaDB:

sudo systemctl restart mariadb
✓ Safe to Restart: Changes to innodb_log_buffer_size can be applied with a simple restart.

2. InnoDB Buffer Pool Size

The buffer pool is the most critical InnoDB memory component. It caches frequently accessed table data and indexes in RAM, dramatically reducing disk I/O operations.

🎯 Standard Configuration:
  • Recommended: 80% of total server RAM
  • Purpose: Prevent memory swapping to disk
  • Example: For a 1GB RAM server, set buffer pool to 800MB

Understanding Memory Swapping

What is Swapping?

Swapping occurs when the system moves data from RAM to disk storage due to insufficient memory. For databases, this causes:

  • Slower query execution
  • Increased latency
  • Reduced throughput
  • Potential server crashes

Monitor Buffer Pool Usage

Check actual memory consumption of the InnoDB buffer pool:

sudo mariadb
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_bytes_data';

The query returns the value in bytes. Remove the last six digits to convert to megabytes.

Example Interpretation:
Result: 6,700,000 bytes = 6.7 MB
Server RAM: 1 GB (1,000 MB)
Buffer Pool Limit: 80% = 800 MB
Current Usage: 6.7 MB / 800 MB = 0.84% (very low)

Buffer Pool Usage Visualization

Current Usage Example

0.84%

6.7 MB used out of 800 MB available

🚨 Critical Thresholds:
  • Monitor at 25-30% usage: Begin planning for RAM upgrade
  • Action Required: Take screenshots and consult course Q&A
  • Prevention: Regular monitoring prevents performance degradation

📊 Database Monitoring Queries

View Database Sizes

Monitor the size of all databases on your server:

SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.tables GROUP BY table_schema;

View InnoDB Database Sizes

Filter to show only InnoDB engine databases:

SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.tables WHERE engine = 'InnoDB' GROUP BY table_schema;

View Buffer Pool Usage by Database

See how much buffer pool memory each database consumes:

SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.tables WHERE engine = 'InnoDB' GROUP BY table_schema;
💡 Expected Results:
For WordPress sites using exclusively InnoDB tables, the database size and buffer pool usage will be identical. This is normal and indicates all tables use the InnoDB storage engine.

View Table I/O Statistics

Monitor read/write operations and performance metrics per table:

SELECT object_schema AS 'Database', object_name AS 'Table', COUNT_READ AS 'Reads', COUNT_WRITE AS 'Writes', SUM_TIMER_READ / 1000000000 AS 'Read Time (ms)', SUM_TIMER_WRITE / 1000000000 AS 'Write Time (ms)' FROM performance_schema.table_io_waits_summary_by_table ORDER BY SUM_TIMER_READ + SUM_TIMER_WRITE DESC;

Understanding I/O Statistics

Column Description
Database Name of the database
Table Name of the table
Reads Number of read operations performed
Writes Number of write operations performed
Read Time (ms) Total time spent on read operations in milliseconds
Write Time (ms) Total time spent on write operations in milliseconds

🛠️ Database Maintenance Operations

Optimize All Databases

Database optimization reorganizes physical storage, reduces fragmentation, reclaims unused space, and improves query performance.

sudo mysqlcheck --optimize --all-databases
📝 Expected Message:
"Table does not support optimize, doing recreate and analyze instead"

This is not an error. It indicates MySQL is using an alternative optimization method that recreates the table and updates index statistics. This approach ensures proper optimization even for tables that don't support the traditional OPTIMIZE TABLE command.

Auto-Repair Databases

Automatically check and repair corrupted tables:

sudo mysqlcheck --auto-repair --check --all-databases
✓ Successful Result: "All done" with no errors reported

Maintenance Process Flow

Check
Scan for errors
→
Repair
Fix corruption
→
Optimize
Reorganize data
→
Analyze
Update statistics

⚠️ Critical: InnoDB Log File Size

Changing the InnoDB log file size requires a specific procedure to prevent data corruption.

🛑 CRITICAL PROCEDURE:
  1. STOP the MariaDB service first
  2. MODIFY the innodb_log_file_size directive
  3. START the MariaDB service

⚠️ WARNING: If you restart MariaDB instead of stopping, modifying, and then starting, you may corrupt InnoDB tables!

Correct Procedure for Log File Size Changes

sudo systemctl stop mariadb

Edit the configuration file:

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

Modify the directive:

innodb_log_file_size = 200M

Start the service:

sudo systemctl start mariadb

Comparison: Safe vs Unsafe Methods

✓ SAFE METHOD ✗ UNSAFE METHOD
1. Stop service
2. Modify config
3. Start service 		1. Modify config
2. Restart service
⚠️ Risk of corruption!

📈 Interpreting MySQLTuner Results

Performance Metrics Section

The Performance Metrics section provides critical information about memory usage and server capacity.

Metric Description Action Threshold
Maximum Reached Memory Highest memory usage achieved Monitor if approaching 150-200%
Maximum Possible Memory Theoretical maximum if all connections used Should not exceed server RAM
Overall Possible Memory Usage Percentage of total memory that could be used Take action at 150-200%

Common Recommendations

First-Run Recommendations (Can be ignored):
  • "MySQL started within the last 24 hours - recommendations may be inaccurate"
  • "Reduce your overall MySQL memory footprint"
  • "Dedicate this server to your database for highest performance"
⚠️ When Memory Usage Reaches 150-200%:
  1. Take a screenshot of the Performance Metrics section
  2. Post in the course Q&A MySQLTuner thread
  3. Request advice on reducing max_connections
  4. Default connections: 151 (may need reduction)

Memory Usage Threshold Examples

Memory Usage Status

Normal (127%)

127% - Normal

Warning (175%)

175% - Take Action

Critical (200%+)

200%+ - Urgent!

✅ Implementation Best Practices

Variable Adjustment Strategy

Step 1
Add one variable
→
Step 2
Restart MariaDB
→
Step 3
Test functionality
→
Step 4
Monitor performance
✓ Advantages of Incremental Changes:
  • Easier troubleshooting if issues arise
  • Clear cause-and-effect relationships
  • Ability to roll back specific changes
  • Better understanding of performance impacts

When to Seek Help

Use the course Q&A MySQLTuner featured question thread when:

  • First time receiving variables to adjust
  • Uncertain about implementing recommendations
  • Memory usage reaches 150-200%
  • Buffer pool usage exceeds 25-30%
  • Experiencing performance degradation
  • Need guidance on connection limits

🚀 Caching for Database Optimization

Proper caching implementation is crucial for reducing database queries and improving overall server performance.

💡 Caching Benefits:
  • Page Caching: Stores rendered HTML pages, eliminating repeated database queries
  • Object Caching: Stores database query results in memory (Redis, Memcached)
  • Combined Impact: Drastically reduces database load and improves response times

Request Flow: Without vs With Caching

Without Caching With Caching
Request → PHP → Database
Query → Process → Render
Slow 		Request → Cache → Response
(Database bypassed)
Fast

By implementing proper page and object caching, database queries are reduced dramatically, leading to improved server performance and better resource utilization.

📚 Quick Reference Commands

System Updates

sudo apt update && sudo apt upgrade -y && sudo apt autoremove -y

MariaDB Service Management

sudo systemctl restart mariadb
sudo systemctl stop mariadb
sudo systemctl start mariadb
sudo systemctl status mariadb

Configuration File Locations

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

Database Maintenance

sudo mysqlcheck --optimize --all-databases
sudo mysqlcheck --auto-repair --check --all-databases

Monitor Buffer Pool

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_bytes_data';

Run MySQLTuner

cd ~/MySQLTuner/
sudo ./mysqltuner

📋 Additional Server Tasks Reference

WordPress Updates with Permission Management

Default PHP Pool User - Standard Permissions

cd /var/www/example.com/
sudo ls -l public_html/
sudo chown -R PHP_POOL_USER:PHP_POOL_USER public_html/
sudo find /var/www/example.com/public_html/ -type d -exec chmod 770 {} \;
sudo find /var/www/example.com/public_html/ -type f -exec chmod 660 {} \;

After setting permissions, open the WordPress Dashboard to run updates, then restart PHP-FPM:

sudo systemctl reload php8.3-fpm

Default PHP Pool User - Hardened Permissions

cd /var/www/example.com/
sudo chown -R PHP_POOL_USER:PHP_POOL_USER public_html/
sudo find /var/www/example.com/public_html/ -type d -exec chmod 550 {} \;
sudo find /var/www/example.com/public_html/ -type f -exec chmod 440 {} \;
sudo find /var/www/example.com/public_html/wp-content/ -type d -exec chmod 770 {} \;
sudo find /var/www/example.com/public_html/wp-content/ -type f -exec chmod 660 {} \;

Security Scanning Tools

ClamAV Installation and Usage

sudo apt update
sudo apt install clamav

Disable automatic updates:

sudo systemctl stop clamav-freshclam
sudo systemctl disable clamav-freshclam

Manual database update and scan:

sudo freshclam
sudo clamscan -r /path/to/scan

RKHunter (Rootkit Hunter)

sudo apt install rkhunter
sudo rkhunter --propupd
sudo rkhunter --checkall --sk

View log files:

sudo cat /var/log/rkhunter.log
sudo less /var/log/rkhunter.log

Remove automatic cron jobs:

cd /etc/cron.daily/
sudo rm rkhunter
cd /etc/cron.weekly/
sudo rm rkhunter

Disk Space Management

df -h
sudo apt autoremove && sudo apt clean
sudo journalctl --vacuum-time=1days

View directory sizes:

cd /
du -ah --max-depth=1 | sort -h

🎯 Summary

Optimizing MariaDB requires careful planning, systematic implementation, and ongoing monitoring. Key takeaways include:

  • Run MySQLTuner sparingly (every 60-90 days) with active traffic
  • Implement configuration changes incrementally
  • Monitor buffer pool usage regularly (alert at 25-30%)
  • Follow proper procedures for InnoDB log file size changes
  • Leverage caching to reduce database load
  • Maintain regular database optimization and repair schedules
  • Seek guidance when memory usage approaches critical thresholds
✓ Next Steps:
  1. Verify current InnoDB buffer pool configuration
  2. Adjust log buffer size to 32MB
  3. Run baseline MySQLTuner analysis
  4. Set up monitoring schedule
  5. Implement page and object caching
  6. Schedule regular maintenance tasks