We should be able to get stats on how many users disable each default gadget, so that we can reconsider which ones should actually be turned off by default.
See also
Description
Description
Details
Details
Related Changes in Gerrit:
Related Objects
Related Objects
Event Timeline
Comment Actions
Change 591179 had a related patch set uploaded (by Ammarpad; owner: Ammarpad):
[mediawiki/extensions/Gadgets@master] Add support for opt-out statistics of default gadgets
Comment Actions
@DannyS712 I was going to tag you on this ticket if you're interested in helping review @Ammarpad's code but then I saw your comment on the patch. Thanks for all your help. Much appreciated.
Comment Actions
Change 591179 abandoned by Ammarpad:
[mediawiki/extensions/Gadgets@master] Add support for limited opt-out statistics of default gadgets
Reason:
Comment Actions
Change #1109990 had a related patch set uploaded (by SD0001; author: SD0001):
[mediawiki/extensions/Gadgets@master] GadgetUsage: Show opt-out statistics for default gadgets
Comment Actions
I think this is an useful feature as it helps communities assess what kind of gadgets would be good as defaults.
Wondering if DBA can help with testing this patch. As the tables are redacted from the wiki replicas, I am unable to verify the runtime of the updated queries. It's also hard to test locally as it requires simulating a large number of active users.
I guess what we need to check is if the new queries run fine on large wikis for both values of $wgSpecialGadgetUsageActiveUsers. Raw SQL as per debug logs:
- enwiki ($wgSpecialGadgetUsageActiveUsers is set false):
SELECT CONCAT('{','name=',up_property,',','optin=',COUNT(up_value NOT IN ('0','')),',','optout=',COUNT(up_value IN ('0','')),'}') AS `title`,COUNT(up_value NOT IN ('0','')) AS `value`,0 AS `namespace` FROM `user_properties` WHERE (up_property LIKE 'gadget-%' ESCAPE '`') GROUP BY up_property ORDER BY value DESC LIMIT 1001
- commonswiki or wikidata ($wgSpecialGadgetUsageActiveUsers is set true):
SELECT CONCAT('{','name=',up_property,',','optin=',COUNT(up_value NOT IN ('0','')),',','optout=',COUNT(up_value IN ('0','')),'}') AS `title`,COUNT(up_value NOT IN ('0','')) AS `value`,COUNT(up_value NOT IN ('0','') AND qcc_title) AS `namespace` FROM `user_properties` LEFT JOIN `user` ON ((up_user = user_id)) LEFT JOIN `querycachetwo` ON ((user_name = qcc_title) AND qcc_type = 'activeusers') WHERE (up_property LIKE 'gadget-%' ESCAPE '`') GROUP BY up_property ORDER BY value DESC LIMIT 1001
Comment Actions
These queries aren't particularely good:
cumin2024@db1163.eqiad.wmnet[enwiki]> explain SELECT CONCAT('{','name=',up_property,',','optin=',COUNT(up_value NOT IN ('0','')),',','optout=',COUNT(up_value IN ('0','')),'}') AS `title`,COUNT(up_value NOT IN ('0','')) AS `value`,0 AS `namespace` FROM `user_properties` WHERE (up_property LIKE 'gadget-%' ESCAPE '`') GROUP BY up_property ORDER BY value DESC LIMIT 1001;
+------+-------------+-----------------+-------+---------------+-------------+---------+------+----------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------------+-------+---------------+-------------+---------+------+----------+--------------------------------------------------------+
| 1 | SIMPLE | user_properties | range | up_property | up_property | 257 | NULL | 15994926 | Using index condition; Using temporary; Using filesort |
+------+-------------+-----------------+-------+---------------+-------------+---------+------+----------+--------------------------------------------------------+
1 row in set (0.008 sec)On commons:
cumin2024@db1190.eqiad.wmnet[commonswiki]> explain SELECT CONCAT('{','name=',up_property,',','optin=',COUNT(up_value NOT IN ('0','')),',','optout=',COUNT(up_value IN ('0','')),'}') AS `title`,COUNT(up_value NOT IN ('0','')) AS `value`,COUNT(up_value NOT IN ('0','') AND qcc_title) AS `namespace` FROM `user_properties` LEFT JOIN `user` ON ((up_user = user_id)) LEFT JOIN `querycachetwo` ON ((user_name = qcc_title) AND qcc_type = 'activeusers') WHERE (up_property LIKE 'gadget-%' ESCAPE '`') GROUP BY up_property ORDER BY value DESC LIMIT 1001;
+------+-------------+-----------------+--------+---------------------------------+-------------+---------+-------------------------------------+---------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------------+--------+---------------------------------+-------------+---------+-------------------------------------+---------+--------------------------------------------------------+
| 1 | SIMPLE | user_properties | range | up_property | up_property | 257 | NULL | 4564284 | Using index condition; Using temporary; Using filesort |
| 1 | SIMPLE | user | eq_ref | PRIMARY | PRIMARY | 4 | commonswiki.user_properties.up_user | 1 | |
| 1 | SIMPLE | querycachetwo | ref | qcc_type,qcc_title,qcc_titletwo | qcc_title | 34 | const | 15433 | Using where; Using index |
+------+-------------+-----------------+--------+---------------------------------+-------------+---------+-------------------------------------+---------+--------------------------------------------------------+
3 rows in set (0.006 sec)They are both pretty much doing a full table scan so they'd take quite lots of minutes to finish and they'd be killed by the query killer before they finish.
Comment Actions
@Marostegui Thanks for checking. These are for execution in a QueryPage, so will only run once a week or so. Does the query killer affect such queries? IIRC, there are some QueryPages which take a full day to run.
Comment Actions
Strictly speaking, no since nothing is running on mwmaint anymore. All have been migrated to k8s and every new job sets up a new container and run it (I think the k8s namespace is mw-cron). But in terms of in the spirit of what you're asking. Yes. It's part of updateSpecialPages script that gets run weekly via wikiadmin db user which won't get killed since our query killer only works on wikiuser db user.
Comment Actions
Could we get a summary here on what needs to happen to resolve this? Is it just the slow database query that is the problem, or are there other problems as well? Can that problem be solved by just running it slower over a greater period of time? In case it helps: we (interface administrators) don't need these stats to be updated every day or every week. It would be fine if it was every month. Even every 6 months would be better than nothing. Does that alleviate the issue in any way?