# Mariadb Slow log
**/var/log/mysql/mariadb-slow.log is the standard file path used to store the slow query log in MariaDB and MySQL databases on Linux systems.** This log records any SQL queries that exceed a defined execution time threshold, helping database administrators diagnose and troubleshoot performance bottlenecks. [[1](https://mariadb.com/docs/server/server-management/server-monitoring-logs/slow-query-log/slow-query-log-overview), [2](https://nirsa.tistory.com/227), [3](https://support.cpanel.net/hc/en-us/articles/360052538914-How-to-Enable-the-Slow-Query-Log-in-MySQL-or-MariaDB), [4](https://support.sciencelogic.com/s/article/1098)]
Here is a quick guide on how to verify, enable, and analyze this log file.
1. View Current Settings (MySQL Console)
Log into your MariaDB/MySQL console to see if the slow query log is active and where it is pointing: [[1](https://technoroots.org/insights/how-to-enable-mysql-slow-query-log-complete-setup-guide-PnQYT)]
sql
``` SHOW VARIABLES LIKE 'slow_query_log%'; SHOW VARIABLES LIKE 'long_query_time'; ```
Use code with caution.
- **slow_query_log**: Should be ON to record queries. - **slow_query_log_file**: Path to the log file (e.g., /var/log/mysql/mariadb-slow.log). - **long_query_time**: The threshold in seconds. Queries taking longer than this value are logged. \[[1](https://support.plesk.com/hc/en-us/articles/12377515355031-How-to-enable-the-MySQL-MariaDB-slow-query-log-and-analyze-it-on-a-Plesk-for-Linux-server), [2](https://mosei.tistory.com/entry/MysqlMariaDB-Slow-Query-Log-%EC%84%B8%ED%8C%85), [3](https://da-new.tistory.com/250), [4](https://technoroots.org/insights/how-to-enable-mysql-slow-query-log-complete-setup-guide-PnQYT)\]
2. Enable it Temporarily (Runtime)
If the log is disabled, you can turn it on immediately without restarting the database service: [[1](https://technoroots.org/insights/how-to-enable-mysql-slow-query-log-complete-setup-guide-PnQYT), [2](https://da-new.tistory.com/250)]
sql
``` SET GLOBAL slow_query_log = 'ON'; SET GLOBAL slow_query_log_file = '/var/log/mysql/mariadb-slow.log'; SET GLOBAL long_query_time = 2; -- Logs queries taking over 2 seconds SET GLOBAL log_queries_not_using_indexes = 'ON'; -- Optional: logs unindexed queries ```
Use code with caution.
3. Enable it Permanently (Configuration File)
Runtime changes disappear if the database restarts. To make them permanent, edit your server's configuration file (usually /etc/my.cnf, /etc/mysql/my.cnf, or /etc/mysql/mariadb.conf.d/50-server.cnf): [[1](https://gombach7.tistory.com/16), [2](https://support.plesk.com/hc/en-us/articles/12377515355031-How-to-enable-the-MySQL-MariaDB-slow-query-log-and-analyze-it-on-a-Plesk-for-Linux-server), [3](https://technoroots.org/insights/how-to-enable-mysql-slow-query-log-complete-setup-guide-PnQYT)]
ini
``` [mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/mariadb-slow.log long_query_time = 2 log_queries_not_using_indexes = 1 ```
Use code with caution.
4. Set Linux Permissions
Because the mysql system user must write to this file, the directory and file permissions must be configured correctly in Linux: [[1](https://docs.cpanel.net/knowledge-base/sql/how-to-enable-the-slow-query-log-in-mysql-or-mariadb/), [2](https://nirsa.tistory.com/227)]
bash
``` sudo mkdir -p /var/log/mysql sudo touch /var/log/mysql/mariadb-slow.log sudo chown -R mysql:mysql /var/log/mysql sudo chmod 750 /var/log/mysql ```
Use code with caution.
*After updating the configuration file and permissions, restart the service:* [[1](https://support.plesk.com/hc/en-us/articles/12377515355031-How-to-enable-the-MySQL-MariaDB-slow-query-log-and-analyze-it-on-a-Plesk-for-Linux-server), [2](https://docs.cpanel.net/knowledge-base/sql/how-to-enable-the-slow-query-log-in-mysql-or-mariadb/)]
bash
``` sudo systemctl restart mariadb # Or on some systems: sudo systemctl restart mysql ```
Use code with caution.
5. How to Analyze the Log
Slow query logs can get incredibly large and difficult to read manually. Instead of using cat or vi, use native parsing tools: [[1](https://discuss.frappe.io/t/how-do-i-turn-on-and-then-view-mariadb-slow-query-log-with-v10/70826), [2](https://support.plesk.com/hc/en-us/articles/12377515355031-How-to-enable-the-MySQL-MariaDB-slow-query-log-and-analyze-it-on-a-Plesk-for-Linux-server)]
- **Using mysqldumpslow**: A built-in command-line tool that groups similar queries together and sorts them by execution time.
bash
``` mysqldumpslow -s t /var/log/mysql/mariadb-slow.log ```
Use code with caution.
- **Using pt-query-digest**: Part of the advanced Percona Toolkit, this creates highly detailed and easily readable performance summaries.
bash
``` pt-query-digest /var/log/mysql/mariadb-slow.log ```
Use code with caution.
\[[1](https://support.sciencelogic.com/s/article/1098), [2](https://support.plesk.com/hc/en-us/articles/12377515355031-How-to-enable-the-MySQL-MariaDB-slow-query-log-and-analyze-it-on-a-Plesk-for-Linux-server)\]
If you are experiencing issues with this log, please let me know:
- Is your database **currently experiencing slow downs** or high CPU usage? - Do you need help **optimizing a specific slow query** found in this log? - Are you getting a **permission denied error** when MariaDB tries to create the file?
AI responses may include mistakes. [Learn more](https://support.google.com/websearch?p=aimode)
-
mariadb.com
Slow Query Log Overview \| Server \| MariaDB Documentation
Slow Query Log Overview. Complete Slow Query Log Overview gu...
-
Plesk
How to enable the MySQL/MariaDB slow query log and analyze it on ...
Answer \* Connect to your Plesk server via SSH. \* Enable the ...
-
cPanel
How to Enable the Slow Query Log in MySQL® or MariaDB
To enable the Slow Query Log for MySQL or MariaDB: \* Log in ...
Show all
what it could be useful?
The slow query log is one of the most powerful tools available for database optimization. It functions like a traffic camera for your database, catching the exact queries that are slowing down your applications.
Here is exactly how it is useful:
1. Finding Performance Bottlenecks
- **Identifies slow pages:** If a website page takes 5 seconds to load, this log reveals the exact SQL query causing the delay. - **Pins down high CPU/Memory:** Queries that scan millions of rows without using indexes spike server resources. The log flags them immediately.
2. Discovering Missing Indexes
- **Spotting unindexed tables:** By enabling log_queries_not_using_indexes, the log captures queries performing full table scans. - **Easy fixes:** Adding a single index to a column flagged in this log can often drop a query's execution time from 3 seconds to 3 milliseconds.
3. Catching Code Problems (Application Bugs)
- **Poorly written ORM queries:** Modern web frameworks (like Laravel, Hibernate, or Django) sometimes generate massive, inefficient SQL joins behind the scenes. - **N+1 query problems:** It reveals loops where your application accidentally runs hundreds of tiny, redundant queries instead of one efficient query.
4. Database Capacity Planning
- **Tracking growth trends:** By comparing log sizes and query counts month-over-month, you can see if your database performance is degrading as your user base grows. - **Postponing costly upgrades:** Optimizing the top 3 slow queries in this log can often slash server resource usage by 50%, saving you from paying for a larger, more expensive server.
---
To help you get the most out of this, let me know:
- Is your database **currently running slow**, or are you just setting this up proactively? - What **application or framework** (e.g., WordPress, Node.js, Python) is connecting to this database? - Would you like an example of **how to read a raw entry** from this log file?
AI responses may include mistakes. [Learn more](https://support.google.com/websearch?p=aimode)
mariadb-slow.log.1 is about 3.0G is it normal ?
**No, a 3.0 GB mariadb-slow.log.1 is not normal for a healthy, optimized production database.**
The .1 extension indicates this is a **rotated log file** (an older log preserved by Linux logrotate), meaning your database generated 3 GB of slow query data in a single log rotation period—typically just one week or one day.
A file this large means your database server is wasting massive amounts of CPU, RAM, and disk I/O processing inefficient queries.
---
Why is it so large?
1. **Missing Indexes (Most Common):** If a query searches a table with 1 million rows without an index, MariaDB must read all 1 million rows from the disk every single time. This is called a "Full Table Scan." 2. **log_queries_not_using_indexes is ON:** If this setting is enabled, MariaDB logs **every single query** that doesn't use an index, even if it executes in 0.001 seconds. On a busy site, this will inflate the log file to gigabytes in hours. 3. **long_query_time is set too low:** If this is set to 0 or a very low fraction (like 0.01 seconds) on a high-traffic application, almost every single query gets logged. 4. **An Application Loop Bug:** Your application code might be trapped in a loop, running the same slow query thousands of times per minute.
---
Step-by-Step Action Plan
1. Safely inspect it without crashing your terminal
Do **not** try to open a 3 GB file with cat, nano, or vi, as it can freeze your server's memory. Instead, look at just the first few lines:
bash
``` head -n 50 /var/log/mysql/mariadb-slow.log.1 ```
Use code with caution.
2. Check your configuration
Run this command inside the MariaDB console to check if it's logging non-indexed queries:
sql
``` SHOW VARIABLES LIKE 'log_queries_not_using_indexes'; SHOW VARIABLES LIKE 'long_query_time'; ```
Use code with caution.
- If log_queries_not_using_indexes is ON and you have a busy database, **turn it off** to stop the log explosion:
sql
``` SET GLOBAL log_queries_not_using_indexes = 'OFF'; ```
Use code with caution.
3. Summarize the 3 GB file instantly
Do not read it manually. Use mysqldumpslow to extract the top 5 worst offending queries that are taking up all that space:
bash
``` mysqldumpslow -s c -t 5 /var/log/mysql/mariadb-slow.log.1 ```
Use code with caution.
*(This sorts the file by **count** (-s c) and shows the top 5 (-t 5) query patterns causing the bulk of the log size).*
4. Reclaim your disk space safely
Once you have reviewed or backed up the file, you can empty it to reclaim 3 GB of disk space instantly without restarting MariaDB:
bash
``` sudo truncate -s 0 /var/log/mysql/mariadb-slow.log.1 ```
Use code with caution.
---
If you'd like to fix this, let me know:
- What is the output of the **mysqldumpslow command** above? (You can paste a small snippet of the top query pattern). - What are your current **long_query_time** and **log_queries_not_using_indexes** values? - Is your server currently **running out of disk space** because of this?
AI responses may include mistakes. [Learn more](https://support.google.com/websearch?p=aimode)
meson@CiCADA: 17:56:36 /var/log/mysql$ head -n 50 /var/log/mysql/mariadb-slow.log.1 /usr/sbin/mariadbd, Version: 10.11.14-MariaDB-0ubuntu0.24.04.1-log (Ubuntu 24.04). started with: Tcp port: 0 Unix socket: /run/mysqld/mysqld.sock Time Id Command Argument # Time: 260725 22:46:02 # User@Host: cloud\_user\[cloud\_user\] @ localhost \[\] # Thread\_id: 162 Schema: cloud QC\_hit: No # Query\_time: 0.089309 Lock\_time: 0.000092 Rows\_sent: 1 Rows\_examined: 191623 # Rows\_affected: 0 Bytes\_sent: 68 # Full\_scan: Yes Full\_join: No Tmp\_table: No Tmp\_table\_on\_disk: No # Filesort: No Filesort\_on\_disk: No Merge\_passes: 0 Priority\_queue: No # # explain: id select\_type table type possible\_keys key key\_len ref rows r\_rows filtered r\_filtered Extra # explain: 1 SIMPLE oc\_filecache ALL NULL NULL NULL NULL 186499 191623.00 100.00 79.09 Using where # use `cloud`; SET timestamp=1784987162; SELECT COUNT(\*) FROM `oc\_filecache` WHERE `path` LIKE 'appdata\_ocnu72fp1mua%'; # Time: 260726 1:46:03 # User@Host: cloud\_user\[cloud\_user\] @ localhost \[\] # Thread\_id: 536 Schema: cloud QC\_hit: No # Query\_time: 0.067386 Lock\_time: 0.000065 Rows\_sent: 1 Rows\_examined: 191623 # Rows\_affected: 0 Bytes\_sent: 68 # Full\_scan: Yes Full\_join: No Tmp\_table: No Tmp\_table\_on\_disk: No # Filesort: No Filesort\_on\_disk: No Merge\_passes: 0 Priority\_queue: No # # explain: id select\_type table type possible\_keys key key\_len ref rows r\_rows filtered r\_filtered Extra # explain: 1 SIMPLE oc\_filecache ALL NULL NULL NULL NULL 186499 191623.00 100.00 79.09 Using where # SET timestamp=1784997963; SELECT COUNT(\*) FROM `oc\_filecache` WHERE `path` LIKE 'appdata\_ocnu72fp1mua%'; # Time: 260726 4:51:03 # User@Host: cloud\_user\[cloud\_user\] @ localhost \[\] # Thread\_id: 975 Schema: cloud QC\_hit: No # Query\_time: 0.079616 Lock\_time: 0.000082 Rows\_sent: 1 Rows\_examined: 191623 # Rows\_affected: 0 Bytes\_sent: 68 # Full\_scan: Yes Full\_join: No Tmp\_table: No Tmp\_table\_on\_disk: No # Filesort: No Filesort\_on\_disk: No Merge\_passes: 0 Priority\_queue: No # # explain: id select\_type table type possible\_keys key key\_len ref rows r\_rows filtered r\_filtered Extra # explain: 1 SIMPLE oc\_filecache ALL NULL NULL NULL NULL 186499 191623.00 100.00 79.09 Using where # SET timestamp=1785009063; SELECT COUNT(\*) FROM `oc\_filecache` WHERE `path` LIKE 'appdata\_ocnu72fp1mua%'; # Time: 260726 7:56:02 # User@Host: cloud\_user\[cloud\_user\] @ localhost \[\] # Thread\_id: 1316 Schema: cloud QC\_hit: No # Query\_time: 0.083674 Lock\_time: 0.000084 Rows\_sent: 1 Rows\_examined: 191623 # Rows\_affected: 0 Bytes\_sent: 68 # Full\_scan: Yes Full\_join: No Tmp\_table: No Tmp\_table\_on\_disk: No # Filesort: No Filesort\_on\_disk: No Merge\_passes: 0 Priority\_queue: No MariaDB \[(none)\]> SHOW VARIABLES LIKE 'log\_queries\_not\_using\_indexes'; +-------------------------------+-------+ \| Variable\_name \| Value \| +-------------------------------+-------+ \| log\_queries\_not\_using\_indexes \| ON \| +-------------------------------+-------+ 1 row in set (0.003 sec) MariaDB \[(none)\]> SHOW VARIABLES LIKE 'long\_query\_time'; +-----------------+----------+ \| Variable\_name \| Value \| +-----------------+----------+ \| long\_query\_time \| 2.000000 \| +-----------------+----------+ 1 row in set (0.003 sec)
This output explains exactly why your log file reached 3 GB.
Your database belongs to a **Nextcloud or OwnCloud instance** (indicated by the oc_filecache table). The log is exploding because **log_queries_not_using_indexes is turned ON**, combined with Nextcloud constantly checking its app data folder.
---
The Root Cause
Look closely at this specific part of your log entry:
- **Query_time: 0.089309**: The query is actually very fast, taking only **0.08 seconds** (well below your 2.0-second long_query_time threshold). - **Full_scan: Yes**: The query is doing a full table scan, checking all 191,623 rows in the oc_filecache table every single time it runs. - **Why it's logged:** Because log_queries_not_using_indexes = ON, MariaDB is forced to write this fast query to your log file every few hours. On a busy server with multiple users or cron jobs, this fills up gigabytes of text very quickly.
---
How to Fix It
Step 1: Turn off the unindexed query logging (Instant Relief)
This stops MariaDB from writing fast, unindexed queries to your log, immediately freezing the growth of the file. Run this in your MariaDB console:
sql
``` SET GLOBAL log_queries_not_using_indexes = 'OFF'; ```
Use code with caution.
To make this change permanent so it survives a server reboot, open your MariaDB configuration file (e.g., /etc/mysql/mariadb.conf.d/50-server.cnf or /etc/my.cnf) and change or add this line under the [mysqld] section:
ini
``` log_queries_not_using_indexes = 0 ```
Use code with caution.
Step 2: Reclaim your 3 GB disk space
Now that the logging behavior is changed, you can safely wipe out the bloated backup log file to free up your hard drive:
bash
``` sudo truncate -s 0 /var/log/mysql/mariadb-slow.log.1 ```
Use code with caution.
Step 3: Address the Nextcloud Index Issue (Optional but Recommended)
The query SELECT COUNT(*) FROM oc_filecache WHERE path LIKE 'appdata_...%' uses a wildcard (%) at the end of the string. Nextcloud *should* be using its built-in index on the path column, but sometimes these indexes get corrupted or missing during major Nextcloud upgrades.
You can tell Nextcloud to check and repair its database indexes automatically. Run this command in your terminal from your Nextcloud installation directory (usually /var/www/nextcloud or /var/www/html):
bash
``` sudo -u www-data php occ db:add-missing-indices ```
Use code with caution.
*(Replace www-data with your web server user if you are using a different one like nginx or apache).*
---
If you run into any issues, let me know:
- Did turning off log_queries_not_using_indexes successfully stop the log from growing? - Do you know the **exact directory path** where your Nextcloud/OwnCloud instance is installed? - Are you running this instance inside a **Docker container**, or directly on the host system?