Drop user properties related to RC tours
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Urbanecm_WMF
	May 5 2024, 9:14 AM

Description

WikimediaMessages was (unconditionally) setting rcenhancedfilters-seen-tour and wlenhancedfilters-seen-tour to true to show a tour to only experienced users. @Dreamy_Jazz removed that tour in https://gerrit.wikimedia.org/r/c/mediawiki/extensions/WikimediaMessages/+/1027200 during the Hackathon, which means those rows can now be safely dropped. There are also other properties that are removable.

Probably best done once https://gerrit.wikimedia.org/r/c/mediawiki/extensions/WikimediaMessages/+/1027200 is deployed to production, so that we can drop all the rows.

Properties to remove

rcenhancedfilters-seen-tour
wlenhancedfilters-seen-tour
rcenhancedfilters-tried-highlight
rcenhancedfilters-seen-highlight-button-counter

Details

	Subject	Repo	Branch	Lines +/-
	Backport several WikimediaMessages patches	mediawiki/extensions/WikimediaMessages	wmf/1.43.0-wmf.3	+15 -693

Customize query in gerrit

Related Objects
Search...

Status	Subtype	Assigned	Task
Resolved		None	T340453 [Epic] FY 2023-24 Growth Maintenance Work
Resolved		KStoller-WMF	T347840 [Epic] Q2 FY 2023-24 Growth Maintenance Work
Open		None	T54777 user_properties table bloat
Resolved		Urbanecm_WMF	T364269 Drop user properties related to RC tours
Resolved	BUG REPORT	Urbanecm_WMF	T364311 userOptions.php fails to delete very large number of DB rows
Resolved		Dreamy_Jazz	T217451 Remove RCFilters Guided Tours

Event Timeline

Urbanecm_WMF created this task.May 5 2024, 9:14 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMay 5 2024, 9:14 AM

Urbanecm_WMF renamed this task from Drop rcenhancedfilters-seen-tour and wlenhancedfilters-seen-tour properties to Drop user properties related to RC tours.May 5 2024, 9:17 AM

Urbanecm_WMF added a project: MediaWiki-extensions-GuidedTour.

Urbanecm_WMF updated the task description. (Show Details)

Restricted Application added a project: Growth-Team. · View Herald TranscriptMay 5 2024, 9:17 AM

Addshore awarded a token.May 5 2024, 11:10 AM

Urbanecm_WMF mentioned this in T357072: Echo: Drop droppable rows from user_properties.May 5 2024, 11:27 AM

Urbanecm_WMF claimed this task.May 5 2024, 11:35 AM

Urbanecm_WMF triaged this task as Medium priority.

Urbanecm_WMF mentioned this in T217451: Remove RCFilters Guided Tours.

Urbanecm_WMF edited projects, added Growth-Team (Sprint 13 (Growth Team)); removed Growth-Team.

Can be only done after T217451: Remove RCFilters Guided Tours hits production, to avoid people re-seeing the tours.

Change #1027571 had a related patch set uploaded (by Urbanecm; author: Dreamy Jazz):

[mediawiki/extensions/WikimediaMessages@wmf/1.43.0-wmf.3] Backport several WikimediaMessages patches

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

gerritbot added a project: Patch-For-Review.May 5 2024, 3:49 PM

Urbanecm_WMF moved this task from Blocked / Needs Work to Doing on the Growth-Team (Sprint 13 (Growth Team)) board.May 5 2024, 4:08 PM

Change #1027571 merged by jenkins-bot:

[mediawiki/extensions/WikimediaMessages@wmf/1.43.0-wmf.3] Backport several WikimediaMessages patches

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

Mentioned in SAL (#wikimedia-operations) [2024-05-06T12:25:42Z] <urbanecm@deploy1002> Started scap: Backport for [[gerrit:1027237|iglwiki: Enable GrowthExperiments (T364130)]], [[gerrit:1027571|Backport several WikimediaMessages patches (T217451 T362538 T364213 T315774 T364269)]]

Stashbot mentioned this in T364213: Remove feedback link on Special:Investigate.May 6 2024, 12:25 PM

Stashbot mentioned this in T362538: Disable or redirect the feedback link for the IP Info infobox.

Maintenance_bot removed a project: Patch-For-Review.May 6 2024, 12:31 PM

Mentioned in SAL (#wikimedia-operations) [2024-05-06T12:45:38Z] <urbanecm@deploy1002> urbanecm: Backport for [[gerrit:1027237|iglwiki: Enable GrowthExperiments (T364130)]], [[gerrit:1027571|Backport several WikimediaMessages patches (T217451 T362538 T364213 T315774 T364269)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)

Mentioned in SAL (#wikimedia-operations) [2024-05-06T12:51:54Z] <urbanecm@deploy1002> Started scap: Backport for [[gerrit:1027237|iglwiki: Enable GrowthExperiments (T364130)]], [[gerrit:1027571|Backport several WikimediaMessages patches (T217451 T362538 T364213 T315774 T364269)]]

Mentioned in SAL (#wikimedia-operations) [2024-05-06T12:56:55Z] <urbanecm@deploy1002> urbanecm: Backport for [[gerrit:1027237|iglwiki: Enable GrowthExperiments (T364130)]], [[gerrit:1027571|Backport several WikimediaMessages patches (T217451 T362538 T364213 T315774 T364269)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)

Mentioned in SAL (#wikimedia-operations) [2024-05-06T13:15:55Z] <urbanecm@deploy1002> Finished scap: Backport for [[gerrit:1027237|iglwiki: Enable GrowthExperiments (T364130)]], [[gerrit:1027571|Backport several WikimediaMessages patches (T217451 T362538 T364213 T315774 T364269)]] (duration: 24m 01s)

Mentioned in SAL (#wikimedia-operations) [2024-05-06T13:35:56Z] <urbanecm> Run mwscript userOptions.php --wiki=testwiki --delete for "rcenhancedfilters-seen-tour", "wlenhancedfilters-seen-tour", "rcenhancedfilters-tried-highlight", "rcenhancedfilters-seen-highlight-button-counter" (T364269)

Okay...userOptions.php has a bug in itself:

[urbanecm@mwmaint1002 ~]$ mwscript userOptions.php --wiki=loginwiki --delete rcenhancedfilters-seen-tour
[...] # very long SQL query with a lot of user IDs
Function: UserOptionsMaintenance::deleteOptions
Error: 2006 MySQL server has gone away

#0 /srv/mediawiki/php-1.43.0-wmf.3/includes/libs/rdbms/database/Database.php(1187): Wikimedia\Rdbms\Database->getQueryException('MySQL server ha...', 2006, 'DELETE FROM `us...', 'UserOptionsMain...')                                             
#1 /srv/mediawiki/php-1.43.0-wmf.3/includes/libs/rdbms/database/Database.php(1161): Wikimedia\Rdbms\Database->getQueryExceptionAndLog('MySQL server ha...', 2006, 'DELETE FROM `us...', 'UserOptionsMain...')                                       
#2 /srv/mediawiki/php-1.43.0-wmf.3/includes/libs/rdbms/database/Database.php(652): Wikimedia\Rdbms\Database->reportQueryError('MySQL server ha...', 2006, 'DELETE FROM `us...', 'UserOptionsMain...', false)                                        
#3 /srv/mediawiki/php-1.43.0-wmf.3/includes/libs/rdbms/database/Database.php(1735): Wikimedia\Rdbms\Database->query(Object(Wikimedia\Rdbms\Query), 'UserOptionsMain...')                                                                            
#4 /srv/mediawiki/php-1.43.0-wmf.3/includes/libs/rdbms/database/DBConnRef.php(126): Wikimedia\Rdbms\Database->delete('user_properties', Array, 'UserOptionsMain...')                                                                                
#5 /srv/mediawiki/php-1.43.0-wmf.3/includes/libs/rdbms/database/DBConnRef.php(583): Wikimedia\Rdbms\DBConnRef->__call('delete', Array)                                                                                                              
#6 /srv/mediawiki/php-1.43.0-wmf.3/includes/libs/rdbms/querybuilder/DeleteQueryBuilder.php(229): Wikimedia\Rdbms\DBConnRef->delete('user_properties', Array, 'UserOptionsMain...')                                                                  
#7 /srv/mediawiki/php-1.43.0-wmf.3/maintenance/userOptions.php(289): Wikimedia\Rdbms\DeleteQueryBuilder->execute()
#8 /srv/mediawiki/php-1.43.0-wmf.3/maintenance/userOptions.php(80): UserOptionsMaintenance->deleteOptions()
#9 /srv/mediawiki/php-1.43.0-wmf.3/maintenance/includes/MaintenanceRunner.php(698): UserOptionsMaintenance->execute()
#10 /srv/mediawiki/php-1.43.0-wmf.3/maintenance/run.php(51): MediaWiki\Maintenance\MaintenanceRunner->run()
#11 /srv/mediawiki/multiversion/MWScript.php(158): require_once('/srv/mediawiki/...')
#12 {main}
[urbanecm@mwmaint1002 ~]$

Filled as T364311: userOptions.php fails to delete very large number of DB rows.

Urbanecm_WMF added a subtask: T364311: userOptions.php fails to delete very large number of DB rows.May 6 2024, 1:52 PM

Mentioned in SAL (#wikimedia-operations) [2024-05-06T15:09:39Z] <urbanecm> mwmaint1002: mwscript userOptions.php --wiki=loginwiki --delete rcenhancedfilters-seen-tour # T364269

mysql:research@dbstore1007.eqiad.wmnet [loginwiki]> select count(*) from user_properties where up_property in ("rcenhancedfilters-seen-tour", "wlenhancedfilters-seen-tour", "rcenhancedfilters-tried-highlight", "rcenhancedfilters-seen-highlight-button-counter");
+----------+
| count(*) |
+----------+
| 41997588 |
+----------+
1 row in set (44.737 sec)

mysql:research@dbstore1007.eqiad.wmnet [loginwiki]> select count(*) from user_properties;
+----------+
| count(*) |
+----------+
| 59344056 |
+----------+
1 row in set (8.587 sec)

mysql:research@dbstore1007.eqiad.wmnet [loginwiki]>

this should save quite a lot of DB space.

Mentioned in SAL (#wikimedia-operations) [2024-05-06T15:16:01Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ mwscript userOptions.php --wiki=enwiki --delete rcenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T15:16:12Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s2 userOptions.php --delete rcenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T15:17:26Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s{3-8} userOptions.php --delete rcenhancedfilters-seen-tour # T364269

Urbanecm_WMF added a parent task: T54777: user_properties table bloat.May 6 2024, 3:54 PM

[urbanecm@mwmaint1002 ~]$ mwscript userOptions.php --wiki=loginwiki --delete rcenhancedfilters-seen-tour
The script is about to delete 'rcenhancedfilters-seen-tour' option for ALL USERS from user_properties table.
This action is IRREVERSIBLE.

Abort with control-c in the next five seconds....0
Done! Deleted 20935134 rows.
[urbanecm@mwmaint1002 ~]$

First property gone on loginwiki

Mentioned in SAL (#wikimedia-operations) [2024-05-06T18:18:28Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ mwscript userOptions.php --wiki=loginwiki --delete wlenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T18:20:12Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s2 userOptions.php --delete wlenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T18:20:34Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s4 userOptions.php --delete wlenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T18:20:45Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s5 userOptions.php --delete wlenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T18:20:53Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s6 userOptions.php --delete wlenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T19:15:58Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s3 userOptions.php --delete wlenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T19:17:07Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s5 userOptions.php --delete rcenhancedfilters-tried-highlight # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T19:17:38Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s6 userOptions.php --delete rcenhancedfilters-tried-highlight # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T19:21:03Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s6 userOptions.php --delete rcenhancedfilters-seen-highlight-button-counter # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T19:23:30Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ mwscript userOptions.php --wiki=enwiki --delete wlenhancedfilters-seen-tour # T364269

Mentioned in SAL (#wikimedia-operations) [2024-05-06T19:25:10Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s5 userOptions.php --delete rcenhancedfilters-seen-highlight-button-counter # T364269

Tagging DBA for awareness. I'm dropping millions of rows from user_properties across all wikis. Quite a substantial number of rows is being dropped – on certain wikis (such as loginwiki), this accounts for as many as 90% of the rows. @Ladsgroup was already informed during the Hackathon.

FYI @jcrespo, in case this ends up firing some backup-related alerts.

Mentioned in SAL (#wikimedia-operations) [2024-05-06T19:46:26Z] <urbanecm> [urbanecm@mwmaint1002 ~]$ foreachwikiindblist s7 userOptions.php --delete wlenhancedfilters-seen-tour # T364269

Probably worth optimizing this table once you are done. Can you let us know when all finished?

Thanks!

Marostegui edited projects, added Data-Persistence, Data-Persistence-Backup; removed DBA.May 6 2024, 7:57 PM

Urbanecm_WMF added a subscriber: • jcrespo.May 6 2024, 8:01 PM

matmarex subscribed.May 6 2024, 8:25 PM

Krinkle awarded a token.May 6 2024, 8:50 PM

Krinkle added a project: Wikimedia-Performance-publish.

Krinkle moved this task from Untriaged to Misc bookmarks on the Wikimedia-Performance-publish board.

Krinkle subscribed.

-----------------------------------------------------------------                                                                                                                                                                                    
metawiki                                                                                                                                                                                                                                             
-----------------------------------------------------------------                                                                                                                                                                                    
metawiki:  The script is about to delete 'rcenhancedfilters-seen-tour' option for ALL USERS from user_properties table.                                                                                                                              
metawiki:  This action is IRREVERSIBLE.                                                                                                                                                                                                              
metawiki:                                                                                                                                                                                                                                            
metawiki:  Abort with control-c in the next five seconds....0                                                                                                                                                                                        
metawiki:  Done! Deleted 20694833 rows.

Meta is going to have an impressive drop (to ~65% of its size) as well. Not as impressive as loginwiki, but still a big difference.

Dreamy_Jazz awarded a token.May 7 2024, 5:14 AM

Ladsgroup awarded a token.May 7 2024, 9:30 AM

In T364269#9775691, @Marostegui wrote:

Probably worth optimizing this table once you are done. Can you let us know when all finished?

Absolutely, I finished dropping the rows today. Please feel free to ping me if there is anything else that's needed from me here.

For the Growth team: Moving to Blocked for the lack of a better column; this is essentially handed over to the DBAs for the moment. Can likely be moved off-sprint if so desired.

Stupid q: Did you drop it in loginwiki and metawiki or all wikis? That changes where we need to optimize the tables

In T364269#9786259, @Ladsgroup wrote:

Stupid q: Did you drop it in loginwiki and metawiki or all wikis? That changes where we need to optimize the tables

I dropped it on all wikis, but the impact was lower on most wikis (on enwiki, it made a 10% difference, for example).

DannyS712 awarded a token.May 10 2024, 4:43 PM

Urbanecm_WMF added a subtask: T217451: Remove RCFilters Guided Tours.May 11 2024, 10:59 AM

Urbanecm_WMF edited projects, added Growth-Team; removed Growth-Team (Sprint 13 (Growth Team)).May 13 2024, 8:34 AM

Urbanecm_WMF moved this task from Inbox to Triaged on the Growth-Team board.

Resolving as part of cleaning up tasks assigned to me. The rows were dropped, DBAs pinged as requested. Not sure if the optimisation @Marostegui mentioned happened, but if not (and we need to track that), feel free to reopen this task (reassigning to someone else) or fill a new one, as appropriate.

Drop user properties related to RC toursClosed, ResolvedPublicActions