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

[operations/puppet@production] Add growthexperiments_mentee_data to private tables

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

@Tgr Hello, could you review the table design and this task please? Thanks!

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

[mediawiki/extensions/GrowthExperiments@master] Create database table to store data about mentees

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

As this is a cache table with discardable content, we hope to do all the searching and sorting on the client side, and our understanding of what is and isn't important data for this table will no doubt be shaped by initial experiences and user feedback, maybe just go with something unstructured, such as a mentee ID -> JSON blob lookup table? That will make changes to the data dimensions much less painful.

In T279587#6982058, @Tgr wrote:

As this is a cache table with discardable content, we hope to do all the searching and sorting on the client side, and our understanding of what is and isn't important data for this table will no doubt be shaped by initial experiences and user feedback, maybe just go with something unstructured, such as a mentee ID -> JSON blob lookup table? That will make changes to the data dimensions much less painful.

Good point. @Marostegui What would be your opinion on this?

I was going to ask what @Tgr actually explained, so thanks for that :-)
Just some more questions:

• What would happen if this table isn't writable for any reason? (ie: host down, host under maintenance...).
• What would happen if the script doesn't run for a day or a week? (ie: broken for whatever reason, mwmaint host down?)
• What would happen if the script gets killed in the middle of a run?
• Is this a table per wiki or a table containing all the wikis and living in x1?

In T279587#6982489, @Marostegui wrote:

I was going to ask what @Tgr actually explained, so thanks for that :-)

Okay, good to know :).

Just some more questions:

• What would happen if this table isn't writable for any reason? (ie: host down, host under maintenance...).

If the table only isn't writeable, the script simply won't update the cached data, and the frontend will reuse the old one. Users will experience a delay in updating the data, but that isn't a concern.

• What would happen if the script doesn't run for a day or a week? (ie: broken for whatever reason, mwmaint host down?)

Same as above. A breakage taking days or weeks would definitely be a noticeable issue. But, nothing would terribly break (no exceptions thrown, etc.), it would just not update.

• What would happen if the script gets killed in the middle of a run?

It will update the cache only for some of the mentees. Mentors (consumers of the data about their mentees) will know the feature heavily caches the data, and the only thing they'll notice is that the data is out-of-date partially. Unless the script gets killed regularly (like on every run), it shouldn't be an issue.

• Is this a table per wiki or a table containing all the wikis and living in x1?

It should be a per-wiki table living in x1 cluster (ie. in the per-wiki databases, not wikishared), unless you prefer to put that table elsewhere, of course.

Thanks for the answers. They look good to me. x1 should be fine as this doesn't look like a table that will have much load (neither writes or reads).
As for next steps, if you can come up with a draft of the schema and some of the read queries it will be receiving?
If we use BLOB and you'd need an index on that column, we'd need to specify how many characters we'd be indexing.

Sure, here you go:

CREATE TABLE /*_*/growthexperiments_mentee_data (
  mentee_id INT UNSIGNED NOT NULL,
  mentee_data BLOB NOT NULL,
  PRIMARY KEY(mentee_id)
) /*$wgDBTableOptions*/;

I don't need any other index besides the primary key. Example of read query: SELECT mentor_id, mentor_data FROM growthexperiments_mentee_data WHERE mentee_id IN (123, 342, 965, ...). The JSON blob will be processed by application-level logic.

Does that look good to you?\

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

[mediawiki/extensions/WikimediaMaintenance@master] GrowthExperiments: Create growthexperiments_mentee_data DB table

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

Change 677655 merged by jenkins-bot:

[mediawiki/extensions/GrowthExperiments@master] Create database table to store data about mentees

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

Change 678312 merged by jenkins-bot:

[mediawiki/extensions/WikimediaMaintenance@master] GrowthExperiments: Create growthexperiments_mentee_data DB table

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

In T279587#6984196, @Urbanecm_WMF wrote:

Sure, here you go:

CREATE TABLE /*_*/growthexperiments_mentee_data (
  mentee_id INT UNSIGNED NOT NULL,
  mentee_data BLOB NOT NULL,
  PRIMARY KEY(mentee_id)
) /*$wgDBTableOptions*/;

I don't need any other index besides the primary key. Example of read query: SELECT mentor_id, mentor_data FROM growthexperiments_mentee_data WHERE mentor_id IN (123, 342, 965, ...). The JSON blob will be processed by application-level logic.

Does that look good to you?\

This looks good. In some rare cases we've seen mariadb optimizer going a bit crazy when doing massive selects with IN clause (especially with big tables, but it doesn't look like this table will be reaching crazy amount of rows).

Change 677653 merged by Marostegui:

[operations/puppet@production] Add growthexperiments_mentee_data to private tables

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

Mentioned in SAL (#wikimedia-operations) [2021-04-13T08:16:37Z] <marostegui> Restart sanitarium hosts db1124, db1125, db1154, db1155, db2094, db2095 T279587

All sanitarium hosts have been restarted

Thanks!

Mentioned in SAL (#wikimedia-operations) [2021-04-20T23:28:51Z] <Urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist growthexperiments sql.php --cluster=extension1 /srv/mediawiki/php-1.37.0-wmf.1/extensions/GrowthExperiments/maintenance/schemas/mysql/growthexperiments_mentee_data.sql # T279587