Skip to content
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.

Sign up for GitHub

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

Jump to bottom

Slow aggregate load performance for SingleStreamStrategy #230

Open
fritz-gerneth opened this issue Jun 16, 2021 · 4 comments · May be fixed by #232
Open

Slow aggregate load performance for SingleStreamStrategy #230

fritz-gerneth opened this issue Jun 16, 2021 · 4 comments · May be fixed by #232

Comments

@fritz-gerneth
Copy link
Contributor

Not sure yet why / what is happening here exactly. This is happening on a table with ~500k recorded events in a SingleStreamStrategy. The original query takes about 2 seconds for us to load. The second query takes 70ms to load. This was live-tested with a modified MySqlEventStore to use the second query on a MySql 5.7 & MySQL 8 instance. Loading all aggregates one after another caused the server to have 90% CPU utliziation

Anyone got any ideas what might be the cause here? I mean the fix would be relatively simple - let the Storage strategy decide how they want to match metadata... but I'd like to understand the underlying cause here first..

EXPLAIN 
   SELECT * FROM `_my_stream`  WHERE 
       JSON_UNQUOTE(metadata->"$._aggregate_id") = "ff"  
       AND JSON_UNQUOTE(metadata->"$._aggregate_version") > 3  AND `no` >= 5   
       AND JSON_UNQUOTE(metadata->"$._aggregate_type") = "dffg"  ORDER BY `no` ASC IMIT 1000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE _my_stream ref PRIMARY,ix_unique_event,ix_query_aggregate ix_unique_event 754 const,const 1 50.0 Using index condition; Using where; Using filesort 
EXPLAIN 
   SELECT * FROM `_my_stream`  WHERE 
       aggregate_id = "ff"  
       AND aggregate_version > 3  AND `no` >= 5   
       AND aggregate_type = "dffg"  ORDER BY `no` ASC IMIT 1000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE _my_stream ref PRIMARY,ix_unique_event,ix_query_aggregate ix_query_aggregate 754 const,const 1 16.66 Using index condition; Using where
@fritz-gerneth
Copy link
Contributor Author

I only noticed now our custom implementation of the storage strategy does not force the index (everything else is the same). With the forced index, performance gets even worse (~5sec/query).

EXPLAIN 
   SELECT * FROM `_my_stream` igo USE INDEX(`ix_query_aggregate`) 
   	   WHERE JSON_UNQUOTE(metadata->"$._aggregate_id") = "ff"  
       AND JSON_UNQUOTE(metadata->"$._aggregate_version") > 3 
       AND `no` >= 5   
       AND JSON_UNQUOTE(metadata->"$._aggregate_type") = "dffg"  
       ORDER BY `no` ASC LIMIT 1000;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE _my_stream range ix_query_aggregate ix_query_aggregate 762 1 100.0 Using index condition; Using where

@codeliner
Copy link
Member

@sandrokeil do you have an idea?

@fritz-gerneth fritz-gerneth linked a pull request Jul 8, 2021 that will close this issue
Draft
@sandrokeil
Copy link
Member

The original query takes about 2 seconds for us to load. The second query takes 70ms to load.

Maybe it depends on the query cache configuration. Something like this.

@fritz-gerneth
Copy link
Contributor Author

We have the default query cache configuration. I'll try the original version with your configuration when I am back.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Optionally delegate metadata matching to persistence strategy funct/pdo-event-store
3 participants
@fritz-gerneth @sandrokeil @codeliner