How many tables do you have? This query is from the info_schema.tables collector <https://github.com/prometheus/mysqld_exporter/blob/f5d5289f7627016d8b53b0b3fa7615da8a73b56a/collector/info_schema_tables.go#L31-L44>, you can disable it by passing --collect.info_schema.tables=false to the exporter. The trade-off is that you will lose the table_size, table_rows, and table_version metrics.
/MR On Fri, Sep 4, 2020 at 4:29 PM Nemanja Delic <[email protected]> wrote: > # Time: 200825 0:22:29 17.997272 > # Time: 200825 0:22:55 15.140218 > # Time: 200825 0:28:41 10.765048 > # Time: 200825 0:29:04 20.452691 > # Time: 200825 0:29:10 19.051607 > # Time: 200825 0:29:30 14.527131 > # Time: 200825 0:29:35 13.516632 > # Time: 200825 0:30:19 20.151073 > # Time: 200825 0:30:31 19.574232 > # Time: 200825 0:30:45 15.090952 > # Time: 200825 0:30:55 14.512230 > # Time: 200825 0:31:35 15.285046 > # Time: 200825 0:31:44 16.159200 > # Time: 200825 0:31:55 11.869288 > # Time: 200825 0:32:05 12.312913 > # Time: 200825 0:32:34 15.871714 > # Time: 200825 0:32:45 16.849624 > # Time: 200825 0:32:55 12.729294 > # Time: 200825 0:33:05 12.249799 > # Time: 200825 0:33:32 14.696986 > # Time: 200825 0:33:41 13.607583 > # Time: 200825 0:33:53 13.282164 > # Time: 200825 0:34:01 10.635700 > # Time: 200825 0:34:31 13.888990 > # Time: 200825 0:34:40 12.963708 > # Time: 200825 0:34:48 10.172652 > # Time: 200825 0:34:57 10.488111 > # Time: 200825 0:35:28 11.879728 > # Time: 200825 0:35:36 10.987992 > # Time: 200825 0:35:45 10.337784 > # Time: 200825 0:36:20 12.589146 > # Time: 200825 0:36:28 12.319763 > # Time: 200825 0:36:45 10.202031 > # Time: 200825 0:37:06 10.762339 > # Time: 200825 0:37:17 13.026294 > # Time: 200825 0:37:24 10.156477 > # Time: 200825 0:37:58 12.332323 > # Time: 200825 0:38:06 10.914180 > # Time: 200825 0:38:45 10.615069 > # Time: 200825 0:39:55 11.195226 > # Time: 200825 0:40:25 11.588065 > # Time: 200825 0:42:23 10.072030 > # Time: 200825 0:44:53 10.128290 > # Time: 200825 0:49:53 10.101208 > # Time: 200825 0:51:17 10.116917 > # Time: 200825 0:57:48 14.967894 > # Time: 200825 6:05:12 13.711824 > # Time: 200825 7:28:32 10.213536 > # User@Host: mysqld_exporter[mysqld_exporter] @ localhost [] > # Query_time: 10.213536 Lock_time: 0.000045 Rows_sent: 112 Rows_examined: > 112 > SET timestamp=1598365712; > > SELECT >> TABLE_SCHEMA, >> TABLE_NAME, >> TABLE_TYPE, >> ifnull(ENGINE, 'NONE') as ENGINE, >> ifnull(VERSION, '0') as VERSION, >> ifnull(ROW_FORMAT, 'NONE') as ROW_FORMAT, >> ifnull(TABLE_ROWS, '0') as TABLE_ROWS, >> ifnull(DATA_LENGTH, '0') as DATA_LENGTH, >> ifnull(INDEX_LENGTH, '0') as INDEX_LENGTH, >> ifnull(DATA_FREE, '0') as DATA_FREE, >> ifnull(CREATE_OPTIONS, 'NONE') as CREATE_OPTIONS >> FROM information_schema.tables >> WHERE TABLE_SCHEMA = 'foodb'; > > > > On Fri, Sep 4, 2020 at 6:01 PM Matthias Rampke <[email protected]> > wrote: > >> Are these queries actually slow (long run time) or are they notifying >> about full table scans? What is your slow query log related MySQL >> configuration? >> >> The latter happens e.g. when you have many connections open (long >> `information_schema.processlist`). These MySQL-internal tables are >> in-memory or not really materialized in any form at all, so they do not >> have any indexes; any query is a full table scan. This is okay because they >> are not materialized anyway. However, MySQL has a threshold for what it >> considers "too many rows to do a full table scan" and logs a slow query >> when that is exceeded. >> >> /MR >> >> On Thu, Sep 3, 2020 at 3:30 PM [email protected] <[email protected]> >> wrote: >> >>> We're trying to use mysqld_exporter for monitoring performance and at >>> some moments it's causing slow running queries. Some tables are reaching >>> 10-20G in size so that could be one of the reasons. Is there a way to >>> prevent such queries? >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "Prometheus Users" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/prometheus-users/45ac56cd-aba5-430f-b61a-94cdec02540dn%40googlegroups.com >>> <https://groups.google.com/d/msgid/prometheus-users/45ac56cd-aba5-430f-b61a-94cdec02540dn%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >> -- You received this message because you are subscribed to the Google Groups "Prometheus Users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/prometheus-users/CAMV%3D_gYqCrFUp5Ebj45oAeFzW_qoBB2eE3RiYF9_q%3DphsDarHA%40mail.gmail.com.
