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

[Marostegui]

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 this up!

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

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

To: Marostegui
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
LSobanski, Alter-paule, Beast1978, Un1tY, Akuckartz, Hook696, Iflorez, 
darthmon_wmde, Kent7301, joker88john, CucyNoiD, Nandana, Gaboe420, Giuliamocci, 
Cpaulf30, Lahi, Gq86, Af420, Bsandipan, GoranSMilovanovic, QZanden, 
LawExplorer, Lewizho99, Minhnv-2809, Maathavan, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[gerritbot]

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

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

To: Marostegui, gerritbot
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
LSobanski, Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[gerritbot]

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, 
LSobanski, Alter-paule, Beast1978, Un1tY, Akuckartz, Hook696, Iflorez, 
darthmon_wmde, Kent7301, joker88john, CucyNoiD, Nandana, Gaboe420, Giuliamocci, 
Cpaulf30, Lahi, Gq86, Af420, Bsandipan, GoranSMilovanovic, QZanden, 
LawExplorer, Lewizho99, Minhnv-2809, Maathavan, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Marostegui]

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 reported unused indexes are consistent across 
all the hosts and they seem to be:
  
db2100.codfw.wmnet:3318
object_schema   object_name index_name
wikidatawikiwb_changes  wb_changes_change_type
wikidatawikiwb_changes  wb_changes_change_object_id
wikidatawikiwb_changes  wb_changes_change_user_id
wikidatawikiwb_changes  wb_changes_change_revision_id
db2094.codfw.wmnet:3318
object_schema   object_name index_name
wikidatawikiwb_changes  wb_changes_change_type
wikidatawikiwb_changes  wb_changes_change_time
wikidatawikiwb_changes  wb_changes_change_object_id
wikidatawikiwb_changes  wb_changes_change_user_id
wikidatawikiwb_changes  wb_changes_change_revision_id
db2091.codfw.wmnet:3306
object_schema   object_name index_name
wikidatawikiwb_changes  wb_changes_change_type
wikidatawikiwb_changes  wb_changes_change_object_id
wikidatawikiwb_changes  wb_changes_change_user_id
wikidatawikiwb_changes  wb_changes_change_revision_id
db2086.codfw.wmnet:3318
object_schema   object_name index_name
wikidatawikiwb_changes  wb_changes_change_type
wikidatawikiwb_changes  wb_changes_change_object_id
wikidatawikiwb_changes  wb_changes_change_user_id
wikidatawikiwb_changes  wb_changes_change_revision_id
db2085.codfw.wmnet:3318
object_schema   object_name index_name
wikidatawikiwb_changes  wb_changes_change_type
wikidatawikiwb_changes  wb_changes_change_object_id
wikidatawikiwb_changes  wb_changes_change_user_id
wikidatawikiwb_changes  wb_changes_change_revision_id
db2084.codfw.wmnet:3306
db2083.codfw.wmnet:3306
object_schema   object_name index_name
wikidatawikiwb_changes  wb_changes_change_type
wikidatawikiwb_changes  wb_changes_change_object_id
wikidatawikiwb_changes  wb_changes_change_user_id
wikidatawikiwb_changes  wb_changes_change_revision_id
db2082.codfw.wmnet:3306
object_schema   object_name index_name
wikidatawikiwb_changes  wb_changes_change_type
wikidatawikiwb_changes  wb_changes_change_object_id
wikidatawikiwb_changes  wb_changes_change_user_id
wikidatawikiwb_changes  wb_changes_change_revision_id
db2081.codfw.wmnet:3306
object_schema   object_name index_name
wikidatawikiwb_changes  wb_changes_change_type
wikidatawikiwb_changes  wb_changes_change_object_id
wikidatawikiwb_changes  wb_changes_change_user_id
wikidatawikiwb_changes  wb_changes_change_revision_id
db2080.codfw.wmnet:3306
object_schema   object_name index_name
wikidatawikiwb_changes  wb_changes_change_type
wikidatawikiwb_changes  wb_changes_change_object_id
wikidatawikiwb_changes  wb_changes_change_user_id
wikidatawikiwb_changes  wb_changes_change_revision_id
db2079.codfw.wmnet:3306
object_schema   object_name index_name
wikidatawikiwb_changes  wb_changes_change_type
wikidatawikiwb_changes  wb_changes_change_object_id
wikidatawikiwb_changes  wb_changes_change_user_id
  
  Some of them do report `wb_changes_change_revision_id`, some others don't. 
But as Amir said at T262856#6486318 
, it might be used, so let's 
leave it there.
  @Ladsgroup I think we can probably go ahead and create a proper schema change 
ticket and delete: `wb_changes_change_type wb_changes_change_type 
wb_changes_change_user_id`.
  It is not a huge table, so if we see something very strange once in 
production on more hosts, we can probably alter it quickly.
  
-rw-rw 1 mysql mysql 2.0G Sep 24 07:28 wb_changes.ibd
  
  Anything else left on this task then?

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

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

To: Marostegui
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
LSobanski, Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
_

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

[Ladsgroup]

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_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
LSobanski, Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Marostegui]

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 spikes on anything (scanned rows, or query latency).
  Tendril is also not showing slow queries, I will try to get some more queries 
involving the `wb_changes` table tomorrow to manually analyze them, but so far 
so good.

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

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

To: Marostegui
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
LSobanski, Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Stashbot]

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 
/var/cache/conftool/dbconfig/20200923-100156-marostegui.json

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

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

To: Marostegui, Stashbot
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
LSobanski, Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Marostegui]

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, Addshore, Aklapper, Ladsgroup, 
LSobanski, Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Stashbot]

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 to 
/var/cache/conftool/dbconfig/20200923-094511-marostegui.json

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

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

To: Marostegui, Stashbot
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
LSobanski, Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Marostegui]

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

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

To: Marostegui
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
LSobanski, Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Stashbot]

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 to 
/var/cache/conftool/dbconfig/20200923-083200-marostegui.json

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

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

To: Marostegui, Stashbot
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
LSobanski, Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Stashbot]

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 to 
/var/cache/conftool/dbconfig/20200923-080651-marostegui.json

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

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

To: Marostegui, Stashbot
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
LSobanski, Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Marostegui]

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]>
root@db2084.codfw.wmnet[wikidatawiki]> show create table wb_changes;

++--
| Table  | Create Table

++--
| wb_changes | CREATE TABLE `wb_changes` (
  `change_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `change_type` varbinary(25) NOT NULL,
  `change_time` varbinary(14) NOT NULL,
  `change_object_id` varbinary(14) NOT NULL,
  `change_revision_id` int(10) unsigned NOT NULL,
  `change_user_id` int(10) unsigned NOT NULL,
  `change_info` mediumblob NOT NULL,
  PRIMARY KEY (`change_id`),
  KEY `wb_changes_change_time` (`change_time`),
  KEY `wb_changes_change_revision_id` (`change_revision_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1274893656 DEFAULT CHARSET=binary 
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 |

++--
1 row in set (0.032 sec)

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

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

To: Marostegui
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
LSobanski, Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Stashbot]

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 to 
/var/cache/conftool/dbconfig/20200923-071129-marostegui.json

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

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

To: Marostegui, Stashbot
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
LSobanski, Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Stashbot]

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 
saved to /var/cache/conftool/dbconfig/20200923-070926-marostegui.json

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

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

To: Marostegui, Stashbot
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
LSobanski, Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Marostegui]

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 performance schema of db2079 too):
  >
  > SELECT `change_id` , `change_type` , `change_time` , `change_object_id` , 
`change_revision_id` , `change_user_id` , `change_info` FROM `wb_changes` WHERE 
`change_revision_id` = ? LIMIT ?
  >
  >   (With exec count of ~12,510,000 and total latency of 3150724249736000) so 
I think we need to bring back the index on revision id but the rest can be 
safely dropped.
  
  Doing that now

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

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

To: Marostegui
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
LSobanski, Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Ladsgroup]

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 in the performance schema of db2083 are:
  
SELECT `chd_site` , `chd_db` , `chd_seen` , `chd_touched` , `chd_lock` , 
`chd_disabled` , `change_time` FROM `wb_changes_dispatch` LEFT JOIN 
`wb_changes` ON ( ( `chd_seen` = `change_id` ) ) WHERE `chd_disabled` = ? ORDER 
BY `chd_seen` AS
UPDATE `wb_changes` SET `change_type` = ? , `change_time` = ? , 
`change_object_id` = ? , `change_revision_id` = ? , `change_user_id` = ? , 
`change_info` = ? WHERE `change_id` = ?
INSERT INTO `wb_changes` ( `change_type` , `change_time` , 
`change_object_id` , `change_revision_id` , `change_user_id` , `change_info` ) 
VALUES (...)
SELECT MIN ( `change_id` ) AS `min_id` , MAX ( `change_id` ) AS `max_id` , 
MIN ( `change_time` ) AS `min_time` , MAX ( `change_time` ) AS `max_time` FROM 
`wb_changes` LIMIT ?
DELETE FROM `wb_changes` WHERE ( `change_time` < ? )
SELECT MAX ( `change_id` ) AS `maxid` FROM `wb_changes` LIMIT ?
SELECT `change_id` , `change_type` , `change_time` , `change_object_id` , 
`change_revision_id` , `change_user_id` , `change_info` FROM `wb_changes` WHERE 
`change_id` IN (...)
SELECT `change_id` , `change_type` , `change_time` , `change_object_id` , 
`change_revision_id` , `change_user_id` , `change_info` FROM `wb_changes` WHERE 
`change_id` = ?
SELECT MIN ( `change_time` ) AS TIMESTAMP FROM `wb_changes_dispatch` , 
`wb_changes` WHERE `chd_disabled` = ? AND ( `chd_seen` = `change_id` ) LIMIT ?
  
  Mostly are straightforward but I actually found a query on master in the code 
that's like this (found it in performance schema of db2079 too):
  
SELECT `change_id` , `change_type` , `change_time` , `change_object_id` , 
`change_revision_id` , `change_user_id` , `change_info` FROM `wb_changes` WHERE 
`change_revision_id` = ? LIMIT ?
  
  (With exec count of ~12,510,000 and total latency of 3150724249736000) so I 
think we need to bring back the index on revision id but the rest can be safely 
dropped.

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

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

To: Marostegui, Ladsgroup
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
LSobanski, Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Stashbot]

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 to 
/var/cache/conftool/dbconfig/20200923-055850-marostegui.json

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

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

To: Marostegui, Stashbot
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
LSobanski, Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Marostegui]

Marostegui added a comment.


  db2084 got those keys dropped:
  
root@db2084.codfw.wmnet[wikidatawiki]> show create table wb_changes;

++-+
| Table  | Create Table 








   |

++-+
| wb_changes | CREATE TABLE `wb_changes` (
  `change_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `change_type` varbinary(25) NOT NULL,
  `change_time` varbinary(14) NOT NULL,
  `change_object_id` varbinary(14) NOT NULL,
  `change_revision_id` int(10) unsigned NOT NULL,
  `change_user_id` int(10) unsigned NOT NULL,
  `change_info` mediumblob NOT NULL,
  PRIMARY KEY (`change_id`),
  KEY `wb_changes_change_type` (`change_type`),
  KEY `wb_changes_change_time` (`change_time`),
  KEY `wb_changes_change_object_id` (`change_object_id`),
  KEY `wb_changes_change_user_id` (`change_user_id`),
  KEY `wb_changes_change_revision_id` (`change_revision_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1274872346 DEFAULT CHARSET=binary 
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 |

++-+
1 row in set (0.032 sec)

root@db2084.codfw.wmnet[wikidatawiki]> alter table wb_changes drop key 
wb_changes_change_type, drop key wb_changes_change_object_id, drop key 
wb_changes_change_user_id, drop key wb_changes_change_revision_id;
Query OK, 0 rows affected (0.101 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db2084.codfw.wmnet[wikidatawiki]> show create table wb_changes;

++---+
| Tabl

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

[Stashbot]

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/dbconfig/20200923-055531-marostegui.json

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

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

To: Marostegui, Stashbot
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
LSobanski, Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, 
GoranSMilovanovic, QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, 
Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Marostegui]

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.
  Not sure if I will be able to do it today, but I will try to remove them 
tomorrow

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

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

To: Marostegui
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
Akuckartz, Iflorez, darthmon_wmde, Nandana, Lahi, Gq86, GoranSMilovanovic, 
QZanden, LawExplorer, Minhnv-2809, _jensen, rosalieper, Scott_WUaS, Jonas, 
Wikidata-bugs, aude, Lydia_Pintscher, Mbch331, Jay8g, Krenair
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Ladsgroup]

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://phabricator.wikimedia.org/T262856

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

To: Ladsgroup
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
Akuckartz, darthmon_wmde, Nandana, Lahi, Gq86, GoranSMilovanovic, QZanden, 
LawExplorer, _jensen, rosalieper, Scott_WUaS, Jonas, Wikidata-bugs, aude, 
Lydia_Pintscher, Mbch331
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Ladsgroup]

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 pretty small and the writes on them are not much, I 
meant it's not in every db so people don't get confused when checking it.

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

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

To: Ladsgroup
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
Akuckartz, darthmon_wmde, Nandana, Lahi, Gq86, GoranSMilovanovic, QZanden, 
LawExplorer, _jensen, rosalieper, Scott_WUaS, Jonas, Wikidata-bugs, aude, 
Lydia_Pintscher, Mbch331
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Lucas_Werkmeister_WMDE]

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/emailpreferences/

To: Lucas_Werkmeister_WMDE
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
Akuckartz, darthmon_wmde, Nandana, Lahi, Gq86, GoranSMilovanovic, QZanden, 
LawExplorer, _jensen, rosalieper, Scott_WUaS, Jonas, Wikidata-bugs, aude, 
Lydia_Pintscher, Mbch331
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Marostegui]

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';
+---+-+---+
| object_schema | object_name | index_name|
+---+-+---+
| wikidatawiki  | wb_changes  | wb_changes_change_type|
| wikidatawiki  | wb_changes  | wb_changes_change_object_id   |
| wikidatawiki  | wb_changes  | wb_changes_change_user_id |
| wikidatawiki  | wb_changes  | wb_changes_change_revision_id |
+---+-+---+
4 rows in set (0.054 sec)
  
  My advise would be to drop these on an active slave and see if we see 
performance regressions.

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

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

To: Marostegui
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
Akuckartz, darthmon_wmde, Nandana, Lahi, Gq86, GoranSMilovanovic, QZanden, 
LawExplorer, _jensen, rosalieper, Scott_WUaS, Jonas, Wikidata-bugs, aude, 
Lydia_Pintscher, Mbch331
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs

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

[Ladsgroup]

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 
 I realized structure of wb_changes 
is this 
:
  
CREATE TABLE IF NOT EXISTS /*_*/wb_changes (
  change_id  INT unsignedNOT NULL PRIMARY KEY 
AUTO_INCREMENT, -- Id of change
  change_typeVARCHAR(25) NOT NULL, -- Type of the 
change
  change_timevarbinary(14)   NOT NULL, -- Time the 
change was made
  change_object_id   varbinary(14)   NOT NULL, -- The full id 
of the object (ie item, query) the change affects
  change_revision_id INT unsignedNOT NULL, -- The id of the 
revision on the repo that made the change
  change_user_id INT unsignedNOT NULL, -- The id of the 
user on the repo that made the change
  change_infoMEDIUMBLOB  NOT NULL -- Holds 
additional info about the change, inc diff and stuff
) /*$wgDBTableOptions*/;

CREATE INDEX /*i*/wb_changes_change_type ON /*_*/wb_changes (change_type);
CREATE INDEX /*i*/wb_changes_change_time ON /*_*/wb_changes (change_time);
CREATE INDEX /*i*/wb_changes_change_object_id ON /*_*/wb_changes 
(change_object_id);
CREATE INDEX /*i*/wb_changes_change_user_id ON /*_*/wb_changes 
(change_user_id);
CREATE INDEX /*i*/wb_changes_change_revision_id ON /*_*/wb_changes 
(change_revision_id);
  
  It seems all of indexes are complete random sense of "each column will have 
an index" (because why not). I'm pretty sure most of these indexes are not used 
at all and we can drop them. wb_changes is rather small but it's a pretty 
heavy-write table (twice of ratio of edits) so cleaning up the indexes would 
make a big difference in replication (also we can turn the ones that are used 
to covering index for faster read/write)
  
  I add our DBAs to check if they can confirm the hypotheses. This table only 
exists on s8.

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

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

To: Ladsgroup
Cc: Kormat, Marostegui, Lucas_Werkmeister_WMDE, Addshore, Aklapper, Ladsgroup, 
Akuckartz, darthmon_wmde, Nandana, Lahi, Gq86, GoranSMilovanovic, QZanden, 
LawExplorer, _jensen, rosalieper, Scott_WUaS, Jonas, Wikidata-bugs, aude, 
Lydia_Pintscher, Mbch331
___
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs