Slow Query Log, General Log, Error Log, and Query-Digest Workflows

Learning outcomes

Some incidents are easiest to understand from event streams rather than in-memory counters: a query crossed a latency threshold, a client sent a destructive statement, or the server emitted a startup/replication error. MariaDB’s slow, general, and error logs serve different purposes and have different cost and security profiles. The operational skill is not merely turning them on—it is defining a bounded capture, protecting sensitive content, rotating/retaining it, and reducing the raw records into query shapes that can be investigated.

1. Three logs, three questions

The binary log is not a substitute for any of these: its job is replication/recovery change history, not diagnostic query logging.

DROP DATABASE IF EXISTS servicehub17;CREATE DATABASE servicehub17 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub17;CREATE TABLE tickets (  ticket_id BIGINT PRIMARY KEY AUTO_INCREMENT,  customer_id BIGINT NOT NULL,  status ENUM('open','waiting','closed') NOT NULL,  priority TINYINT NOT NULL,  opened_at DATETIME(6) NOT NULL,  closed_at DATETIME(6) NULL,  INDEX ix_status_opened(status, opened_at),  INDEX ix_customer(customer_id)) ENGINE=InnoDB;CREATE TABLE ticket_events (  event_id BIGINT PRIMARY KEY AUTO_INCREMENT,  ticket_id BIGINT NOT NULL,  event_type VARCHAR(40) NOT NULL,  event_at DATETIME(6) NOT NULL,  payload VARCHAR(500) NULL,  INDEX ix_ticket_time(ticket_id, event_at)) ENGINE=InnoDB;INSERT INTO tickets(customer_id,status,priority,opened_at,closed_at)WITH RECURSIVE seq AS (  SELECT 1 AS n  UNION ALL SELECT n+1 FROM seq WHERE n < 500)SELECT 1000 + (n % 80),       CASE WHEN n % 7 = 0 THEN 'closed' WHEN n % 3 = 0 THEN 'waiting' ELSE 'open' END,       1 + (n % 5),       NOW(6) - INTERVAL n MINUTE,       CASE WHEN n % 7 = 0 THEN NOW(6) - INTERVAL (n-2) MINUTE ELSE NULL ENDFROM seq;INSERT INTO ticket_events(ticket_id,event_type,event_at,payload)SELECT ticket_id,       CASE WHEN ticket_id % 4=0 THEN 'comment' ELSE 'status_change' END,       opened_at + INTERVAL 30 SECOND,       RPAD('x', 120, 'x')FROM tickets;

2. Slow log: use current names, verify effective values

MariaDB 10.11 introduced log_slow_query, log_slow_query_time, and related names while retaining familiar aliases such as slow_query_log and long_query_time. On a current server, query the actual variables rather than assuming which spelling your package exposes.

SHOW VARIABLES LIKE 'log_output';SHOW VARIABLES LIKE 'log_slow_query';SHOW VARIABLES LIKE 'log_slow_query_time';SHOW VARIABLES LIKE 'slow_query_log';SHOW VARIABLES LIKE 'long_query_time';SET GLOBAL log_output='TABLE';SET GLOBAL log_slow_query=OFF;TRUNCATE TABLE mysql.slow_log;SET GLOBAL log_slow_query_time=0.05;SET GLOBAL log_slow_query=ON;

TABLE output keeps this lesson local and easy to inspect. Production FILE logging is common, but file ownership, disk capacity, rotation, and OS service configuration then become part of the design.

SELECT SLEEP(0.10);SELECT start_time, query_time, rows_sent, rows_examined, db, sql_textFROM mysql.slow_logORDER BY start_time DESCLIMIT 10;

The sleep proves the capture path, not that your real workload has the same problem. For real queries, correlate rows examined/sent, digest frequency, and EXPLAIN/ANALYZE evidence.

3. Filters, verbosity, and why “not using index” can flood logs

Current MariaDB slow logging supports filtering and verbosity controls. A common mistake is to log every statement that does not use an index; many fast small-table queries legitimately scan, so the volume can obscure the expensive work.

SHOW VARIABLES LIKE 'log_slow_filter';SHOW VARIABLES LIKE 'log_slow_verbosity';SHOW VARIABLES LIKE 'log_slow_min_examined_row_limit';SHOW VARIABLES LIKE 'log_slow_rate_limit';

Choose criteria from an operational question. For example, a short investigation may target temp tables or full scans, while a production baseline may use a latency threshold plus a minimum examined-row limit. Never copy a universal threshold: 100 ms can be catastrophic for one API and irrelevant for a batch warehouse job.

4. General log: powerful, expensive, and easy to misuse

SET GLOBAL log_output='TABLE';TRUNCATE TABLE mysql.general_log;SET GLOBAL general_log=ON;SELECT COUNT(*) FROM servicehub17.tickets WHERE status='open';SET GLOBAL general_log=OFF;SELECT event_time, user_host, thread_id, command_type, argumentFROM mysql.general_logORDER BY event_time DESCLIMIT 20;

The general log records statements received by the server, making it useful for a short “what is this client actually sending?” investigation. Leaving it enabled indefinitely on a busy system can create substantial I/O/storage overhead and sensitive-data exposure. Your runbook should state who may enable it, for how long, where output goes, and how it is removed afterward.

5. Error log: destination is an operational property

SHOW VARIABLES LIKE 'log_error';SHOW VARIABLES LIKE 'log_basename';

On Linux distributions managed by systemd, some server messages may also be available through the service journal; packages can choose different defaults. On Windows, the service and Event Viewer integration differs. Therefore “tail this hard-coded path” is not portable guidance. Verify the effective log_error, service configuration, and filesystem permissions on the target host.

6. Query-digest workflow: reduce records, then return to mechanisms

MariaDB ships mariadb-dumpslow for slow-log summarization on installations that include the client utilities. You can also use Performance Schema digests. The important idea is fingerprinting: normalize literal differences, rank query shapes, then inspect the real statement and plan.

SELECT DIGEST_TEXT, COUNT_STAR,       ROUND(SUM_TIMER_WAIT/1000000000000,6) AS total_seconds,       SUM_ROWS_EXAMINED, SUM_ROWS_SENTFROM performance_schema.events_statements_summary_by_digestWHERE SCHEMA_NAME='servicehub17'ORDER BY SUM_TIMER_WAIT DESCLIMIT 10;EXPLAINSELECT * FROM servicehub17.ticketsWHERE customer_id=1021ORDER BY opened_at DESCLIMIT 10;

A digest rank and a slow-log rank should tell a compatible story if they cover the same workload/window, but they are not identical datasets. Slow-log filters/sampling can omit fast statements; Performance Schema summaries can be reset or bounded by digest capacity.

7. Wrong approach: log everything to FILE forever

The unsafe pattern is to enable general logging plus an extremely low slow threshold on a production server, write both to an unrestricted volume, and forget them. The first symptom may be disk exhaustion; the deeper problem is unbounded sensitive telemetry.

SET GLOBAL general_log=OFF;SET GLOBAL log_slow_query=OFF;SELECT @@global.general_log,       @@global.log_slow_query,       @@global.log_output;

In production, also restore the previous threshold/filter values, secure/delete the capture according to policy, and document the change in the incident timeline.

8. Production judgment and cleanup

SET GLOBAL general_log=OFF;SET GLOBAL log_slow_query=OFF;DROP DATABASE IF EXISTS servicehub17;