Excessive database load retrieving users when using MySQL with millions of users

[swalchemist]

What version of UAA are you running?

The problem was found after upgrading from 77.1.0 to 77.3.0.

How are you deploying the UAA?

I am deploying the UAA as part of a commercial Cloud Foundry distribution.

What did you do?

A customer has millions of users in a MySQL database in production. We're getting queries like this that are taking more than 10 minutes, and they're stacking up and bogging down the database, causing CF commands to time out. They originated from calls to cf's /v3/users endpoint.

select count(*) from users u join identity_provider idp on u.origin = idp.origin_key and u.identity_zone_id = idp.identity_zone_id where idp.active is true and (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((LOWER(u.id) = LOWER('...id here...') OR LOWER(u.id) [...]

This caused an outage, so they rolled UAA back to 77.1.0 and saw the database load return to normal. They don't have any issues in their testing environment that only has a few thousands of users.

We see that LOWER(u.id) is indexed for postgres, but we don't create an index for mysql.

[cf-gitbot]

We have created an issue in Pivotal Tracker to manage this:

https://www.pivotaltracker.com/story/show/187511412

The labels on this github issue will be updated when the story is started.

[strehle]

@swalchemist @bruce-ricard @Tallicia FYI, still you should think about the UAA option database.caseinsensitive.
If a MYSQL landscape has set database.caseinsensitive=false, then this still can happen because for MYSQL you dont have the needed indexes

For postgresql this parameter cannot be used , but for MYSQL it can be used.
See
database.caseinsensitive

[torsten-sap]

@Tallicia Please decide whether you want to keep this open after PR #2859.