Not the answer you need?
Register and ask your own question!

Could I use SQL text data in Prometheus?

bckimbckim ContributorCurrent User Role Beginner
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

const perfEventsStatementsQuery = `



SELECT



ifnull(SCHEMA_NAME, 'NONE') as SCHEMA_NAME,



DIGEST,



LEFT(DIGEST_TEXT, %d) as DIGEST_TEXT,



COUNT_STAR,



SUM_TIMER_WAIT,



SUM_ERRORS,



SUM_WARNINGS,



SUM_ROWS_AFFECTED,



SUM_ROWS_SENT,



SUM_ROWS_EXAMINED,



SUM_CREATED_TMP_DISK_TABLES,



SUM_CREATED_TMP_TABLES,



SUM_SORT_MERGE_PASSES,



SUM_SORT_ROWS,



SUM_NO_INDEX_USED



FROM (



SELECT *



FROM performance_schema.events_statements_summary_by_digest



WHERE SCHEMA_NAME NOT IN ('mysql', 'performance_schema', 'information_schema')



AND LAST_SEEN > DATE_SUB(NOW(), INTERVAL %d SECOND)



ORDER BY LAST_SEEN DESC



)Q



GROUP BY



Q.SCHEMA_NAME,



Q.DIGEST,



Q.DIGEST_TEXT,



Q.COUNT_STAR,



Q.SUM_TIMER_WAIT,



Q.SUM_ERRORS,



Q.SUM_WARNINGS,



Q.SUM_ROWS_AFFECTED,



Q.SUM_ROWS_SENT,



Q.SUM_ROWS_EXAMINED,



Q.SUM_CREATED_TMP_DISK_TABLES,



Q.SUM_CREATED_TMP_TABLES,



Q.SUM_SORT_MERGE_PASSES,



Q.SUM_SORT_ROWS,



Q.SUM_NO_INDEX_USED



ORDER BY SUM_TIMER_WAIT DESC



LIMIT %d



`


"

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.

Comments

  • PeterPeter Percona CEO Percona Moderator Role
    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 https://github.com/prometheus/mysqld_exporter 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
  • bckimbckim Contributor Current User Role Beginner
    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!
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.