Page MenuHomePhabricator

Add new indexes from eec016ece6d2b30addcdf3d3efcc2ba59b10e858 to production databases
Closed, ResolvedPublic

Description

Jaime, please add new indexes from eec016ece6d2b30addcdf3d3efcc2ba59b10e858 (https://gerrit.wikimedia.org/r/#/c/272416/) to production databases, per your comment at T58041#2102116 :)

Where to run those changes: all wikis except testwiki and ruwiktionary
When to run those changes: Whenever you have time
If the schema change is backwards compatible: Compatible with the current code deployed
If the schema change has been tested already: Yes, tested by jcrespo (T58041#2064462) and kaldari (T58041#2290289)
If it involves new columns or tables, if the data should be made available on the labs replicas: Only index changes

Related Objects

StatusAssignedTask
Resolvedhashar
Resolvedhashar
ResolvedJoe
ResolvedLegoktm
Declineddemon
ResolvedJoe
ResolvedReedy
ResolvedNone
OpenNone
OpenNone
Resolvedtomasz
Resolvedkaldari
Resolvedkaldari
OpenNone
DeclinedNone
ResolvedNone
ResolvedJoe
OpenNone
ResolvedJoe
ResolvedNone
ResolvedJoe
Resolvedkaldari
Resolvedjcrespo
ResolvedVolans
Resolvedaaron
InvalidNone

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMar 23 2016, 1:00 AM

If we want to check again how it performs before doing it for all databases, I'd recommend starting with dewiki and T128806 or ruwiki and T129411. :)

Can you alter the description and include all information required on https://wikitech.wikimedia.org/wiki/Schema_changes#Workflow_of_a_schema_change ? Thank you!

jcrespo moved this task from Triage to Backlog on the DBA board.Mar 23 2016, 9:58 AM
jcrespo moved this task from Backlog to Blocked external/Not db team on the DBA board.
kaldari updated the task description. (Show Details)Mar 23 2016, 4:49 PM
kaldari moved this task from Blocked external/Not db team to Backlog on the DBA board.
DannyH added a subscriber: DannyH.Mar 23 2016, 6:10 PM
jcrespo added a comment.EditedMar 23 2016, 7:01 PM

This will be done "soon", and as an index change, it is easier than a table modification, but it still involves a change on all 900 wikis, on all 150 servers, so it will require some time (sadly, we still have 5.5 masters on almost all shards). I cannot guarantee reaching the March 29 date.

Would it work to have this applied on all slaves only- until the master failover preview for the 18 April? Does the query use the index only for reads? Applying to the 10.0 slaves -and the master, in the special case of s2- is something that I could do for next week.

jcrespo added a comment.EditedMar 23 2016, 7:04 PM

Sorry, I should have read properly. I can do it on ruwiktionary and testwiki with no problem.

jcrespo triaged this task as Normal priority.Mar 23 2016, 7:05 PM
jcrespo moved this task from Backlog to Next on the DBA board.

Would it work to have this applied on all slaves only- until the master failover preview for the 18 April? Does the query use the index only for reads? Applying to the 10.0 slaves -and the master, in the special case of s2- is something that I could do for next week.

The script, as currently written, only queries from master

jcrespo moved this task to Next on the DBA workboard.

Why is this "Next" when T69223 isn't?

Mentioned in SAL [2016-03-29T18:51:38Z] <jynus> performing schema change on testwiki T130692

jcrespo changed the task status from Open to Stalled.Mar 29 2016, 7:11 PM

I've made testwiki:

CREATE TABLE `categorylinks` (
  `cl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `cl_to` varbinary(255) NOT NULL DEFAULT '',
  `cl_sortkey` varbinary(230) NOT NULL DEFAULT '',
  `cl_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `cl_sortkey_prefix` varbinary(255) NOT NULL DEFAULT '',
  `cl_collation` varbinary(32) NOT NULL DEFAULT '',
  `cl_type` enum('page','subcat','file') NOT NULL DEFAULT 'page',
  UNIQUE KEY `cl_from` (`cl_from`,`cl_to`),
  KEY `cl_timestamp` (`cl_to`,`cl_timestamp`),
  KEY `cl_sortkey` (`cl_to`,`cl_type`,`cl_sortkey`,`cl_from`),
  KEY `cl_collation_ext` (`cl_collation`,`cl_to`,`cl_type`,`cl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary;

I cannot do ruwiktionary yet. It has 10 million rows and the s3 hosts are too slow. It needs the slow method and even with it, it has to be blocked until the proper previsioning of the new hosts/decommission of the old ones, and probably requires mariadb 10 masters to take advantage of the online index creation.

This means it will be blocked until the failover programmed for the 18 April.

jcrespo moved this task from Next to Backlog on the DBA board.Mar 29 2016, 7:11 PM

@jcrespo: Thanks for the update. How did switching over testwiki go? Did it take long?

jcrespo added a comment.EditedMar 29 2016, 7:50 PM

testwiki has 40K rows and no real load, so by preloading the tables on the active servers on the buffer pool it wasn't an issue.

@jcrespo: Since the s3 hosts are too slow, are there any other hosts that we can go ahead and test this on? Has s2 been reprovisioned?

Volans added a subscriber: Volans.Apr 6 2016, 6:49 PM

@kaldari: jcrespo is on vacation and what he said before still apply.

This means it will be blocked until the failover programmed for the 18 April.

@Volans: I asked two questions that haven't been answered. Perhaps you could help answer them?

@kaldari: all shards except s2 still have masters on Maria 5.5, we will have Maria 10 masters after the scheduled failover. Regarding s2 the reprovision is in progress (new hosts were added but all the old ones are still part of the cluster). That's why I was saying that what jcrespo said still apply given the current status and the fact that he's on vacation.

@Volans: Thanks for the info. I'll check up on things again after the 18th.

Volans closed blocking task T128353: Switchover to new s3 master as "Resolved".

Yay! Does this mean that work on this can continue after the datacenter-switch-back tomorrow? :)

jcrespo added a comment.EditedApr 22 2016, 9:51 AM

Yes, this has no blockers and can be done soon. Please give me some days, because we are still tuning/fixing things from the switchover/switchback. But I think it could be done next week (and subsequent requests, much faster).

jcrespo moved this task from Backlog to Next on the DBA board.Apr 22 2016, 9:52 AM
jcrespo moved this task from Next to In progress on the DBA board.May 3 2016, 8:03 AM
jcrespo changed the task status from Stalled to Open.May 3 2016, 10:03 AM
jcrespo raised the priority of this task from Normal to High.

Mentioned in SAL [2016-05-03T10:03:33Z] <jynus> applying schema change on codfw-s3 db servers T130692

Mentioned in SAL [2016-05-03T10:24:51Z] <jynus> applying schema change on eqiad-s3 db servers T130692

Mentioned in SAL [2016-05-03T13:18:20Z] <jynus> applying schema change on s3-master db T130692

jcrespo closed this task as Resolved.May 3 2016, 1:31 PM

Done for ruwiktionary, too:

$ for host in db1015 db1027 db1035 db1038 db1044 db1075 db1077 db1078; do mysql -h $host ruwiktionary -e "SHOW CREATE TABLE categorylinks\G"; done
*************************** 1. row ***************************
       Table: categorylinks
Create Table: CREATE TABLE `categorylinks` (
  `cl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `cl_to` varbinary(255) NOT NULL DEFAULT '',
  `cl_sortkey` varbinary(230) NOT NULL DEFAULT '',
  `cl_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `cl_sortkey_prefix` varbinary(255) NOT NULL DEFAULT '',
  `cl_collation` varbinary(32) NOT NULL DEFAULT '',
  `cl_type` enum('page','subcat','file') NOT NULL DEFAULT 'page',
  UNIQUE KEY `cl_from` (`cl_from`,`cl_to`),
  KEY `cl_timestamp` (`cl_to`,`cl_timestamp`),
  KEY `cl_sortkey` (`cl_to`,`cl_type`,`cl_sortkey`,`cl_from`),
  KEY `cl_collation_ext` (`cl_collation`,`cl_to`,`cl_type`,`cl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
*************************** 1. row ***************************
       Table: categorylinks
Create Table: CREATE TABLE `categorylinks` (
  `cl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `cl_to` varbinary(255) NOT NULL DEFAULT '',
  `cl_sortkey` varbinary(230) NOT NULL DEFAULT '',
  `cl_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `cl_sortkey_prefix` varbinary(255) NOT NULL DEFAULT '',
  `cl_collation` varbinary(32) NOT NULL DEFAULT '',
  `cl_type` enum('page','subcat','file') NOT NULL DEFAULT 'page',
  UNIQUE KEY `cl_from` (`cl_from`,`cl_to`),
  KEY `cl_timestamp` (`cl_to`,`cl_timestamp`),
  KEY `cl_sortkey` (`cl_to`,`cl_type`,`cl_sortkey`,`cl_from`),
  KEY `cl_collation_ext` (`cl_collation`,`cl_to`,`cl_type`,`cl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
*************************** 1. row ***************************
       Table: categorylinks
Create Table: CREATE TABLE `categorylinks` (
  `cl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `cl_to` varbinary(255) NOT NULL DEFAULT '',
  `cl_sortkey` varbinary(230) NOT NULL DEFAULT '',
  `cl_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `cl_sortkey_prefix` varbinary(255) NOT NULL DEFAULT '',
  `cl_collation` varbinary(32) NOT NULL DEFAULT '',
  `cl_type` enum('page','subcat','file') NOT NULL DEFAULT 'page',
  UNIQUE KEY `cl_from` (`cl_from`,`cl_to`),
  KEY `cl_timestamp` (`cl_to`,`cl_timestamp`),
  KEY `cl_sortkey` (`cl_to`,`cl_type`,`cl_sortkey`,`cl_from`),
  KEY `cl_collation_ext` (`cl_collation`,`cl_to`,`cl_type`,`cl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
*************************** 1. row ***************************
       Table: categorylinks
Create Table: CREATE TABLE `categorylinks` (
  `cl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `cl_to` varbinary(255) NOT NULL DEFAULT '',
  `cl_sortkey` varbinary(230) NOT NULL DEFAULT '',
  `cl_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `cl_sortkey_prefix` varbinary(255) NOT NULL DEFAULT '',
  `cl_collation` varbinary(32) NOT NULL DEFAULT '',
  `cl_type` enum('page','subcat','file') NOT NULL DEFAULT 'page',
  UNIQUE KEY `cl_from` (`cl_from`,`cl_to`),
  KEY `cl_timestamp` (`cl_to`,`cl_timestamp`),
  KEY `cl_sortkey` (`cl_to`,`cl_type`,`cl_sortkey`,`cl_from`),
  KEY `cl_collation_ext` (`cl_collation`,`cl_to`,`cl_type`,`cl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
*************************** 1. row ***************************
       Table: categorylinks
Create Table: CREATE TABLE `categorylinks` (
  `cl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `cl_to` varbinary(255) NOT NULL DEFAULT '',
  `cl_sortkey` varbinary(230) NOT NULL DEFAULT '',
  `cl_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `cl_sortkey_prefix` varbinary(255) NOT NULL DEFAULT '',
  `cl_collation` varbinary(32) NOT NULL DEFAULT '',
  `cl_type` enum('page','subcat','file') NOT NULL DEFAULT 'page',
  UNIQUE KEY `cl_from` (`cl_from`,`cl_to`),
  KEY `cl_timestamp` (`cl_to`,`cl_timestamp`),
  KEY `cl_sortkey` (`cl_to`,`cl_type`,`cl_sortkey`,`cl_from`),
  KEY `cl_collation_ext` (`cl_collation`,`cl_to`,`cl_type`,`cl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
*************************** 1. row ***************************
       Table: categorylinks
Create Table: CREATE TABLE `categorylinks` (
  `cl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `cl_to` varbinary(255) NOT NULL DEFAULT '',
  `cl_sortkey` varbinary(230) NOT NULL DEFAULT '',
  `cl_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `cl_sortkey_prefix` varbinary(255) NOT NULL DEFAULT '',
  `cl_collation` varbinary(32) NOT NULL DEFAULT '',
  `cl_type` enum('page','subcat','file') NOT NULL DEFAULT 'page',
  UNIQUE KEY `cl_from` (`cl_from`,`cl_to`),
  KEY `cl_timestamp` (`cl_to`,`cl_timestamp`),
  KEY `cl_sortkey` (`cl_to`,`cl_type`,`cl_sortkey`,`cl_from`),
  KEY `cl_collation_ext` (`cl_collation`,`cl_to`,`cl_type`,`cl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
*************************** 1. row ***************************
       Table: categorylinks
Create Table: CREATE TABLE `categorylinks` (
  `cl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `cl_to` varbinary(255) NOT NULL DEFAULT '',
  `cl_sortkey` varbinary(230) NOT NULL DEFAULT '',
  `cl_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `cl_sortkey_prefix` varbinary(255) NOT NULL DEFAULT '',
  `cl_collation` varbinary(32) NOT NULL DEFAULT '',
  `cl_type` enum('page','subcat','file') NOT NULL DEFAULT 'page',
  UNIQUE KEY `cl_from` (`cl_from`,`cl_to`),
  KEY `cl_timestamp` (`cl_to`,`cl_timestamp`),
  KEY `cl_sortkey` (`cl_to`,`cl_type`,`cl_sortkey`,`cl_from`),
  KEY `cl_collation_ext` (`cl_collation`,`cl_to`,`cl_type`,`cl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
*************************** 1. row ***************************
       Table: categorylinks
Create Table: CREATE TABLE `categorylinks` (
  `cl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `cl_to` varbinary(255) NOT NULL DEFAULT '',
  `cl_sortkey` varbinary(230) NOT NULL DEFAULT '',
  `cl_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `cl_sortkey_prefix` varbinary(255) NOT NULL DEFAULT '',
  `cl_collation` varbinary(32) NOT NULL DEFAULT '',
  `cl_type` enum('page','subcat','file') NOT NULL DEFAULT 'page',
  UNIQUE KEY `cl_from` (`cl_from`,`cl_to`),
  KEY `cl_timestamp` (`cl_to`,`cl_timestamp`),
  KEY `cl_sortkey` (`cl_to`,`cl_type`,`cl_sortkey`,`cl_from`),
  KEY `cl_collation_ext` (`cl_collation`,`cl_to`,`cl_type`,`cl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
bd808 moved this task from Ready to Q1 2018-19 on the Community-Tech-Sprint board.May 3 2016, 5:06 PM
DannyH moved this task from Untriaged to Archive on the Community-Tech board.May 10 2016, 4:59 PM
matmarex reopened this task as Open.May 13 2016, 6:35 PM

Right, we definitely need this on all wikis eventually, and as I understand it was only done for s3 wikis (https://noc.wikimedia.org/conf/highlight.php?file=s3.dblist)?

I believe it was only done for testwiki and ruwiktionary. Now that we've confirmed that the new index works and dramatically speeds up the script, let's go ahead and add it to all the production databases.

kaldari updated the task description. (Show Details)May 14 2016, 12:08 AM

jcrespo moved this task to Next on the DBA workboard.

Why is this "Next" when T69223 isn't?

jcrespo updated the task description. (Show Details)May 16 2016, 7:53 AM
jcrespo updated the task description. (Show Details)

Mentioned in SAL [2016-05-17T10:30:34Z] <jynus> running schema change on s1 T130692

Mentioned in SAL [2016-05-18T08:36:16Z] <jynus> performing schema change on s2 T130692

Mentioned in SAL [2016-05-19T07:17:30Z] <jynus> performing schema change on s4 T130692

Mentioned in SAL [2016-05-20T05:56:09Z] <volans> Killed transaction 3262258 on db1040 (alter table stuck in "Waiting for table metadata lock" blocking the replica) T130692

@jcrespo on wake up I found db1040 lagging 15k seconds behind, it's the only vslow/dump role in s4 shard and apparently was creating user-facing issues for some jobs that were not going ahead because of the lagged slave.

I've killed the alter table (see SAL above) and db1040 started recovering, I considered adding another slave as vslow/dump but the speed of recovery was good enough and in less than 5 minutes got recovered.

CCing @ori to add more context on the user-facing side, see attached an example of job ack rate that got stuck:

@jcrespo @faidon: that's why I don't like that a single lag doesn't page. I agree pages should be based on service issues, but if:

  • there is only 1 DB on a specific role
  • the service doesn't use it if it lags

until the service will page for that and/or we have 2 hosts for each role, I think that lag should page, maybe with a more higher threshold to avoid false positives. Thoughts?

For the specific DB issue some quick info from processlist:
(I have the engine innodb status in full if needed)

*************************** 4. row ***************************
      Id: 2911145
    User: system user
    Host:
      db: commonswiki
 Command: Connect
    Time: 15498
   State: Waiting for table metadata lock
    Info: INSERT /* LinksUpdate::incrTableUpdate 127.0.0.1 */ IGNORE INTO `categorylinks` (cl_from,cl_to,cl_so
Progress: 0.000
*************************** 9. row ***************************
      Id: 3262258
    User: root
    Host: 10.64.32.20:58446
      db: commonswiki
 Command: Query
    Time: 15499
   State: Waiting for table metadata lock
    Info: alter table categorylinks DROP INDEX cl_collation, ADD INDEX cl_collation_ext (cl_collation, cl_to,
Progress: 0.000

The alter table is now running on db1056, it's lag is stable between 5 and 15 seconds so far.

@jcrespo @faidon: that's why I don't like that a single lag doesn't page. I agree pages should be based on service issues, but if:

Your complain is legit. In a world where a single lagged host creates issues throughout the service (T135809) , pages should be sent. I wonder why people on the appropriate timezones did not respond to the error?

Change 289822 had a related patch set uploaded (by Jcrespo):
Revert "mariadb: set replication check's contact_group to admins"

https://gerrit.wikimedia.org/r/289822

Change 289822 merged by Jcrespo:
Revert "mariadb: set replication check's contact_group to admins"

https://gerrit.wikimedia.org/r/289822

Change 289825 had a related patch set uploaded (by Jcrespo):
Increase retries to 10 to avoid small bumps to alert

https://gerrit.wikimedia.org/r/289825

Change 289825 merged by Jcrespo:
Increase retries to 10 to avoid small bumps to alert

https://gerrit.wikimedia.org/r/289825

I've semi-reverted the previous state- I do not say things should stay like that, I am saying that that should be the initial state and then we could start the conversation.

Mentioned in SAL [2016-05-20T15:52:54Z] <jynus> performing schema change on s5 T130692

@jcrespo @faidon: that's why I don't like that a single lag doesn't page. I agree pages should be based on service issues, but if:

Your complain is legit. In a world where a single lagged host creates issues throughout the service (T135809) , pages should be sent. I wonder why people on the appropriate timezones did not respond to the error?

Probably because the only notification was a single message on IRC with a relatively small lag that could have been considered transient (time is in GMT+2):

Fri 03:29:11   icinga-wm| PROBLEM - MariaDB Slave Lag: s4 on db1040 is CRITICAL: CRITICAL slave_sql_lag Replication lag: 377.86 seconds

But I'm just guessing...

I have reverted the pages, as you can see, with a 10 loop check, that will not solve people not attending pages, but at least it will notify DBAs if the problem is sustained.

Mentioned in SAL [2016-05-22T14:02:40Z] <jynus> performing schema change on s6 T130692

Mentioned in SAL [2016-05-23T08:01:28Z] <jynus> performing schema change on s7 T130692

Mentioned in SAL [2016-05-23T15:13:35Z] <jynus> performing schema change on s3 T130692

jcrespo closed this task as Resolved.May 24 2016, 9:36 AM
jcrespo moved this task from Backlog to Done on the Blocked-on-schema-change board.

I've checked and, aside from a table on labsdb1001 that had failed (maybe during its crash a few days ago) -which has been subsequently done- this has been applied to all production, labs, analytics and support dbs on dblists according to its state a few days ago.

Change 298016 had a related patch set uploaded (by Mholloway):
Featured image card: enable sharing from card footer button

https://gerrit.wikimedia.org/r/298016