WordPress sites can become slow over time. The database is often the main cause. When your site grows, the database works harder. This guide shows you how to fix this. We will cover the MariaDB slow query log setup for WordPress. This tool helps you find bad database requests. You can then fix them to make your site fast again. This guide is for beginners. We will use simple words and clear steps.
Quick Reference: MariaDB Slow Query Log Configuration for WordPress
Before we start, here is a quick look at the settings. You can use these values for your site.
Recommended Settings for Production WordPress Sites
For a live site, you want to catch real problems without slowing things down. Set the time to 1 second. Turn on logging. Do not log queries without indexes unless you are testing. This keeps your site fast.
Diagnostic Settings for Troubleshooting Slow Queries
When you are fixing a problem, you need more details. Set the time to 0.5 seconds. Turn on logging for queries without indexes. This gives you more data to read. Remember to turn it back to normal later.
Version Compatibility: MariaDB 10.6, 10.11 LTS, and 11.x
The settings are mostly the same for all versions. MariaDB 10.6, 10.11, and 11.x use the same variable names. You do not need to change your commands for different versions. The file paths might change slightly, but the core setup is identical.
Prerequisites for MariaDB Slow Query Log Setup on WordPress
Before we change any settings, you need to prepare. This part of the MariaDB slow query log setup for WordPress ensures you have the right tools.
Checking Your MariaDB Version
You need to know your database version. Open your terminal. Type this command: mysql -V. The system will show your version. Write it down. This helps if you need to read official docs later.
Required Server Access and Permissions
You must have root access to your server. You need to edit system files. You also need to restart services. If you use shared hosting, you cannot do this. You need a VPS or a dedicated server. Ask your host for root or sudo access.
Understanding WordPress Database Structure
WordPress stores data in tables. Posts, users, and settings are in different tables. When a page loads, WordPress asks the database for this data. If the database takes too long, the page loads slowly. The log will show you exactly which table request is slow.
How to Enable MariaDB Slow Query Log for WordPress (Temporary Setup)
Sometimes you just want to test the log. You can turn it on without restarting the server. This is a good way to start the MariaDB slow query log setup for WordPress safely.
Enable Slow Query Log Without Restarting MariaDB
Step 1: Open your terminal.
Step 2: Log into your database. Type: sudo mysql -u root -p
Step 3: Enter your password.
Step 4: Type this command to turn on the log: SET GLOBAL slow_query_log = ‘ON’;
Step 5: The log is now active. It will start recording immediately.
Set Query Time Threshold for WordPress Sites
You need to tell the database what “slow” means. By default, it is 10 seconds. This is too long for WordPress. Type this command: SET GLOBAL long_query_time = 1; This means any query taking more than 1 second is slow. You can change 1 to 0.5 for stricter testing.
Verify Temporary Slow Query Log Settings
Step 1: Stay in the database prompt.
Step 2: Type: SHOW VARIABLES LIKE ‘slow_query%’;
Step 3: Look at the results. You should see the file path and the status as ON.
Step 4: Type: SHOW VARIABLES LIKE ‘long_query_time’;
Step 5: Check if the value is 1.000000. If it is, your setup is working.
How to Configure MariaDB Slow Query Log Permanently for WordPress
Temporary settings disappear when the server restarts. To make the MariaDB slow query log setup for WordPress permanent, you must edit the config file.
Locate the Correct MariaDB Configuration File
The file location depends on your system. On Ubuntu or Debian, it is usually at /etc/mysql/mariadb.conf.d/50-server.cnf. On CentOS or AlmaLinux, it is at /etc/my.cnf.d/server.cnf. Open your terminal to find it.
Add Slow Query Log Settings for MariaDB 10.6
Step 1: Open the config file. Type: sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
Step 2: Find the [mysqld] section.
Step 3: Add these lines under that section:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 1
Step 4: Save the file and exit the editor.
Add Slow Query Log Settings for MariaDB 10.11 and 11.x
The settings for newer versions are the same. You just add the same lines to the [mysqld] section. The variable names have not changed. This makes the MariaDB slow query log setup for WordPress very consistent across updates.
Create Log File with Correct Permissions
The database needs permission to write to the log file. Step 1: Create the file. Type: sudo touch /var/log/mysql/mariadb-slow.log. Step 2: Set the owner. Type: sudo chown mysql:mysql /var/log/mysql/mariadb-slow.log. Step 3: Set permissions. Type: sudo chmod 640 /var/log/mysql/mariadb-slow.log. This keeps the log secure.
Restart MariaDB and Verify Configuration
Step 1: Restart the service. Type: sudo systemctl restart mariadb
Step 2: Check the status. Type: sudo systemctl status mariadb
Step 3: Ensure it says “active (running)”.
Step 4: Log into the database again. Type: sudo mysql -u root -p
Step 5: Verify the settings using the SHOW VARIABLES commands from the temporary section.
WordPress-Specific MariaDB Slow Query Log Settings
WordPress has special needs. You should tweak the MariaDB slow query log setup for WordPress to fit these needs.
Recommended Threshold Values for WordPress Sites
A normal WordPress page should load in under 1 second. The database part should take less than 0.2 seconds. Setting the threshold to 1 second is good for live sites. If you are debugging, set it to 0.5 seconds. Do not set it to 0, or the log will fill up too fast.
Logging Queries Without Indexes in WordPress
Sometimes a query is slow because it lacks an index. You can log these specifically. Add this line to your config file: log_queries_not_using_indexes = 1. This helps you find bad database designs. Be careful, as this can create huge logs on busy sites.
Handling WP-Cron and REST API Slow Queries
WordPress uses WP-Cron for background tasks. These can be slow. The REST API can also be slow. They will show up in your log. Do not panic. Check if they are normal background tasks. If they happen too often, consider setting up a real system cron job instead of WP-Cron.
Balancing Log Detail vs Site Performance
Writing to a log takes some server power. If you log every tiny delay, your site will slow down. The MariaDB slow query log setup for WordPress should balance detail and speed. Keep the threshold at 1 second for daily use. Only lower it when you are actively fixing a problem.
How to Set Up MariaDB Slow Query Log for WordPress on Docker
Many people use Docker for WordPress. The setup is a bit different here.
Configure Slow Query Log in Docker Compose
You need to edit your docker-compose.yml file. Find the mariadb or db service. Add a command section. Type this: command: –slow-query-log –long-query-time=1. This passes the settings directly to the database when it starts.
Mount Log Directory to Host System
You need to see the log outside the container. Add a volume mapping. Type this under volumes: ./logs:/var/log/mysql. This maps the container log folder to your host machine. Now you can read the log easily.
Access Slow Query Log from Docker Container
If you do not map the volume, you can still read it. Step 1: Find your container name. Type: docker ps. Step 2: Open a shell in the container. Type: docker exec -it your_container_name bash. Step 3: Read the log. Type: cat /var/log/mysql/mariadb-slow.log.
How to Analyze MariaDB Slow Query Log for WordPress Optimization
Collecting data is only half the work. You must read and understand it.
Read and Interpret Slow Query Log Entries
Open the log file. Each entry has a header. It shows the user, the host, and the time taken. Below the header is the actual SQL query. Look at the time first. Focus on queries that take several seconds. Then read the SQL to see which table it is asking for.
Identify Slow WordPress Plugins and Themes
WordPress plugins create their own tables. If you see a slow query asking for a table named wp_yourplugin_data, you found the problem. The plugin named “yourplugin” is causing the delay. You can disable it or look for an update. This is a major benefit of the MariaDB slow query log setup for WordPress.
Use pt-query-digest to Analyze WordPress Slow Logs
Reading raw logs is hard. Use a tool called pt-query-digest. It is part of Percona Toolkit. Step 1: Install it on your server. Step 2: Run it against your log. Type: pt-query-digest /var/log/mysql/mariadb-slow.log. It will give you a summary. It groups similar queries and shows the worst offenders.
Common WordPress Query Patterns to Watch For
Look for queries on the wp_options table. WordPress loads options on every page. If this is slow, your site will crawl. Look for queries with no LIMIT clause. These might be loading all posts at once. Fixing these patterns will greatly improve your site speed.
How to Manage and Rotate MariaDB Slow Query Log for WordPress
The log file will grow forever if you do not manage it. You need a plan to delete old data.
Configure Logrotate for MariaDB Slow Query Log
Linux uses logrotate to manage logs. Step 1: Create a new file. Type: sudo nano /etc/logrotate.d/mariadb-slow. Step 2: Add these rules: /var/log/mysql/mariadb-slow.log { daily rotate 7 compress delaycompress missingok notifempty create 640 mysql mysql }. Step 3: Save the file. This keeps the last 7 days of logs.
Prevent Disk Space Issues from Slow Query Logs
A busy site can create a huge log. If your disk fills up, your site will crash. Always use logrotate. Check your disk space regularly. Type: df -h. If the log is too big, increase the time threshold to reduce the amount of data recorded.
Archive and Compress Old WordPress Slow Logs
The logrotate config above uses the “compress” option. This turns old logs into .gz files. This saves a lot of space. If you need to keep logs for a long time, move the .gz files to a backup server. This keeps your main server clean and fast.
Troubleshooting MariaDB Slow Query Log Setup for WordPress
Sometimes things go wrong. Here is how to fix common errors.
Fix Permission Denied Errors on Log File
If the log is empty, check permissions. The database user must own the file. Step 1: Check the owner. Type: ls -l /var/log/mysql/mariadb-slow.log. Step 2: If it is not owned by mysql, fix it. Type: sudo chown mysql:mysql /var/log/mysql/mariadb-slow.log. Step 3: Restart the service.
Resolve “Variable is Read Only” Errors
If you get this error in the database prompt, it means the variable is set in the config file. You cannot change it with the SET GLOBAL command. You must edit the config file and restart the service instead. This is a common mistake during the MariaDB slow query log setup for WordPress.
Slow Query Log Not Recording After Restart
Check the database error log. Type: sudo tail -f /var/log/mysql/error.log. Look for messages about the slow query log. It might be a wrong file path. Ensure the directory exists. Ensure the file exists and has the right permissions.
Handle SELinux and AppArmor Restrictions
Security tools can block the database from writing to the log. If you use SELinux, you need to set the right context. Type: sudo chcon -t mysqld_log_t /var/log/mysql/mariadb-slow.log. If you use AppArmor, you might need to edit the MariaDB profile to allow write access to that specific file path.
FAQ: MariaDB Slow Query Log Setup for WordPress
Here are answers to common questions about this setup.
What Is the Best Threshold for WordPress Slow Query Log?
For a live site, 1 second is best. It catches real problems without creating too much data. For testing, use 0.5 seconds. Never use 0, as it will log everything and slow down your server.
Does Slow Query Log Affect WordPress Performance?
Yes, a little bit. Writing to a disk takes time. But if you set the threshold to 1 second, the impact is very small. You will not notice it. The benefit of finding slow queries is much greater than the tiny performance cost.
How Do I Disable MariaDB Slow Query Log for WordPress?
To turn it off temporarily, log into the database. Type: SET GLOBAL slow_query_log = ‘OFF’;. To turn it off permanently, open your config file. Change slow_query_log = 1 to slow_query_log = 0. Restart the service.
Can I Use Slow Query Log on Shared Hosting WordPress?
No. Shared hosting does not give you root access. You cannot edit the database config files. You also cannot view the log files. You need a VPS, a cloud server, or a dedicated server to use this feature.
What’s the Difference Between MariaDB 10.6 and 10.11 Slow Query Log Variables?
There is no difference. The variable names are exactly the same. The way they work is also the same. You can use the exact same commands and config lines for both versions. This makes upgrading very easy.
Conclusion and Next Steps for WordPress Database Optimization
You now know how to complete the MariaDB slow query log setup for WordPress. You can find the exact database requests that slow down your site. You can identify bad plugins and fix them. Remember to keep your threshold at 1 second for daily use. Use logrotate to manage the file size. Next, look into adding database indexes or using an object cache to make your site even faster. Your users will thank you for the fast loading times.