[Wikidata-bugs] [Maniphest] T292548: Drop index on change_revision_id in wb_changes table

[Michael]

Michael edited projects, added Wikidata Change Dispatching & Watchlists, 
wmde-wikidata-tech; removed [DEPRECATED] wdwb-tech.

TASK DETAIL
  https://phabricator.wikimedia.org/T292548

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Michael
Cc: karapayneWMDE, Marostegui, Lucas_Werkmeister_WMDE, Aklapper, Michael, 
Danny_Benjafield_WMDE, Astuthiodit_1, Invadibot, maantietaja, ItamarWMDE, 
Akuckartz, Nandana, Lahi, Gq86, GoranSMilovanovic, QZanden, KimKelting, 
LawExplorer, _jensen, rosalieper, Scott_WUaS, Wikidata-bugs, aude, 
Lydia_Pintscher, Mbch331, Ullasoff
___
Wikidata-bugs mailing list -- wikidata-bugs@lists.wikimedia.org
To unsubscribe send an email to wikidata-bugs-le...@lists.wikimedia.org

[Wikidata-bugs] [Maniphest] T292548: Drop index on change_revision_id in wb_changes table

[karapayneWMDE]

karapayneWMDE added a comment.


  Change Dispatch Review 2021-10-27
  -confirm that the table is not being used, determine if a schema change is 
needed, then drop the table (maybe)
  -work falls into the standard process, so no longer needs to be included in 
the hike scope

TASK DETAIL
  https://phabricator.wikimedia.org/T292548

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: karapayneWMDE
Cc: karapayneWMDE, Marostegui, Lucas_Werkmeister_WMDE, Aklapper, Michael, 
Invadibot, maantietaja, Akuckartz, Nandana, Lahi, Gq86, GoranSMilovanovic, 
QZanden, LawExplorer, _jensen, rosalieper, Scott_WUaS, Wikidata-bugs, aude, 
Lydia_Pintscher, Addshore, Mbch331
___
Wikidata-bugs mailing list -- wikidata-bugs@lists.wikimedia.org
To unsubscribe send an email to wikidata-bugs-le...@lists.wikimedia.org

[Wikidata-bugs] [Maniphest] T292548: Drop index on change_revision_id in wb_changes table

[Marostegui]

Marostegui added a comment.


  We normally don't track the restarts, but db1171 was restarted 21 days ago, 
db1104 is way older (around 80 days) and db1126 two days ago, so that's the one 
we can double check whether the query arrives or not in a few weeks.
  db1116 is a backup host so we shouldn't really use it for this analysis.

TASK DETAIL
  https://phabricator.wikimedia.org/T292548

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Marostegui
Cc: Marostegui, Lucas_Werkmeister_WMDE, Aklapper, Michael, karapayneWMDE, 
Invadibot, maantietaja, Akuckartz, Nandana, Lahi, Gq86, GoranSMilovanovic, 
QZanden, LawExplorer, _jensen, rosalieper, Scott_WUaS, Wikidata-bugs, aude, 
Lydia_Pintscher, Addshore, Mbch331
___
Wikidata-bugs mailing list -- wikidata-bugs@lists.wikimedia.org
To unsubscribe send an email to wikidata-bugs-le...@lists.wikimedia.org

[Wikidata-bugs] [Maniphest] T292548: Drop index on change_revision_id in wb_changes table

[Lucas_Werkmeister_WMDE]

Lucas_Werkmeister_WMDE added a comment.


  > On those yes, but if you check that, it is indeed used on _some_ of them. 
So there is (or was) something hitting that index at the time.
  
  Yes, but I thought we were mostly interested in those hosts where it was 
still being used (db1104, db1126, db1114), to see if it was still reported as 
used after a restart there.
  
  > I just checked again and it is not being reported as unused on 
db1172.eqiad.wmnet (and that host has an uptime of 21 days, so it is pretty 
recent)
  
  Hm, IIUC we only stopped the old change dispatching system on October 5 
, so if db1172 was 
restarted 21 days ago (October 1st 
) it could still have seen 
some old queries, I think. (Though it’s strange that it //did// report the 
index as unused when you checked on September 20…)

TASK DETAIL
  https://phabricator.wikimedia.org/T292548

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Lucas_Werkmeister_WMDE
Cc: Marostegui, Lucas_Werkmeister_WMDE, Aklapper, Michael, karapayneWMDE, 
Invadibot, maantietaja, Akuckartz, Nandana, Lahi, Gq86, GoranSMilovanovic, 
QZanden, LawExplorer, _jensen, rosalieper, Scott_WUaS, Wikidata-bugs, aude, 
Lydia_Pintscher, Addshore, Mbch331
___
Wikidata-bugs mailing list -- wikidata-bugs@lists.wikimedia.org
To unsubscribe send an email to wikidata-bugs-le...@lists.wikimedia.org

[Wikidata-bugs] [Maniphest] T292548: Drop index on change_revision_id in wb_changes table

[Marostegui]

Marostegui added a comment.


  In T292548#7450949 , 
@Lucas_Werkmeister_WMDE wrote:
  
  > Hm, but if I understand the comment at T291086#7364424 
 correctly, the index was 
already unused on those hosts?
  
  On those yes, but if you check that, it is indeed used on _some_ of them. So 
there is (or was) something hitting that index at the time. 
  I just checked again and it is not being reported as unused on 
db1172.eqiad.wmnet (and that host has an uptime of 21 days, so it is pretty 
recent)

TASK DETAIL
  https://phabricator.wikimedia.org/T292548

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Marostegui
Cc: Marostegui, Lucas_Werkmeister_WMDE, Aklapper, Michael, karapayneWMDE, 
Invadibot, maantietaja, Akuckartz, Nandana, Lahi, Gq86, GoranSMilovanovic, 
QZanden, LawExplorer, _jensen, rosalieper, Scott_WUaS, Wikidata-bugs, aude, 
Lydia_Pintscher, Addshore, Mbch331
___
Wikidata-bugs mailing list -- wikidata-bugs@lists.wikimedia.org
To unsubscribe send an email to wikidata-bugs-le...@lists.wikimedia.org

[Wikidata-bugs] [Maniphest] T292548: Drop index on change_revision_id in wb_changes table

[Lucas_Werkmeister_WMDE]

Lucas_Werkmeister_WMDE added a comment.


  Hm, but if I understand the comment at T291086#7364424 
 correctly, the index was 
already unused on those hosts?

TASK DETAIL
  https://phabricator.wikimedia.org/T292548

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Lucas_Werkmeister_WMDE
Cc: Marostegui, Lucas_Werkmeister_WMDE, Aklapper, Michael, karapayneWMDE, 
Invadibot, maantietaja, Akuckartz, Nandana, Lahi, Gq86, GoranSMilovanovic, 
QZanden, LawExplorer, _jensen, rosalieper, Scott_WUaS, Wikidata-bugs, aude, 
Lydia_Pintscher, Addshore, Mbch331
___
Wikidata-bugs mailing list -- wikidata-bugs@lists.wikimedia.org
To unsubscribe send an email to wikidata-bugs-le...@lists.wikimedia.org

[Wikidata-bugs] [Maniphest] T292548: Drop index on change_revision_id in wb_changes table

[Marostegui]

Marostegui added a comment.


  I am restarting quite a bunch of s8 (wikidata) hosts as I am upgrading them. 
  Restarted db1101:3318, db1178 today so we can check by the end of the week if 
you want. (please ping me to do so)

TASK DETAIL
  https://phabricator.wikimedia.org/T292548

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Marostegui
Cc: Marostegui, Lucas_Werkmeister_WMDE, Aklapper, Michael, karapayneWMDE, 
Invadibot, maantietaja, Akuckartz, Nandana, Lahi, Gq86, GoranSMilovanovic, 
QZanden, LawExplorer, _jensen, rosalieper, Scott_WUaS, Wikidata-bugs, aude, 
Lydia_Pintscher, Addshore, Mbch331
___
Wikidata-bugs mailing list -- wikidata-bugs@lists.wikimedia.org
To unsubscribe send an email to wikidata-bugs-le...@lists.wikimedia.org

[Wikidata-bugs] [Maniphest] T292548: Drop index on change_revision_id in wb_changes table

[Lucas_Werkmeister_WMDE]

Lucas_Werkmeister_WMDE added a comment.


  Alright, thanks. Should we wait until the databases have been restarted 
naturally (next DC switch?) and then check again if the index is still used? 
(The index would’ve been used regularly prior to T290367#7347326 
, if I’m not mistaken.)

TASK DETAIL
  https://phabricator.wikimedia.org/T292548

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Lucas_Werkmeister_WMDE
Cc: Marostegui, Lucas_Werkmeister_WMDE, Aklapper, Michael, karapayneWMDE, 
Invadibot, maantietaja, Akuckartz, Nandana, Lahi, Gq86, GoranSMilovanovic, 
QZanden, LawExplorer, _jensen, rosalieper, Scott_WUaS, Wikidata-bugs, aude, 
Lydia_Pintscher, Addshore, Mbch331
___
Wikidata-bugs mailing list -- wikidata-bugs@lists.wikimedia.org
To unsubscribe send an email to wikidata-bugs-le...@lists.wikimedia.org

[Wikidata-bugs] [Maniphest] T292548: Drop index on change_revision_id in wb_changes table

[Marostegui]

Marostegui added a comment.


  It gets cleaned up after each restart.

TASK DETAIL
  https://phabricator.wikimedia.org/T292548

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Marostegui
Cc: Marostegui, Lucas_Werkmeister_WMDE, Aklapper, Michael, karapayneWMDE, 
Invadibot, maantietaja, Akuckartz, Nandana, Lahi, Gq86, GoranSMilovanovic, 
QZanden, LawExplorer, _jensen, rosalieper, Scott_WUaS, Wikidata-bugs, aude, 
Lydia_Pintscher, Addshore, Mbch331
___
Wikidata-bugs mailing list -- wikidata-bugs@lists.wikimedia.org
To unsubscribe send an email to wikidata-bugs-le...@lists.wikimedia.org

[Wikidata-bugs] [Maniphest] T292548: Drop index on change_revision_id in wb_changes table

[Lucas_Werkmeister_WMDE]

Lucas_Werkmeister_WMDE added a subscriber: Marostegui.
Lucas_Werkmeister_WMDE added a comment.


  Hm, strange indeed. @Marostegui how far back does the `schema_unused_indexes` 
data go? Is it possible the index only appeared to be used due to some old 
queries, or maybe some manual queries we ran?
  
  (I tried to find some related queries in Tendril, but I’ve evidently 
forgotten how Tendril works, because I didn’t get anywhere.)

TASK DETAIL
  https://phabricator.wikimedia.org/T292548

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Lucas_Werkmeister_WMDE
Cc: Marostegui, Lucas_Werkmeister_WMDE, Aklapper, Michael, karapayneWMDE, 
Invadibot, maantietaja, Akuckartz, Nandana, Lahi, Gq86, GoranSMilovanovic, 
QZanden, LawExplorer, _jensen, rosalieper, Scott_WUaS, Wikidata-bugs, aude, 
Lydia_Pintscher, Addshore, Mbch331
___
Wikidata-bugs mailing list -- wikidata-bugs@lists.wikimedia.org
To unsubscribe send an email to wikidata-bugs-le...@lists.wikimedia.org

[Wikidata-bugs] [Maniphest] T292548: Drop index on change_revision_id in wb_changes table

[Michael]

Michael added a comment.


  Mh, but that makes things only more confusing.
  
  There is more background information about this in the comments of T291086 
 and particularly T291086#7364424 
 seems to point out that 
this index is indeed still in use on "master, main traffic and api" hosts.
  
  If that view is not the thing that uses that index, then what is?

TASK DETAIL
  https://phabricator.wikimedia.org/T292548

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Michael
Cc: Lucas_Werkmeister_WMDE, Aklapper, Michael, karapayneWMDE, Invadibot, 
maantietaja, Akuckartz, Nandana, Lahi, Gq86, GoranSMilovanovic, QZanden, 
LawExplorer, _jensen, rosalieper, Scott_WUaS, Wikidata-bugs, aude, 
Lydia_Pintscher, Addshore, Mbch331
___
Wikidata-bugs mailing list -- wikidata-bugs@lists.wikimedia.org
To unsubscribe send an email to wikidata-bugs-le...@lists.wikimedia.org

[Wikidata-bugs] [Maniphest] T292548: Drop index on change_revision_id in wb_changes table

[Lucas_Werkmeister_WMDE]

Lucas_Werkmeister_WMDE added a comment.


  Yeah, I don’t think the `wb_changes_change_revision_id` is used for that view.
  
MariaDB [wikidatawiki]> explain select change_id, change_type, change_time, 
change_object_id, change_revision_id, if(rev_deleted&4 OR 
ar_deleted&4,null,change_user_id) as change_user_id, if(rev_deleted&6 OR 
ar_deleted&6,null,change_info) as change_info from wb_changes left join 
revision on rev_id = change_revision_id left join archive on ar_rev_id = 
change_revision_id limit 10;

+--+-+++---+---+-++--+---+
| id   | select_type | table  | type   | possible_keys | key   
| key_len | ref| rows | Extra |

+--+-+++---+---+-++--+---+
|1 | SIMPLE  | wb_changes | ALL| NULL  | NULL  
| NULL| NULL   | 7|   |
|1 | SIMPLE  | revision   | eq_ref | PRIMARY   | PRIMARY   
| 4   | wikidatawiki.wb_changes.change_revision_id | 1|   |
|1 | SIMPLE  | archive| eq_ref | ar_revid_uniq | ar_revid_uniq 
| 4   | wikidatawiki.wb_changes.change_revision_id | 1|   |

+--+-+++---+---+-++--+---+

TASK DETAIL
  https://phabricator.wikimedia.org/T292548

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Lucas_Werkmeister_WMDE
Cc: Lucas_Werkmeister_WMDE, Aklapper, Michael, karapayneWMDE, Invadibot, 
maantietaja, Akuckartz, Nandana, Lahi, Gq86, GoranSMilovanovic, QZanden, 
LawExplorer, _jensen, rosalieper, Scott_WUaS, Wikidata-bugs, aude, 
Lydia_Pintscher, Addshore, Mbch331
___
Wikidata-bugs mailing list -- wikidata-bugs@lists.wikimedia.org
To unsubscribe send an email to wikidata-bugs-le...@lists.wikimedia.org

[Wikidata-bugs] [Maniphest] T292548: Drop index on change_revision_id in wb_changes table

[Lucas_Werkmeister_WMDE]

Lucas_Werkmeister_WMDE added a comment.


  > See 
https://gerrit.wikimedia.org/g/operations/puppet/+/79971d5304a5643b56547ffb956296103fa800c1/modules/profile/templates/wmcs/db/wikireplicas/maintain-views.yaml#839
  
  If I understand correctly, this controls the `wikidatawiki_p.wb_changes` view 
in the Toolforge / Cloud VPS replicas (e.g. in Quarry). We don’t want to make 
`wb_changes` rows for deleted revisions public, so we join the `revision` and 
`archive` tables, and only make the `change_user_id` and `change_info` 
available if the corresponding revision wasn’t deleted in either of those 
tables. (We leave the entity ID public in that case, presumably because that’s 
also more or less public on Special:Log 
.)
  
  I’m not sure if this requires an index on `change_revision_id` in the 
`wikidatawiki.wb_changes` table in those replicas (the source of the 
`wikidata_p.wb_changes` view) – I feel like the most important thing would be 
that the `revision` and `archive` tables have an index for the join, whereas on 
`wb_changes` it might not be needed? But in any case, I’m pretty sure that we 
don’t need this index in production, because those views don’t directly operate 
on the production tables anyways.

TASK DETAIL
  https://phabricator.wikimedia.org/T292548

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Lucas_Werkmeister_WMDE
Cc: Lucas_Werkmeister_WMDE, Aklapper, Michael, karapayneWMDE, Invadibot, 
maantietaja, Akuckartz, Nandana, Lahi, Gq86, GoranSMilovanovic, QZanden, 
LawExplorer, _jensen, rosalieper, Scott_WUaS, Wikidata-bugs, aude, 
Lydia_Pintscher, Addshore, Mbch331
___
Wikidata-bugs mailing list -- wikidata-bugs@lists.wikimedia.org
To unsubscribe send an email to wikidata-bugs-le...@lists.wikimedia.org

[Wikidata-bugs] [Maniphest] T292548: Drop index on change_revision_id in wb_changes table

[Maintenance_bot]

Maintenance_bot added a project: Wikidata.

TASK DETAIL
  https://phabricator.wikimedia.org/T292548

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Maintenance_bot
Cc: Aklapper, Michael, karapayneWMDE, Invadibot, maantietaja, Akuckartz, 
Nandana, Lahi, Gq86, GoranSMilovanovic, QZanden, LawExplorer, _jensen, 
rosalieper, Scott_WUaS, Wikidata-bugs, aude, Lydia_Pintscher, Addshore, Mbch331
___
Wikidata-bugs mailing list -- wikidata-bugs@lists.wikimedia.org
To unsubscribe send an email to wikidata-bugs-le...@lists.wikimedia.org