MySQL slow query and error logs on one OpenSearch dashboard

The MySQL pack in the Logit.io Dashboard Library installs one OpenSearch Dashboards overview, Logit.io MySQL log overview, that reads your MySQL error log and slow query log together on filebeat-*. It shows whether database logs are still arriving, whether slow queries or server warnings are rising and the newest slow statements with their duration, host, user and rows examined. It only fills when Filebeat ships both files through its mysql module, because every panel filters on event.module:mysql and event.dataset.

This guide is for teams who run their own MySQL servers (on VMs, bare metal or in containers with the log files on a volume) and already send, or want to send, logs to a Logit.io Logs stack. It covers the two MySQL settings most servers are missing, the Filebeat module config, the checks that prove the fields are right before you install, how to work a slow query from the dashboard to the query plan and what to do when a panel stays empty.

One page, eight panels and what each one counts

The pack is called MySQL in the Dashboard Library and its single dashboard is Logit.io MySQL log overview. It reads the filebeat-* index pattern with @timestamp as the time field, opens on the last 24 hours and refreshes every 60 seconds. No panel overrides the time range you pick.

Every panel starts from the same base query, written in Lucene:

event.module:mysql AND agent.type:filebeat AND event.dataset:(mysql.error OR mysql.slowlog)

That line explains most of what you'll see. A document only counts if Filebeat's mysql module produced it, so the two datasets are mysql.error (the error log) and mysql.slowlog (the slow query log). Each panel then narrows the base query:

Two things are worth knowing before you rely on it. First, the slow query count is not query throughput. MySQL only writes a statement to the slow log when it crosses your threshold, so the tile moves when you change long_query_time as much as when the workload changes. Second, a record in the error file isn't necessarily an error. MySQL writes startup, shutdown and informational lines to the same file, which is why severity has its own donut and why the warnings tile filters on log.level.

The page deliberately leaves out what logs can't tell you: total query rate, connections, replication lag, buffer pool use and latency percentiles. Those are metrics, and the MySQL integration guide covers them separately with Telegraf.

Turn on the logs MySQL doesn't write by default

The error log is always on. The slow query log isn't: slow_query_log defaults to off, and when it is on, long_query_time defaults to 10 seconds, which is too high to catch most of the queries that hurt a web application. On MySQL 8.0 and 8.4 this block in my.cnf (or a file under /etc/mysql/mysql.conf.d/ on Debian and Ubuntu) gives you both files somewhere predictable:

[mysqld]
# Error log: always on, give it a fixed path
log_error           = /var/log/mysql/error.log
log_error_verbosity = 2          # errors and warnings; 3 adds notes

# Slow query log
slow_query_log      = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time     = 1          # seconds; decimals such as 0.5 are allowed
log_output          = FILE       # the module reads files, not the mysql.slow_log table

You don't need a restart to start the slow log. As a user with the SYSTEM_VARIABLES_ADMIN privilege, run:

SET PERSIST slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET PERSIST long_query_time = 1;
SET PERSIST slow_query_log = ON;

SHOW GLOBAL VARIABLES WHERE Variable_name IN
  ('slow_query_log', 'slow_query_log_file', 'long_query_time', 'log_output', 'log_error');

SET PERSIST writes the change to mysqld-auto.cnf so it survives a restart. The new long_query_time applies to connections opened after the change, so connection pools keep the old threshold until they reconnect. Make sure the directory exists and the mysql user can write to it, or the server refuses to open the file and writes the reason to the error log.

Pick the threshold with your workload in mind. One second is a sensible start for an OLTP application. Going lower (0.2 or 0.1) catches more, but on a busy server the slow log can grow quickly and every line is a document on your stack. Leave log_queries_not_using_indexes off to begin with: it logs every unindexed query regardless of time, which on most schemas means a flood of small lookups on tiny tables.

Ship both filesets with the Filebeat mysql module

The fields the pack reads (event.dataset, log.level, event.duration, mysql.slowlog.*) come from Filebeat's mysql module. A plain log or filestream input pointed at the same files gets the lines into your stack, but with no event.module, no parsing and no match, so the Library won't detect the pack and the panels stay empty.

Start with the MySQL integration guide in your Logit.io account. Choose Install Integration first, which configures your stack for this source, then install Filebeat for your OS as the guide shows. The guide's examples target Filebeat 8.x. On a Debian or Ubuntu server, enable the module:

sudo filebeat modules enable mysql

Both filesets are disabled by default, so edit /etc/filebeat/modules.d/mysql.yml. Setting the paths explicitly saves guessing which defaults your distribution uses:

- module: mysql
  error:
    enabled: true
    var.paths: ["/var/log/mysql/error.log*"]
  slowlog:
    enabled: true
    var.paths: ["/var/log/mysql/mysql-slow.log*"]

On Red Hat family servers the error log is often /var/log/mysqld.log, and if you never set slow_query_log_file the slow log sits in the data directory as <hostname>-slow.log. Run SHOW GLOBAL VARIABLES LIKE '%log_file%' and point the paths at what the server reports. The trailing * keeps rotated files in scope.

Then set the output in /etc/filebeat/filebeat.yml. Copy the block from the integration guide while signed in, because it fills in your stack's Logstash host and port:

filebeat.config.modules:
  path: ${path.config}/modules.d/*.yml
  reload.enabled: false

output.logstash:
  hosts: ["your-logstash-host:your-ssl-port"]
  loadbalance: true
  ssl.enabled: true

processors:
  - add_host_metadata:
      when.not.contains.tags: forwarded

Keep add_host_metadata. It sets host.name, which is how you tell servers apart in the Slow queries to analyse table. Then test and start:

sudo filebeat test config
sudo filebeat test output
sudo systemctl enable --now filebeat
sudo journalctl -u filebeat -f

test output should end with the TLS handshake and talk to server... OK. If it can't connect, fix that before anything else: nothing downstream will help. Beats data sent through your stack's Logstash lands in daily filebeat-* indices, which is the pattern the pack reads. If you have renamed the index in your Logstash pipeline, the dashboards won't see the data.

If MySQL runs in a container, the same module works as long as the container writes its logs to files on a volume that Filebeat can read. Many MySQL images log the error stream to stderr instead, in which case point log_error at a file on a mounted volume or run Filebeat with access to that path.

Prove the fields before you install

Ten minutes here saves an hour of staring at an empty table. First, give the slow log something to record. Open a new connection (so it picks up the new threshold) and run:

SELECT SLEEP(2);

On the server, sudo tail -n 8 /var/log/mysql/mysql-slow.log should show an entry starting # User@Host: with Query_time: 2.0 and the statement. That proves MySQL is writing. Now open Discover in OpenSearch Dashboards, choose filebeat-*, set the query language to Lucene and paste the base query from above. Then narrow it to slow-log records of two seconds or more:

event.module:mysql AND event.dataset:mysql.slowlog AND event.duration:>=2000000000

Set the time picker to the last 15 minutes and the sleep should be the newest hit.

Open the document and check four things. event.duration is about 2000000000, because the module stores duration in nanoseconds. mysql.slowlog.rows_examined and mysql.slowlog.rows_sent are numbers. user.name is the account you connected as. And there's no message field: the slow log parser removes it once it has pulled the fields out, so a slow-log document that still has a raw multi-line message wasn't parsed. For the error log, look at a mysql.error document and confirm log.level reads System, Warning, Error or Note and, on MySQL 8, that event.code holds the MY- code.

Next, check the types with the OpenSearch API on your stack. The endpoint and stack API key are on the Endpoints page in your stack settings. The guide to connecting to your stack's OpenSearch API covers both authentication modes. In API key mode:

curl -s "https://<stack-id>-es.logit.io/filebeat-*/_field_caps?fields=event.module,event.dataset,event.duration,log.level,mysql.slowlog.query,mysql.slowlog.rows_examined,user.name,source.ip&apikey=<stack-api-key>&pretty"

You want event.module, event.dataset, log.level, user.name and mysql.slowlog.query as keyword, event.duration and mysql.slowlog.rows_examined as long and source.ip as ip. Unlike logstash-*, where string fields are text with a .keyword sub-field, the Beats mapping on filebeat-* maps these ECS fields as keyword directly, which is why the panels group on event.dataset rather than event.dataset.keyword. The default Filebeat template is applied when an index is created, so fix types in the pipeline rather than editing the live mapping. Existing daily indices keep their first type until they age out.

Finally, count what the Library will see. Detection looks at the last 24 hours of filebeat-* for event.module: mysql and needs event.dataset to be aggregatable:

curl -s -H 'Content-Type: application/json' \
  "https://<stack-id>-es.logit.io/filebeat-*/_search?apikey=<stack-api-key>" -d '{
  "size": 0,
  "query": { "bool": { "filter": [
    { "range": { "@timestamp": { "gte": "now-24h" } } },
    { "term":  { "event.module": "mysql" } }
  ] } },
  "aggs": {
    "datasets": { "terms": { "field": "event.dataset" } },
    "hosts":    { "terms": { "field": "host.name", "size": 20 } }
  }
}'

You should see both mysql.error and mysql.slowlog buckets and one hosts bucket per server. A missing dataset bucket tells you which fileset to fix before you install.

Install the pack and read the page

Open your Logs stack's card in your Logit.io account and choose Dashboard Library. Search for MySQL. Once matching events have arrived the pack shows a Data detected badge. If you've only just started shipping, press Rescan stack. The pack has a single dashboard, so tick its card, choose Install dashboards and open it in OpenSearch Dashboards a minute or two later. The Dashboard Library guide covers the permissions you need and what gets installed, and the post on how Dashboard Library detection works explains the badge in more depth.

Read the page top to bottom, the way it was laid out:

1. The four tiles. Last event received tells you whether quiet means healthy or means logs have stopped. If your servers log continuously, a timestamp more than about two hours old is worth a look. Server warnings is the one tile designed to go red.
2. The arrivals trend. The two datasets are stacked, so a slow-log band that suddenly thickens at 02:00 points at a batch job, and one that thickens at the same time as a deploy points at a code change.
3. The two donuts. Log stream share shows the balance of error-file and slow-log records. Server message severity shows what the error file actually contains. A large Unknown slice means lines without a parsed level, usually multi-line output such as an InnoDB status dump.
4. Slow queries to analyse. The newest ten slow statements, newest first, with the host, user and SQL. Expand a row to read long SQL in full.

Work a slow query from tile to plan

The page is most useful on the afternoon the application gets slow and somebody asks whether it's the database. A typical path:

1. Recorded slow queries. Compare today with the same window yesterday using the time picker. A tile that has doubled with the same threshold is a real change.
2. Database log arrivals over time. Find when the slow-log band grew. Line that up with deploys, batch jobs and traffic.
3. Slow queries to analyse. Filter to the host from the table (click the host.name value and add it as a filter) and read the SQL. One statement repeated means one query to fix. Many different statements slowing down at once usually means something shared: locks, I/O or a saturated server.
4. Discover on the row counts. Open the same time range in Discover and compare rows examined with rows sent.
5. EXPLAIN on the server. Run EXPLAIN (or EXPLAIN ANALYZE on MySQL 8.0.18 and later) on the statement to confirm the plan before you change an index.

The row counts in step 4 are the quickest way to tell a missing index from a wait. A statement that examines hundreds of thousands of rows to return a handful is scanning:

event.dataset:mysql.slowlog AND mysql.slowlog.rows_examined:>=100000 AND mysql.slowlog.rows_sent:<100

A statement that is slow but examines few rows was waiting on something. Lock time is in its own field, in seconds:

event.dataset:mysql.slowlog AND mysql.slowlog.lock_time.sec:>=0.5

And because duration is in nanoseconds, the filter for everything over five seconds is:

event.dataset:mysql.slowlog AND event.duration:>=5000000000

Here's the check that settles most "is it one query or everything?" arguments, as one API call. It sums slow-query time per host and per schema over the last six hours:

curl -s -H 'Content-Type: application/json' \
  "https://<stack-id>-es.logit.io/filebeat-*/_search?apikey=<stack-api-key>" -d '{
  "size": 0,
  "query": { "bool": { "filter": [
    { "range": { "@timestamp": { "gte": "now-6h" } } },
    { "term":  { "event.dataset": "mysql.slowlog" } }
  ] } },
  "aggs": {
    "by_host": {
      "terms": { "field": "host.name", "size": 10, "order": { "slow_ns": "desc" } },
      "aggs": {
        "slow_ns":  { "sum": { "field": "event.duration" } },
        "worst_ns": { "max": { "field": "event.duration" } },
        "rows":     { "sum": { "field": "mysql.slowlog.rows_examined" } },
        "by_schema": { "terms": { "field": "mysql.slowlog.schema", "size": 5, "missing": "(not logged)" } }
      }
    }
  }
}'

Divide slow_ns by 1,000,000,000 for seconds. One host carrying most of the total points at that server; an even spread points at the application or a shared dependency. The schema only appears when the slow log entry includes a use line, which MySQL writes when the default database changes, so expect some entries under "(not logged)".

Error log searches worth saving

The error file is quieter, but a few searches are worth saving next to the dashboard. Error-level lines only (MySQL 8.0 writes title-case labels such as Note, Warning and Error, and log.level is a case-sensitive keyword, so the query must use that exact case):

event.dataset:mysql.error AND log.level:Error

Everything from one MySQL error code, for example the self-signed certificate warning a fresh install writes at startup:

event.dataset:mysql.error AND event.code:"MY-010068"

Failed logins, which MySQL 8 only writes at log_error_verbosity = 3. The module parses these lines into event.code and message, so search the message text:

event.dataset:mysql.error AND message:"Access denied for user"

If that search starts returning bursts, you can build an alert on the same query. Raising verbosity to 3 also logs every aborted connection and other notes, so check the volume for a day before you leave it on.

When a panel stays empty or looks wrong

• The pack isn't detected. No document in the last 24 hours of filebeat-* has event.module: mysql. Either Filebeat is using a plain input instead of the module, the module is enabled but both filesets are still disabled or the events are going to a renamed index. The _search check above tells you which.
• Detected, but slow queries stay at zero. The slowlog fileset is disabled, var.paths points at the wrong file, slow_query_log is off, log_output is TABLE or nothing has crossed long_query_time. Run the SELECT SLEEP(2) test from a new connection.
• Slow-log documents have a raw message and no mysql.slowlog fields. The events arrived but weren't parsed. Check that you chose Install Integration on the MySQL integration page for this stack. If they still arrive unparsed, contact support with one example document.
• One slow query is split across several documents. The lines are being read without the module's multi-line handling, which means a plain input is reading the slow log as well as the module. Remove the duplicate input.
• Server warnings is always zero. The tile filters on log.level:Warning, exactly as MySQL writes it. If you've rewritten levels in a Logstash filter (to WARN or warning), the tile won't match. Leave log.level as MySQL writes it.
• The duration column shows huge numbers. That's nanoseconds. 2500000000 is 2.5 seconds. Whether the column is formatted as seconds depends on the field format of event.duration on your filebeat-* index pattern.
• source.ip is empty on some slow queries. Connections over the local socket are logged as localhost [] with no IP. That's normal for applications on the same host.
• A long query can't be searched or grouped. mysql.slowlog.query is a keyword with a 1,024 character limit. Longer statements are kept in the document and shown in the table, but they aren't indexed, so a search on that field won't find them. Filter on host.name, user.name or the time range instead.
• Last event received is empty. No MySQL events in the selected range. That is not a healthy server. Check Filebeat's own log first with journalctl -u filebeat; permission errors on /var/log/mysql and TLS errors to Logstash are the usual causes.
• Times look shifted. Slow-log times come from the SET timestamp= line, a Unix timestamp that doesn't depend on the server's time zone. Error-log times come from the line itself, in UTC by default. If you set log_timestamps = SYSTEM, make sure the server clock and time zone are right, or the two datasets drift apart on the trend.

What the pack doesn't tell you

• It's a sample, not a census. The slow log only holds statements above your threshold. Fast queries that run a million times an hour never appear, even when together they cost more than one slow report query.
• No query fingerprinting. The same statement with different literal values is a different mysql.slowlog.query value, so a terms breakdown on the SQL splits one problem into many rows. For a digest of normalised statements, run a slow-log digest tool against the file on the server, or use performance_schema statement digests.
• Managed MySQL is a different path. Amazon RDS, Aurora, Cloud SQL and Azure Database for MySQL don't give Filebeat access to the log files, and the pack only counts events from Filebeat's mysql module. Those services export logs through their own cloud logging, which is a separate setup.
• The format has to be the classic one. The parser expects MySQL's default text formats. If you've switched the error log to the JSON sink (log_sink_json), lines won't parse into these fields. Percona Server and MariaDB write closely related formats, but this guide was written against MySQL.
• Elastic Agent and OpenTelemetry don't match. The base query requires agent.type:filebeat. Logs sent another way still reach your stack, but not this dashboard.
• Slow logs can contain data. Statements are logged with their literal values, which can include email addresses or other personal data in WHERE clauses. Treat the slow log with the same care as the database, and check who can view filebeat-* on your stack.

FAQ

Which MySQL versions does the pack work with?

Any version whose error and slow log formats the Filebeat mysql module parses, which covers MySQL 5.7 and the 8.x releases in their default text formats. The examples in this guide use MySQL 8.0 and 8.4 settings such as SET PERSIST, which 5.7 doesn't have; on 5.7, put the settings in my.cnf and restart.

What should long_query_time be?

Start at one second for an OLTP application and watch the Recorded slow queries tile for a day. If it's near zero and users still complain, lower it to 0.5 or 0.2. If it's thousands an hour, fix the worst statements first rather than raising the threshold to hide them.

Do I need both filesets, or just the slow log?

Turn on both. The pack is detected from either, but the warnings tile, severity donut and the error searches above need the error file, and a crash or a failed startup only shows up there.

Why does the pack read filebeat-* rather than logstash-*?

Beats events sent through your stack's Logstash land in daily filebeat-* indices, and the Beats mapping there types the module's fields for you: duration and row counts as numbers, source.ip as an IP and the ECS strings as keywords. The dashboard is built for that mapping.

Can I change the slow query table or add panels?

Yes. Save a copy of the dashboard in OpenSearch Dashboards and change the copy, for example to add mysql.slowlog.lock_time.sec or mysql.slowlog.rows_sent as columns. Edit the copy rather than the installed dashboard, so the Library version stays as a reference.

Can I try it on the free trial?

Yes. The 14-day free trial includes every feature, including the Dashboard Library and Hosted OpenSearch MCP. Point Filebeat at one MySQL server, run the SELECT SLEEP(2) test and install the pack.

Next steps

Turn on the slow query log with a one-second threshold, enable both filesets in the Filebeat mysql module, run the sleep test and the _search check, then install the pack and look at it again tomorrow with a day of data. If you're running your own OpenSearch cluster for database logs, a Logit.io Logs stack gives you managed Logstash, OpenSearch and OpenSearch Dashboards with the Dashboard Library built in. For a dedicated cluster, see Hosted OpenSearch and OpenSearch pricing. To ask questions of the same slow query logs from any MCP-compatible AI assistant, see Hosted OpenSearch MCP.