Implement connectionPool into mysql scaler based on grpc connectionPool

[bbrala]

Proposal

When using mysql scaler with a large amounts of namespaces, but against the same connection settings we run out of connections on the database. It seems the external scaler has a connectionPool implementation which could work for mysql also if we use the connection string as key.

Use-Case

When using a single connection string to scale multiple namespaces, for example; when you 'enable' en envoroment using a mysql database. It would be helpfull to poll using a single connection instead of ending up with 100 connections if you look at 100 namespaces/deployments.

Is this a feature you are interested in implementing yourself?

Maybe

Anything else?

Looks pretty simple to do, just not sure about the caveats of go and mysql connections. It looks like keda doesn't check parralel, but perhaps that could be an issue if multiple scalers try to use the same connection? Although this might be what the mutex is fixing in the keda grps scaler?

This also kinda assumed the queries are very very light. The ones we are using should be extremly easy (a simple where with 2 indexes, namespace and expires timestamp).

[robpickerill]

I put together an implementation that provides a set of global connection pools for MySQL. It uses an atomic reference counted concurrent hashmap to do so. The biggest problem I found when writing this with respect to the GRPC implementation, is that the cleanup of the pools becomes problematic as the pools are shared across multiple scalers. This creates a problem whereby the close function could prematurely close a connection pool being utilized by another scaler.

This approach is still naive in its handling of the connection pool key, and its handling of the connection pool settings in that first conn pool in the cache gets settings applied, and there's no reconciliation afterwards -- solvable problems but I parked it up here for now, as the complexity was growing, and wasn't sure if the Keda project wants to move in this direction.

[bbrala]

Type for trying. This could be an awesome way to make mysql more usefull.

In our setup we moved to redis since that has way less overhead, just makes the app controlling the scaling a little less confortable.

Still think this could be helpfull though

[bbrala]

Didnt get to the key part (on mobile) but id assume using the connection string as key would be good and make things seperate enough. That would skip some issues with having to reauth and such.

[robpickerill]

Yeah, I'll patch it up properly over the next few days then leave some comments on the PR for others to weigh in