Execution time 60s+ with 4000 databases

[deubert-it]

Use Case

I'm not managing databases with puppet, however this module will always loop all databases and do a "show variables" on them.
This currently takes ~60 seconds for ~4.000 database.

Debug: Executing: '/usr/bin/mysql --defaults-extra-file=/root/.my.cnf -NBe show variables like '%_database' db_982'

Describe the Solution You Would Like

If I'm not managing databases via the module at all, maybe this information is not required for everything else to function, and the module could detect itself if it isn't managing databases.

Describe Alternatives You've Considered

An alternative could be a new parameter to just disable this behaviour on demand.

[WBasson]

We would also like this behaviour to be configureable.

[alexjfisher]

I assume there must be at least 1 database that this module is managing or I don't think the code would get called at all.

Perhaps instead of looping over every database and calling show variables here

puppetlabs-mysql/lib/puppet/provider/mysql_database/mysql.rb

Lines 9 to 21 in f51fabd

	def self.instances
	mysql_caller('show databases', 'regular').split("\n").map do |name|
	attributes = {}
	mysql_caller(["show variables like '%_database'", name], 'regular').split("\n").each do |line|
	k, v = line.split(%r{\s})
	attributes[k] = v
	end
	new(name: name,
	ensure: :present,
	charset: attributes['character_set_database'],
	collate: attributes['collation_database'])
	end
	end

a single query of the information_schema database could do the job.

ie this single query should return everything required to create the mysql_database instances. select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA;

It'd need testing on all the mysql variants the module supports though...