Implement Memory information capture from performance_schema on MySQL 5.7

[PeterZaitsev]

Note this table only exists on MySQL 5.7 so graceful handling of its absence is recommended

mysql> select * from memory_summary_global_by_event_name where count_alloc>0 order by current_number_of_bytes_used desc limit 2 \G
*************************** 1. row ***************************
EVENT_NAME: memory/innodb/buf_buf_pool
COUNT_ALLOC: 192
COUNT_FREE: 0
SUM_NUMBER_OF_BYTES_ALLOC: 26398949376
SUM_NUMBER_OF_BYTES_FREE: 0
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 192
HIGH_COUNT_USED: 192
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 26398949376
HIGH_NUMBER_OF_BYTES_USED: 26398949376
*************************** 2. row ***************************
EVENT_NAME: memory/innodb/hash0hash
COUNT_ALLOC: 82
COUNT_FREE: 6
SUM_NUMBER_OF_BYTES_ALLOC: 1976975584
SUM_NUMBER_OF_BYTES_FREE: 1223839920
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 76
HIGH_COUNT_USED: 76
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 753135664
HIGH_NUMBER_OF_BYTES_USED: 1161079744
2 rows in set (0.01 sec)

There are a lot of columns here. I think most interesting are ones related to the memory usage not number of allocation calls:

CURRENT_NUMBER_OF_BYTES_USED: 753135664
HIGH_NUMBER_OF_BYTES_USED: 1161079744

Which correspond to current usage and highest usage ever. Former is helpful for catching high memory usage by stored procedures or other areas where allocation is done for short term.

[dafydd-t]

I'd like to take on this work. We'd like to export some of this data so we can better visualise our MySQL 5.7 memory usage.

Proposed work

Handle case where this table doesn't exist, so the exporter is still compatible with earlier MySQL versions.

Use the following SQL query, with some explanations below:

select EVENT_NAME,COUNT_FREE,COUNT_ALLOC,SUM_NUMBER_OF_BYTES_ALLOC,SUM_NUMBER_OF_BYTES_FREE,CURRENT_COUNT_USED,CURRENT_NUMBER_OF_BYTES_USED from performance_schema.memory_summary_global_by_event_name where HIGH_COUNT_USED > 0;

• don’t filter any events out by name. This means performance_schema events will be exported. I think it is useful to see how much memory the performance_schema engine itself is using.
• filter out events where HIGH_COUNT_USED is 0. This will remove events that have never allocated any memory (we could filter out on CURRENT_COUNT_USED instead, which would be a more aggressive filter).

Generate these metrics:

metric name	metric type	associated column
memory_blocks_freed_total	Count	COUNT_FREE
memory_blocks_alloc_total	Count	COUNT_ALLOC
Memory_bytes_alloc_total	Count	SUM_NUMBER_OF_BYTES_ALLOC
memory_bytes_free_total	Count	SUM_NUMBER_OF_BYTES_FREE

We can calculate current, high and low usage for bytes and blocks from the totals above, using promQL functions. This will reduce the number of unique series we export.

Number of series

There are 376 events without any filtering. So this will be highest number of series we generate. Real world usage might be lower.
4 metrics * 376 events makes 1504 series.
We could put this exporter behind its own flag, so that we don’t contribute this significant number of extra series by default.

I'd really appreciate your thoughts!

[SuperQ]

@dafydd-t Thanks for taking this on.

• I'm not sure how useful the block counts are compared to bytes. What if we just exposed SUM_NUMBER_OF_BYTES_{ALLOC,FREE} and not the block metrics?
• I think the original proposal of WHERE count_alloc > 0 is "more correct" for determining which metrics to expose.
• I think we should include CURRENT_NUMBER_OF_BYTES_USED as a gauge.

That would reduce the number of metrics per event name to 3.

[dafydd-t]

@SuperQ those recommendations sound great, thanks for the response.

[dafydd-t]

@SuperQ I think we can close this ticket now :)

[SuperQ]

Thanks!