Page MenuHomePhabricator

Clean up search user preferences in user_properties table on Wikimedia wikis
Closed, ResolvedPublic

Description

enwiki alone has a bunch of spurious keys set collectively over 10m times – not just the infamous searchNs-1, but also searchNs4294967295, searchNsNS_LQT_SUMMARY and more:

1mysql:research@analytics-store.eqiad.wmnet [enwiki]> select up_property, up_value, count(*) from user_properties where up_property like 'searchNs%' group by up_property, up_value;
2+-----------------------------+----------+----------+
3| up_property | up_value | count(*) |
4+-----------------------------+----------+----------+
5| searchNs-0 | 1 | 432 |
6| searchNs-1 | 0 | 9632094 |
7| searchNs-1 | 1 | 4 |
8| searchNs-10 | 1 | 4 |
9| searchNs-100 | 1 | 3 |
10| searchNs-101 | 1 | 2 |
11| searchNs-108 | 1 | 3 |
12| searchNs-109 | 1 | 3 |
13| searchNs-11 | 1 | 5 |
14| searchNs-12 | 1 | 5 |
15| searchNs-13 | 1 | 4 |
16| searchNs-14 | 1 | 5 |
17| searchNs-15 | 1 | 4 |
18| searchNs-2 | 1 | 4 |
19| searchNs-3 | 1 | 3 |
20| searchNs-4 | 1 | 4 |
21| searchNs-446 | 1 | 3 |
22| searchNs-447 | 1 | 3 |
23| searchNs-5 | 1 | 4 |
24| searchNs-6 | 1 | 3 |
25| searchNs-7 | 1 | 3 |
26| searchNs-710 | 1 | 3 |
27| searchNs-711 | 1 | 3 |
28| searchNs-8 | 1 | 4 |
29| searchNs-828 | 1 | 3 |
30| searchNs-829 | 1 | 3 |
31| searchNs-9 | 1 | 5 |
32| searchNs0 | | 12886 |
33| searchNs0 | 0 | 4575 |
34| searchNs1 | 1 | 40393 |
35| searchNs10 | 1 | 33830 |
36| searchNs100 | 1 | 35731 |
37| searchNs101 | 1 | 26020 |
38| searchNs106 | 0 | 1755 |
39| searchNs106 | 1 | 25 |
40| searchNs107 | 0 | 1767 |
41| searchNs107 | 1 | 13 |
42| searchNs108 | 1 | 13372 |
43| searchNs109 | 1 | 9459 |
44| searchNs11 | 1 | 237016 |
45| searchNs118 | 1 | 1254 |
46| searchNs119 | 1 | 1141 |
47| searchNs12 | 1 | 108382 |
48| searchNs13 | 1 | 26880 |
49| searchNs14 | 1 | 42326 |
50| searchNs15 | 1 | 28511 |
51| searchNs2 | 1 | 43034 |
52| searchNs2300 | 1 | 77 |
53| searchNs2301 | 1 | 75 |
54| searchNs2302 | 1 | 78 |
55| searchNs2303 | 1 | 76 |
56| searchNs2600 | 1 | 597 |
57| searchNs3 | 1 | 35595 |
58| searchNs4 | 1 | 70200 |
59| searchNs4294967295 | 0 | 247058 |
60| searchNs442 | 1 | 21 |
61| searchNs443 | 1 | 17 |
62| searchNs444 | 1 | 20 |
63| searchNs445 | 1 | 16 |
64| searchNs446 | 1 | 3668 |
65| searchNs447 | 1 | 3012 |
66| searchNs5 | 1 | 37722 |
67| searchNs6 | 1 | 49632 |
68| searchNs7 | 1 | 30864 |
69| searchNs710 | 1 | 2518 |
70| searchNs711 | 1 | 2354 |
71| searchNs8 | 1 | 40388 |
72| searchNs828 | 1 | 2278 |
73| searchNs829 | 1 | 2055 |
74| searchNs866 | 1 | 8 |
75| searchNs867 | 1 | 5 |
76| searchNs9 | 1 | 299449 |
77| searchNs90 | 1 | 12 |
78| searchNs91 | 1 | 11 |
79| searchNs92 | 1 | 13 |
80| searchNs93 | 1 | 10 |
81| searchNsNS_LQT_SUMMARY | 1 | 21 |
82| searchNsNS_LQT_SUMMARY_TALK | 1 | 21 |
83| searchNsNS_LQT_THREAD | 1 | 21 |
84| searchNsNS_LQT_THREAD_TALK | 1 | 21 |
85+-----------------------------+----------+----------+
8680 rows in set (5 min 25.06 sec)

Event Timeline

Jdforrester-WMF raised the priority of this task from to Normal.
Jdforrester-WMF updated the task description. (Show Details)
Krenair claimed this task.Sep 29 2015, 3:04 PM

foreachwiki runBatchedQuery.php "delete from user_properties where up_property = 'searchNs-1' limit 500;" has been running for a while now to get rid of those searchNs-1 entries, enwiki completed earlier and it's currently on fiwiki.

searchNs-1 is gone from all wikis.

searchNsNS_LQT_%:

arwiki:  Batch 1: 8 rows
commonswiki:  Batch 1: 4 rows
cswiki:  Batch 1: 8 rows
dawiki:  Batch 1: 4 rows
dewiki:  Batch 1: 12 rows
elwiki:  Batch 1: 4 rows
enwiki:  Batch 1: 84 rows
enwikinews:  Batch 1: 4 rows
eswiki:  Batch 1: 4 rows
frwiki:  Batch 1: 4 rows
hewiki:  Batch 1: 4 rows
huwiki:  Batch 1: 4 rows
itwiki:  Batch 1: 4 rows
jawiki:  Batch 1: 16 rows
metawiki:  Batch 1: 4 rows
nowiki:  Batch 1: 4 rows
ptwiki:  Batch 1: 12 rows
rowiki:  Batch 1: 4 rows
ruwiki:  Batch 1: 24 rows
ruwiktionary:  Batch 1: 4 rows
skwiki:  Batch 1: 4 rows
svwiki:  Batch 1: 8 rows
ukwiki:  Batch 1: 4 rows
viwiki:  Batch 1: 8 rows
viwikiquote:  Batch 1: 4 rows
zhwiki:  Batch 1: 16 rows

Negative entries:

azwikiquote:  Batch 1: 1 rows
betawikiversity:  Batch 1: 1 rows
commonswiki:  Batch 1: 347 rows
dewikisource:  Batch 1: 3 rows
dewikiversity:  Batch 1: 6 rows
dewikivoyage:  Batch 1: 24 rows
elwikivoyage:  Batch 1: 1 rows
enwiki:  Batch 1: 500 rows
enwiki:  Batch 2: 18 rows
enwikibooks:  Batch 1: 18 rows
enwikinews:  Batch 1: 2 rows
enwikiquote:  Batch 1: 1 rows
enwikisource:  Batch 1: 3 rows
enwikiversity:  Batch 1: 3 rows
enwikivoyage:  Batch 1: 2 rows
enwiktionary:  Batch 1: 5 rows
eswikibooks:  Batch 1: 2 rows
eswikinews:  Batch 1: 2 rows
eswikiquote:  Batch 1: 1 rows
eswikisource:  Batch 1: 2 rows
eswikiversity:  Batch 1: 1 rows
fjwiktionary:  Batch 1: 1 rows
frwiki:  Batch 1: 1 rows
frwikinews:  Batch 1: 1 rows
frwikiquote:  Batch 1: 1 rows
frwiktionary:  Batch 1: 2 rows
hewikisource:  Batch 1: 3 rows
hewikivoyage:  Batch 1: 1 rows
idwikibooks:  Batch 1: 3 rows
idwikiquote:  Batch 1: 1 rows
incubatorwiki:  Batch 1: 3 rows
itwiki:  Batch 1: 1 rows
kowiktionary:  Batch 1: 1 rows
labswiki:  Batch 1: 189 rows
mediawikiwiki:  Batch 1: 48 rows
metawiki:  Batch 1: 40 rows
miwiktionary:  Batch 1: 1 rows
mkwiktionary:  Batch 1: 1 rows
mywiktionary:  Batch 1: 1 rows
nawiktionary:  Batch 1: 1 rows
ndswiktionary:  Batch 1: 1 rows
nlwikiquote:  Batch 1: 1 rows
omwiktionary:  Batch 1: 1 rows
orwiktionary:  Batch 1: 1 rows
pawiktionary:  Batch 1: 1 rows
plwiktionary:  Batch 1: 3 rows
ptwikibooks:  Batch 1: 1 rows
ptwikisource:  Batch 1: 3 rows
ptwikiversity:  Batch 1: 2 rows
ruwikibooks:  Batch 1: 1 rows
ruwikiquote:  Batch 1: 1 rows
ruwikisource:  Batch 1: 4 rows
ruwikiversity:  Batch 1: 2 rows
ruwikivoyage:  Batch 1: 3 rows
ruwiktionary:  Batch 1: 3 rows
specieswiki:  Batch 1: 3 rows
suwikibooks:  Batch 1: 1 rows
svwikiversity:  Batch 1: 1 rows
svwikivoyage:  Batch 1: 3 rows
swwikibooks:  Batch 1: 1 rows
tawikibooks:  Batch 1: 1 rows
tewikibooks:  Batch 1: 1 rows
tgwikibooks:  Batch 1: 1 rows
thwikibooks:  Batch 1: 1 rows
tkwikibooks:  Batch 1: 1 rows
tlwikibooks:  Batch 1: 2 rows
ttwikibooks:  Batch 1: 1 rows
ukwikibooks:  Batch 1: 1 rows
ukwikinews:  Batch 1: 1 rows
ukwikisource:  Batch 1: 1 rows
ukwikivoyage:  Batch 1: 3 rows
urwikibooks:  Batch 1: 1 rows
uzwikibooks:  Batch 1: 1 rows
vecwiktionary:  Batch 1: 1 rows
viwikibooks:  Batch 1: 4 rows
wikidatawiki:  Batch 1: 29 rows
zhwikibooks:  Batch 1: 4 rows
zhwikinews:  Batch 1: 1 rows
zhwikiquote:  Batch 1: 1 rows
zhwiktionary:  Batch 1: 1 rows
Krenair closed this task as Resolved.Sep 29 2015, 4:15 PM

Also did searchNs4294967295 (2^32-1)