Note creating new tables is not a risky operation and it can be done by anyone with access to the cluster. I will give it a look anyway, as per request.

The main blocker, aside from comments, would be to filter it before reaching labs. Please do not deploy it to production until proper filtering is in place or it could lead to a private data leak. In particular, centralauth is normally handled with extra care for obvious reasons.

Change 403833 had a related patch set uploaded (by MaxSem; owner: MaxSem):
[operations/puppet@production] labs: add GlobalPreferences to Sanitarium

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

We are currently experiencing a bit of a crisis with several emergencies on core systems, we may have some delay on attending features requests due to that- but site reliability is taking precedence-, I ask you to be patient, but contact us louder (through SOS or management) if that will block you a lot (but please only for unbreak now or similar).

Hey @jcrespo, we're done with the security review for the extension and are hoping to go to beta cluster next week. Can you give it a review now? Thanks!

Ok, so let's see where we're at here.

This extension is basically copying the core user_preferences table, which mostly works as designed, and doesn't cause any major problems.

I've filed T188657 to make the gp_user int unsigned (and also T188658 for CA itself to make that unsigned too), then the DBAs don't need to modify it later, might aswell fix it now.

MaxSem has made a patch to filter the table, so we just need to ask cloud-services-team nicely to merge/deploy it before the table has been created in the central auth db. That addresses one immediate concern

One thing to check is that s7 has no immediate storage constraints. enwiki's user_properties table takes just shy of 22G. It's not a tiny amount, but it's not insurmountable. Obviously, with many more users, the table (and it's indexes) are going to be bigger for a global version

+---------------------------------------+----------+---------+---------+------------+---------+
| CONCAT(table_schema, '.', table_name) | rows     | DATA    | idx     | total_size | idxfrac |
+---------------------------------------+----------+---------+---------+------------+---------+
| enwiki.user_properties                | 122.36M  | 15.22G  | 6.56G   | 21.79G     |    0.43 |
+---------------------------------------+----------+---------+---------+------------+---------+

We should get a DBA or at least an SRE member to have a quick look and make sure there's no immediate space constraints to s7 potentially adding 10s of GB of data in the near future. Possibly the load in general, but I think that should be fine.

The table has a PK (as all new additions should), so if modifications are needed, it's easier to do so. Also helps with ensuring DB consistency across hosts

CR +1 (pending the unsigned fix). I don't see any obvious reason why we should block on a DBA review when they're doing things that are much more important (I couldn't decide how to word that that didn't sound sarcastic. It's not supposed to be; the stuff they're doing *is* more important). Bar the minor outstanding questions/pre-requisites. We're deploying a version of something that's basically working in practice on the rest of the wikis.

In T184666#4016082, @Reedy wrote:

One thing to check is that s7 has no immediate storage constraints. enwiki's user_properties table takes just shy of 22G. It's not a tiny amount, but it's not insurmountable. Obviously, with many more users, the table (and it's indexes) are going to be bigger for a global version

+---------------------------------------+----------+---------+---------+------------+---------+
| CONCAT(table_schema, '.', table_name) | rows     | DATA    | idx     | total_size | idxfrac |
+---------------------------------------+----------+---------+---------+------------+---------+
| enwiki.user_properties                | 122.36M  | 15.22G  | 6.56G   | 21.79G     |    0.43 |
+---------------------------------------+----------+---------+---------+------------+---------+

We should get a DBA or at least an SRE member to have a quick look and make sure there's no immediate space constraints to s7 potentially adding 10s of GB of data in the near future. Possibly the load in general, but I think that should be fine.

Space should not be a problem in s7.
Load-wise: need to check that deeper.

I don't mind if global_properties get filled in, but would that mean, the local ones would get cleaned up eventually (or that a user would use one, or the other, but not both)? I would like to avoid duplication of the same data (it is ok for some time after deployment, but not once things are working as intended).

In T184666#4017331, @jcrespo wrote:

I don't mind if global_properties get filled in, but would that mean, the local ones would get cleaned up eventually (or that a user would use one, or the other, but not both)? I would like to avoid duplication of the same data (it is ok for some time after deployment, but not once things are working as intended).

So in many cases it'll be possible that users have a global preference, and also numerous local preferences as an override on specific wikis. Which is going to be a net gain in storage space used

On local preferences, if the selection is the same as default, it shouldn't be stored (there's some exceptions for users that haven't updated preferences since the wiki defaults changed)... I'm not sure we can say the same for GlobalPreferences, as wikis can have different default preferences... So the global needs to be there to override the lot?

I'm presuming if a global is set on a wiki, it'll remove it from the preferences locally (one of the people doing the work on the extension will have to say)... But I'm guessing on the rest of the wikis in all.dblist nothing changes (I don't see any evidence of looping over all wikis, whether in a job queue job or similar). Unless some sort of save action is done on that wiki, and things are recalculated... local preferences might be removed then

I guess there'd be scope to have job queue jobs that go through and removes local user preferences that are the same as global preferences

I guess there'd be scope to have job queue jobs that go through and removes local user preferences that are the same as global preferences

I am asking to know what is the model- if it is "you can copy preferences around", or "you can override global preferences" or "all preferences are global, but you can have special preferences for some wikis". The model doesn't have to be coded now, but expectations should be clear and at a later time cleanups could be done? I am asking because I am not sure what the "multiple preferences model" is going to be (and there was already ongoing issues with the preferences, like having set up options that were default).

10GB is not much in the order of things, but remember that redundancy makes those around ~20 more costly, and I would like to see clear "cleanup intentions", going forward for future maintainability, if you understand what I mean.

To clarify, I don't need a cleanup solution now, but I would like to see a plan was thought to avoid problems like ones we are having such as T54921, T183485 and T143366.

The model is the following:

• When the user makes a setting global, a row is inserted into centralauth.global_preferences. The remaining user_properties rows on local wikis are retained.
• If the user decides to override a global preference, a user_properties row with a special key is inserted to mark the preference as overridden, the local preference row, if any, will be used as the overriding value.
• If the user removes the global setting, all wikis fall back to their previous settings.

This means that the local row are retained, but not retaining them would be a user experience disaster because we will be essentially punishing users for experimenting with global preferences by wiping their existing settings.

I see the point, plus I have read about it on the help page:

The global preferences page has a link, just like the normal preferences page, to remove all preferences. This will (after prompting for confirmation) delete all global preferences and all preferences will be restored to either the local value a user has set, or the site default.

Note that this deletes global values even for preferences that are not present on the current wiki. For example, if you set a global preference for an extension that's only on one wiki, and then remove global preferences via another wiki, when you go back to the first wiki's global preferences page you will not have any global preferences set. Removing global preferences does not remove local exceptions, but rather just disables them (i.e. local preferences or site defaults will be in effect). If a preference is made global again, its local exception will be reinstated.

So this has several concerns, mostly related to "dead rows"- basically data on the database that is not live (it will never -or likely never- be read or written again):

• Local preferences overriden by global preferences (I am assuming that in most cases, people will want to setup global preferences once and forget -long term- about local ones)
• [Implementation assumption]All setup global preferences will be recorded to the database, even if they are mediawiki defaults
• Deleted global preferences will also be kept, in case they are reenabled

While I see the point of keeping those (as you said, to not punish testing), I see an increase of more than double- but n times on the current capacity for preferences- which is normally not a huge issues, but the truth is in most cases those will be dead rows (never will be touched again). On top of that, preference storing has not been very efficient, because with the former storage, it didn't take that much space. I see that changing for the new global model.

Currently, there are 13GB of preferences:

/srv/backups$ find dump.s* -name '*.user_properties.*sql.gz' -exec zcat '{}' \; | wc --bytes
13327693685

Note that is the raw space without indexes, the actual space used can be between 2x and 5x that. With an average of 22 instances per section, and RAID mirroring, I get between 1-4 TB extra of storage. Not every user will use global preferences, but the average user will take more space than with only local ones (because of the reasons seen above).

With that in mind, I have three potential proposals:

1. Work on avoiding redundancies- for example, maybe you want to allow users to revert to local preferences, but maybe that could be done after some amount of time. For example, if a user has not changed its global preferences in 6 months, maybe the local ones can be deleted then. Or if a global preference is disabled for 6 months, it gets "forgotten". I don't think that should be exactly that, but there should be some kind of clean up "dead rows" process. Maybe some preferences that are now the default can be detected and cleaned up?
2. Work on improving preferences storage so it takes so little space that redundancy is not a huge issue. For example, by normalizing preferences names with ids. I bet we can get 10x less footprint this way, and there is already a function in mediawiki core implemented for comment normalization, so it shouldn't be a huge time sink
3. Purchase additional server for s7 to compensate the extra storage needed

In other order of things- if global preferences retrieval fails- how does the viewing or editing experience degrades? What if the local wiki goes read only but not centralauth. What if centralauth goes read only but not the local wiki?

Maybe some preferences that are now the default can be detected and cleaned up?

We have cleanupPreferences.php for that, but it can't do much, so probably we could resurrect https://gerrit.wikimedia.org/r/#/c/101233/ to do a more thorough cleanup. This will probably free some rows if ran periodically. It'll need an update for GlobalPreferences anyway - filed as T188966.

So this has several concerns, mostly related to "dead rows"- basically data on the database that is not live (it will never -or likely never- be read or written again):

• Local preferences overriden by global preferences (I am assuming that in most cases, people will want to setup global preferences once and forget -long term- about local ones)

We need to always give the user the option to stop overriding the local preferences, thus we have to keep storing the local prefs.

• [Implementation assumption]All setup global preferences will be recorded to the database, even if they are mediawiki defaults

I believe that's correct, but needed to avoid the pref changing if the default changes.

• Deleted global preferences will also be kept, in case they are reenabled

This should probably be handled by a maintenance script. We'll look into it.

While I agree that figuring out how to optimize preference storage is something we need to do, I don't see how it's directly related to implementing GlobalPreferences. We're creating and using a separate table that should have minimal storage implications (it's a feature intended for power-users). Max's estimate of the impact on user_properties (400k rows across all SUL wikis) seems pretty liberal to me. I would be surprised if it was more than 100k. That's a drop in the bucket compared to the half billion user_property rows that exist for all SUL wikis. If we're really that concerned about increasing the size by such a small amount, we should put a freeze on introducing any new prefs across the board.

• Deleted global preferences will also be kept, in case they are reenabled

This should probably be handled by a maintenance script. We'll look into it.

They are not kept in the database.

While I agree that figuring out how to optimize preference storage is something we need to do, I don't see how it's directly related to implementing GlobalPreferences.

To be clear- I don't need you to fix the current preference storage, the issue is that the current one is broken, and if you are going to make it worse by duplicating it many times- for little to no value. As I explained before, I need the guarantee that someone is going to be there to maintain it- either your team, or some other you have spoken to and agreed to it. You seem to not seeing that as a problem, so I have to block further development until that is fixed (maintenance ownership) so that the problem isn't worse.

We're creating and using a separate table that should have minimal storage implications (it's a feature intended for power-users). Max's estimate of the impact on
user_properties (400k rows across all SUL wikis) seems pretty liberal to me. I would be surprised if it was more than 100k.

Is this a guarantee that if global preferences cross the 100K row limit, your team will agree to work with high priority on preference normalization/cleanup or I will block the table from receiving further updates? "We'll look into" it is not enough for me to allow this.

Some data: there is currently 135M rows on enwiki only- my estimation is that for 100K rows, only 0.04% of users using preferences will use this over all languages- if that is ok, please give me the above guarantees that community tech will work on one of the first 2 bullet points here T184666#4021193 if it goes over 100K rows. As I said, I don't need you to do the work now, I need the guarantee to fix things once they go wrong ("but it is only a few rows" is no longer true).

The impact of this feature on the user_properties table should be less than introducing a single new preference. We're talking about an edge case of a power-user feature (overriding a global preference). As confident as I am that the impact will be small, I realize that words are cheap. Unfortunately, I can't commit CommTech to refactoring prefs storage, as the GlobalPrefs project is already a quarter overdue (mainly due to other prefs refactoring we decided to do while we were in there). I can, however, propose budget for an additional s7 db server as part of this project if you think that would be helpful (as the FY2018-19 budget is not yet finalized). How much would such a server cost? (Feel free to move this discussion to email if that would be more appropriate.)

@mark T184666#4021193

@jcrespo: One thing that hasn't been mentioned is that GlobalPreferences will almost certainly reduce the growth of the user_properties tables in the long run. Instead of users having to duplicate all of their preference selections across all the wikis they use, they will only have to set them once on their home wiki and declare them to be global. So the net effect on db storage should actually be positive in the long run.

@jcrespo: if we shaved off 80M+ rows totaling 6G+ in size from a different place, would it alleviate your disk space concerns?

@jcrespo: Any further thoughts on this or are you waiting on feedback from @mark?

In T184666#4033042, @kaldari wrote:

@jcrespo: One thing that hasn't been mentioned is that GlobalPreferences will almost certainly reduce the growth of the user_properties tables in the long run. Instead of users having to duplicate all of their preference selections across all the wikis they use, they will only have to set them once on their home wiki and declare them to be global. So the net effect on db storage should actually be positive in the long run.

So to summarize and make sure I understand things correctly: this new GlobalPreferences feature is taking the existing design in MediaWiki which unfortunately is space-inefficient, and amplifies it a few times. This is causing additional space growth on s7, although we can't quite yet predict how much actual impact there will be. Besides additional space, there are no other concerns with this schema change, e.g. additional load. Although it will remain suboptimal, we may be able to alleviate this with additional s7 server(s) for the extra space needed.

It sounds like this would have benefit from a joint design discussion much earlier in the process, but it's rather late now, for the reasons @kaldari mentioned. Perhaps we should strive to work closer together in the future and have schema design discussions & signoff at the start of projects that involve database changes, and avoid frustrations like these. :)

In any case, it sounds like the additional space won't yet cause a problem for s7 in the very short term, and I'm not sure yet if there's any need to budget additional machines for next FY before we know what actual impact will be. And it would be a short term fix at best.

@kaldari, However, could you please indicate what level of commitment you're able to give for supporting maintenance/resolving problems with this feature after the deployment is done and dusted? If you're not able to support it going forward should the need arise, will any other team be responsible for this?

Thanks!

I agree that we should have consulted with the DBAs earlier. We went through 2 TechComm RfCs early on, but didn't ask for a DBA review until the end (partially because we didn't expect there to be any significant issues). Lesson learned.

I think we can commit to a few things:

• If the storage impact unexpectedly exceeds our estimates and is likely to cause problems we can implement some of Jaime's non-schema-related suggestions or even remove the ability to locally override global preferences. We would rather not make these changes unless they are really needed though, as I think they will negatively impact the feature.
• Max will work on identifying some areas elsewhere that we can trim unneeded data from storage in order to offset the short-term impact of rolling out GlobalPreferences.
• If there are bugs or issues with GlobalPreferences (or the refactoring work we did on the core Preferences code), we will definitely address that (and assist with ongoing maintenance).

We cannot, however, commit to refactoring storage for Preferences. I think Jaime's normalization idea is great, but I think the MediaWiki Platform team would be better suited to take on a project like that.

Hi, after Mark's comments and Kaldari response, this seems reasonable-- we will deploy as is, but we should revisit some kind of cleanup/optimization, as you mention, if the size of the new table grows over the predicted maximum, around 1 million rows. We will need your help in that case.

As creates are not consider "real" schema changes https://wikitech.wikimedia.org/wiki/Schema_changes#What_is_not_a_schema_change , you can on your own run the create table script (on s7/centralauth) to create the table. I think you already deployed the labsdb filters, but check with cloud on that (no filter needed for sanitarium, so that would be good to go).

Change 403833 merged by Bstorm:
[operations/puppet@production] wiki replicas: add GlobalPreferences to maintain-views

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