Repository navigation
KEYCLOAK-16801 Improve performance of ClearExpiredEvents background task - #7718
Conversation
|
Tried test of performance of ClearExpiredEvents with 200 realms in the DB. BEFORE FIX: ClearExpiredEvents finished in 36004 ms |
hmlnarik
left a comment
There was a problem hiding this comment.
Thank you for the patch. Please see comments inline.
There was a problem hiding this comment.
- This can be a very long cycle, and since done in a single transaction, can happen to be run simultaneously for the same records by all the nodes in the cluster. Should the number of iterations be limited? Should it be possible to force previous behaviour even for
jparealm storage? - Can it happen that the events would be generated faster than expired? Since there is now a roundtrip to the database, the deletion is overall slower than the original variant.
There was a problem hiding this comment.
@hmlnarik I've did some performance testing with big number of login events and the ClearExpiredEvents is able to clear the 10K events on my laptop within 60 ms. Much better performance than before for the environment with 200 realms. Since last review, I've added "long currentTimeMillis" on the top of the method, so time is not changing when there are multiple iterations needed. As you pointed, the using latest time in the cycle caused issues as the "delete" SQL command finished, there were new events added to the table, hence the "select" SQL returned another items and there was a chance to have this cycle never-ending. With this change, it should always finish in the finite time.
I have another alternative solution here mposolda@306b7a6 . This has advantage that it doesn't explicitly specify the event IDs in the IN clause. It tries to group realms by the expiration time and use single SQL delete command for each expiration time. Would be effective in case that most of the realms have same expiration times, which I think would be the case in most of the environments. In my local environment, I used 200 realms with 10 various expiration times and hence this run 10 SQL commands. The performance seems to be in general little worse than for the solution 1, however still able to always finish within less than 100 ms in the worst case (in most of the cases in less than 50 ms). In general, this may be more effective in case with same/similar eventExpiration for all realms. And it does not require stuff in the IN clause and can re-use indexes on the "time" column of EventEntity table, so I am slightly for use this one solution. WDYT?
I've tried few other things to have more effective solution, which we discuss last time, but wasn't able to do the JPQL, which would work for all databases. For the native SQL, it is possible. I have at least 2 variants, which are able to remove all the events in single SQL command. Also the "delete" with the limits is possible with the native SQL. However that is native SQL, which I suppose is something, we want to avoid? WDYT?
Since there can be limitations in some environments as mentioned in the other JIRA https://issues.redhat.com/browse/KEYCLOAK-16665 , I am slightly towards to have the option for fallback to the previous behaviour. I did not yet add it to the PR. Do you want me to add the option?
There was a problem hiding this comment.
Thanks @mposolda, great job! Especially the results from your tests are clearly very good!
I like the alternative solution better than the current one. With that one, the throughput in the events table should be better for the price of being negligibly slower and with the benefit of sending much less data over the wire due to the removed the IN clause.
As for the native query, I'd rather suggest to avoid that one. IMO the performance gain is already tremendous and the maintenance cost would be smaller with only JPQL there. If needed, we could obtain the similar behaviour to the native query with DELETE ... LIMIT queries by imposing a condition to limit the delete to a closed interval (time BETWEEN :x AND :y rather than time < :time, but I believe even this may be unnecessary with the changes proposed in this PR.
Since there can be limitations in some environments as mentioned in the other JIRA https://issues.redhat.com/browse/KEYCLOAK-16665 , I am slightly towards to have the option for fallback to the previous behaviour. I did not yet add it to the PR. Do you want me to add the option?
I would prefer the option present.
There was a problem hiding this comment.
@hmlnarik Thanks!
I replaced this PR with the "alternative" solution (The one, which avoids sending IDs in the IN clause).
@hmlnarik @vramik Pipeline run with this patch is here: https://keycloak-jenkins.com/view/pipelines/job/universal-test-pipeline-server/2502 . There is no regression in the current master and especially the EventStoreProviderTest is passing on all the databases. However the issue is Oracle 19c RAC, which currently does not work in pipeline at all. No regression of this PR as it is failing for the master as well. Looks like the issue on the DB side. IMO it is probably better to wait with this PR until Oracle is fixed to be sure that no regression happens for the Oracle. WDYT?
FYI. I've did some more testing with the 10 millions of events in the database spread over all 200 realms and the measure how much time the background task will take.
Current master: Transaction timeout after 300 seconds
Previous solution (the one with the IDs in the IN clause): 103 seconds
Current solution in the PR: 58 seconds
55641bb to
6bc16f4
Compare
|
Thanks guys. Pipeline results are here: https://keycloak-jenkins.com/view/pipelines/job/universal-test-pipeline-server/2479/ . The failing tests are known issues with the exception of OIDCPairwiseClientCredentialsTest for MSSQL, which doesn't look like known issue. At least, I did not found the JIRA for it. However this one shouldn't be related anyhow to the changes in this PR, so I am re-running tests just to be sure. Added comment inline as a response to @hmlnarik comment. |
6bc16f4 to
ed9f116
Compare
|
@hmlnarik @vramik I have a pipeline run here https://keycloak-jenkins.com/view/pipelines/job/universal-test-pipeline-server/2529 with the base tests, DB tests and DB migration tests. Finally executed successfully with Oracle19c. Especially EventStoreProviderTest and AdminEventStorePRoviderTest passed with all the DBs. I've checked that all the test failures are known issues in the pipeline. |
|
Hi @mposolda. It looks like this change was introduced in 13.0.0, since then we've run into the following issue which I believe is directly related to this PR: https://issues.redhat.com/browse/KEYCLOAK-19052 Would you be able to advise on a potential way forward or perhaps optimisations to the query? |
No description provided.