Could I use SQL text data in Prometheus?

Percona Monitoring and Management (PMM) PMM 1.x
1

Hi, this is BcKim.

I have one question about Mysql user query data in Prometheus.

I need to check processlist information based on timeline.

but PMM can’t support processlist information with SQL text data.

so I found this.

– "mysqld_exporter/collector/perf_schema_events_statements"

This collector can gather Mysql user query data.

this is the gathering query.

– "perf_schema_events_statements" gather query [TABLE]
[TR]
[TD]const perfEventsStatementsQuery = [/TD] [/TR] [TR] [TD] [/TD] [TD]SELECT[/TD] [/TR] [TR] [TD] [/TD] [TD]ifnull(SCHEMA_NAME, 'NONE') as SCHEMA_NAME,[/TD] [/TR] [TR] [TD] [/TD] [TD]DIGEST,[/TD] [/TR] [TR] [TD] [/TD] [TD][COLOR=#FF0000][B]LEFT(DIGEST_TEXT, %d) as DIGEST_TEXT,[/B][/TD] [/TR] [TR] [TD] [/TD] [TD]COUNT_STAR,[/TD] [/TR] [TR] [TD] [/TD] [TD]SUM_TIMER_WAIT,[/TD] [/TR] [TR] [TD] [/TD] [TD]SUM_ERRORS,[/TD] [/TR] [TR] [TD] [/TD] [TD]SUM_WARNINGS,[/TD] [/TR] [TR] [TD] [/TD] [TD]SUM_ROWS_AFFECTED,[/TD] [/TR] [TR] [TD] [/TD] [TD]SUM_ROWS_SENT,[/TD] [/TR] [TR] [TD] [/TD] [TD]SUM_ROWS_EXAMINED,[/TD] [/TR] [TR] [TD] [/TD] [TD]SUM_CREATED_TMP_DISK_TABLES,[/TD] [/TR] [TR] [TD] [/TD] [TD]SUM_CREATED_TMP_TABLES,[/TD] [/TR] [TR] [TD] [/TD] [TD]SUM_SORT_MERGE_PASSES,[/TD] [/TR] [TR] [TD] [/TD] [TD]SUM_SORT_ROWS,[/TD] [/TR] [TR] [TD] [/TD] [TD]SUM_NO_INDEX_USED[/TD] [/TR] [TR] [TD] [/TD] [TD]FROM ([/TD] [/TR] [TR] [TD] [/TD] [TD]SELECT *[/TD] [/TR] [TR] [TD] [/TD] [TD]FROM performance_schema.events_statements_summary_by_digest[/TD] [/TR] [TR] [TD] [/TD] [TD]WHERE SCHEMA_NAME NOT IN ('mysql', 'performance_schema', 'information_schema')[/TD] [/TR] [TR] [TD] [/TD] [TD]AND LAST_SEEN > DATE_SUB(NOW(), INTERVAL %d SECOND)[/TD] [/TR] [TR] [TD] [/TD] [TD]ORDER BY LAST_SEEN DESC[/TD] [/TR] [TR] [TD] [/TD] [TD])Q[/TD] [/TR] [TR] [TD] [/TD] [TD]GROUP BY[/TD] [/TR] [TR] [TD] [/TD] [TD]Q.SCHEMA_NAME,[/TD] [/TR] [TR] [TD] [/TD] [TD]Q.DIGEST,[/TD] [/TR] [TR] [TD] [/TD] [TD]Q.DIGEST_TEXT,[/TD] [/TR] [TR] [TD] [/TD] [TD]Q.COUNT_STAR,[/TD] [/TR] [TR] [TD] [/TD] [TD]Q.SUM_TIMER_WAIT,[/TD] [/TR] [TR] [TD] [/TD] [TD]Q.SUM_ERRORS,[/TD] [/TR] [TR] [TD] [/TD] [TD]Q.SUM_WARNINGS,[/TD] [/TR] [TR] [TD] [/TD] [TD]Q.SUM_ROWS_AFFECTED,[/TD] [/TR] [TR] [TD] [/TD] [TD]Q.SUM_ROWS_SENT,[/TD] [/TR] [TR] [TD] [/TD] [TD]Q.SUM_ROWS_EXAMINED,[/TD] [/TR] [TR] [TD] [/TD] [TD]Q.SUM_CREATED_TMP_DISK_TABLES,[/TD] [/TR] [TR] [TD] [/TD] [TD]Q.SUM_CREATED_TMP_TABLES,[/TD] [/TR] [TR] [TD] [/TD] [TD]Q.SUM_SORT_MERGE_PASSES,[/TD] [/TR] [TR] [TD] [/TD] [TD]Q.SUM_SORT_ROWS,[/TD] [/TR] [TR] [TD] [/TD] [TD]Q.SUM_NO_INDEX_USED[/TD] [/TR] [TR] [TD] [/TD] [TD]ORDER BY SUM_TIMER_WAIT DESC[/TD] [/TR] [TR] [TD] [/TD] [TD]LIMIT %d[/TD] [/TR] [TR] [TD] [/TD] [TD][/TD]
[/TR]
[/TABLE]
"

as you can see, this collector gather the sql text data.

but I couldn’t find Mysql user query data in Prometheus.

please let me know about finding sql text data in Prometheus.

thanks.

2

PMM by default does not enable this collector, however you can do this if you like:

pmm-admin add mysql:metrics – --collect.perf_schema.eventsstatements=true and other appropriate options as described here [url]https://github.com/prometheus/mysqld_exporter[/url] We however do not recommend that as this will produce very heavy load on your Prometheus in case you have large number of different query kinds. The query text will be stored as a “labels” in Prometheus as it required values themselves to be numerical

3

Thank you for your answer.

I have one more question.

If I use Oracle Mysql db, Can I use the “–collect.perf_schema.eventsstatements” option? Can I collect sql text data using this option?

thank you!