Image data refresh's "update media popularity constants" step hung on last run

[AetherUnbound]

Description

The refresh_popularity_metrics_and_constants.update_media_popularity_constants_view task for the image data refresh DAG (which typically takes about 10 hours) ended up running for over 9 days. The query was still running on the postgres backend at that time. We took action to pause the DAG and kill the query. If we're able to reproduce this behavior, we need to investigate why it's happening.

Additional context

Resolution

• 🙋 I would be interested in resolving this bug.

[AetherUnbound]

I've marked this as "critical" because we are unable to run the image data refresh until this issue is resolved. We may be able to enable the DAG and skip the popularity recalculation steps since those only run once a month, but if there's a root cause for this we should work to try and find it.

[AetherUnbound]

It looks like I failed to update the image_popularity_metrics table after WordPress/openverse-catalog#795, meaning that rawpixel was not present in that table. My first assumption was that the extra time calculating metrics was the result of rawpixels being present in the table, so the fact that nothing was changed with the popularity metrics and this task still hung is quite confusing. I'm inclined to see if we could attempt to run the data refresh on Monday of next week and monitor its progress; I'd like to think this was just a very odd fluke 😕

[zackkrida]

@AetherUnbound Starting the data refresh today sounds fine. Could you do that and let folks know in Slack when it's been kicked off?

Two questions as well:

• I don't believe so, but is there any way the missing rawpixel data could have caused the hangup?
• Is there any way to test the data refresh that would give us faster results, for example in staging or something? We'll have to wait several days to determine if this was a fluke, right?

[AetherUnbound]

I don't believe so, but is there any way the missing rawpixel data could have caused the hangup?

Since we're just stuffing that as a field into the meta_data JSON blob (and the popularity calculation knows nothing about it because it's not in the image_popularity_metrics table), it should not affect runtime any more than some other unrelated field being present there.

Is there any way to test the data refresh that would give us faster results, for example in staging or something? We'll have to wait several days to determine if this was a fluke, right?

We could potentially try running the matview refresh command directly on postgres, but that would require one of us to have a connection to postgres open for the duration of the query which seems less feasible than simply kicking off the DAG.

Good questions!

[AetherUnbound]

I've started the image data refresh and will report back here with the results tomorrow.

[AetherUnbound]

Oddly enough, the data refresh was able to move right past that step after 13 hours!

I'll go ahead and close this issue, as it was likely a fluke.

[zackkrida]

I wonder if it was related to our increased scraping traffic from the past few weeks. Interesting!

[AetherUnbound]

I would be really surprised, since this is all happening on the catalog database which should be isolated from user traffic!