[keycloak-user] Keycloak very slow on retrieving "events"

[Sarp Kaya]

Hey again,

I checked the database there were 14 million events. After running some basic queries I noticed that even basic count query would take around 4 minutes.

One thing I realised is, if you want an instant result, you can use LIMIT, because UI uses 0-5 etc. it can definitely make use of it to make this faster.

How I fixed is by adding an index on Event_time:


 CREATE INDEX `idx_EVENT_ENTITY_EVENT_TIME`  ON `keycloak`.`EVENT_ENTITY` (EVENT_TIME) COMMENT '' ALGORITHM DEFAULT LOCK DEFAULT;


Now it works really quick. I am not going to create a ticket, but if you wish, you can add this to your liquibase for the next updates.


Thanks,

Sarp

From: Stian Thorgersen <sthorger at redhat.com<mailto:sthorger at redhat.com>>
Reply-To: "stian at redhat.com<mailto:stian at redhat.com>" <stian at redhat.com<mailto:stian at redhat.com>>
Date: Wednesday, July 5, 2017 at 3:29 PM
To: Abdullah Sarp <akaya at expedia.com<mailto:akaya at expedia.com>>
Cc: "keycloak-user at lists.jboss.org<mailto:keycloak-user at lists.jboss.org>" <keycloak-user at lists.jboss.org<mailto:keycloak-user at lists.jboss.org>>
Subject: Re: [keycloak-user] Keycloak very slow on retrieving "events"

Can you create a bug report please? Include as much info as you can so it's easy for us to reproduce the problem (how many events you have, what the query is you're running, etc..)

On 5 July 2017 at 01:10, Sarp Kaya <akaya at expedia.com<mailto:akaya at expedia.com>> wrote:
Yes, I have a lot of events, but I thought it shouldn’t matter when picking the first 5 events.

Thanks,
Sarp

From: Stian Thorgersen <sthorger at redhat.com<mailto:sthorger at redhat.com>>
Reply-To: "stian at redhat.com<mailto:stian at redhat.com>" <stian at redhat.com<mailto:stian at redhat.com>>
Date: Wednesday, July 5, 2017 at 3:56 AM
To: Abdullah Sarp <akaya at expedia.com<mailto:akaya at expedia.com>>
Cc: "keycloak-user at lists.jboss.org<mailto:keycloak-user at lists.jboss.org>" <keycloak-user at lists.jboss.org<mailto:keycloak-user at lists.jboss.org>>
Subject: Re: [keycloak-user] Keycloak very slow on retrieving "events"

That's not good, it should be much quicker than that. Do you have a lot of events?

On 4 July 2017 at 08:41, Sarp Kaya <akaya at expedia.com<mailto:akaya at expedia.com>> wrote:
Hello,

When I click on Events, it takes very long time (more than a minute) just to display first 5 events. I checked what endpoint it uses and it’s using this:

"/auth/admin/realms/<realm-id>/events?first=0&max=5” endpoint.

I believe that there is something broken with the filtering option as it should never take more than a minute to just retrieve 5 results.

I’m using Keycloak 3.1.0 and MySQL for the database.
_______________________________________________
keycloak-user mailing list
keycloak-user at lists.jboss.org<mailto:keycloak-user at lists.jboss.org>
https://lists.jboss.org/mailman/listinfo/keycloak-user