Slow scrape time and consuming lots of cpu and memory when there are lots of databases in MySQL.

[mvdboogaard]

Host operating system: output of uname -a

Linux mysql-www-xxxxxxx.yyyy.zzzz 4.18.0-513.11.1.el8_9.x86_64 #1 SMP Wed Jan 17 02:00:40 EST 2024 x86_64 x86_64 x86_64 GNU/Linux

mysqld_exporter version: output of mysqld_exporter --version

mysqld_exporter, version 0.15.1 (branch: HEAD, revision: cc349684494b5038ec5a52233bdca9eb9291e6f2)
  build user:       root@d89c15b9f5ad
  build date:       20231212-07:55:09
  go version:       go1.21.5
  platform:         linux/amd64
  tags:             unknown

MySQL server version

mysql Ver 8.0.31-23 for Linux on x86_64 (Percona Server (GPL), Release 23, Revision 71449379)

mysqld_exporter command line flags

[Unit]
Description=MySQL Prometheus exporter
After=network-online.target
User=prometheus
Group=prometheus

[Service]
Type=simple
Restart=always
ExecStart=/usr/local/bin/mysqld_exporter \
--config.my-cnf /etc/mysqld_exporter/config.cnf \
--web.config.file /etc/mysqld_exporter/web_config.cnf \
--collect.global_status \
--collect.info_schema.innodb_metrics \
--collect.auto_increment.columns \
--collect.info_schema.processlist \
--collect.binlog_size \
--collect.info_schema.tablestats \
--collect.global_variables \
--collect.info_schema.query_response_time \
--collect.info_schema.userstats \
--collect.info_schema.tables \
--collect.perf_schema.tablelocks \
--collect.perf_schema.file_events \
--collect.perf_schema.eventswaits \
--collect.perf_schema.indexiowaits \
--collect.perf_schema.tableiowaits \
--collect.slave_status \
--web.listen-address=0.0.0.0:9104

[Install]
WantedBy=multi-user.target

Also removed the following collects to see if it makes a difference:

--collect.info_schema.tables \
--collect.perf_schema.tablelocks \
--collect.perf_schema.tableiowaits \

This had the same results as with them enabled. So didn't made a difference

What did you do that produced an error?

A stress-test is done on the mysql-database cluster which created about 186 databases with each 147 tables in it.

What did you expect to see?

mysqld_exporter just using limited amount of cpu and memory and scrape time is within a second

What did you see instead?

mysqld_exporter was just running but was consuming more and more memory and eventually triggered the OOM-killer.
mysqld_exporter had spikes of using about 150% cpu but was using at least 50% CPU. Memory usage is about 2.5 - 3.5 GB and growing.
The scrape time is more than 9 seconds and often more than 10 seconds (which is the timeout setting in Prometheus)

[pkrul]

I'm experiencing the same isse, with the difference that I start mysqld_exporter on an existing Percona MySQL cluster that already has ~400 databases running.

CPU load and memory usage increase a lot:

After disabling a number of options for now, I can keep mysqld_exporter from freaking out when I only use basic options at startup:

ExecStart=/usr/local/bin/mysqld_exporter \
--config.my-cnf /etc/mysqld_exporter/config.cnf \
--web.config.file /etc/mysqld_exporter/web_config.cnf \
--collect.global_status \
--collect.global_variables \
--collect.slave_status \
--collect.binlog_size \
--collect.info_schema.processlist \
--web.listen-address=0.0.0.0:9104
# --collect.auto_increment.columns \
# --collect.info_schema.innodb_metrics \
# --collect.info_schema.tablestats \
# --collect.info_schema.query_response_time \
# --collect.info_schema.userstats \
# --collect.info_schema.tables \
# --collect.perf_schema.tablelocks \
# --collect.perf_schema.file_events \
# --collect.perf_schema.eventswaits \
# --collect.perf_schema.indexiowaits \
# --collect.perf_schema.tableiowaits \