MySQL Tuner Configuration Guide

Overview

MySQL Tuner is a powerful Perl script designed to analyze your MySQL/MariaDB performance and provide actionable recommendations for optimizing your database configuration. By examining various performance metrics and statistics, it identifies areas where your database can be fine-tuned to achieve maximum efficiency and performance.

                Key Benefits:
                Automated performance analysis
Customized recommendations based on your server's actual usage patterns
Improved database efficiency and response times
Better resource utilization

            

MySQL Tuner Workflow

MySQL Tuner Optimization Process

Download MySQL Tuner

↓

Set Executable Permissions

↓

Run Initial Analysis

↓

Install WordPress Site(s)

↓

Wait 30+ Days for Data Collection

↓

Run MySQL Tuner Again

↓

Implement Recommendations

↓

Repeat Every 60-90 Days

⚠ Important Guidelines:

Run MySQL Tuner every 60 to 90 days, not continuously
Wait a minimum of 30 days between implementing recommendations and running the script again
Longer monitoring periods produce more accurate recommendations
Do NOT implement recommendations and immediately re-run the script

Installation and Setup

1Navigate to Home Directory

First, ensure you are in your user's home directory and clean up any unnecessary files:

cd ~
ls
rm fail2ban.db
rm phpmail-test.php

2Create MySQL Tuner Directory

Create a dedicated directory for MySQL Tuner and navigate into it:

mkdir mysqltuner
cd mysqltuner

3Download MySQL Tuner

Download the MySQL Tuner script using wget:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl -O mysqltuner.pl
Output: mysqltuner.pl (264KB) downloaded successfully

4Set Executable Permissions

Grant executable permissions to the script:

sudo chmod +x mysqltuner.pl
ls -l
Before: -rw-rw-r-- (664)
After:  -rwxrwxr-x (775)

Note: The chmod +x command adds execute permissions to the file, changing permissions from 664 to 775, which allows the script to be run.

5Run MySQL Tuner

Execute the MySQL Tuner script with sudo privileges:

sudo ./mysqltuner.pl

Understanding MySQL Tuner Output

Section	Description	Action Required
Storage Engine Statistics	Analysis of database storage engines in use	Review after site installation
Performance Metrics	Current database performance indicators	Monitor trends over time
Security Recommendations	Security-related configuration suggestions	Implement immediately
InnoDB Metrics	InnoDB storage engine specific statistics	Review after WordPress installation
Variables to Adjust	Specific configuration parameters to modify	Implement after 30+ days of data

Initial Run Results: When running MySQL Tuner before installing WordPress sites, you'll see a message stating "Further optimization is dependent on MariaDB having one or more databases installed." This is expected and normal. Ignore all recommendations at this stage.

Configuring MariaDB Open Files Limit

Open Files Limit Configuration Process

Check Current Limit (32,768)

↓

Create Configuration Directory

↓

Create limits.conf File

↓

Set New Limit (40,000)

↓

Reload systemd

↓

Restart MariaDB

↓

Verify New Limit

The open files limit controls how many files MariaDB can have open simultaneously. The default limit of 32,768 may be insufficient for busy WordPress sites. We'll increase this to 40,000, which should be suitable for most WordPress installations.

1Check Current Open Files Limit

First, identify the MariaDB process ID and check its current limits:

ps aux | grep mysql
Process ID: 54009

cat /proc/54009/limits
Max open files: 32768 (soft) / 32768 (hard)

2Navigate to systemd Directory

Change to the systemd system configuration directory:

cd /etc/systemd/system
ls

3Create MariaDB Service Directory

Create a drop-in configuration directory for MariaDB:

sudo mkdir mariadb.service.d
cd mariadb.service.d

Note: The .d suffix is a systemd convention for drop-in configuration directories that override or extend the main service file.

4Create limits.conf Configuration File

Create and edit the limits configuration file:

sudo nano limits.conf

Add the following content to the file:

[Service]
LimitNOFILE=40000

                    Important:
                    The "S" in "Service" must be uppercase
The "L" in "LimitNOFILE" must be uppercase
"NOFILE" must be completely uppercase
Proper capitalization is critical for the configuration to work

                

Save the file by pressing Ctrl+X, then Y, then Enter.

5Reload systemd and Restart MariaDB

Apply the changes by reloading systemd and restarting MariaDB:

sudo systemctl daemon-reload
sudo systemctl restart mariadb

What happens: The daemon-reload command forces systemd to re-read all configuration files, including our new limits.conf file. Restarting MariaDB applies these new settings to the database service.

6Verify the New Limit

Confirm that the new limit has been applied successfully:

ps aux | grep mysql
Process ID: 54457

cat /proc/54457/limits
Max open files: 40000 (soft) / 40000 (hard)

Success! The open files limit has been successfully increased from 32,768 to 40,000. If you encounter "too many open files" errors in the future, you can repeat this process with a higher value.

Best Practices and Recommendations

When to Run MySQL Tuner

Initial Run: After installation but before WordPress setup (to establish baseline)
First Real Analysis: 30-60 days after WordPress installation
Regular Maintenance: Every 60-90 days for ongoing optimization
After Major Changes: Wait 30 days after implementing recommendations before re-running

Implementing Recommendations

Critical Guidelines:

Never implement all recommendations at once
Make changes incrementally to isolate effects
Document each change you make
Monitor performance after each change
If unsure about a recommendation, seek expert guidance

Troubleshooting

Issue	Possible Cause	Solution
Permission denied when running script	Missing execute permissions	Run: `sudo chmod +x mysqltuner.pl`
Too many open files error	Open files limit too low	Increase LimitNOFILE value beyond 40,000
Configuration not applying	Systemd not reloaded	Run: `sudo systemctl daemon-reload`
Script not found	Incorrect directory	Navigate to ~/mysqltuner directory

Configuration File Reference

limits.conf File Structure

# MariaDB Open Files Limit Configuration
# Location: /etc/systemd/system/mariadb.service.d/limits.conf

[Service]
LimitNOFILE=40000

# Alternative configurations:
# LimitNOFILE=50000  # For high-traffic sites
# LimitNOFILE=60000  # For very large installations

Customization Tips: While 40,000 is suitable for most WordPress sites, you may need to adjust this value based on your specific requirements. High-traffic sites with multiple databases may benefit from higher limits.

Next Steps

After completing these configurations, proceed to:

Install your first WordPress site
Allow the site to run for at least 30 days
Return to MySQL Tuner for performance optimization
Continue with PHP 8.3 hardening and optimization

Summary

In this guide, we've covered the installation and configuration of MySQL Tuner, a powerful tool for database performance optimization. We've also configured MariaDB's open files limit to ensure your database can handle the demands of WordPress sites efficiently.

Remember that MySQL Tuner is most effective when used as part of a regular maintenance schedule, not as a continuous monitoring tool. Patience is key: allow sufficient time for data collection before acting on recommendations, and always implement changes incrementally.

Configuration Complete! Your MariaDB installation is now optimized with proper open files limits, and MySQL Tuner is ready for use once your WordPress sites are operational.