periodic infoSchemaAutoIncrementQuery can sometimes be very slow; merge fix from PMM #725
Description
Host operating system: output of uname -a
Linux <pod> 4.19.0-18-cloud-amd64 #1 SMP Debian 4.19.208-1 (2021-09-29) x86_64 GNU/Linux
mysqld_exporter version: output of mysqld_exporter --version
0.12.1
MySQL server version
5.7
mysqld_exporter command line flags
What did you do that produced an error?
The periodic infoSchemaAutoIncrementQuery that is run by the mysqld_exporter can sometimes be extremely slow, especially if you have a lot of tables and columns. This bit us many years ago and we found we could force MySQL to use its normal high-performance query by using STRAIGHT_JOIN from the columns to the tables:
SELECT ...
FROM information_schema.columns c
STRAIGHT_JOIN information_schema.tables t USING (table_schema,table_name)
WHERE c.extra = 'auto_increment' AND t.auto_increment IS NOT NULL
We have been using this fix for 4 years without issue. About 3.5 years ago we submitted that fix into the PMM fork of the prometheus mysqld_exporter and it has been working without issue there ever since. However we recently had a new project use this prometheus/mysqld_exporter version of the code and issue popped up there after a few months.
Here is the PMM ticket: https://jira.percona.com/browse/PMM-4001
And here is the PMM PR: https://github.com/percona/mysqld_exporter/pull/48/files
Here is a PR to to apply the fix here as well: #726
What did you expect to see?
The infoSchemaAutoIncrementQuery typically runs in < 1 sec.
What did you see instead?
With the original query, MySQL sometimes chooses a bad query plan that start with the tables and then joins to the columns, causing the infoSchemaAutoIncrementQuery to take > 1 minute to complete in our environment [7 schemas, 840 tables, 41,000 columns]. That's disastrous with a polling interval of 1 minute or less, as the queries never return in time and actually stack up until the mysqld_exporter runs out of memory and is OOM-killed.