Page MenuHomePhabricator
Create Task
Maniphest T71127

Discrepancies with logging table on different wikis
Closed, ResolvedPublic
Actions

Assigned To
Marostegui
Authored By
• bzimport
Aug 4 2014, 9:39 PM
Tags
Referenced Files
None
Subscribers
Aklapper
Andrew
• chasemp
coren
• jcrespo
kaldari
Krenair
View All 13 Subscribers
Tokens
"Orange Medal" token, awarded by jcrespo."Burninate" token, awarded by Liuxinyu970226."Burninate" token, awarded by MusikAnimal.

Description

Author: metatron

Description:
Queries on logging-table perform different on different wikis.
Atm there are different key definitions on eg. enwiki and dewiki

  • enwiki has a key log_user_text_time(ok to have), dewiki doesn't
  • dewiki has a key log_user_type_time(important to have), enwiki doesn't
  • same for log_title_time / log_page_id_time
enwiki | CREATE TABLE `logging`
...
PRIMARY KEY (`log_id`), 
  KEY `type_time` (`log_type`,`log_timestamp`), 
  KEY `user_time` (`log_user`,`log_timestamp`), 
  KEY `page_time` (`log_namespace`,`log_title`,`log_timestamp`),
  KEY `times` (`log_timestamp`), 
  KEY `log_page_id_time` (`log_page`,`log_timestamp`), 
  KEY `log_user_text_time` (`log_user_text`(16),`log_timestamp`), 
  KEY `log_title_time` (`log_title`(16),`log_timestamp`) 
) ENGINE=TokuDB AUTO_INCREMENT=58015836 DEFAULT CHARSET=binary `compression`='tokudb_zlib'
dewiki | CREATE TABLE `logging`
...
PRIMARY KEY (`log_id`),
  KEY `type_time` (`log_type`,`log_timestamp`),
  KEY `user_time` (`log_user`,`log_timestamp`),
  KEY `page_time` (`log_namespace`,`log_title`,`log_timestamp`),
  KEY `times` (`log_timestamp`),
  KEY `log_user_type_time` (`log_user`,`log_type`,`log_timestamp`),
  KEY `log_page_id_time` (`log_page`,`log_timestamp`)
) ENGINE=TokuDB AUTO_INCREMENT=62684641 DEFAULT CHARSET=binary `compression`='tokudb_zlib'

Schema change progress

  • wikitech
    • labswiki
    • labtestwiki

Details

Reference
bz69127

Related Objects
Search...

StatusSubtypeAssignedTask
 ResolvedMarosteguiT71127 Discrepancies with logging table on different wikis
 ResolvedLadsgroupT104459 Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas
 ResolvedNoneT57455 Provide dynamic report of differences between replica databases and production databases
 ResolvedMarosteguiT51199 Add index log_type_action
 ResolvedMarosteguiT217397 logging.log_title_time and logging.log_title_type_time indexes are not on tables.sql but they exist on most of the wikis
 ResolvedMarosteguiT250055 Remove image.img_deleted column from production
 ResolvedMarosteguiT250067 user_newtalk has two indexes not renamed in s4
 ResolvedMarosteguiT250062 ipb_parent_block_id_2 index on ipblocks table on s8 only
 ResolvedMarosteguiT250060 tl_namespace index on templatelinks is unique only in s8
 ResolvedReedyT249972 Wikitech has lots of database drifts with core and rest of the databases
 ResolvedMarosteguiT249973 db1110 has 5 important database drifts that are unique to the host
 ResolvedMarosteguiT250056 lc_lang_key index is lingering in production
 ResolvedMarosteguiT250057 type_acton index in logging table is lingering in production
 DeclinedNoneT250058 searchindex indexes are missing in production
 ResolvedMarosteguiT250063 inverse_timestamp column exists in text table, it shouldn't
 ResolvedMarosteguiT250066 text table still has old_* fields and indexes on some hosts
 ResolvedLadsgroupT250071 Rename ipb_address index on ipb_address to ipb_address_unique
 ResolvedMarosteguiT252126 tl_from index on templatelinks is lingering in production
 ResolvedMarosteguiT256679 text table in db1144 drifts from core considerably
 ResolvedMarosteguiT256680 imagelinks has index mismatch on s8
 ResolvedMarosteguiT256682 page_restrictions indexes have been majestically drifting from code
 ResolvedMarosteguiT256684 pl_from index still lingers in random hosts
 ResolvedMarosteguiT256685 pl_namespace index on pagelinks is unique only in s8
 ResolvedMarosteguiT260111 All sorts of random drifts in wikis in s3
 ResolvedLadsgroupT265321 ipblocks_restrictions.ir_type is tinyint(1) in code but tinyint(4) in production
 ResolvedMarosteguiT265349 querycache qc_type and qc_title have different nullabality on s1 only
 ResolvedMarosteguiT265445 Index uniqueness mismatches in links tables in wikis that were moved from s3 to s5
 ResolvedLadsgroupT269348 wikitech database has almost all of its varbinary fields wrong
 Resolved• KormatT277118 iw_url in interwiki is varbinary(127) in production but blob in code
 ResolvedMarosteguiT277354 "chemical" major mime type was never added to production database
 Resolved• KormatT278619 ipb_timestamp is varbinary(14) in old wikis while being binary(14) in the code since 2007
 ResolvedMarosteguiT278621 job_cmd is varbinary(255) in production while being varbinary(60) in code since 2007
 Resolved• KormatT277116 fa_deleted_timestamp and fa_timestamp are binary(14) in code but varbinary(14) in production
 ResolvedMarosteguiT308206 Slow query on echo_event table

Event Timeline

• bzimport raised the priority of this task from to Needs Triage.Nov 22 2014, 3:36 AM
• bzimport added a project: Cloud-VPS.
• bzimport set Reference to bz69127.
• bzimport created this task.Aug 4 2014, 9:39 PM
Aklapper added a project: Cloud-Services.Apr 29 2015, 12:18 PM
Krenair added a project: DBA.Aug 18 2015, 3:51 AM
Krenair subscribed.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 18 2015, 3:51 AM
• Springle set Security to None.Aug 28 2015, 4:22 AM
• Springle added a subscriber: • jcrespo.
Krinkle moved this task from Triage to Backlog on the DBA board.Sep 23 2015, 4:27 AM
Krinkle moved this task from Backlog to Triage on the DBA board.Sep 23 2015, 7:02 AM
• chasemp triaged this task as Low priority.Nov 30 2015, 4:18 PM
• chasemp subscribed.
bd808 removed • Springle as the assignee of this task.Jun 8 2017, 4:39 AM
Marostegui subscribed.EditedJun 9 2017, 1:34 PM

For the record, this is the current status of the discrepancies of this table (based on tables.sql: https://phabricator.wikimedia.org/source/mediawiki/browse/master/maintenance/tables.sql

s1 (enwiki )and s2(bgwiki bgwiktionary cswiki enwikiquote enwiktionary eowiki fiwiki idwiki itwiki nlwiki nowiki plwiki ptwiki svwiki thwiki trwiki zhwiki), s4 (commonswiki):
missing:

KEY `type_action` (`log_type`,`log_action`,`log_timestamp`),
KEY `log_user_text_type_time` (`log_user_text`(191),`log_type`,`log_timestamp`),

Extra indexes:

KEY `log_title_time` (`log_title`(16),`log_timestamp`),
KEY `log_title_type_time` (`log_title`(16),`log_type`,`log_timestamp`)

s5:
dewiki all the hosts are missing:

KEY `type_action` (`log_type`,`log_action`,`log_timestamp`),
KEY `log_user_text_type_time` (`log_user_text`(191),`log_type`,`log_timestamp`),

And some hosts have 1 or 2 extra indexes:

db1092.eqiad.wmnet
  KEY `log_title_time` (`log_title`(16),`log_timestamp`)
db1063.eqiad.wmnet
  KEY `log_title_time` (`log_title`(16),`log_timestamp`),
  KEY `log_title_type_time` (`log_title`(16),`log_type`,`log_timestamp`)

wikidatawiki is missing:

KEY `log_user_text_type_time` (`log_user_text`(191),`log_type`,`log_timestamp`),

And have the two extra indexes:

KEY `log_title_time` (`log_title`(16),`log_timestamp`),
KEY `log_title_type_time` (`log_title`(16),`log_type`,`log_timestamp`)

s6 (frwiki, jawiki, ruwiki):
They are missing:

KEY `log_user_text_type_time` (`log_user_text`(191),`log_type`,`log_timestamp`),

And they have the two extra indexes:

KEY `log_title_time` (`log_title`(16),`log_timestamp`),
KEY `log_title_type_time` (`log_title`(16),`log_type`,`log_timestamp`)

s7 (arwiki cawiki eswiki fawiki frwiktionary hewiki huwiki kowiki metawiki rowiki ukwiki viwiki):
Missing:

KEY `log_user_text_type_time` (`log_user_text`(191),`log_type`,`log_timestamp`),

Has the two extra indexes:

KEY `log_title_time` (`log_title`(16),`log_timestamp`),
KEY `log_title_type_time` (`log_title`(16),`log_type`,`log_timestamp`)

I have not checked s3.
So why do we have those two extra indexes everywhere? I haven't found anything on gerrit about them - found this though: T68961

KEY `log_title_time` (`log_title`(16),`log_timestamp`),
KEY `log_title_type_time` (`log_title`(16),`log_type`,`log_timestamp`)

Should we unify this table to what tables.sql says?
If so, we'd need to first check if removing those indexes will have any impact and I suspect it would as otherwise why would they be there?
If they are important why aren't them on tables.sql?
Should we add the other two missing indexes where they are indeed missing?

MusikAnimal awarded a token.Jul 24 2017, 11:24 PM
MusikAnimal subscribed.
MusikAnimal mentioned this in T171264: Long queries not being killed.Jul 24 2017, 11:35 PM
kaldari subscribed.Jul 25 2017, 12:21 AM

FYI, the lack of log indexes on some wikis is causing issues for XTools. See, for example, T171264#3468493.

kaldari raised the priority of this task from Low to Medium.Jul 25 2017, 12:22 AM
MusikAnimal added a comment.Jul 25 2017, 1:35 AM

Yeah, the log_title_time index seems especially helpful when doing things like looking up a block log. You could use log_page instead of log_title, using the ID of the user's userpage, but that doesn't work if they haven't created a userpage :( Also we'd have to look up the page ID first. If there's another, quicker cross-wiki way of checking the block log, please enlighten me :)

bd808 edited projects, added Data-Services; removed Cloud-Services, Cloud-VPS.Jul 25 2017, 5:13 AM
bd808 moved this task from Backlog to Wiki replicas on the Data-Services board.
Liuxinyu970226 awarded a token.Jul 25 2017, 12:32 PM
Liuxinyu970226 subscribed.
• jcrespo renamed this task from Database upgrade MariaDB 10: Discrepancies with logging table on different wikis to Discrepancies with logging table on different wikis.Aug 7 2017, 8:19 AM

Nothing to do with MariaDB 10, I assume at some point was a blocker. This is probably a multi-month maintenance such as T132416.

• jcrespo mentioned this in T68961: Logging needs an index to optimize searching by log_title.Aug 7 2017, 1:45 PM
• jcrespo added a comment.Jan 4 2018, 8:48 AM

As far as I can see, mediawiki doesn't define log_title_time nor log_title_type_time ( see https://phabricator.wikimedia.org/source/mediawiki/browse/master/maintenance/tables.sql ). Many hosts lack log_user_text_type_time (log_user_text, log_type, log_timestamp), and some tables have some string-containing keys only indexing the first 16 bytes/characters.

• jcrespo added a comment.Jan 4 2018, 8:57 AM

It is important to separate production from wikireplicas- wikireplicas can have additional indexes, production should not- or we should patch mediawiki first.

Marostegui mentioned this in T71222: list=logevents slow for users with last log action long time ago.Jul 27 2018, 2:15 PM
Marostegui added a parent task: T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas.Oct 16 2018, 9:45 AM
Marostegui moved this task from Triage to Backlog on the DBA board.
Marostegui mentioned this in T212692: DBQueryTimeoutError on Wikimedia Login's Special:CheckUser .Dec 30 2018, 7:07 AM
Marostegui added a parent task: T212692: DBQueryTimeoutError on Wikimedia Login's Special:CheckUser .Jan 2 2019, 7:22 AM
Peachey88 updated the task description. (Show Details)Jan 2 2019, 8:23 AM
• jcrespo removed parent tasks: T212692: DBQueryTimeoutError on Wikimedia Login's Special:CheckUser , T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas.Feb 8 2019, 9:53 AM
• jcrespo added a subtask: T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas.
Marostegui moved this task from Backlog to Pending comment on the DBA board.Feb 25 2019, 2:23 PM
Marostegui claimed this task.Mar 1 2019, 8:43 AM
Marostegui moved this task from Pending comment to In progress on the DBA board.

I am going to try to work on this, as this has bitten us already a few times already, the most recent time on a global user rename.
This is in a not very good shape (T71127#3335238), I will try to combine this with T51199

Marostegui mentioned this in T217397: logging.log_title_time and logging.log_title_type_time indexes are not on tables.sql but they exist on most of the wikis.Mar 1 2019, 9:36 AM
Marostegui added a comment.Mar 11 2019, 8:25 AM

After analyzing the indexes on T217397 and testing a few hosts (T217397#4997997 and T217397#5010633) with the exact table definition on tables.sql, we are going to unify logging table based on tables.sql indexes.

Marostegui added a comment.EditedMar 11 2019, 8:51 AM

The following hosts on s1 eqiad have the indexes unified with tables.sql (T217397#5010633)

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • dbstore1001
  • db1124
  • db1119
  • db1118
  • db1106
  • db1105
  • db1099
  • db1089
  • db1083
  • db1080
  • db1067
Marostegui added a comment.EditedMar 11 2019, 8:54 AM

The following hosts on s4 eqiad have the indexes unified with tables.sql (T217397#5010633)

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1125
  • db1121
  • db1103
  • db1102
  • db1097
  • db1091
  • db1084
  • db1081
  • db1068
Marostegui added a comment.EditedMar 11 2019, 8:56 AM

The following hosts on s5 eqiad have the indexes unified with tables.sql (T217397#5010633)

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1124
  • db1113
  • db1110
  • db1102
  • db1100
  • db1097
  • db1096
  • db1082
  • db1070
Marostegui updated the task description. (Show Details)Mar 11 2019, 9:00 AM
Marostegui updated the task description. (Show Details)Mar 11 2019, 9:11 AM
Marostegui updated the task description. (Show Details)Mar 11 2019, 9:13 AM
Stashbot subscribed.Mar 12 2019, 7:18 AM

Mentioned in SAL (#wikimedia-operations) [2019-03-12T07:18:34Z] <marostegui> Deploy schema change on db2052 (s5 codfw master), this will generate lag on codfw T71127 T51199

Stashbot mentioned this in T51199: Add index log_type_action.Mar 12 2019, 7:18 AM
Marostegui updated the task description. (Show Details)Mar 12 2019, 8:02 AM
Marostegui updated the task description. (Show Details)Mar 12 2019, 12:10 PM
Marostegui updated the task description. (Show Details)Mar 12 2019, 2:04 PM
Marostegui added a comment.EditedMar 12 2019, 2:58 PM

s6 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • dbstore1001
  • db1125
  • db1113
  • db1098
  • db1096
  • db1093
  • db1088
  • db1085
  • db1061
Marostegui updated the task description. (Show Details)Mar 12 2019, 3:30 PM
Marostegui updated the task description. (Show Details)Mar 14 2019, 8:43 AM
Marostegui updated the task description. (Show Details)Mar 14 2019, 9:26 AM
Marostegui updated the task description. (Show Details)EditedMar 18 2019, 6:37 AM

s8 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db1124
  • db1116
  • db1109
  • db1104
  • db1101
  • db1099
  • db1092
  • db1087
  • db1071
Marostegui updated the task description. (Show Details)Mar 18 2019, 6:38 AM
Marostegui updated the task description. (Show Details)Mar 18 2019, 9:33 AM
Marostegui updated the task description. (Show Details)Mar 20 2019, 9:20 AM
Marostegui updated the task description. (Show Details)Mar 20 2019, 10:13 AM
Marostegui added a comment.EditedMar 20 2019, 11:02 AM

s7 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1125
  • db1116
  • db1101
  • db1098
  • db1094
  • db1090
  • db1086
  • db1079
  • db1062
Marostegui updated the task description. (Show Details)Mar 22 2019, 7:08 AM
Marostegui updated the task description. (Show Details)
Marostegui updated the task description. (Show Details)Mar 25 2019, 7:20 AM
Marostegui updated the task description. (Show Details)Mar 26 2019, 8:08 AM
Marostegui added a comment.EditedMar 26 2019, 8:21 AM

s2 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1125
  • db1122
  • db1105
  • db1103
  • db1095
  • db1090
  • db1076
  • db1074
  • db1066
Marostegui updated the task description. (Show Details)Mar 26 2019, 8:36 AM
Marostegui updated the task description. (Show Details)Mar 28 2019, 6:20 AM
Marostegui updated the task description. (Show Details)Mar 28 2019, 7:16 AM
Marostegui added a comment.EditedMar 28 2019, 8:01 AM

s3 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1124
  • db1123
  • db1095
  • db1075
  • db1077
  • db1078
Marostegui updated the task description. (Show Details)Mar 28 2019, 8:01 AM
Marostegui closed this task as Resolved.Apr 1 2019, 7:01 AM
Marostegui updated the task description. (Show Details)

All done! One less drift between production and tables.sql

• jcrespo awarded a token.Apr 1 2019, 7:17 AM
Ladsgroup closed subtask T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas as Resolved.May 15 2021, 11:43 AM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits