How to Configure MariaDB Slow Query Log
You set up the mariadb slow query log by turning on slow query tracking. Then you pick a time limit and choose a file or table output. Most teams set this up using shared admin accounts with broad database roles. Those wide roles let anyone change server settings or read raw query logs. That creates a real risk for your data and server uptime. How the mariadb slow query log works MariaDB watches every statement that runs on the database server. It measures how long each query takes from start
Your coding agents run with your access.
Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.
You set up the mariadb slow query log by turning on slow query tracking. Then you pick a time limit and choose a file or table output. Most teams set this up using shared admin accounts with broad database roles. Those wide roles let anyone change server settings or read raw query logs. That creates a real risk for your data and server uptime.
How the mariadb slow query log works
MariaDB watches every statement that runs on the database server. It measures how long each query takes from start to finish. If a query takes longer than your time limit, the server records it. You can also tell the server to record queries that skip indexes. Even fast queries on small tables get caught if they scan every row.
Admins can turn this feature on in the main server file at startup. They can also flip it on at runtime using global system variables. Changing runtime settings avoids a database restart during busy hours. Still, runtime changes reset if you do not save them to the disk file.
You can send these slow query records to a local disk file. You can also write them straight into a system table inside the database. File output uses less CPU power and works well for external log shipping tools. Table output lets engineers search slow statements using standard SQL reads. Many teams pick files for live servers and tables for test systems.
Tuning thresholds and storage targets
Picking the right time threshold is the most important part of this setup. If the threshold is too high, you miss bad queries that hurt your app. If the threshold is too low, the log fills up your disk fast. Heavy disk writes can slow down the database even more during traffic spikes.
Teams should start with a safe threshold and lower it step by step. You can also limit logging to specific user sessions during a debug window. This keeps log volume low while you hunt for a specific bottleneck. Once you find the slow query, you can raise the threshold back up.
Log files on disk also need a strict rotation plan. Without log rotation, a sudden wave of slow queries can eat all free space. Full disks will crash the database and stop all write traffic. Make sure your host cleans up old log files on a set schedule.
End-to-End Encryption + Log Aggregation & Correlation: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
What native logging solves and what it misses
Native slow query logging helps teams spot missing indexes and bad table joins. It shows you the exact execution time, lock wait time, and rows scanned. This data is great for tuning app performance and planning hardware upgrades.
Still, the native setup leaves two major operational gaps open. First, changing runtime variables or reading system log tables requires high database privileges. Teams often hand out admin roles so developers can debug slow queries themselves. Database roles only check who logs in at the start of a session. They do not inspect what individual statements do while in flight. An engineer with admin rights to check logs can also drop tables by mistake.
Second, the slow query log records the full text of slow SQL statements. Those statements often contain raw emails, phone numbers, card numbers, or national IDs. Anyone who reads the slow log file can see those sensitive values in plain text. Native logging also struggles to tie shared app user queries back to specific people. When an engineer runs a heavy ad-hoc query, you need better session tracking.
Inspect every MySQL query and response in flight
Static grants and network rules block bad logins. They cannot pause a risky query for review or mask emails in a result set. By design, hoop.dev sits inline in the MySQL protocol as a sidecar for your MySQL. For every plain query, prepared statement, or cursor an agent or human runs in production, hoop.dev decides to pass, mask, block, or hold it for review.
First, hoop.dev guardrails block banned actions before MySQL runs them, and an AI Analyzer you configure can score intent or escalate for approval. Next, hoop.dev data masking rewrites sensitive string columns in results in memory, and every verdict goes to the audit trail. Find the open-source binary on GitHub.
Frequently asked questions
Does turning on the slow query log hurt MariaDB speed?
It adds very little overhead when your time limit is set to a high number. Disk wear only spikes if thousands of queries cross your threshold every second.
Should I write slow queries to a file or a table?
Write to a local file on busy production servers to keep overhead low. Use table output only when you need to run quick SQL checks on logs.
How do I keep sensitive data out of slow query logs?
Limit who can read log files and avoid putting raw user data in queries. You can also control query access before statements reach the database server.
Related guides
In this post