How to best handle migration from Cairn to Aspects for large instances?

Hi everyone,

We are migrating from Cairn to Aspects and would appreciate feedback on our approach before proceeding with production.

Our setup

  • Open edX was previously running Quince with Cairn and now runs Ulmo with Aspects.
  • We are using tutor-contrib-aspects 3.0.3.
  • The deployment runs on OpenShift through Helm and ArgoCD.
  • Historical data exists in Cairn’s ClickHouse and in tracking logs stored on persistent volumes (single tracking.log file).
  • Single log file size is approx. 1 TB.

We understand that newer Aspects versions exist. Are there relevant fixes or migration tools in those versions, and what version would you recommend for Ulmo?

Migration approach

Our approach has been to:

  1. Enable live Aspects ingestion.
  2. Backfill current LMS data, such as course enrollments, course information and user information, using dump_data_to_clickhouse.
  3. Extract supported historical events from Cairn’s ClickHouse and transform them into the format expected by Aspects.
  4. Process tracking logs for periods not covered by Cairn.
  5. Import in bounded batches, checking event identifiers for duplicates and recording progress so interrupted imports can resume.

Issues we have encountered

Enrollment counts differ between current-state data and dashboards

For many courses, Django/MySQL and the latest enrollment snapshots in event_sink.course_enrollment showed 100 active enrollments, while the Course Dashboard’s “Current Enrollees” showed a lower count.

Our investigation suggests that the chart uses enrollment events rather than simply counting the latest active enrollment snapshots. Some historical activation events were absent from the sources we checked. (Present in logs but not in clickhouse or vice versa.)

For another course, we also found that comparing enrollments created after our migration cutoff with the dashboard was not a like-for-like comparison: the dashboard included some learners enrolled before that cutoff.

tracking.log records are malformed or missing

We found:

  • Malformed or truncated JSON.
  • Interleaved log records.
  • Large NUL-containing spans in historical files.
  • Enrollment changes present in the LMS database/audit history without a recoverable activation event in the log records inspected.
  • Some intact events in application logs that were damaged or absent in tracking.log.

Resource usage during backfill

We encountered ClickHouse memory pressure during ingestion, including significant memory consumption from diagnostic-table merges. This made us concerned about the operational requirements for processing production history while live ingestion continues.

Questions

  1. Is this a recommended migration strategy?
    Is extracting and transforming events from Cairn’s ClickHouse a supported approach, or is replaying the original tracking logs generally preferable? Is there an existing Cairn-to-Aspects migration tool? If not, will community benefit from such a tool if it existed ?

  2. What is the recommended workflow for approximately 1 TB of historical logs?
    Should we use transform_tracking_logs, transform to intermediate files/object storage, or use another supported ingestion path? How do you handle checkpoints, retries and resumption after a failure?

  3. How should overlap and duplicates be handled?
    We potentially have the same activity in Cairn, Quince logs, Ulmo logs and live ingestion. Are event identifiers stable across these paths? What deduplication guarantees can we rely on, including in derived reporting tables?

  4. How should missing historical enrollment events be handled?
    Is there a supported way to reconcile enrollment dashboards with current LMS enrollment state? Should “Current Enrollees” agree with current active enrollments, or does it intentionally represent a different population?

  5. What is the recommended treatment of malformed records?
    Are there supported tools for quarantining invalid events, diagnosing transformation failures and retrying recoverable records without silently losing data?

  6. What production validation checks do you recommend?
    Beyond raw event counts, how do you validate enrollment, learner/course sinks, problem attempts, video activity and derived dashboard results after a historical import?

  7. How should we size and operate the backfill?
    Are there useful benchmarks or guidance for ClickHouse storage, memory, batch sizes and concurrency? How should historical imports be throttled to protect live ingestion?

Our goal is a repeatable migration procedure that operators can run independently in each environment(Prod, Dev, UAT), with explicit reporting of imported, duplicate, rejected and unrecoverable records.

If anyone has migrated a similarly sized installation from Cairn to Aspects, we would appreciate hearing what worked, what you would avoid, and which supported tools or documentation we should follow.

Thank you!

Hi Chintan, sorry for the delay.

There are a number of issues in play here, I’ll try to give you the best information I can. First and foremost, we’ve very recently fixed a number of issues caused by insert performance on Clickhouse so answers for older versions and newer ones will be different. In general it seems like most lost events historically have been due to the large insert pressure causing bulk inserts from the Celery tasks to fail. As you say, however, malformed or broken tracking logs will also have an impact.

As to your questions:

  1. It is rare to have installations do a full historical backfill of this much data, each install will have unique needs. My recommendation would be to replay the events to block storage and load from there. If any data wasn’t loaded into Cairn it would be caught this way instead of carrying those gaps forward. I’m not sure that a Cairn to Aspects migration tool helps much since it still has to go through the xAPI transform step and selecting lots of events would add more load to Clickhouse.
  2. The overall recommendation is to not hold on to all historical data (for both privacy and performance reasons). If that’s not feasible, I would recommend the Vector backend and transform to S3 and load from there as above (notes on this below). The transform tool has decent logs for determining if events can’t be transformed, and should be scriptable to run batches and quit on a bad batch, but could likely use improvement for machine-readable output and data-driven recovery. The output as-is should tell operators where to resume from in a failure, but on massive long-running jobs that is likely not enough. I know @Felipe has learned a lot of painful lessons here that he might be able to share.
  3. Aspects xAPI event IDs are stable across user/event type/timestamp and events are deduplicated at several levels and so should be reliable at the report levels.
  4. There are a couple of ways of handling missing enrollment events, but they are currently fairly manual. Aspects has an event sink for enrollment events in addition to the tracking logs, which we really should move over to use as our source of truth but just haven’t been able to get to yet. However you can use the dump_data_to_clickhouse command to fully backfill that table, and re-create the missing xAPI events in SQL - or I have a script around somewhere that creates xAPI events suitable for direct insertion from the MySQL historical enrollment table. Not pretty, but it has worked for other folks.
  5. transform_tracking_logs will skip any tracking events that don’t transform, to the best of my knowledge there is no way to recover any events that don’t parse. The biggest risk of lost data is inserting to Clickhouse in big batches and having some or all of the batch fail due to Clickhouse running out of resources. Inserting small batches kills performance of the backfill while still risking some amount of lost data. This is generally mitigated by using the Vector backend instead of Celery, which has more robust retries and dynamic insert sizing but was not thoroughly exercised before Aspects v5 and may have issues to work around.
  6. Much of the data Aspects deals with has no analog source of truth (most engagement data) to compare as it only exists in tracking events. The most important data, enrollments and grades, can be checked on a rolling basis against the MySQL source of truth, provided you don’t try to compare records recent enough that they haven’t landed in Clickhouse yet. There was a report planned for this in the Operator Dashboard, but that all got deprioritized. Some operators run the backfill scripts on a cadence (hourly or daily) as a backstop against lost data.
  7. I would defer to Felipe on this one again, but I suspect it is highly dependent on your configuration. Historical imports should definitely be throttled, and as much as possible run during off hours. They should also be run in chronological-order (or probably reverse-chronological) batches, as Clickhouse can fail with “too many parts” errors if a batch touches too many year-month partitions.

We haven’t had a lot of feedback from large scale deployments or migrations, so I would love to partner with you to develop best practices and roll fixes / improvements into the product (even in older theoretically unsupported versions) for migrations and all installations at this scale. There are some known issues in older versions that I can help fix forward, but I would need your help and some trial-and-error to do that.

Here are things I know will help get you where you want to go, but would need some collaboration:

  1. Carry back and test the performance and accuracy fixes from Aspects 6 to 3-5, this should improve insert performance and report accuracy tremendously
  2. Use the Vector backend. This has issues on earlier versions of Aspects, but again it’s probably well worth the time given the higher rate of insert failure, event loss, and resource consumption with Celery at this scale (likely to persist to some extent even after the fixes above).
  3. Add / improve machine readable reporting on the backfill scripts so automated systems can better track failures in the process and recover without help
  4. Backport or create Tutor patches to emulate the “Event logs → xAPI → S3” pipeline that creates Clickhouse import-ready event files partitioned by hour. That makes recovery and backfill replay tremendously faster
  5. Change the enrollment source of truth to the event sink, which should be much easier to replay, instead of the events
  6. Create reports that compares Clickhouse report data to the MySQL source of truth, expose duplicates,
    1. Debug, fix forward anything unexpected
  7. Probably some kind of wrapper script around all of the backfill commands and output that can aggregate results, retry failures, and surface status. This should probably also wrap the “S3 → Clickhouse” inserts so that they can be loaded an hour or day at a time and retried at that smaller scale

The end result would be a process of:

  1. Configure your instance, especially the TTL and new Vector settings so new events get captured to block storage as well as Clickhouse
  2. Run the event sink backfills with modest batch sizes and sleep times (this doc needs a section for the enrollment events, but it’s the same process)
    1. New tooling would capture failures and retry until everything succeeded or fail with detailed logs
  3. Run the xAPI backfill in reverse chronological order, but only to S3
  4. As data becomes available, import from S3 → Clickhouse hours or days at a time
  5. Validate with the new reports
    1. Repeat any necessary backfill that the automation couldn’t, fix forward and found issues
    2. Repeat until everything validates

This all sounds like a lot, but honestly isn’t a ton of work. Mostly the development time would be spent in testing backports of existing functionality and making sure we haven’t broken anything along the way. The S3 → Clickhouse pipeline is how I do our high volume performance testing at up to a couple billion events, and has been very reliable.

The real wins would be in backfill time, automation, and reliability which I know have been a struggle in the past. Anyway, that’s probably more than enough for now. I’m happy to help however I can and make this process better for all. Feel free to reach out on Slack in any of the Aspects rooms if you have specific questions. I’m going to write up the above as issues so they don’t get lost, and will link them from here.

Here is the epic that tracks all of the large instance import work that I know of, tickets are in all relevant repos (several already existed and have been updated). I will try to find time to start chipping away at these.

Hi @chintan, thanks @TyHob for the ping and for the epic.

For context: at edunext we spent most of the last year doing this for a large scale instance under our service. It is a long-running instance, roughly 2018 → today, with 300k–560k events/day live and ~7 years of tracking logs in S3-compatible object storage. We didn’t come from Cairn, but nearly everything you describe we hit as well: enrollment counts that don’t match MySQL, broken log files, and ClickHouse falling over during backfill while live ingestion continued. So I’ll answer your questions with what actually happened to us, then add a few things to Ty’s plan.

Short version

  • Ty’s diagnosis matches ours exactly. Our historical data was not the main source of loss; the live pipeline was. On a course that started after go-live and never had a backfill, we were missing 30–40% of enrollment activations every day (758 active in MySQL vs 432 in Aspects). The raw logs had every event. Running Ralph and a pure Vector pipeline side by side for ~2 weeks gave identical hourly counts, so the loss was upstream of both, in the Celery/event-bus batching. Moving to Vector, with EVENT_ROUTING_BACKEND_BATCHING_ENABLED: false and Ralph turned off, is what fixed it.
  • Fix live ingestion before you backfill anything. This will allow you to have some real comparison data.
  • Don’t replay history straight into the live ClickHouse. We did, three times, and it cost us an incident. Go through object storage, as Ty describes.

Your questions

1. Is this a recommended strategy? Cairn extraction vs replaying logs?
I think you are in the right path, but I would put “make live ingestion lossless and verify it” first. On Cairn vs logs I agree with Ty: replay the logs. Before trusting any copy in a database, check it against the raw logs. Every time we did that, the logs were complete and the database copy wasn’t. He hit this several times when we didn’t know that we were being constrained by the big batch inserts in Ralph. If you go to vector straight away it will be less difficult.

2. Workflow for ~1 TB of logs, checkpoints, retries, resume

  • Split the single 1 TB tracking.log first. transform_tracking_logs works per object through libcloud, so cut the file into hourly or daily objects under a date prefix (e.g. date=YYYY-MM-DD/) in object storage. A day then becomes your unit of work, checkpoint, retry and validation.
  • Make sure the files are really what their names say. Our bucket had .gz files that weren’t gzip and gzip files that broke streaming. In the end we wrote a plain-text copy of the whole bucket under a *-plain/ prefix and pointed the transform at that.
  • Drive it one day at a time from a wrapper script that records success or failure per day. Ours had to guess success from the command’s text output, and for one 5-year run it reported 1,717 “failures” for days whose data had in fact landed. The machine-readable report in event-routing-backends#597 matters a lot.
  • Don’t let one bad batch kill a multi-day run. We patched ERB to skip a failed batch and carry on, then went back and re-ran the days that came up short.
  • Run it in a dedicated pod, not inside a live consumer or LMS pod. When the session drops you lose state, and the job competes with live traffic. We also use this to schedule our backfill runs into the periods of low traffic in the late night and early morning.

3. Overlap and duplicates
In our experience the event IDs were stable across re-runs: we re-ingested the same days several times over. Duplicates collapse in the base table through ReplacingMergeTree merges, but that happens eventually. Count straight after a load and you’ll see them. We checked with count(*) vs count(DISTINCT event_id) per day, and ran OPTIMIZE TABLE … PARTITION … FINAL on a single month when we needed exact numbers right away. Don’t run that across the whole table on a big dataset. For the derived reporting tables I’ll defer to Ty.

4. Missing historical enrollment events
Same experience: “Current Enrollees” is built from events, so it can only be as good as the events. Ty’s plan to make the course_enrollment event sink the source of truth (aspects-dbt#165) is the real fix. We did a lot of work trying to bridge the gap with this difference, and while it definitely got better after we fine tuned everything in our pipeline, I would still prefer to have the course_enrollment sinc.

5. Malformed records
Two more places events go missing, besides bad log lines:

  • If you ran the Celery/event-bus pipeline, check Redis for dead_queue_xapi. We found ~3.4M events stuck there and recovered them with manage.py lms recover_failed_events --transformer_type xapi, in batches, at night.
  • Failed bulk inserts under ClickHouse pressure. Per Ty, that is the biggest source of loss, and it doesn’t show up as malformed data anywhere.

6. Validation
What worked for us:

  • Pipeline vs pipeline: hourly event counts from two independent paths, e.g. Celery vs Vector, run side by side for a week or two.
  • Logs vs ClickHouse per day: a line count per log file/day against xAPI rows per day. They won’t be 1:1, because only some event types transform, but the ratio should be stable. When the count was too low we could re run that day.
  • MySQL vs ClickHouse for enrollments on a handful of courses, using only courses created after go-live, on a rolling window that skips the last few hours. This is where we spent a lot of time trying to find tune our pipeline. My recommendation after this would be to move immediately to Vector as I said before.

7. Sizing and throttling
Our numbers, for comparison:

  • 3-node ClickHouse cluster (25.3) on local SSD. Measure disk write speed with dd before you plan anything. One of our environments did 2 KB/s against 6.7 MB/s on the other, and we dropped it from the plan.
  • Through the old Ralph/LRS path it took ≈4 hours per day of logs. Our first estimate for the full history was over a year of continuous running, which is what pushed us to a cluster and eventually away from Ralph.
  • Throttle per batch, not per day started. We limited the backfill to 00:00–07:00, but one job covers a whole day of logs, and one day had ~10× the usual volume. That job ran well into business hours. We ended up with a loop that checks the clock before each day (large batches with short sleeps off-peak, smaller batches with long sleeps at peak), and it should check before each batch.

The incident, since you asked what to avoid

Our historical re-ingest had written many small inserts into a single month’s partition. ClickHouse then tried to merge them in one operation, ran out of memory and retried, around the clock, for 6 days. Two things made it worse, and they are probably what you’re seeing as “diagnostic-table merges”:

  • The server log level was trace. System log tables grew to ~76 GiB on one node and caused their own merge storm. Set a production log level and put TTLs on the system.*_log tables before you start.
  • Vector’s defaults: 9 agents each inserting every ~1s through one VIP with no concurrency limit. Inserts stalled for up to 2 minutes and piled up.

What fixed it:

  • Cleaning up the system logs.
  • Vector set to larger batches every ~10s with a ceiling of ~3 connections per node.
  • On the ClickHouse user Vector inserts with: async_insert = 1, wait_for_async_insert = 0.

One sign to watch: we had a steady ~30 min lag from LMS to ClickHouse. It wasn’t slowness; it was Vector timing out and dropping batches because ClickHouse took longer per batch than Vector would wait. Async inserts made it go away.

On Ty’s plan

+1 to all of it, especially “xAPI to S3 first, then load an hour/day at a time”. Looking back, that alone would have avoided our incident and made our gap re-runs cheap. Some things I would add to the runbook:

  1. Check ASPECTS_DATA_TTL_EXPRESSION before anything else. The default TTL silently drops anything older than the window, which is all of your history. We set it to ''.
  2. Ship production Vector defaults: batch size/timeout, a concurrency ceiling, and async_insert on the insert user. The current defaults are fine for small instances and harmful at this size.
  3. System log level and TTLs as an explicit pre-flight step.
  4. Drain the dead queue as part of the Celery → Vector migration.

Thank you so much @Felipe @TyHob

These are great insights and pointers.

I’ll try to implement these with my approach and come up with an update.