mysqld_exporter causing slow running queries

[deln...@gmail.com]

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?

[Matthias Rampke]

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 deln...@gmail.com <deln...@gmail.com> 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 prometheus-use...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/prometheus-users/45ac56cd-aba5-430f-b61a-94cdec02540dn%40googlegroups.com.

[Matthias Rampke]

How many tables do you have?

This query is from the info_schema.tables collector, 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 <deln...@gmail.com> 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';