Find Slow MySQL Queries on Your VPS: Turn On the Slow Log
A slow WordPress site often has two or three bad queries behind it. Turn on the MySQL slow query log in WHM or my.cnf and let it name them in one afternoon.
The short answer
The MySQL slow query log records every query that runs longer than `long_query_time`, which defaults to 10 seconds and is off by default on both MySQL and MariaDB. On a WHM server you enable it under Database Services > Edit Database Configuration or in /etc/my.cnf, set the limit to about 1 second, and then rank the results with mysqldumpslow -s t. Shared cPanel users cannot change this setting and need to ask their host.

Reading options
On this page
- What the MySQL slow query log actually is
- Before you start
- Get ready on the server
- Turn on the slow log in WHM
- Turn it on by editing my.cnf
- MariaDB and MySQL: the names that differ
- Read the log and find the worst queries
- How to check it worked
- Troubleshooting
- Pro tips and security notes
- When to ask your host
- The one message worth forwarding
- Reader questions
- Sources & further reading
Before you start
Server-root SSH or WHM Terminal access; enough free disk space for the log; a quiet restart window; the unique /etc/my.cnf backup path saved in maintenance notes; a plan to disable or raise the logging threshold after diagnosis.
The MySQL slow query log is a file where your database server writes down every query that took longer than a time limit you choose. It is off by default on both MySQL and MariaDB, and you turn it on with one setting in WHM or a few lines in /etc/my.cnf. You need root access to a WHM VPS or dedicated server for this, so if you are on shared cPanel hosting, skip the steps and send your host the message at the end of this guide.
A WordPress site that feels slow often has two or three queries doing most of the damage. You can guess at plugins for a week, or you can let the slow query log name those queries for you in one afternoon.
By the end of this guide you will have the log running and a short ranked list of the queries worth fixing.
What the MySQL slow query log actually is
Think of a shop with a bell over the counter that only rings when a customer has waited too long. Most customers are served quickly and the bell stays quiet. The few that ring it are the ones worth looking at.
A query is one request from your site to the database, such as "give me the ten newest posts". The MySQL 8.4 manual says the slow log holds statements that take more than long_query_time seconds to run and examine at least min_examined_row_limit rows. That second setting defaults to 0, so in practice only the time limit matters.
long_query_time defaults to 10 seconds on both MySQL 8.4 and MariaDB. That is far too relaxed for a website, where a one-second query is already a problem.
Each entry starts with a # line showing Query_time, Lock_time, Rows_sent and Rows_examined, followed by the query. Rows_examined is how many rows the server had to read. A huge Rows_examined next to a tiny Rows_sent usually points to a missing index.
Why this matters: the manual notes the time is measured in real time, not CPU time, so a query that is fast on a quiet server can cross the limit on a busy one.
Before you start
Work through this checklist before you change anything.
- Root or host access. You need to log in to WHM as
root, or have SSH access asroot. A reseller or cPanel account is not enough. - A backup of the config file. You will copy
/etc/my.cnfbefore editing it. Step 3 shows how. - A quiet time window. Saving changes in WHM restarts MySQL or MariaDB, and sites that need the database can show errors during the restart. Pick a low-traffic hour.
- Your risk level. Turning on the log is low risk. Editing
/etc/my.cnfby hand is medium risk, because a typo can stop the database from starting until you fix it. - Some free disk space. The log is a plain text file that grows as slow queries arrive.
Heads up: on shared hosting you cannot change these settings at all, because one database server is shared by many accounts. Your host can do it for you.
Get ready on the server
These steps are the same whether you use WHM or edit the file yourself.
Step 1: Check whether you have root on the server
Log in to WHM as root, or open an SSH session as root. cPanel's support article says to "Login as root using SSH, or the Terminal in WHM", so the Terminal inside WHM works too.
If you cannot log in as root, you are on a reseller or managed plan. Stop here and use the message at the end.
Step 2: Check your current settings
This command opens the database client.
mysqlcPanel's documentation says WHM stores the database root password in /root/.my.cnf, and the client reads ~/.my.cnf for user options, which is why plain mysql usually connects without a password prompt when you are root. On MariaDB the client is called mariadb, with a mysql symlink on Unix systems, so the same command works.
At the prompt, this statement shows your server version.
SELECT VERSION();This statement lists the slow log settings, using the % wildcard to match every variable whose name starts with slow_query_log.
SHOW GLOBAL VARIABLES LIKE 'slow_query_log%';Made-up example: on a fresh server you would see something like this, with your own hostname.
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | server1.example.com-slow.log |
+---------------------+-------------------------------+OFF confirms the log is not running yet. Note the version number, because it decides which variable names you see later. Type exit to leave the client.
Step 3: Back up your config file
Create a unique backup path before editing, copy the file with its metadata, and print the exact path you will need for rollback.
SLOWLOG_BACKUP="/root/my.cnf.before-slowlog.$(date +%Y%m%d-%H%M%S)"
cp -a /etc/my.cnf "$SLOWLOG_BACKUP"
printf 'Backup saved to %s\n' "$SLOWLOG_BACKUP"
ls -lh /etc/my.cnf "$SLOWLOG_BACKUP"Keep the printed path in your maintenance notes. Do not guess the timestamp later.
Turn on the slow log in WHM
This is the easiest route on cPanel servers, because the settings are form fields and there is no file to mistype.
Step 4: Open the database configuration screen
In cPanel and WHM version 120 and later, go to WHM > Database Services > Edit Database Configuration. In version 118 and earlier, the same screen is WHM > SQL Services > Edit SQL Configuration. cPanel's documentation confirms the rename, so if you do not see one name, look for the other.
Step 5: Set the slow log fields
Find these fields and set them.
- Slow Query Log: set to Enable. On MariaDB 10.11 and later it is labelled Log Slow Queries.
- Long Query Time: set to
1for a first pass. cPanel accepts decimals here, from 0 to 30 seconds. - Slow Query Log File Name: leave the default, which cPanel documents as
server_hostname-slow.log.
Also check that Log Output points to a file, since it controls where the slow log is written.
Why this matters: one second is a sensible starting line for a website. Raise it if the log fills too fast.
Step 6: Save and let the service restart
Click Save at the bottom of the page. cPanel's documentation says plainly: "Changes you make in this interface will cause the MySQL or MariaDB services to restart."
Load your website once afterwards. If a database error does not clear within a minute, our guide to fixing Error Establishing a Database Connection walks through the checks. Then skip ahead to "How to check it worked".
Turn it on by editing my.cnf
Use this route if your server has no WHM or a field is missing. cPanel itself notes that "Not all options are exposed in this interface".
Step 4b: Add the slow log lines to /etc/my.cnf
This command opens the file in the nano editor, as cPanel's article shows.
nano /etc/my.cnfFind the [mysqld] line. Both MySQL and MariaDB servers read the [mysqld] group, according to their option file documentation, so it is the safe place on either one. Add these lines directly under it.
slow_query_log = 1
long_query_time = 1
log_output = FILEThe first line turns the log on, the second sets the limit to one second, and the third sends entries to a file. Leaving out slow_query_log_file keeps the default name in the data directory. Save and exit.
Heads up: MySQL ignores world-writable option files as a security measure. If the file's permissions changed while you worked on it, the server may skip it.
Step 5b: Restart the database service
This cPanel script restarts MySQL or MariaDB so it reads the new settings.
/usr/local/cpanel/scripts/restartsrv_mysqlRead the last lines of its output, which should say the service is up. If they report a failure, go straight to Troubleshooting.
Optional: turn it on now without a restart
If a restart has to wait until tonight, you can switch the log on live from the client. These two statements enable the log and set the limit to one second.
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;A SET GLOBAL change lasts only until the next restart, so keep the my.cnf lines too. The MySQL manual also says a global change "does not affect the session value for any current client sessions", so connections that are already open keep the old limit until they reconnect. On MySQL 8.4 this needs the SYSTEM_VARIABLES_ADMIN privilege or the deprecated SUPER privilege, so connect as the database root user as in Step 2.
MariaDB and MySQL: the names that differ
MariaDB renamed the slow log variables in version 10.11, which is why commands copied from different tutorials look inconsistent.
slow_query_logbecamelog_slow_query.slow_query_log_filebecamelog_slow_query_file.long_query_timebecamelog_slow_query_time.min_examined_row_limitbecamelog_slow_min_examined_row_limit.
MariaDB's system variable reference lists each old name as an alias from 10.11.0 onward. So the old names in Step 4b work on MySQL 8.4, on older MariaDB and on MariaDB 10.11 and newer. That is why this guide uses them.
On MariaDB 10.11 and later you can use either the old or the new names in SET statements and option files. This statement lists every variable whose name starts with log_slow.
SHOW GLOBAL VARIABLES LIKE 'log_slow%';A few smaller differences are worth knowing.
- Config group. MariaDB's own examples use a
[mariadb]group, which is meant for MariaDB Server.[mysqld]works on both. - Dashes and underscores. MariaDB treats
slow-query-logandslow_query_logas the same option in config files. In SQL statements you must use underscores. - The summary tool. MySQL ships
mysqldumpslow. MariaDB calls itmariadb-dumpslowand keepsmysqldumpslowas a symlink on Linux. - Extra detail. MariaDB's
log_slow_verbositycan add the query plan to each entry, withlog_slow_verbosity=query_plan,explain,engineas its documented config example. MySQL 8.4 haslog_slow_extra, which adds fields such asThread_id,StartandEnd.
Quick tip: if WHM shows Log Slow Queries instead of Slow Query Log, you are on MariaDB 10.11 or later.
Read the log and find the worst queries
Give the log a full business day of normal traffic, so queries that only run at busy times get caught.
Step 7: Find the log file
This statement prints the file name and the data directory together.
SHOW GLOBAL VARIABLES WHERE Variable_name IN ('slow_query_log_file', 'datadir');If the file name has no leading /, it sits inside the data directory, so join the two values to get the full path.
Made-up example: datadir is /var/lib/mysql/ and the file is server1.example.com-slow.log, giving /var/lib/mysql/server1.example.com-slow.log. Your path may differ, so swap in your own below.
Step 8: Look at the newest entries
This command prints the last 40 lines of the file.
tail -n 40 /var/lib/mysql/server1.example.com-slow.logMade-up example: a WordPress entry looks roughly like this.
# User@Host: example_wp[example_wp] @ localhost []
# Query_time: 3.912044 Lock_time: 0.000081 Rows_sent: 1 Rows_examined: 845210
SET timestamp=1794652937;
SELECT option_value FROM wp_options WHERE option_name = 'example_plugin_cache' LIMIT 1;Almost four seconds and 845,210 rows read to return one row is exactly what you are hunting for. The user line tells you which account's database it came from.
Step 9: Rank the queries with mysqldumpslow
The mysqldumpslow tool groups queries that differ only in their values, showing numbers as N and strings as 'S', so 500 copies of one query become one line with a count. This command sorts by total query time and shows the top 10.
mysqldumpslow -s t -t 10 /var/lib/mysql/server1.example.com-slow.logOn MariaDB, mariadb-dumpslow takes the same options. Without -s, both tools sort by average time. Total time is usually more useful for a website, because a two-second query that runs 3,000 times a day hurts more than a 30-second query that runs once.
Made-up example: the output looks like this.
Count: 412 Time=2.31s (951s) Lock=0.00s (0s) Rows=1.0 (412), example_wp[example_wp]@localhost
SELECT option_value FROM wp_options WHERE option_name = 'S' LIMIT N
Count: 37 Time=4.80s (177s) Lock=0.00s (0s) Rows=20.0 (740), example_shop[example_shop]@localhost
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta ...Count is how many times the query ran. The first Time is the average and the bracketed number is the total. The top two or three lines are your shortlist.
Quick tip: adding -g wp_options to the command only considers queries that match that grep-style pattern, which helps when one table is the suspect.
Step 10: Ask the database how it runs the top query
Put real values back in place of N and 'S' in your worst query and run it with EXPLAIN in front, which shows the plan without changing anything.
EXPLAIN SELECT option_value FROM wp_options WHERE option_name = 'example_plugin_cache' LIMIT 1;In the output, key is the index the database chose and rows is its estimate of rows to read. A type of ALL means a full table scan, which the MySQL manual calls normally not good for the first table in a query and usually very bad in other cases. That is the moment to hand the query and this output to a developer.
How to check it worked
Run these checks right after turning the log on.
- The setting is on.
SHOW GLOBAL VARIABLES LIKE 'slow_query_log';should showON. - The limit is right.
SHOW GLOBAL VARIABLES LIKE 'long_query_time';should show1.000000. - The file exists.
ls -lhon the path from Step 7 should show a small file with today's date. - A test query lands in it. In a fresh client session, run
SELECT SLEEP(2);, which pauses two seconds and returns0. Thentail -n 5on the log should show an entry withQuery_timejust over 2. - The counter moves.
SHOW GLOBAL STATUS LIKE 'Slow_queries';should go up by one after the test, because the server incrementsSlow_querieswhenever a query passes the limit.
Troubleshooting
The setting says ON, but the file stays empty
Cause: log_output is TABLE or NONE. The MySQL manual says that with NONE no queries are written even with the log enabled, and TABLE sends entries to the mysql.slow_log table instead.
Fix: set Log Output to file in WHM, or keep log_output = FILE in /etc/my.cnf and restart. Live, run SET GLOBAL log_output = 'FILE';.
The log was on yesterday and is off today
Cause: you used SET GLOBAL only, and the database restarted, perhaps for an update.
Fix: save the setting in WHM or add the Step 4b lines to /etc/my.cnf.
The test query does not show up
Cause: you changed long_query_time with SET GLOBAL and tested in the same session. The manual says the change does not reach current sessions, "not even the session within which the global value change occurs".
Fix: type exit, reconnect with mysql, and test again.
SET GLOBAL fails with an access denied error
Symptom: the message starts with Access denied; you need (at least one of) the and names a privilege such as SYSTEM_VARIABLES_ADMIN.
Cause: you are connected as an account's database user, not the database root user.
Fix: log in to the server as root and run plain mysql. Without server root, this is a job for your host.
The database will not start after editing my.cnf
Symptom: /usr/local/cpanel/scripts/restartsrv_mysql reports a failure and sites show database errors.
Cause: usually a typo, a line outside the [mysqld] group, or an option name the server does not recognise.
Fix: this command restores your backup from Step 3 so the sites come back first.
Replace the placeholder below with the exact path printed in Step 3, then restore the file and restart the service.
cp -a /root/my.cnf.before-slowlog.YYYYMMDD-HHMMSS /etc/my.cnf
/usr/local/cpanel/scripts/restartsrv_mysqlDo not copy the placeholder literally. If you no longer have the saved path, list matching backups with ls -lt /root/my.cnf.before-slowlog.*, verify the timestamp and file contents, and restore only the backup created for this change.
A custom log path never creates a file
Cause: slow_query_log_file points to a folder the database server cannot write to. The MySQL manual notes the location must be writable by the server.
Fix: remove the custom path so the log returns to the data directory, or give the database user write access to that folder.
Pro tips and security notes
- Keep the log from growing forever. The MySQL manual's log maintenance section describes a runtime method: run
SET GLOBAL slow_query_log = 'OFF';, rename the file from the shell, then runSET GLOBAL slow_query_log = 'ON';and the server opens a fresh file with the original name. MariaDB's documentation points tologrotatefor regular rotation on Linux. - Be careful with "not using indexes".
log_queries_not_using_indexesalso logs fast queries that skip indexes, and the manual warns the log "may grow quickly". If you try it, setlog_throttle_queries_not_using_indexesto cap entries per minute, since its default of 0 means no limit. - Turn it down when you are done. Once you have your shortlist, disable the log or raise
long_query_timeso only real trouble gets logged. - Treat the log as private. It holds the full text of slow queries, which can include customer emails or order numbers. The manual says passwords are rewritten so they do not appear in plain text, but other values are not hidden. Share the
mysqldumpslowsummary, where values becomeNand'S', rather than the raw file. - Pair it with the PHP side. The slow log names the query, not the plugin that sent it. Our guide on using the WordPress debug log safely after an update helps you trace the PHP side.
- Menus move. At the time of writing (15 November 2026), cPanel documents these fields under Edit Database Configuration for version 120 and later. Check your version if the screen looks different.
When to ask your host
Ask your host or a database specialist if you are on shared or managed hosting without server root, or if the database will not start and restoring the backup did not fix it. The same goes when the top queries come from a plugin or theme you cannot change. If you are about to move to a bigger server anyway, read our website migration plan first, because a slow query usually moves with the site.
The one message worth forwarding
Copy this, fill in the brackets, and send it to your host or developer.
Hi, my website [example.com] on server [192.0.2.10] has been slow, especially [describe when, for example the shop page in the evening].
Could you please turn on the MySQL/MariaDB slow query log with long_query_time set to 1 second, leave it running for one normal business day, and then send me the top 10 queries by total time from mysqldumpslow (sorted with -s t)?
For the top 3 queries, please also run EXPLAIN and tell me which table and plugin they come from, and whether an index or a plugin change would help.
Please switch the log off or raise the limit afterwards, and keep the raw log private because it can contain customer data.
Thank you.Turn the log on, wait one busy day, and let the top lines of mysqldumpslow tell you where the slowness really lives.
Reader questions
Does turning on the slow query log slow down my server?
It adds some disk writes, and MariaDB's documentation notes the slow log can create a lot of I/O. Keep the time limit sensible and avoid logging every query on a busy server.
Are ALTER TABLE and other admin statements written to the slow log?
Not by default on MySQL. Administrative statements such as ALTER TABLE, OPTIMIZE TABLE and CREATE INDEX are only logged when you turn on the separate log slow admin statements setting.
What is a good long query time for a WordPress site?
One second is a practical starting point. Once the worst queries are fixed you can lower it to catch smaller problems, or raise it if the log grows faster than you can read it.
Can I store the slow log in a database table instead of a file?
Yes. Setting the log output option to TABLE sends entries to the slow log table in the mysql system schema on both MySQL and MariaDB. A file is easier to summarise with mysqldumpslow, which is why this guide uses file output.
Will the slow log tell me which WordPress plugin is causing the problem?
Not directly. It shows the query text, the database user and the timing, and you then match the table or option names in the query to the plugin that uses them. A developer can confirm this with a profiling plugin or the WordPress debug log.
Does the slow log record queries that failed with a syntax error?
No. The MySQL manual says statements that cannot be parsed, for example because of a syntax error, are not written to the slow query log.
Was this guide helpful?
Help us make the next update better.
Sources & further reading
- MySQL 8.4 Reference Manual: The Slow Query Log
- MySQL 8.4 Reference Manual: mysqldumpslow
- MySQL 8.4 Reference Manual: Server Log Maintenance
- MySQL 8.4 Reference Manual: SET Syntax for Variable Assignment
- MariaDB Documentation: Slow Query Log Overview
- MariaDB Documentation: Server System Variables
- MariaDB Documentation: mariadb-dumpslow
- cPanel Documentation: Edit Database Configuration
- cPanel Support: How to edit the MySQL/MariaDB configuration (my.cnf) file
What changed
Safety review on 15 November 2026: replaced the hard-coded dated my.cnf backup with a unique timestamped backup variable, made rollback use the exact saved path, updated the cPanel restart script to its canonical full path, refreshed dated references, and added an original accessible cover.
Originally published . About our editorial updates.



