search

puppetlabs / puppetlabs-mysql

MySQL Puppet Module / Manifests + Types & Providers
Apache License 2.0
381 stars 791 forks source link

Execution time 60s+ with 4000 databases #1603

Open deubert-it opened 8 months ago

deubert-it commented 8 months ago

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 commented 2 months ago

We would also like this behaviour to be configureable.

alexjfisher commented 2 months ago

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 https://github.com/puppetlabs/puppetlabs-mysql/blob/f51fabd6dd907c57885632a4a0ca26065c38497f/lib/puppet/provider/mysql_database/mysql.rb#L9-L21

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...