-
Notifications
You must be signed in to change notification settings - Fork 58
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Running projections causes constant full table scans due incorrect forced index #224
Comments
Can you post your configuration please? |
Config
|
I think this can be solved by using a different projection strategy when running the projections. I am currently not using a v7 instance in production, so I can't verify easily right now. ping @codeliner can you provide some info? I think you are running v7 in production right now. |
yes, but we're using postgres. I remember that someone mentioned this or a similar issue when using MySql. Maybe it was @fritz-gerneth ? Unfortunately, I cannot find the discussion in an older issue, maybe it was in the chat :( Anyway, you could implement your own stream strategy to change the index the way you need it: https://github.com/prooph/pdo-event-store/blob/master/src/PersistenceStrategy/MySqlSingleStreamStrategy.php#L55 and change your configuration so that your custom strategy is used instead of the one provided by prooph. |
Prolic provided a bit of background in #191 . Never ran into this issue myself due to a custom strategy (for larger IDs). |
Ah thanks @fritz-gerneth that's the issue I was looking for. @atymic does it help you? |
The issue is that this index is useful when looking up aggregates in the application - it's just not when running projections. Ideally, since it just requires removing that specification of the index it would be able to be disabled just from projections |
Yep, that's the exact issue. Will try to figure out how to use a different strategy just for projections. We're running laravel so hopefully we can sub it out in the container. |
Ok, subbing out the strategy for projections only worked fine. I just added another event store to the config, and set the projections to use that instead. Thanks for all the help everyone. Could it be worth documenting the performance issues for projections in the docs, since it seems like others have come accross this as well? |
@atymic yes a pull request for documentation is always welcome! I'm closing the issue now. |
@ahmed-alaa I think this is important for your set up as well ☝️ |
The following index hint solved this problem for us, in https://github.com/prooph/pdo-event-store/blob/master/src/PersistenceStrategy/MySqlSingleStreamStrategy.php#L95
It works for both, running projections and loading aggregates. Might be worth trying before adding an additional event store configuration. |
Ahh, interesting. @codeliner is this a reasonable solution to accept into the library? |
Can you test this please and let me know? If this works for both cases I
would definitely merge a pull request.
…On Wed, Apr 15, 2020, 19:21 atymic ***@***.***> wrote:
Ahh, interesting. @codeliner <https://github.com/codeliner> is this a
reasonable solution to accept into the library?
—
You are receiving this because you modified the open/close state.
Reply to this email directly, view it on GitHub
<#224 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AADAJPEB5WADMCTRWGTLHQDRMY6RHANCNFSM4MHLNSIQ>
.
|
No worries, i'll test shortly :) |
We're developing a pretty new app using prooph, with only ~20k events in a single stream. We have ~30 projections, and when running these all at once the database gets completely destroyed.
I dived in with a debugger and figured out this is because we are doing 30x full table scans per 100ms, as every loop of each projection is doing a full table scan.
Here's an example query:
Note the
USE INDEX(ix_query_aggregate)
. This forces mysql to not use the the primary index onno
.Removing the
USE INDEX
means that the index is used properly, and the query is orders of magnitude faster.Apologies if this is a misconfiguration from our end, but I think that the
ix_query_aggregate
index should only be using when doing queries based on metadata, not when scanning the event stream.Running:
prooph/pdo-event-store
:v1.12.0
prooph/event-store
:v7.5.6
Thanks in advanced!
The text was updated successfully, but these errors were encountered: