[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Marostegui closed this task as "Resolved". Marostegui added a comment. @Ladsgroup I am going to close this - once you've got all the changes merged, let's create a normal #blocked-on-schema-change task. Thank you for cleaning

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

gerritbot added a comment. Change 630224 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup): [mediawiki/extensions/Wikibase@master] Drop three unused indexes from wb_changes https://gerrit.wikimedia.org/r/630224 TASK DETAIL https://phabricator.wikimedia.org/T262856 E

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

gerritbot added a project: Patch-For-Review. TASK DETAIL https://phabricator.wikimedia.org/T262856 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: Marostegui, gerritbot Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, LS

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Marostegui added a comment. So, I have captured a lots of queries involving `wb_changes` and I haven't found any single query that has a crazy query plan as a result of deleting `wb_changes_change_type wb_changes_change_object_id wb_changes_change_user_id`. Also, I have checked if those rep

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Ladsgroup added a comment. Thank you so much! Let me know If I can be of any help. TASK DETAIL https://phabricator.wikimedia.org/T262856 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: Marostegui, Ladsgroup Cc: Kormat, Marostegui, Lucas_Werkmeiste

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Marostegui added a comment. @Ladsgroup after a few hours, I have not seen any significant impact on the host's slow queries for now. The host dashboard at https://grafana.wikimedia.org/d/00273/mysql?orgId=1&from=now-24h&to=now&var-server=db2084&var-port=9104 also doesn't show any spike

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Stashbot added a comment. Mentioned in SAL (#wikimedia-operations) [2020-09-23T10:01:56Z] dbctl commit (dc=all): 'Fully repool db2084 after index changes T262856 ', diff saved to https://phabricator.wikimedia.org/P12751 and previous config saved to

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Marostegui added a comment. db2084 is fully repooled, let's monitor its performance TASK DETAIL https://phabricator.wikimedia.org/T262856 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/emailpreferences/ To: Marostegui Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Ad

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Stashbot added a comment. Mentioned in SAL (#wikimedia-operations) [2020-09-23T09:45:11Z] dbctl commit (dc=all): 'Slowly repool db2084 after index changes T262856 ', diff saved to https://phabricator.wikimedia.org/P12750 and previous config saved t

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Marostegui added a comment. @Ladsgroup db2084 has half the weight it used to, I am capturing live queries arriving to `wb_changes`, so far I haven't found anything strange with their query plans, or extremely slow queries showing up. TASK DETAIL https://phabricator.wikimedia.org/T262856 E

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Stashbot added a comment. Mentioned in SAL (#wikimedia-operations) [2020-09-23T08:32:00Z] dbctl commit (dc=all): 'Slowly repool db2084 after index changes T262856 ', diff saved to https://phabricator.wikimedia.org/P12748 and previous config saved t

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Stashbot added a comment. Mentioned in SAL (#wikimedia-operations) [2020-09-23T08:06:51Z] dbctl commit (dc=all): 'Slowly repool db2084 after index changes T262856 ', diff saved to https://phabricator.wikimedia.org/P12747 and previous config saved t

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Marostegui added a comment. root@db2084.codfw.wmnet[wikidatawiki]> CREATE INDEX /*i*/wb_changes_change_revision_id ON /*_*/wb_changes (change_revision_id); Query OK, 0 rows affected (10.702 sec) Records: 0 Duplicates: 0 Warnings: 0 root@db2084.codfw.wmnet[wikidatawiki

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Stashbot added a comment. Mentioned in SAL (#wikimedia-operations) [2020-09-23T07:11:29Z] dbctl commit (dc=all): 'Slowly repool db2084 after index changes T262856 ', diff saved to https://phabricator.wikimedia.org/P12746 and previous config saved t

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Stashbot added a comment. Mentioned in SAL (#wikimedia-operations) [2020-09-23T07:09:26Z] dbctl commit (dc=all): 'Depool db2084 to re-add change_revision_id index T262856 ', diff saved to https://phabricator.wikimedia.org/P12745 and previous config

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Marostegui added a comment. I can also try to live capture some on db2084. In T262856#6486318 , @Ladsgroup wrote: > Mostly are straightforward but I actually found a query on master in the code that's like this (found it in perfor

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Ladsgroup added a comment. So tendril doesn't have anything in sampled queries: https://tendril.wikimedia.org/report/sampled_queries?host=^db&user=wikiuser&schema=wik&hours=1 (maybe it's not sampling from codfw?) I looked at the code in depth and found a couple of queries. The queries I see

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Stashbot added a comment. Mentioned in SAL (#wikimedia-operations) [2020-09-23T05:58:51Z] dbctl commit (dc=all): 'Slowly repool db2084 after index removal T262856 ', diff saved to https://phabricator.wikimedia.org/P12742 and previous config saved t

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Marostegui added a comment. db2084 got those keys dropped: root@db2084.codfw.wmnet[wikidatawiki]> show create table wb_changes; ++-

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Stashbot added a comment. Mentioned in SAL (#wikimedia-operations) [2020-09-23T05:55:31Z] dbctl commit (dc=all): 'Depool db2084 T262856 ', diff saved to https://phabricator.wikimedia.org/P12741 and previous config saved to /var/cache/conftool/dbco

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Marostegui added a project: DBA. Marostegui triaged this task as "Medium" priority. Marostegui claimed this task. Marostegui added a comment. Yep, I will try to pick db2082 which is the one with the less weight. We can also try to capture some of the heaviest queries and replay them manually.

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Ladsgroup added a comment. In T262856#6461221 , @Marostegui wrote: > My advise would be to drop these on an active slave and see if we see performance regressions. Sounds good to me, do you want to do it? TASK DETAIL https://phab

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Ladsgroup added a comment. In T262856#6461884 , @Lucas_Werkmeister_WMDE wrote: >> This table only exists on s8. > > Surely it must also exist on s4 (commonswiki, testcommonswiki) and s3 (testwikidatawiki)? Indeed. But they are p

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Lucas_Werkmeister_WMDE added a comment. > This table only exists on s8. Surely it must also exist on s4 (commonswiki, testcommonswiki) and s3 (testwikidatawiki)? TASK DETAIL https://phabricator.wikimedia.org/T262856 EMAIL PREFERENCES https://phabricator.wikimedia.org/settings/panel/

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Marostegui added a comment. So a quick look (this needs to be taken with care) reveals that some of them are indeed unused: root@db2091.codfw.wmnet[sys]> select * from schema_unused_indexes where object_name='wb_changes'; +---+-+-

[Wikidata-bugs] [Maniphest] T262856: Investigate indexes of wb_changes

Ladsgroup created this task. Ladsgroup added projects: Wikidata-Campsite, Wikidata. Restricted Application added a subscriber: Aklapper. TASK DESCRIPTION While doing T205094: Investigate and restructure SQL directory, and use new MediaWiki abstract schema change features