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

Difference between revisions of "MariaDB/Rebooting a host"

From Wikitech-static
Jump to navigation Jump to search
imported>Jcrespo
imported>Jcrespo
(→‎After boot: bullet points for second section)
 
(2 intermediate revisions by 2 users not shown)
Line 1: Line 1:
== Clean shutdown==
#First, get a list of instances on the host (see [[MariaDB/Multiinstance]] for details).
# Check if the host is used by mediawiki.<syntaxhighlight lang="shell">
sudo dbctl instance <instance> get
# E.g. for single-instance host:
#  sudo dbctl instance db1123 get
# E.g. for multi-instance host:
#  sudo dbctl instance db1102:3312 get
#  sudo dbctl instance db1102:3313 get
#  sudo dbctl instance db1102:3320 get
</syntaxhighlight>If any of the instances are known to dbctl, then the host will need to be depooled if it's in an active DC, and repooled afterwards.
#Downtime the host in icinga for 1h.<syntaxhighlight lang="shell">
sudo cookbook sre.hosts.downtime --hours 1 -r "Rebooting dbXXXX TXXXXXX" '<fqdn>'
</syntaxhighlight>If the host has any replicas, they will also need to be downtimed, to prevent replication alerts from firing.
# On the default config, MariaDB will dump its buffer pool index to disk, and load it automatically on start, [[MariaDB/buffer_pool_dump|decreasing its warmup period]]. If you want to avoid this (e.g. because the current buffer pool is not fully loaded), connect to each mysql instance and run:<syntaxhighlight lang="sql">
mysql> SET GLOBAL innodb_buffer_pool_dump_at_shutdown = OFF;
</syntaxhighlight>Dynamically doing this will make this option not persist, and revert to ON on next reboot.
# Stop mariadb instance(s) on the host<syntaxhighlight lang="shell">
# single-instance:
sudo systemctl stop mariadb
# multi-instance, for each section:
sudo systemctl stop mariadb@<section>
# E.g.:
#  sudo systemctl stop mariadb@s2
#  sudo systemctl stop mariadb@s3
#  sudo systemctl stop mariadb@x1
</syntaxhighlight>
#Unmount <code>/srv</code> and disable swap, and finally reboot the host<syntaxhighlight lang="shell">
sudo umount /srv
sudo swapoff -a
sudo reboot
</syntaxhighlight>
==After boot==
On most production hosts, the mariadb instance or instances '''won't restart automatically'''. This is intended behavior to prevent a crashed host to be pooled automatically with corrupt data or lag, before its health can be manually checked.
On most production hosts, the mariadb instance or instances '''won't restart automatically'''. This is intended behavior to prevent a crashed host to be pooled automatically with corrupt data or lag, before its health can be manually checked.


After a clean reboot, you can start mariadb by running:
* If you just did an upgrade, or other kind of dangerous maintenance, it is better to avoid an automatic buffer pool load on start up. To do so, '''rename the file on each data directory from ib_buffer_pool to ib_buffer_pool.bak''' This will make the old buffer pool unusable, while allowing a dump to be produced the next time it shuts down for a normal restart.
  systemctl start mariadb
 
of if it is a multi-instance host:
* If an upgrade is about to be done, also make sure mariadb doesn't start replication automatically by running:
  systemctl start mariadb@<section1>
 
  systemctl start mariadb@<section2>
systemctl set-environment MYSQLD_OPTS="--skip-slave-start"
 
* After a clean reboot, you can start mariadb by running:
  sudo systemctl start mariadb
or if it is a multi-instance host:
  sudo systemctl start mariadb@<section1>
  sudo systemctl start mariadb@<section2>


Where section is the sections that are setup on that particular server (<code>m1</code>, <code>x1</code>, etc.). Don't worry, only configured sections on puppet will start, others will fail to start if tried.
Where section is the sections that are setup on that particular server (<code>m1</code>, <code>x1</code>, etc.). Don't worry, only configured sections on puppet will start, others will fail to start if tried.


Replication should start automatically, which can be checked with:
* The prometheus mysql exporter also needs to be started:<syntaxhighlight lang="shell">
  mysql -e "SHOW SLAVE STATUS"
# Single-instance:
sudo systemctl start promtheus-mysqld-exporter
# Multi-instance, per instance:
sudo systemctl start promtheus-mysqld-exporter@<section>
</syntaxhighlight>
 
* Replication should be running, which can be checked with:
  sudo mysql -e "SHOW SLAVE STATUS"


(It should return <code>IO thread running: Yes / SQL thread running: Yes</code>)
(It should return <code>IO thread running: Yes / SQL thread running: Yes</code>)


If it is stopped and should be running, you can run:
If it is stopped and should be running, you can run:
  mysql -e "START SLAVE"
  sudo mysql -e "START SLAVE"


== If the server or the instance crashed ==
==If the server or the instance crashed==
* depool the host from production, if possible (dbctl, haproxy, etc.). If it is not possible, weight the impact of availability vs the possibility of exposing bad or outdated data (e.g. cache db vs enwiki primary server)
*depool the host from production, if possible (dbctl, haproxy, etc.). If it is not possible, weight the impact of availability vs the possibility of exposing bad or outdated data (e.g. cache db vs enwiki primary server)
* determine the root cause of the crash with os logs (syslog), hw logs (mgmt interface), etc.
*determine the root cause of the crash with os logs (syslog), hw logs (mgmt interface), etc.
* start the instance without replication starting automatically (<code>systemctl set-environment MYSQLD_OPTS="--skip-slave-start"</code>)
*start the instance without replication starting automatically (<code>systemctl set-environment MYSQLD_OPTS="--skip-slave-start"</code>)
* start mariadb
*start mariadb
* check the error log <code>journalctl -u mariadb</code> (or mariadb@<section>)
*check the error log <code>journalctl -u mariadb</code> (or mariadb@<nowiki><section>)</nowiki>
* do a table check comparing it to other host check (<code>db-compare</code>) to ensure all data is consistent between all servers of the same section
*do a table check comparing it to other host check (<code>db-compare</code>) to ensure all data is consistent between all servers of the same section
** Most production hosts have a configuration that makes them be durable on crash (<code>innodb_flush_log_at_trx_commit=1</code>). However, not all kinds of crash can ensure consistency (e.g. HW RAID controller failure)
**Most production hosts have a configuration that makes them be durable on crash (<code>innodb_flush_log_at_trx_commit=1</code>). However, not all kinds of crash can ensure consistency (e.g. HW RAID controller failure)
* If the sever looks good, start replication and repool it into service
*If the sever looks good, start replication and repool it into service


== In all cases, including normal restarts ==
==In all cases, including normal restarts==
{{warning|
{{warning|
After booting / rebooting a host, please ensure the following services are running and start them if they aren't, as they will fail to start due to mariadb having stopped/failed:
After booting / rebooting a host, please ensure the following services are running and start them if they aren't, as they will fail to start due to mariadb having stopped/failed:
Line 33: Line 81:
* [[MariaDB/pt-heartbeat|pt-heartbeat]] (only for active primary hosts, otherwise lag alerts will be fired)
* [[MariaDB/pt-heartbeat|pt-heartbeat]] (only for active primary hosts, otherwise lag alerts will be fired)
}}
}}
* systemctl restart <code>prometheus-mysqld-exporter</code> should do the trick. <code>prometheus-mysqld-exporter@<section></code> for multiinstance sections
*systemctl restart <code>prometheus-mysqld-exporter</code> should do the trick. <code>prometheus-mysqld-exporter@<nowiki><section></nowiki></code> for multiinstance sections


We should try not to reboot primary db instances for obvious reasons, and switch its active primary status beforehand, but that is sometimes done not by choice!
We should try not to reboot primary db instances for obvious reasons, and switch its active primary status beforehand, but that is sometimes done not by choice!


{{SRE/Data Persistence/Footer}}
{{SRE/Data Persistence/Footer}}
[[Category:MariaDB]]

Latest revision as of 13:15, 22 December 2021

Clean shutdown

  1. First, get a list of instances on the host (see MariaDB/Multiinstance for details).
  2. Check if the host is used by mediawiki.
    sudo dbctl instance <instance> get
    # E.g. for single-instance host:
    #   sudo dbctl instance db1123 get
    # E.g. for multi-instance host:
    #  sudo dbctl instance db1102:3312 get
    #  sudo dbctl instance db1102:3313 get
    #  sudo dbctl instance db1102:3320 get
    
    If any of the instances are known to dbctl, then the host will need to be depooled if it's in an active DC, and repooled afterwards.
  3. Downtime the host in icinga for 1h.
    sudo cookbook sre.hosts.downtime --hours 1 -r "Rebooting dbXXXX TXXXXXX" '<fqdn>'
    
    If the host has any replicas, they will also need to be downtimed, to prevent replication alerts from firing.
  4. On the default config, MariaDB will dump its buffer pool index to disk, and load it automatically on start, decreasing its warmup period. If you want to avoid this (e.g. because the current buffer pool is not fully loaded), connect to each mysql instance and run:
    mysql> SET GLOBAL innodb_buffer_pool_dump_at_shutdown = OFF;
    
    Dynamically doing this will make this option not persist, and revert to ON on next reboot.
  5. Stop mariadb instance(s) on the host
    # single-instance:
    sudo systemctl stop mariadb
    # multi-instance, for each section:
    sudo systemctl stop mariadb@<section>
    # E.g.:
    #  sudo systemctl stop mariadb@s2
    #  sudo systemctl stop mariadb@s3
    #  sudo systemctl stop mariadb@x1
    
  6. Unmount /srv and disable swap, and finally reboot the host
    sudo umount /srv
    sudo swapoff -a
    sudo reboot
    

After boot

On most production hosts, the mariadb instance or instances won't restart automatically. This is intended behavior to prevent a crashed host to be pooled automatically with corrupt data or lag, before its health can be manually checked.

  • If you just did an upgrade, or other kind of dangerous maintenance, it is better to avoid an automatic buffer pool load on start up. To do so, rename the file on each data directory from ib_buffer_pool to ib_buffer_pool.bak This will make the old buffer pool unusable, while allowing a dump to be produced the next time it shuts down for a normal restart.
  • If an upgrade is about to be done, also make sure mariadb doesn't start replication automatically by running:
systemctl set-environment MYSQLD_OPTS="--skip-slave-start"
  • After a clean reboot, you can start mariadb by running:
sudo systemctl start mariadb

or if it is a multi-instance host:

sudo systemctl start mariadb@<section1>
sudo systemctl start mariadb@<section2>

Where section is the sections that are setup on that particular server (m1, x1, etc.). Don't worry, only configured sections on puppet will start, others will fail to start if tried.

  • The prometheus mysql exporter also needs to be started:
    # Single-instance:
    sudo systemctl start promtheus-mysqld-exporter
    # Multi-instance, per instance:
    sudo systemctl start promtheus-mysqld-exporter@<section>
    
  • Replication should be running, which can be checked with:
sudo mysql -e "SHOW SLAVE STATUS"

(It should return IO thread running: Yes / SQL thread running: Yes)

If it is stopped and should be running, you can run:

sudo mysql -e "START SLAVE"

If the server or the instance crashed

  • depool the host from production, if possible (dbctl, haproxy, etc.). If it is not possible, weight the impact of availability vs the possibility of exposing bad or outdated data (e.g. cache db vs enwiki primary server)
  • determine the root cause of the crash with os logs (syslog), hw logs (mgmt interface), etc.
  • start the instance without replication starting automatically (systemctl set-environment MYSQLD_OPTS="--skip-slave-start")
  • start mariadb
  • check the error log journalctl -u mariadb (or mariadb@<section>)
  • do a table check comparing it to other host check (db-compare) to ensure all data is consistent between all servers of the same section
    • Most production hosts have a configuration that makes them be durable on crash (innodb_flush_log_at_trx_commit=1). However, not all kinds of crash can ensure consistency (e.g. HW RAID controller failure)
  • If the sever looks good, start replication and repool it into service

In all cases, including normal restarts

  • systemctl restart prometheus-mysqld-exporter should do the trick. prometheus-mysqld-exporter@<section> for multiinstance sections
  • We should try not to reboot primary db instances for obvious reasons, and switch its active primary status beforehand, but that is sometimes done not by choice!



    This page is a part of the SRE Data Persistence technical documentation
    (go here for a list of all our pages)