Projection with huge dataset

[dilab]

Our event stored(stored_events table) has grown to 12861457 records. which is in 9.9 GiB in size.

We are facing a challenge when generating some reports.

It will need to read all the events of an aggregate and build the reports (this is correct way of doing it). However due to the size of the data, it has become slow, and it also jams the MySQL, because I have a report server (which spins up 10 workers) to speed up the report generation.

My Question is what I can do to optimize it?

1. Setup a separate MySQL database replicate ?
2. Using a different database engine which is better at I/O?

[etahamer]

@dilab Have you thought about using projectors and projections to aggregate data into more convenient form for your report generation process?

[dilab]

definitely thought about it. However our reports require to fetch point of time data.

[sjardim]

The event sourcing concept you may look for is a snapshot. It's a way to "split" the events into chunks based on the business's requirements.

See this great talk giving a nice example: https://www.youtube.com/watch?v=gG6DGmYKk4I

[dilab]

when building a reports(point of time data), our reports need to examine all the past events.

[sjardim]

I understood that. Snapshots are a way of setting "points in time" for the events and their "results." When creating each snapshot, you can calculate all the metrics you want to report on at that period. Each snapshot will then "feed" into the next, so you have all the past events data in the end. What you are doing is just pre-calculating them in chunks to save time. You don't need to recalculate them again for all the reports thereafter.

It's difficult to give examples without knowing your domain, so that's why I sent that video as an example.

[dilab]

Hi @sjardim thanks for the feedback.

Unfortunately, the business requires to dynamically set the query time. meaning the user will select which period of the report to generate on the fly.

[sjardim]

Ah, okay. So that presents a problem indeed. Well, when you figure out a solution, please care to post it here if possible so we can all learn from it! :) Best of luck!

[dilab]

our current solution is to have a separate database for the reports table, so it won't interfere the normal projection table.

[sjardim]

A separate DB is a common solution for this scenario and a good one. The beauty of the projections is that you can create tables (etc.) the way you need for the reports and create better indexes to handle the query load. And as you having performance issues on MySQL, check if the indexes are really being used (it can be tricky).

[dilab]

yea.. except that it takes hours to build a report.