Slow initial offline session loading / adding indexes

107 views
Skip to first unread message

Schuster Sebastian (IOC/PDL22)

unread,
Aug 28, 2020, 10:31:19 AM8/28/20
to keyclo...@googlegroups.com

Hi everybody,

 

We noticed that for initial loading offline sessions the OFFLINE_USER_SESSION table is missing an index on OFFLINE_FLAG, CREATED_ON, USER_SESSION_ID

Adding that index decreased initial loading time of offline sessions from 4 minutes to 35 seconds for 100K offline sessions in our setup. Since this time includes loading the OFFLINE_CLIENT_SESSION, that’s a dramatic decrease.

 

Furthermore, for the cron job to purge old offline sessions, and index on OFFLINE_FLAG, REALM_ID, LAST_SESSION_REFRESH would be helpful.

 

I know that doing database changes is not very popular atm, but is there any chance these indexes, especially the first one, could make it upstream?

 

Best regards,

Sebastian

 

Mit freundlichen Grüßen / Best regards

Dr.-Ing. Sebastian Schuster

Project Delivery Berlin 22 (IOC/PDL22)
Bosch.IO GmbH | Ullsteinstr. 128 | 12109 Berlin | GERMANY | www.bosch.io
Tel. +49 30 726112-485 | Mobil +49 152 02177668 | Telefax +49 30 726112-100 | Sebastian...@bosch.io

Sitz: Berlin, Registergericht: Amtsgericht Charlottenburg; HRB 148411 B
Aufsichtsratsvorsitzender: Dr.-Ing. Thorsten Lücke; Geschäftsführung: Dr. Stefan Ferber, Dr. Aleksandar Mitrovic, Yvonne Reckling

 

Marek Posolda

unread,
Sep 2, 2020, 5:31:51 AM9/2/20
to Schuster Sebastian (IOC/PDL22), keyclo...@googlegroups.com
Just for the record, this is known issue and I see that you already commented in the JIRA https://issues.redhat.com/browse/KEYCLOAK-11019, which is related to this.

I am not sure when/if we will be able to improve this OOTB as there is also https://issues.redhat.com/browse/KEYCLOAK-11908 as a needed pre-requisite to be able to add DB indexes to the tables with big number of records. And the main focus of the core team is focus on the new storage implementation (which will be more performant and should address zero-downtime upgrades etc) rather than improving the current storage...

Marek
--
You received this message because you are subscribed to the Google Groups "Keycloak Dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to keycloak-dev...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/keycloak-dev/fa73cbfffe2649aea75401d354402039%40bosch.io.


Schuster Sebastian (IOC/PDL22)

unread,
Sep 2, 2020, 6:43:11 AM9/2/20
to Marek Posolda, keyclo...@googlegroups.com

Hi Marek,

 

Completely understandable. I’ll ask the team if we would be able to implement https://issues.redhat.com/browse/KEYCLOAK-11908,

otherwise we’ll just add the index manually.

 

Best regards,

Sebastian

 

Mit freundlichen Grüßen / Best regards

Dr.-Ing. Sebastian Schuster

Project Delivery Berlin 22 (IOC/PDL22)
Bosch.IO GmbH | Ullsteinstr. 128 | 12109 Berlin | GERMANY | www.bosch.io
Tel. +49 30 726112-485 | Mobil +49 152 02177668 | Telefax +49 30 726112-100 | Sebastian...@bosch.io

Sitz: Berlin, Registergericht: Amtsgericht Charlottenburg; HRB 148411 B
Aufsichtsratsvorsitzender: Dr.-Ing. Thorsten Lücke; Geschäftsführung: Dr. Stefan Ferber, Dr. Aleksandar Mitrovic, Yvonne Reckling

 

Christian Becker

unread,
Nov 6, 2020, 6:33:20 AM11/6/20
to Keycloak Dev
@Sebastian: What database are you using? We have tried a similar index on PostgreSQL and it only had hardly any effect.

@Marek: What's the plan with the rewrite regarding backward compatibility and timeline? We're at the moment stuck on rh-sso 7.2 and can't touch the cluster due to 1.5 million sessions and we haven't managed to run the update without less than at least 90 minutes of downtime, which we just can not do.

Thank you,
Christian

Todor Kazakov

unread,
Nov 10, 2020, 9:27:03 AM11/10/20
to Keycloak Dev
Christian,

Have you tried increasing the sessions per segment of the offline session loader? We saw a 5x speedup by adjusting it to 512, instead of the default 64. We tested this on a  t1.micro instance of Postgres 11 with 300K offline sessions. You can find more details here https://groups.google.com/g/keycloak-dev/c/NMeG3yKOIDo.

If you do end up trying this, could you please share your result?

Cheers,
Todd

Christian Becker

unread,
Nov 10, 2020, 12:30:12 PM11/10/20
to Keycloak Dev
Yes, (it's been a while, so) afaik we managed to run it with 768 sessions per segment somewhat stable, everything above ran into a timeout (https://groups.google.com/g/keycloak-user/c/lGFx0ousNPY/m/zZJ3FHRJBwAJ).

A colleague is currently evaluating the upgrade to redhat-sso 7.4 and managed to run it with 20.000 sessions per segment in a very performant way. We don't know yet how this is possible and will continue the evaluation, what we've previously missed.

Schuster Sebastian (IOC/PDL22)

unread,
Nov 23, 2020, 6:35:15 AM11/23/20
to Christian Becker, Keycloak Dev

Sorry Christian, I am just beginning to catch up on the ML. We are using MS SQL.

 

Best regards,

Sebastian

 

Mit freundlichen Grüßen / Best regards

Dr.-Ing. Sebastian Schuster


Project Delivery Berlin 22 (IOC/PDL22)
Bosch.IO GmbH | Ullsteinstr. 128 | 12109 Berlin | GERMANY | www.bosch.io



Sitz: Berlin, Registergericht: Amtsgericht Charlottenburg; HRB 148411 B
Aufsichtsratsvorsitzender: Dr.-Ing. Thorsten Lücke; Geschäftsführung: Dr. Stefan Ferber, Dr. Aleksandar Mitrovic, Yvonne Reckling

Reply all
Reply to author
Forward
0 new messages