You are browsing a read-only backup copy of Wikitech. The primary site can be found at wikitech.wikimedia.org

MariaDB/troubleshooting

From Wikitech-static
< MariaDB
Revision as of 10:55, 18 March 2019 by imported>Marostegui (Added section on how to depool a Labs dbproxy)
Jump to navigation Jump to search
DBAs are working on it

This guide is a work in progress.

Depooling a slave

Checkout the mediawiki-config repo if you haven't already:

git clone ssh://yourusername@gerrit.wikimedia.org:29418/operations/mediawiki-config
cd mediawiki-config
vim wmf-config/db-eqiad.php

Assuming eqiad is the active DC

  • A replica can run just one MySQL instance
  • If a replica runs more than one MySQL instance, it will be specified in its definition with the following format
hostname:port

Example

db1090:3317

This means db1090 has an instance running on 3317 (and probably others in different ports). The port is formed in the following way

3306 -> MySQL standard port
331X -> X meaning the section where the server belongs to. 
1 -> s1
2 -> s2
..
8 -> s8
3320 -> x1
3321 -> m1
3322 -> m2
3323 -> m3
3324 -> m4
3325 -> m5

So db1090:3317 is a replica that runs on s7

  • A replica can handle more than one type of traffic.

These are the types of traffic we have

main
recentchanges
api
vslow

They are normally specified on the definition files, let's use db-eqiad.php as it is the active datacenter Let's examine s7 where db1090 belongs to: This is the main traffic section where the servers and weights are specified. Servers with weight 1 normally means they are still checked for lag by the MediaWiki LB, but the have almost no main traffic, because they are special replicas and they have more weight for special traffic (check below)

's7' => [
'db1062' => 0,      # D4 2.8TB 128GB, master
'db1079' => 300,    # A2 3.6TB 512GB, api # master for sanitarium db1125
'db1086' => 400,    # B3 3.6TB 512GB, api # candidate master
'db1090:3317' => 1, # C3 3.6TB 512GB, vslow, dump: s2 and s7, old master
'db1094' => 500,    # D2 3.6TB 512GB
'db1098:3317' => 1, # B5 3.6TB 512GB # rc, log: s6 and s7
'db1101:3317' => 1, # C2 3.6TB 512GB # rc, log: s7 and s8

And there is also a special slaves section below, look for it: db1090:3317 is a single vslow slave db1079 and db1086 share API traffic, db1079 has more weight for API because db1086 has more weight for main traffic (see above) db1098:3317 db1101:3317 are recentchanges replicas and they share the same amount of load for all those special services.

's7' => [
 'vslow' => [
  'db1090:3317' => 1,
 ],
 'dump' => [
  'db1090:3317' => 1,
],
 'api' => [
  'db1079' => 3,
  'db1086' => 1,
],
 'watchlist' => [
  'db1098:3317' => 1,
  'db1101:3317' => 1,
],
 'recentchanges' => [
  'db1098:3317' => 1,
  'db1101:3317' => 1,
],
 'recentchangeslinked' => [
  'db1098:3317' => 1,
  'db1101:3317' => 1,
],
 'contributions' => [
  'db1098:3317' => 1,
  'db1101:3317' => 1,
],
 'logpager' => [
  'db1098:3317' => 1,
  'db1101:3317' => 1,
],
],


Locate the broken slave - keep in mind it can be in more than one section. The easiest way is probably to look for it on the file. For this example let's assume db1090:3317 is broken with replication broken, but its other instance (3312) is working fine, so no need to depool it.

This host is a vslow host, so we need to replace it with another host from the same section. Let's chose db1079 to replace it.

If the host is a special host and servers some special traffic (vslow,api, recentchanges, it needs to be replaced if it is the only one, we cannot leave a section without a slave)

We need to look for it and comment it out from every single line where db1090:3317 appears. However DO NOT COMMENT the line with its definition which is already really explicit:

    'db1090:3317' => '10.64.32.116:3317', # do not remove or comment out

So this should be the diff once we are ready to commit: https://gerrit.wikimedia.org/r/#/c/operations/mediawiki-config/+/447984/1/wmf-config/db-eqiad.php

git add wmf-config/db-eqiad.php
git commit
git review

Once we have +2 on gerrit and the change has been merged automatically:

ssh deployment.eqiad.wmnet
cd /srv/mediawiki-staging/
git fetch ; git rebase
git show (this should show our change)
scap sync-file wmf-config/db-eqiad.php "Depool db1090:3317 as it is broken"

Once the change is deployed, we should be able to see our change on: https://noc.wikimedia.org/conf/highlight.php?file=db-eqiad.php

If a host is completely down and it has multiple instances it needs to be depooled from all the instances Let's say db1090 had a hardware crash and it is powered off, that means that both instances 3312 and 3317 are down. We need to also depool the instance db1090:3312 with the same method as described above.

Create a task with the DBA tag so DBAs can follow up and checkout what happened, a proper fix etc

  • Monitor that mysql connections to that host slowly drop as soon as the queries finish. For that, connect to that host and use mysql's SHOW PROCESSLIST; and check there are no wikiuser or wikiadmin connctions. You can also monitor connections with regular linux tools like netstat/ss on port 3306 (or the right mysql port). Monitoring tools regularly check the host, but they use separate users.

Example:

MariaDB PRODUCTION x1 localhost (none) > SHOW PROCESSLIST;
+---------+-----------------+-------------------+--------------------+---------+-
| Id      | User            | Host              | db                 | Command | 
+---------+-----------------+-------------------+--------------------+---------+-
# internal process, ignore
|       2 | event_scheduler | localhost         | NULL               | Daemon  | 
# replication users, ignore
| 3192579 | system user     |                   | NULL               | Connect | 
| 3192580 | system user     |                   | NULL               | Connect | 
# monitoring users, ignore
| 6284249 | watchdog        | 10.XX.XX.XX:34525 | information_schema | Sleep   | 
| 6284250 | watchdog        | 10.XX.XX.XX:34716 | information_schema | Sleep   | 
| 6284253 | watchdog        | 10.XX.XX.XX:34890 | mysql              | Sleep   | 
# this is your own connection
| 6311084 | root            | localhost         | NULL               | Query   | 
+---------+-----------------+-------------------+--------------------+---------+-

(no wikiuser or wikiadmin processes, ok to do maintenance, kill the machine, etc.)

  • Except the dump slave -while creating the dumps- or some specific maintenance or long running tasks, connections should go away in seconds/very few minutes. If there is an emergency- killing the process (KILL <#ID>) is the way to go. Selects are ok to kill, writes and alters can create worse issues due to rollback process kicking in- be sure what you kill. Sadly there are some times where idle conenctions keep connected for a long time.

Depooling a master (a.k.a. promoting a new slave to master)

See: Switch master (most of it still relevant).

Planned switchover

There is a script, switchover.py https://phabricator.wikimedia.org/diffusion/OSMD/browse/master/wmfmariadbpy/switchover.py , to be run from a Wikimedia mysql root client (cumin1001 or cumin2001 at the moment), which will automate the most complex steps. However, due to mediawiki dependencies, we still need at the moment to perform some extra steps:

  • Set mediawiki in read only for that master (if possible) or migrate the service away. Normally that is a line uncommenting on db-eqiad.php or db-codfw.php:
'readOnlyBySection' => [
        's1'      => 'English Wikipedia in read only because reasons.',

... pointing the parsercache to another host:

$wmgParserCacheDBs = [
     '10.64.0.12'   => '10.64.32.72',   # pc1004, A3 2.4TB 256GB, temporarily failed over to pc1005 
     '10.64.32.72'  => '10.64.32.72',  # pc1005, C7 2.4TB 256GB

... or depooling it:

$wgDefaultExternalStore = [
       'DB://cluster24',
       # 'DB://cluster25',
];

Once that is deployed, execute switchover.py, with the original master and the target one as parameters:

./switchover.py db1052 db1067

This is an example of a successful output:

Starting preflight checks...
* Original read only values are as expected (master: read_only=0, slave: read_only=1)
* The host to fail over is a direct replica of the master
* Replication is up and running between the 2 hosts
* The replication lag is acceptable: 0 (lower than the configured or default timeout)
* The master is not a replica of any other host
----- OUTPUT of '/bin/ps --no-hea...pid,args -C perl' -----                                                                          
 6313 /usr/bin/perl /usr/local/bin/pt-heartbeat-wikimedia --defaults-file=/dev/null --user=root --host=localhost -D heartbeat --shard=es3 --datacenter=eqiad --update --replace --interval=1 --set-vars=binlog_format=STATEMENT -S /tmp/mysql.sock --daemonize --pid /var/run/pt-heartbeat.pid
================                                                                                                                     
PASS:  |████████████████████████████████████████████████████████████████████████████████| 100% (1/1) [00:00<00:00,  4.23hosts/s]     
FAIL:  |                                                                                        |   0% (0/1) [00:00<?, ?hosts/s]     
100.0% (1/1) success ratio (>= 100.0% threshold) for command: '/bin/ps --no-hea...pid,args -C perl'.
100.0% (1/1) success ratio (>= 100.0% threshold) of nodes successfully executed all commands.
Stopping heartbeat pid 6313 at es1014.eqiad.wmnet:3306/(none)
----- OUTPUT of '/bin/kill 6313' -----                                                                                               
================                                                                                                                     
PASS:  |████████████████████████████████████████████████████████████████████████████████| 100% (1/1) [00:00<00:00,  4.46hosts/s]     
FAIL:  |                                                                                        |   0% (0/1) [00:00<?, ?hosts/s]     
100.0% (1/1) success ratio (>= 100.0% threshold) for command: '/bin/kill 6313'.
100.0% (1/1) success ratio (>= 100.0% threshold) of nodes successfully executed all commands.
Setting up original master as read-only
Slave caught up to the master after waiting 0.010378122329711914 seconds
Servers sync at master: es1014-bin.002508:184384418 slave: es1017-bin.002491:41215873
Stopping original master->slave replication
Setting up replica as read-write
All commands where successful, current status: original master read_only: 1 / original slave read_only: 0
Trying to invert replication direction
Starting heartbeat section es3 at es1017.eqiad.wmnet
----- OUTPUT of '/usr/bin/nohup /...d &> /dev/null &' -----                                                                          
================                                                                                                                     
PASS:  |████████████████████████████████████████████████████████████████████████████████| 100% (1/1) [00:00<00:00,  3.29hosts/s]     
FAIL:  |                                                                                        |   0% (0/1) [00:00<?, ?hosts/s]     
100.0% (1/1) success ratio (>= 100.0% threshold) for command: '/usr/bin/nohup /...d &> /dev/null &'.
100.0% (1/1) success ratio (>= 100.0% threshold) of nodes successfully executed all commands.
----- OUTPUT of '/bin/ps --no-hea...pid,args -C perl' -----                                                                          
12107 /usr/bin/perl /usr/local/bin/pt-heartbeat-wikimedia --defaults-file=/dev/null --user=root --host=localhost -D heartbeat --shard=es3 --datacenter=eqiad --update --replace --interval=1 --set-vars=binlog_format=STATEMENT -S /run/mysqld/mysqld.sock --daemonize --pid /var/run/pt-heartbeat.pid
================                                                                                                                     
PASS:  |████████████████████████████████████████████████████████████████████████████████| 100% (1/1) [00:00<00:00,  3.22hosts/s]     
FAIL:  |                                                                                        |   0% (0/1) [00:00<?, ?hosts/s]     
100.0% (1/1) success ratio (>= 100.0% threshold) for command: '/bin/ps --no-hea...pid,args -C perl'.
100.0% (1/1) success ratio (>= 100.0% threshold) of nodes successfully executed all commands.
Detected heartbeat at es1017.eqiad.wmnet running with PID 12107
Verifying everything went as expected...
SUCCESS: Master switch completed successfully

This will move the replicas below the other host, and perform the replication changes to migrate the service, while maintaining data consistency. Note that at the moment of writing this lines, the replica migration has issues, so you should do it manually with repl.pl (which stops and restart replication and only works for direct working replicas). Thus, you should execute switchover skipping that step:

./switchover.py --skip-slave-move db1052 db1067

Finally, set the service back in read-write/update master configuration by deploying mediawiki.

A checklists of things to do or check after a successful switchover:

  • You can perform an edit on the section you just swichover
  • No further errors on logstash (there will be some that are unavoidable due to the read only period)
  • Semi-sync is enabled on new master and disabled on old master
  • Make sure tendril and zarcillo (dbtree, dbmonitor) have the correct master on its database (in the future this should happen automatically by switchover.py)
  • Update dns example :https://gerrit.wikimedia.org/r/#/c/operations/dns/+/439533/ (these dns aliases are not used)
  • Patch prometheus, dblists example: https://gerrit.wikimedia.org/r/#/c/operations/software/+/439534/ (this should happen automatically in the future, based on zarcillo)
  • Enable GTID on all the replicas, make sure the master is not replicating from anywhere
  • Create decommissioning ticket for OLD host, if necessary
  • Ensure all replicas and masters have the right events on the ops database (events_coredb_slave.sql, events_coredb_master.sql) (in the future this should happen automatically by switchover.py)
  • Update/resolve phabricator ticket about failover

A full list of manual steps can be found at: MariaDB#Production_section_failover_checklist

Emergency failover

If the master is not available, or replication is broken, this is a more complex case. The reasons is that slaves will have executed different amount of transactions and will be in a close, but different state. E.g. slave1 has executed transactions A, while slave2 has executed transactions A, B and C. In addition to that, if we do not have access to the master's binary log (or it has not properly been synchronized to disk after a crash), we will have to recover from a slave. In theory, with semi-sync replication, no transaction will be lost, and at least one slave will have the change, but all other slave will be on different coordinates (and binary log position is only local to the masters).

Scenario 1 -master is recoverable: just wait until the master restarts, it will avoid headaches and be faster and less disruptive than trying to failover it.

Scenario 2 -master is not recoverable, but its binary log is (and all slaves have less or equal amount of data):

  1. For each slave: send the master log position, starting from the last Exec_master_position so all slaves are in the same starting state
  2. Follow regular failover steps as mentioned in the scheduled maintenance

Scenario 3 -neither master is recoverable nor its binary logs (or a master binary log is behind a slave binary log): We need to put all servers in the same state, using the most up-to-date slave, then perform the regular failover process. This is the most complicated part without using GTIDs:

  1. Identify the most up to date slave by comparing Exec_master_log_pos
  2. By comparing binary log positions, try to find the binlog coordinate that corresponds to the the other's slaves binlog to the most up to date slave's binlog. This is the tricky part. pt-heartbeat should be able to find this.
  3. Excute the pending transactions on each slave
  4. Follow the regular steps for regular scheduled maintenance

Again, these steps can be automatized.

Replication lag

See also MySQL#Replication lag for additional tips.

Caused by hardware

This is what a half-failing disks looks like in monitoring (small lag until it becomes critical).

One common cause of lag that is easy to check and repair is hardware issues.

Disks about to fail

Databases have a lot (and I mean a lot) of IO pressure, and while it is not insane, it means that 3-year old drives are very prone to fail.

As an operator, you are already familiar with the way drives fail (not very reliably, to be honest). All important databases have a hardware RAID, which means 1 disk can fail at a time, usually with very little impact. When that happens, the icinga alert "1 failed LD(s) (Degraded)" should tell you it is time to replace at least one disk. Usually there are spares onsite or the servers are under warranty, which means you can create a ticket to ops-eqiad or ops-codfw and let Chris or Papaul know that should take it off and insert a new one, the hw RAID should automatically reconstruct itself.

To check the RAID status, execute:

 megacli -AdpAllInfo -aALL

And check the section "Devices present"

To identify the particular disk

 megacli -PDList -aALL

Check in particular for the Firmware State (on or off), the S.M.A.R.T alerts, and the number of medium errors (a few, like a dozen, should not affect much performance, but when there are hundreds of errors in a short timespan, that is an issue).

Sadly, disks fail in a very creative way, and while our RAIDs controllers are reliable enough to 1) continue despite medium errors and 2) disable the disk when it fails completely; in a state of "almost failing", there could be lag issues. If that is the case, executing:

 megacli -PDOffline -PhysDrv \[#:#\] -aALL

where #:# is enclosure:slot, will take the particular physical drive offline so that it can be replaced later.

Bad or defective BBU

If all the disks are looking good, it can be that the RAID controller went to WriteThrough mode because of a failed BBU or because it is in a learning cycle (which shouldn't because it is disabled in our environment). If the Cache Policy is set to WriteThrough it will dramatically affect performance. In order to check the Current Policy (that is it, the active one):

megacli -LDInfo -LAll -aAll | grep "Cache Policy:"

If it is not in WriteBack mode, it means (most likely) that the BBU has failed for some reason and the default is to switch back to WriteThrough as it is safer. You can check the BBU status with:

megacli -AdpBbuCmd -GetBbuStatus -a0 | grep -e '^isSOHGood' -e '^Charger Status' -e '^Remaining Capacity' -e 'Charging'

If you are in an emergency, you can always force WriteBack, but this can lead to data loss if there is a power failure, so use it carefully

megacli -LDSetProp -ForcedWB -Immediate -Lall -aAll

Data loss

Cloning a slave

The best (and fastest) way to repair a slave (or a master) is to clone from another slave. Either stop a slave (after depooling it) and just send the files away or use xtrabackup to clone it. The rest of the techniques assume that the data loss is fleet-wide (a logical/application/DBA error, rather than a physical problem).

Backups

There are weekly logical backups in /srv/backups/latest on es2001 (for 2 weeks) and in bacula (for 30 days, aiming for 90 with a future expansion). They are very slow to recover (it can take 7-10 hours to recover a full shard), so think twice before recovering fully from them- use instead a binary backup or use them for partial recoveries. See MariaDB/Backups for more details on how they are created and how to recover them.

Data inconsistency between nodes

Depooling a Labs dbproxy

The first thing is to depool it from the Wiki Replicas DNS Once that is done and if you are depooling dbproxy1010 all the traffic will go to dbproxy1011 which only has one server as active. The other one is a backup host as can be seen at the hiera file that lives on our puppet repo:

cat hieradata/hosts/dbproxy1011.yaml
profile::mariadb::proxy::master::primary_name: 'labsdb1009'
profile::mariadb::proxy::master::primary_addr: '10.64.4.14:3306'
profile::mariadb::proxy::master::secondary_name: 'labsdb1010'
profile::mariadb::proxy::master::secondary_addr: '10.64.37.23:3306'

That means when dbproxy1010 is depooled, all its traffic will go to labsdb1009. So it is advised to change haproxy configuration temporary to make labsdb1010 also active (round robin dns). To do so:

ssh dbproxy1011
puppet agent --disable "Changing haproxy temporarily"
vim /etc/haproxy/conf.d/db-master.cfg

Replace the line:

server labsdb1010 10.64.37.23:3306 check backup

With:

server labsdb1010 10.64.37.23:3306 check inter 3s fall 20 rise 99999999

Reload HAProxy

systemctl reload haproxy