We have something around a million users in our app. We use Keycloak instance (https://hub.docker.com/r/jboss/keycloak/) with standard configuration. Also we use Offline tokens, to which this problem is connected somehow
The more offline_user_session table has records, the more time it takes to start the keycloak instance up.
if it has 0 records, a start takes something about 30 seconds.
When it has 800 000 sessions, it takes 8 minutes to start
And when it has around 1 000 000 session, it can start for 30 minutes or more
I tried to find anything on the internet and looked up in the official documentation, but still no results.
When analyzing a user_session offline table, there is a problem related to good practices in defining this table.
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| USER_SESSION_ID | varchar(36) | NO | PRI | NULL | |
| USER_ID | varchar(255) | YES | |||
| REALM_ID | varchar(36) | NO | |||
| CREATED_ON | int(11) | NO | MUL | ||
| OFFLINE_FLAG | varchar(4) | NO | PRI | ||
| DATA | longtext | YES | |||
| LAST_SESSION_REFRESH | int(11) | NO | 0 |
In the database layer, we have a construction problem, when using two columns with “varchar” types composing a primary key. This situation is the only reason for the problem presented, which is only evident in scenarios with many records.
The solution to this problem will be to exchange the primary key for a large integer data type, and leave it as columns (USER_SESSION_ID, OFFLINE_FLAG) as a unique index. However, this goes to the configuration adjustments at the application layer, which should be seen with the solution provider.
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| ID | BIGINT | NO | PRIL | ||
| USER_SESSION_ID | varchar(36) | NO | UNI | NULL | |
| USER_ID | varchar(255) | YES | |||
| REALM_ID | varchar(36) | NO | |||
| CREATED_ON | int(11) | NO | MUL | ||
| OFFLINE_FLAG | varchar(4) | NO | UNI | ||
| DATA | longtext | YES | |||
| LAST_SESSION_REFRESH | int(11) | NO | 0 |