Rewrite processlist collector

[roman-vynar]

Hello,

Rewritten processlist collector to include both Command and State columns and stop doing state remapping.

Couple of reasons for this:

• The existing collector has been last updated 3 years ago when we had no MySQL 8.0. Since that time we have got more process states and changes to the existing ones https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html
  The old re-mapping is not accurate, somewhat outdated and requires constant maintaining. With this PR, we won't bother about thread state mapping and it will flexible for anyone to do what they want to do on the expression side.
• The existing collector does incorrect grouping by Command/State. For example, anything Command=Query is considered as State=executing which may include:

{command="query", state="checking_permissions"}
{command="query", state="executing"}
{command="query", state="reading_event_from_the_relay_log"}
{command="query", state="sending_to_client"}
{command="query", state="slave_has_read_all_relay_log_waiting_for_more_updates"}
{command="query", state="waiting_for_global_read_lock"}
{command="query", state="waiting_for_handler_commit"}

As you can see it is not always an execution phase of regular queries but a whole bunch of stuff that we may want to exclude for alerting and which is the normal thing, for say Group Replication waiting_for_handler_commit (it is a phase of group commit where it is waiting and not consuming CPU) as opposite to the user queries (consuming CPU).
It becomes very handy when we want to sum all queries and exclude some particular states, or just see on the graph how those state are distributed to decide what to do.

• There are other commands like "Connect", "Daemon", "Binlog dump" which we may want to see w/o being grouped/remapped into some states.

Breaking changes:

• Added "command" label to processlist "threads" and "seconds" metrics. No much increase in number of time-series.
• Stop doing remapping of the existing states. Just simple text normalization for command/state values.
• Metrics has been renamed to be easily found by name of the collector and for consistency that they belong to the same one:

mysql_info_schema_threads > mysql_info_schema_processlist_threads
mysql_info_schema_threads_seconds > mysql_info_schema_processlist_seconds
mysql_info_schema_processes_by_user > mysql_info_schema_processlist_processes_by_user
mysql_info_schema_processes_by_host > mysql_info_schema_processlist_processes_by_host

Also added a test.

There is a similar PR #442 but it just adds "command" and retains the state remapping.
Another PR #381 complaining about remapping.

Sample output:

# TYPE mysql_info_schema_processlist_processes_by_host gauge
mysql_info_schema_processlist_processes_by_host{client_host="10.0.7.154"} 8
mysql_info_schema_processlist_processes_by_host{client_host="10.0.7.224"} 1
mysql_info_schema_processlist_processes_by_host{client_host="10.0.7.234"} 24
mysql_info_schema_processlist_processes_by_host{client_host="blank"} 2
mysql_info_schema_processlist_processes_by_host{client_host="localhost"} 1
# HELP mysql_info_schema_processlist_processes_by_user The number of processes by user.
# TYPE mysql_info_schema_processlist_processes_by_user gauge
mysql_info_schema_processlist_processes_by_user{mysql_user="manager"} 20
mysql_info_schema_processlist_processes_by_user{mysql_user="store"} 1
mysql_info_schema_processlist_processes_by_user{mysql_user="event_scheduler"} 1
mysql_info_schema_processlist_processes_by_user{mysql_user="feedback"} 1
mysql_info_schema_processlist_processes_by_user{mysql_user="message"} 4
mysql_info_schema_processlist_processes_by_user{mysql_user="root"} 1
mysql_info_schema_processlist_processes_by_user{mysql_user="system user"} 2
# HELP mysql_info_schema_processlist_threads The number of threads split by current state.
# TYPE mysql_info_schema_processlist_threads gauge
mysql_info_schema_processlist_threads{command="connect",state="waiting_for_handler_commit"} 1
mysql_info_schema_processlist_threads{command="daemon",state="waiting_on_empty_queue"} 1
mysql_info_schema_processlist_threads{command="query",state="slave_has_read_all_relay_log_waiting_for_more_updates"} 1
mysql_info_schema_processlist_threads{command="sleep",state="blank"} 36
# HELP mysql_info_schema_processlist_seconds The number of seconds threads have used split by current state.
# TYPE mysql_info_schema_processlist_seconds gauge
mysql_info_schema_processlist_seconds{command="connect",state="waiting_for_handler_commit"} 7.339619e+06
mysql_info_schema_processlist_seconds{command="daemon",state="waiting_on_empty_queue"} 7.339619e+06
mysql_info_schema_processlist_seconds{command="query",state="slave_has_read_all_relay_log_waiting_for_more_updates"} 7.339619e+06
mysql_info_schema_processlist_seconds{command="sleep",state="blank"} 1030

[SuperQ]

Thanks!

One question, this drops the ORDER BY null, I seem to remember this was a small performance optimization. Possibly for a very old version of MySQL.

[roman-vynar]

Yea, looks like it's from some very old MySQL. Normally, it shouldn't do anything useful but being present :)

[SuperQ]

Looks like there are some unrelated mixin errors.

[SuperQ]

There are some issues with the metric names.

[roman-vynar]

You are right. I corrected the names and the description about breaking changes on the first post.

[SuperQ]

LGTM, Thanks!

[maxemann96]

The example grafana dashboard still uses some old metric names (I didn't checked the other ones)
https://github.com/prometheus/mysqld_exporter/blob/main/mysqld-mixin/dashboards/mysql-overview.json#L2481
https://github.com/prometheus/mysqld_exporter/blob/main/mysqld-mixin/dashboards/mysql-overview.json#L2591