Page MenuHomePhabricator

Move mentor/mentee relationship to a separate database table to make it possible to run more queries on it
Open, LowPublic

Description

Background

The Growth team is soon going to start working on mentor dashboard. This dashboard would likely contain some data about mentees assigned to a particular mentor (or metadata about mentees in general).

Doing such stuff probably would require filtering user_properties based on up_value (a blob), which sounds like a bad idea to do.

Example query to find number of mentees assigned to each mentor:

SELECT user_name, COUNT(*) FROM user_properties JOIN user ON up_value=user_id WHERE up_property="growthexperiments-mentor-id" GROUP BY user_id ORDER BY COUNT(*) DESC;
Proposed solution

Create growthexperiments_mentees table (or similar), and store mentor/mentee relationship there, rather than user_properties. This new table could have mentor_id, mentee_id as a composite index, which would mean queries like the one above would be just an index scan.

Completing this task would also make it possible to expose the mentor/mentee relationship easily to Toolforge replicas, where communities could do their own work around it. That could show us which features are actually used by wikis, and which could be implemented back to our codebase.

Event Timeline

We should probably double-check if it's OK for the mentor-mentee relationship to be public (it is sort of public with {{#mentor}} already but that's still different from publishing the entire dump).

We should probably double-check if it's OK for the mentor-mentee relationship to be public (it is sort of public with {{#mentor}} already but that's still different from publishing the entire dump).

Definitely, but it's a potential advantage to gain (and something we need to do one day as part of Growth-Scaling, unless we want to develop everything a wiki can want ourself).

Change 673999 had a related patch set uploaded (by Urbanecm; owner: Urbanecm):
[mediawiki/extensions/GrowthExperiments@master] WIP: Create DatabaseMentorManager for loading data from database

https://gerrit.wikimedia.org/r/673999

Change 674444 had a related patch set uploaded (by Urbanecm; owner: Urbanecm):
[mediawiki/extensions/GrowthExperiments@master] Factor mentor storage out of MentorManager

https://gerrit.wikimedia.org/r/674444

Change 674444 merged by jenkins-bot:
[mediawiki/extensions/GrowthExperiments@master] Factor mentor store out of MentorManager

https://gerrit.wikimedia.org/r/674444

Change 675190 had a related patch set uploaded (by Urbanecm; author: Urbanecm):
[mediawiki/extensions/GrowthExperiments@master] WIP: Use MultiWriteMentorStore as a migration mentor store

https://gerrit.wikimedia.org/r/675190

Change 675192 had a related patch set uploaded (by Urbanecm; author: Urbanecm):
[mediawiki/extensions/GrowthExperiments@master] Add a script to migrate mentee relationship to database

https://gerrit.wikimedia.org/r/675192

Change 676400 had a related patch set uploaded (by Urbanecm; author: Urbanecm):

[mediawiki/extensions/GrowthExperiments@master] Refactor MentorStore to use IDBAccessObject flags

https://gerrit.wikimedia.org/r/676400

Change 676403 had a related patch set uploaded (by Urbanecm; author: Urbanecm):

[mediawiki/extensions/WikimediaMaintenance@master] GrowthExperiments: Create growthexperiments_mentor_mentee table as well

https://gerrit.wikimedia.org/r/676403

Change 676400 merged by jenkins-bot:

[mediawiki/extensions/GrowthExperiments@master] Refactor MentorStore to use IDBAccessObject flags

https://gerrit.wikimedia.org/r/676400

Change 677921 had a related patch set uploaded (by Urbanecm; author: Urbanecm):

[mediawiki/extensions/GrowthExperiments@master] Create growthexperiments_mentor_mentee to store mentor/mentee relationship

https://gerrit.wikimedia.org/r/677921

Change 677921 merged by jenkins-bot:

[mediawiki/extensions/GrowthExperiments@master] Create growthexperiments_mentor_mentee to store mentor/mentee relationship

https://gerrit.wikimedia.org/r/677921

Change 676403 merged by jenkins-bot:

[mediawiki/extensions/WikimediaMaintenance@master] GrowthExperiments: Create growthexperiments_mentor_mentee table as well

https://gerrit.wikimedia.org/r/676403

Change 673999 merged by jenkins-bot:

[mediawiki/extensions/GrowthExperiments@master] Create DatabaseMentorManager for loading data from database

https://gerrit.wikimedia.org/r/673999

Change 675190 merged by jenkins-bot:

[mediawiki/extensions/GrowthExperiments@master] Use MultiWriteMentorStore as a migration mentor store

https://gerrit.wikimedia.org/r/675190

Change 675192 merged by jenkins-bot:

[mediawiki/extensions/GrowthExperiments@master] Add a script to migrate mentee relationship to database

https://gerrit.wikimedia.org/r/675192

Change 678578 had a related patch set uploaded (by Urbanecm; author: Urbanecm):

[mediawiki/extensions/GrowthExperiments@master] GrowthHooks: Make sure $wgGEMentorshipMigrationStage is string before parsing it

https://gerrit.wikimedia.org/r/678578

Change 678578 merged by jenkins-bot:

[mediawiki/extensions/GrowthExperiments@master] GrowthHooks: Make sure $wgGEMentorshipMigrationStage is string before parsing it

https://gerrit.wikimedia.org/r/678578

Change 678581 had a related patch set uploaded (by Urbanecm; author: Urbanecm):

[mediawiki/extensions/GrowthExperiments@master] Set GEMentorshipMigrationStage to SCHEMA_COMPAT_WRITE_BOTH | SCHEMA_COMPAT_READ_OLD

https://gerrit.wikimedia.org/r/678581

Urbanecm_WMF added a subscriber: Etonkovidova.

@Etonkovidova Hello Elena, this can be QA'ed now. Mentees on the beta wiki are now no longer stored in user_properties table, but in the new growthexperiments_mentor_mentee DB table (which is the new source of truth). Mentor/mentee relationship stored in user_properties were copied to the new database table as well.

Can you please make sure mentorship-related features still work on the beta wikis, before I go ahead with the migration on production wikis? Thanks!

Change 678624 had a related patch set uploaded (by Urbanecm; author: Urbanecm):

[mediawiki/extensions/GrowthExperiments@master] Run MigrateMentorMenteeRelationship in update.php

https://gerrit.wikimedia.org/r/678624

Change 678581 merged by jenkins-bot:

[mediawiki/extensions/GrowthExperiments@master] Set GEMentorshipMigrationStage to SCHEMA_COMPAT_WRITE_BOTH | SCHEMA_COMPAT_READ_OLD

https://gerrit.wikimedia.org/r/678581

Change 678624 merged by jenkins-bot:

[mediawiki/extensions/GrowthExperiments@master] Run MigrateMentorMenteeRelationship in update.php

https://gerrit.wikimedia.org/r/678624