Reduce MV insert memory usage

[bmtcril]

We should do a memory pass on MV inserts and see which insert paths could use tweaking. It seems like there are methods to limit the number of rows being considered on the right side of joins in MVs as documented here: https://stackoverflow.com/questions/75243697/joining-large-tables-in-clickhouse-out-of-memory-or-slow

Where possible we should implement that or projections to speed up inserts and reduce memory consumption. This comes out of an issue reporting this error:

Code: 241. DB::Exception: Memory limit (total) exceeded: would use 14.49 GiB (attempt to allocate chunk of 4238898 bytes), maximum: 13.83 GiB. OvercommitTracker decision: Query was selected to stop by OvercommitTracker.: While executing JoiningTransform: while pushing to view xapi.subsection_problem_engagement_mv (b6e66e2d-e108-463c-ae7b-17ac579daf95): while pushing to view xapi.problem_events_mv (74481e14-1e07-42a2-986e-f7a269a79079): while pushing to view xapi.xapi_events_all_parsed_mv (2831c02c-9fca-494b-b8f6-3d22dc921e72): While executing WaitForAsyncInsert. (MEMORY_LIMIT_EXCEEDED) (version 23.8.2.7 (official build))

Similar errors have been seen with watched_video_duration:

aspects-job-20241023085611-n9mht aspects 13:59:30  34 of 47 ERROR creating sql materialized_view model `xapi`.`watched_video_duration`  [ERROR in 21.92s]
aspects-job-20241023085611-n9mht aspects 13:59:30  
aspects-job-20241023085611-n9mht aspects 13:59:30  Finished running 4 dictionary models, 26 materialized view models, 17 view models in 0 hours 3 minutes and 0.98 seconds (180.98s).
aspects-job-20241023085611-n9mht aspects 13:59:30  
aspects-job-20241023085611-n9mht aspects 13:59:30  Completed with 1 error and 0 warnings:
aspects-job-20241023085611-n9mht aspects 13:59:30  
aspects-job-20241023085611-n9mht aspects 13:59:30    Database Error in model watched_video_duration (models/video/watched_video_duration.sql)
aspects-job-20241023085611-n9mht aspects   Code: 241.
aspects-job-20241023085611-n9mht aspects   DB::Exception: Memory limit (total) exceeded: would use 13.86 GiB (attempt to allocate chunk of 5071098 bytes), maximum: 13.80 GiB. OvercommitTracker decision: Memory overcommit isn't used. Waiting time or overcommit denominator are set to zero.: While executing JoiningTransform. Stack trace:

[saraburns1]

watched_video_duration has been updated to use a parameterized view instead of materialized which allows superset to run the query on a subset of data and eliminates the memory issues.

dbt model - https://github.com/openedx/aspects-dbt/blob/main/models/video/watched_video_duration.sql#L25-L36
superset dataset - https://github.com/openedx/tutor-contrib-aspects/blob/main/tutoraspects/templates/openedx-assets/queries/watched_video_duration.sql#L36-L38

[saraburns1]

We can do similar changes to other high memory models. I'll compile a list of highest memory queries and queries that have been moved out of dbt into superset which we may be able to move back

[bmtcril]

Do you think this is a thing we can actually use to remove most of the SQL pre-where queries from Superset? It seems like it would work.

[saraburns1]

I don't see why not. It might take some refactoring of the queries if we need to use parameters but shouldn't be too bad