Page MenuHomePhabricator

Create a logstash input filter to preprocess mysqld syslog messages
Closed, DeclinedPublic

Description

On the beta cluster, we had mysqld to emit syslog which are relayed to logstash (T119370). I have crafted a basic dashboard that search for syslog messages matching the program mysqld:

https://logstash-beta.wmflabs.org/app/kibana#/dashboard/mysql

Message deduplication does not work quite well since MySQL prefix the message payload with the PID and time.

Seems MariaDB emits all messages at error level (havent checked).

They look like (message shortened by me):

_typesyslog
facility3
facility_labelsystem
levelERROR
severity3
severity_labelError
programmysqld
message160719 9:20:36 [Warning] Unsafe statement written to the binary ...
normalized_message160719 9:20:36 [Warning] Unsafe statement written to the binary ...

At least in the message field, we would want to drop the PID and time.

Ideally parse the message to set / correct the level.


From https://mariadb.com/kb/en/mariadb/error-log/#format

Until MariaDB 10.1.4, the format consisted of the date (yymmdd) and time, followed by the type of error (Note, Warning or Error) and the error message, for example:

160615 16:53:08 [Note] InnoDB: The InnoDB memory heap is disabled

From MariaDB 10.1.5, the date format has been extended to yyyy-mm-dd, and the thread ID has been added, for example:

2016-06-15 16:53:33 139651251140544 [Note] InnoDB: The InnoDB memory heap is disabled

The type of error is one of Note, Warning or Error.

Event Timeline

This is a known issue, and I cannot find one now, but there are numerous examples found elsewere of people that have done that before and shared it.

One thing that you may want for beta, that we could not do for production, is to enable more verbose logging such as the slow log and the general log. It will depend on the resources available and the traffic. But they can be tuned and they would be very useful if debugging is needed (it may be even useful to have them prepared, but not enabled).

Be careful because on start/stop, you will find some multi-line "errors" (aside from a proper error log, it also serves as a core dump indication and recovery log).

I can no longer find these messages in production nor in beta logs. It seems program:mysqld disappeared some time ago?

This task may be safe to close.

The issue was that syslog message were logged as is from syslog and the timestamp included in the messages prevented them from being aggregated in the view. If they are gone, I guess we can ignore this nowadays ;)