nyuuzyou/wikis huggingface dataset:
Maps/Tile storage

=== Setting up Cassandra tile storage ===
* On first start of a Cassandra node, it is necessary to clean the data directory rm -rf /srv/cassandra/*. See Cassandra documentation for more details.
*Also remember to touch /etc/cassandra/service-enabled before starting Cassandra on each node. See https://phabricator.wikimedia.org/T214166
* A new Maps cluster should have only one node in its Cassandra seeds configuration and so will not connect to other hosts to bootstrap when the service starts. See this example hiera configuration for a new single-node cluster. 
* change the cassandra super user password to match the one configured in private repo using cqlsh:
 cqlsh `hostname -f` -u cassandra
 Password: cassandra
 ALTER USER cassandra WITH PASSWORD '<password>';
* Setup of user access / rights for cassandra
 cat /usr/local/bin/maps-grants.cql | cqlsh --cqlshrc=/etc/cassandra/cqlshrc `hostname -f` -
* Setup replication of Cassandra system_auth according to documentation. We simply set a replication factor of 2 in maps at present: ALTER KEYSPACE system_auth WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '2'};
*Very important point to note is there's a catch when setting up replicator factor for system_auth keyspace on cassandra. We recently discovered that increasing replication factor according to docs causes outage on cassandra. See T214434 and T157354. Also this: Incident documentation/20190122-maps.
* Initial creation of cassandra keyspace: To prevent accidental modification of schema, Tilerator source configuration does not allow to create schema by default. The sources file used by tilerator / kartotherian is configured in /etc/(kartotherian|tilerator|tileratorui)/config.yaml, look for the sources: key. This is a reference to a sources file in the kartotherian / tilerator source directory. For example /srv/deployment/tilerator/deploy/src/sources.prod2.yaml.
The easiest way to create a new keyspace is to run Tilerator with a custom sources file, which instructs tilerator to create the missing keyspace. For example, create a temporary file, e.g. /home/yurik/my-source-file with the following configuration (change v4 with the keyspace declared in the sources configuration file):
v4:
 uri: cassandra://
 params:
 keyspace: v4
 cp: {var: cassandra-servers}
 username: {var: cassandra-user}
 password: {var: cassandra-pswd}
 # These parameters are only used if keyspace needs to be created:
 repfactor: 4
 durablewrite: 0
 createIfMissing: true
 copyInfoFrom: {ref: gen}
And run this bash script:
sudo -u tilerator node /srv/deployment/tilerator/deploy/node_modules/@wikimedia/tilerator/scripts/tileshell.js \
 # Use TileratorUI configuration (including password variables)
 --config /etc/tileratorui/config.yaml \
 # Use this sources file instead of the one in the config file
 --source ~/my-source-file.yaml
=== Bulk Copying ===
The fastest way to copy a large number of tiles from one source to another is to use a large number of parts and specify saveSolid=true (skips solid tile detection). E.g. to copy all z16 tiles from v3 to v4, use src=v3 dst=v4 zoom=16 parts=60 saveSolid=true
Maps/v2/Infrastructure

== Infrastructure ==
=== Notes ===
==== Maps nodes ====
* eqiad cluster
** maps{1005..1010}.eqiad.wmnet
*** master node: 1009
*** read replicas: 1005,1006,1006,1008,1010
* codfw
** maps{2005..2010}.codfw.wmnet
*** master node: 1009
*** read replicas: 1005,1006,1006,1008,1010
Each of the nodes runs:
* Postgres/Postgis
* Kartotherian
* (Deprecated) Redis + Cassandra + Tilerator
** Tilerator will be phased out when Tegola is ready for production traffic
For master nodes specifically we also run:
* OpenStreetMap data import script backed by imposm3
** The PostgresSQL DB receives writes only on master nodes the rest of the access is read-only.
==== Kubernetes ====
We currently host tegola on kubernetes
* codfw deploymet --> maps codfw postgres read replicas
* eqiad deployment --> maps eqiad postgres read replicas
=== Diagram ===
Category:Maps V2
Maps/v2/Logs

=== Log files in the server ===
tail -f /srv/log/tilerator/main.log 
tail -f /srv/log/kartotherian/main.log 
tail -f /var/log/postgresql/postgresql-11-main.log
# imposm logs for OSM sync
sudo journalctl -u imposm -f
# tile generation trigger for Tilerator
tail -f /var/log/imposm/imposm.log 
=== Kartotherian ===
* Logstash dashboards
** eqiad
** codfw
=== Tegola server ===
* Logstash dashboards
** staging
** eqiad
** codfw
=== Tegola pregeneration ===
* Logstash dashboards
** codfw
** eqiad
Category:Maps V2
Maps/v2/Metrics

== Metrics ==
=== Maps nodes ===
==== Host overview ====
* Grafana dashboards
** eqiad
*** maps1005
*** maps1006
*** maps1007
*** maps1008
*** maps1009
*** maps1010
** codfw
*** maps2005
*** maps2006
*** maps2007
*** maps2008
*** maps2009
*** maps2010
=== Tegola ===
==== App metrics ====
Tegola exposes app level metrics as prometheus metrics.
* Grafana dashboards
** Staging
** Eqiad prod
** Codfw prod
=== Kartotherian ===
Overall metrics for kartotherian and tilerator are available in the following grafana dashboards:
* eqiad
* codfw
Category:Maps V2
MariaDB/Backups/Example recovery

== Data recovery plan ==
1) Recover up to the drop on db1095: db1109-bin.003832:611995336
 transfer.py --type=decompress dbprov1001.eqiad.wmnet:/srv/backups/snapshots/latest/snapshot.s8.2020-04-05--19-00-02.tar.gz db1095.eqiad.wmnet:/srv/sqldata.s8 # [DONE]
* Setup new instance: https://gerrit.wikimedia.org/r/c/operations/puppet/+/587010 [DONE]
* Start server [DONE]
* setup replication
 CHANGE MASTER TO MASTER_HOST='db1109.eqiad.wmnet', MASTER_USER='repl', MASTER_PASSWORD=, MASTER_SSL=1; -- requires right password [DONE]
GTID stuff on file, check replication works.
 SET GLOBAL gtid_slave_pos = ; -- requires a good gtid pos [DONE]
* Disable gtid out of precaution
 CHANGE MASTER TO MASTER_USE_GTID=no; -- [DONE]
* Start replication until (before) delete
 START SLAVE UNTIL MASTER_LOG_FILE = 'db1109-bin.003832', MASTER_LOG_POS = 611995336; -- [DONE]
* Disable consitency options:
 SET GLOBAL innodb_flush_log_at_trx_commit=0; -- [DONE]
* Add to zarcillo so it can be monitored on prometheus [DONE]
2) Stop replication fully
 STOP SLAVE; [DONE]
3) Rename the table to _old
 set sql_log_bin=0; RENAME TABLE wb_items_per_site TO wb_items_per_site_recovered; [DONE]
4) Dump the table in 10 chunks:
dbprov1001# dbprov1001# backup-mariadb s8_backup --type=dump --host=db1095.eqiad.wmnet --port=3318 --backup-dir /srv/backups/dumps/ongoing --regex='wikidatawiki[.]wb\_items\_per\_site' --rows=1000000 --user=dump --retention=15 --password= # requires a working --password [DONE]
The right dump is /srv/backups/dumps/ongoing/dump.s8_backup.2020-04-07--10-09-21
5) Stop replication on:
* codfw
* labs
* Analytics
6) Recover on eqiad production hosts without replication:
 myloader --directory /srv/backups/dumps/ongoing/dump.s8_backup.2020-04-07--10-09-21 --threads=10 --host={} --port={} --user=root --password= # requires working --password
 myloader --directory /srv/backups/dumps/ongoing/dump.s8_backup.2020-04-07--10-09-21 --threads=10 --host=db1087.eqiad.wmnet --user=root --password= --enable-binlog # on db1087
(list of servers gotten from zarcillo with the section bash oneliner)
# db1116:3318 (backup source, delayed) DONE
# db2079 (codfw master, delayed) DONE
# db1095:3318 (NOT TO BE DONE, delayed, instance to be removed)
# db1124:3318 (NOT TO BE DONE, will be done on its master)
# db1099:3318 DONE
# db1101:3318 DONE
# db1126 DONE
# db1104 DONE
# db1092 DONE
# db1087 (WITH REPLICATION) DONE
# db1111 DONE
# dbstore1005:3318 (analytics) DONE
# db1109 (master) DONE
7) drop? table and rename old to new:
 - Set the master on RO for some time
 db1109> set global read_only=1; RENAME TABLE wb_items_per_site TO wb_items_per_site_old; RENAME TABLE wb_items_per_site_recovered TO wb_items_per_site; set global read_only=0;
* Main issue: metadata locking on the slaves
** Depooling hosts (a bunch of them at the time)
** Repooling them after rename
** Depooling the ones that get metadata locked again
MariaDB/Changing user passwords

How to rotate user passwords. Under construction.
== wikiuser and wikiadmin ==
* clone https://gitlab.wikimedia.org/ladsgroup/db-password-rotation in cumin
* Pick a new username and passowrd, usually the year you are doing it. Like wikiuser2023, if that's taken, add the month, like wikiuser202305.
* Run omg.py to get new report of grants.
* Run user_duplicator.py to duplicate the user and all the grants (edit it beforehand to set the users)
* Update the username and password in mediawiki. In deploy1002, /srv/mediawiki-staging/private/PrivateSettings.php
** Sync it with 'scap sync-file private/PrivateSettings.php 'Rotating wikiuser''
* Make patches to update username in operations/software
* Make patch in puppet to update the username
* Merge the puppet change with a patch made in private puppet to update the passwords
* (not needed for wikiadmin) Update query killer events via running update_events.sh
* Wait for a while and then run drop_old_user.py (update the username in the code)
* update labstestwiki
MariaDB/PII

This page describes the procedure for removing Personally Identifiable Information (PII) from Wiki Replicas.
This procedure must be followed every time a new wiki is added to the database. The full procedure for adding a new wiki is described at Add a wiki.
== Sanitize the Wiki Data == 
Note: As of Jun 2024 - the default section for new wikis is s5 but this might not be the case in the future. 
Go to the sanitarium hosts in both data centers and run the following command to clean up the data:
 redact_sanitarium.sh -d $NEW_WIKI_NAME -S /run/mysqld/mysqld.s5.sock | mysql -S /run/mysqld/mysqld.s5.sock
== Run the Private Data Check Script ==
Execute the check_private_data.py script to identify which table/columns need to be dropped:
 check_private_data.py -S /run/mysqld/mysqld.s5.sock
If the output makes sense, you can pipe it directly to MySQL to drop the necessary table/columns:
 check_private_data.py -S /run/mysqld/mysqld.s5.sock | mysql -S /run/mysqld/mysqld.s5.sock
== Verify changes on each wikireplicas host ==
Ensure that you run check_private_data.py on each replicas host after the whole process on your side is done to avoid leaking Personally Identifiable Information:
 check_private_data.py -S /path/to/socket
== Grant Permissions for SQL Views ==
Identify the wikireplicas hosts that belong to the relevant database section (e.g., s5). Grant the labsdbuser role the appropriate grants by running:
 GRANT SELECT, SHOW VIEW ON `NEW_WIKI_NAME_p`.* TO `labsdbuser`;
This needs to be done on all wikireplicas hosts that have the relevant database section.
== Create the View Database ==
Create the view database on all the wikireplicas hosts that belong to the relevant database section:
 CREATE DATABASE NEW_WIKI_NAME_p;
== Notify Relevant Teams ==
Once the database is sanitized and the view database is created, the work managed by DBAs is complete.
The next step is to create the views, and that is managed by the Wikimedia Cloud Services team.
Until we define a better process, assign the task to fnegri.
'''
MariaDB/Primary switchover

== Prerequisites shared for all MW sections ==
(The following points apply to planned MW switchovers only, skip directly to section links below when responding to an incident)
* Create a switchover task (see an example task or use the script to auto generate the MW switchover task
* Schedule the switchover for one of the two weekly MW maintenance windows (check other open tasks and the deployment calendar for possible conflicts):
** Tuesday at 6:00 AM UTC
** Thursday at 6:00 AM UTC
* Obtain explicit availability confirmation (over any channel) from at least one other person and add them to the task using the "Other assignee" field
* Send a calendar invite to sre-data-persistence@
== Which section are you switching over? ==
* s* (core Wiki section)
* es* (external store section)
* m* (misc section)
* x1 (special section)
* x2 (special section)
Category:MariaDB
MariaDB/Provisioning a host

== Host preparation ==
=== Downtime the host in Icinga ===
Semi-automatically:
 cookbook sre.hosts.downtime DBNAME --days 1 --task-id TXXXXXX --reason "provisioning - TXXXXXX"
Manually:
# Open https://icinga.wikimedia.org/cgi-bin/icinga/status.cgi?search_string=DBNAME.
# Select "Schedule downtime for checked hosts" from the top right menu.
# TBD: next steps
=== Adding a new host ===
# TBD
=== Replacing a failed host ===
# Identify an available host in the same DC
## TBD: how?
## TBD: What to do if there aren't any?
# Match the OS version the replaced host was running
## Reimage the host to Stretch if needed, following the instructions in Server_Lifecycle/Reimage
== Section and replication configuration ==
=== Add the host to a section: ===
# In manifests/site.pp:
## Add the DBNAME to the appropriate regexps
## Remove the insetup role from DBNAME
# In hieradata/hosts/DBNAME.yaml add
## For single-instance hosts: mariadb::shard: 'SECTIONNAME'
## For multi-instance hosts: 
=== Configure the correct replication mode: ===
# In hieradata/hosts/DBNAME.yaml add: mariadb::binlog_format: 'BINLOG_FORMAT', where BINLOG_FORMAT is:
## (TBD: when?) STATEMENT
## (TBD: when?)ROW
=== Add the host to dbctl config (example) ===
# In conftool-data/dbconfig-instance/instances.yaml add - DBNAME in the appropriate location
=== Add the host to Mediawiki's database loadbalancer configuration (example) ===
# Log on to one of the cluster management hosts ()
# Run sudo dbctl --scope DCNAME instance DBNAME edit, where DCNAME is eqiad or codfw
# Fill the template:
## Set pooled: false
## Replacing a failed host?
### Yes: mirror the replaced host configuration
### No: For now, just ask (TBD: guidelines?)
# Run dbctl config commit -m "COMMIT_MESSAGE"
== Data population ==
=== Cloning ===
# Clone via the sre.mysql.clone cookbook
# Start MariaDB
# Wait until replication has caught up (all green on Icinga)
=== Restoring from backup ===
# MariaDB/Backups#Provision_a_precompressed_and_prepared_snapshot_(preferred)
== Replication from master ==
== Enabling notifications (example): ==
# In hieradata/hosts/DBNAME.yaml delete profile::base::notifications: disabled
== Add it to zarcillo DB ==
Example host: 
db1208 going to core s3, lives in eqiad in A5
Zarcillo DB lives in db1215
 set session binlog_format=ROW;
 INSERT INTO instances (name, server, port, `group`) VALUES ('db1208','db1208.eqiad.wmnet',3306, 'core');
 INSERT INTO section_instances (instance, section) VALUES ('db1208','s3');
 INSERT INTO servers (fqdn, hostname, dc, rack) VALUES ('db1208.eqiad.wmnet', 'db1208', 'eqiad', 'a5');
== Next steps ==
# Ask other team members about schema changes that were executed since the timestamp of the backup you restored the host from and apply them (if needed) 
# Pool the host in (TBD: link needed)
Category:MariaDB
MariaDB/Switch Datacenter

== The week before the switchover ==
* 7 days before: no more maintenance on the database clusters.
* 6 days before: Enable circular replication between eqiad and codfw.
** This requires updating section_params in hieradata/common/profile/mariadb.yaml. E.g. gerrit:719168
** Run the sre.switchdc.databases.prepare cookbook.
* In the new DC:
** Check and disable GTID on primaries.
** Check that all replicas have GTID enabled.
** Check for disabled notifications (icinga)/silences (alertmanager).
** Check that the query killers are installed and enabled.
** Review MW weights, comparing them to the old DC.
**Warm up the caches using queries from the old DC.
== The day of the switchover ==
=== Before the switchover ===
*Downtime all db primaries just before the switch, so that read-only alerts won't fire (T285803).
===After the switchover===
*Manually fix parsercache hosts and x2 in tendril: T266723
*Submit a puppet patch changing host-down alerting:
**Background: gerrit:736415
**Move profile::monitoring::is_critical: true from hieradata/role/<old dc>/mariadb/* to hieradata/role/<new dc>/mariadb/
**Re-run puppet: sudo cumin 'A:db-core or A:db-parsercache' 'run-puppet-agent -q'
== After the switchover ==
* 2 days after: disable circular replication again:
** update section_params in hieradata/common/profile/mariadb.yaml again. E.g. gerrit:721421
** Run the sre.switchdc.databases.finalize cookbook.
Category:MySQL
Category:MariaDB
MariaDB/Troubleshooting/external storage failover

# Old master: es1014
# New master: es1017
# Check if there are passwords in the old format / fix grants CHECKED es1017 looks good
# Set expire_logs_days to 30 on the new master DONE
 set global expire_logs_days = 30;
# Check pt-heartbeat is running with the latest puppet parameters, meaning (note the user, host and defaults): CHECKED
 /usr/bin/perl /usr/local/bin/pt-heartbeat-wikimedia --defaults-file=/dev/null --user=root --host=localhost -D heartbeat --shard={shard} --datacenter={dc} --update --replace --interval=1 --set-vars=binlog_format=STATEMENT -S {socket} --daemonize --pid /var/run/pt-heartbeat.pid
and not with the older format, still present on some masters:
 /usr/bin/perl /usr/local/bin/pt-heartbeat-wikimedia --defaults-file=/root/.my.cnf -D heartbeat --shard={shard} --datacenter={dc} --update --replace --interval=1 --set-vars=binlog_format=STATEMENT -S {socket} --daemonize --pid /var/run/pt-heartbeat.pid
# Silence alerts on all hosts DONE
# Disable GTID on es1017 DONE
# Move replicas (es1019, es2017) under the new master (es1017) DONE
# Disable puppet on es1017, es1014 DONE
 @es1014> puppet agent --disable "Switching over es3 from es1014 to es1017"
 @es1017> puppet agent --disable "Switching over es3 from es1014 to es1017"
# merge puppet patch and deploy it: DONE
 https://gerrit.wikimedia.org/r/447584
# merge mediawiki patch and rebase on deployment host DONE
 https://gerrit.wikimedia.org/r/447586
(actual deployment starts here)
# !log the actions about to take place
!log switchover es3 eqiad master from es1014 to es1017 DONE
# Run switchover script from neodymium
 db-switchover --skip-slave-move es1014 es1017 DONE
 [Servers sync at master: es1014-bin.002508:184384418 slave: es1017-bin.002491:41215873]
# Deploy mediawiki change (deployment.eqiad.wmnet) DONE
 scap sync-file --force wmf-config/db-eqiad.php "Switchover es3 master eqiad from es1014 to es1017"
(main deployment finishes here)
# run puppet on es1014 and es1017, and make sure it doesn't break anything DONE
 @es1014> puppet agent --enable && puppet agent -tv
 @es1017> puppet agent --enable && puppet agent -tv
# Check semisync and gtid status of all related servers DONE
# Make the change reflect on dns CNAME: DONE
 https://gerrit.wikimedia.org/r/447587
# Update tendril, zarcillo: DONE
 mysql.py -A -h db1215 tendril -e "update shards set master_id=1231 WHERE name='es3' LIMIT 1"
 mysql.py -A -h db1215 zarcillo -e "UPDATE masters SET instance = 'es1017' WHERE section='es3' and dc = 'eqiad' LIMIT 1"
# Update and close the ticket https://phabricator.wikimedia.org/T197073
# Perform planned maintenance on es1014 (upgrade socket location, upgrade mysql, upgrade kernel, make sure firmware is deployed, change old format passwords if any)
# Remove accounts 'repl'@'10.%' and 'repl'@'208.80.152.%', 'repl'@'10.0.%' maybe others from es1017 (maybe other hosts, too) DONE (es1017 for now)
MariaDB/grants

This page provides a basic introduction on grants management in our production. Note that we are in the process of automation of the work so this might change a lot.
== Gathering report ==
Before doing most of grants fixes. You need to collect the current state of grants. To do so. You need to run omg.py in cumin:
sudo python3 omg.py
It takes around an hour but it will provide you with a file (omg.json) on which the rest of scripts can be built.
== Analyzing grants ==
Once you got the report, you can run the script that aggregates them to spot misc grants or grants that shouldn't be there.
Also if you update the file in people1003.eqiad.wmnet (in /home/ladsgroup/public_html/omg/omg.json), https://people.wikimedia.org/~ladsgroup/omg/ would automatically show you the new report.
== Changing password of a db user ==
The general workflow of changing password follows as:
# Duplicate all grants everywhere into a new user with the new password.
# Make a patch in mediawiki private repo to use the new user and password and deploy it.
# Wait until reads fall over to the new user.
## For wikiuser, it'll take three minutes while for wikiadmin, it might take days and you probably need to restart maint scripts to pick up the new config.
# Drop the user from everywhere that uses it.
# Update mentions in puppet to the user (and use the new user)
# Deploy new querykiller with the new user.
For step 1 and 4, there are scripts you can use. Check omg repo.
Category:MariaDB
MariaDB/monitoring

== Icinga ==
Specific checks:
* Replication running (both IO and SQL thread)
* Replication lag
* Disk space
* Memory used
* Read_only status
* mariadb process
== Metrics ==
=== Prometheus ===
Hosts to be monitored at prometheus are controlled by the instance and server inventory at Zarcillo (db1215). In order to update prometheus, hosts have to be inserted, updated or deleted from Zarcillo and then run /usr/local/sbin/mysqld_exporter_config.py on the prometheus hosts (e.g. prometheus1003 and prometheus1004 for eqiad). This scripts run automatically every 30 minutes to check for changes.
The standard mysqld-prometheus-exporter is used for most metrics.
=== Grafana ===
Relevant dashboards:
* MySQL aggregated: https://grafana.wikimedia.org/d/000000278/mysql-aggregated
* MySQL instance: https://grafana.wikimedia.org/d/000000273/mysql
* Replication lag: https://grafana.wikimedia.org/d/000000303/mysql-replication-lag
* MediaWiki LoadBalancer: https://grafana.wikimedia.org/d/G9kbQdRVz/mediawiki-loadbalancer
== Orchestrator ==
Orchestrator
== Logstash/Kibana ==
Useful dashboards:
* DBQuery https://logstash.wikimedia.org/goto/3063b45795e5d484c75e0039f997ba5b
* DBError https://logstash.wikimedia.org/goto/dce505a8dac307f3b67923f9995d155c
* SlowQuery https://logstash.wikimedia.org/goto/1dda4970a91dd77ee4f322848aef3ae3
Category:Monitoring
Category:MariaDB
MariaDB/pt-heartbeat

pt-heartbeat is a service running on MariaDB servers acting as masters. A process on the source server writes a timestamped row to the heartbeat table every second. A process on a replica compares the most recent entry in the table to the host clock to evaluate the replication lag. MediaWiki load-balancer will avoid replicas that don't have an up-to-date heartbeat entry.
The process writes to a database called heartbeat that contains a table called also heartbeat which has the following structure:
 CREATE TABLE `heartbeat` (
 `ts` varbinary(26) NOT NULL,
 `server_id` int(10) unsigned NOT NULL,
 `file` varbinary(255) DEFAULT NULL,
 `position` bigint(20) unsigned DEFAULT NULL,
 `relay_master_log_file` varbinary(255) DEFAULT NULL,
 `exec_master_log_pos` bigint(20) unsigned DEFAULT NULL,
 `shard` varbinary(10) DEFAULT NULL,
 `datacenter` binary(5) DEFAULT NULL,
 PRIMARY KEY (`server_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=binary
The process is controlled by pt-heartbeat-wikimedia.service systemctl unit and can be stopped and started as follows:
$ systemctl start pt-heartbeat-wikimedia.service
$ systemctl stop pt-heartbeat-wikimedia.service
The upstream version is developed by Percona, we use a modified version ().
== See also ==
* replag.toolforge.org
* [Labs-l] Lag reporting on lab db replicas
* 
Category:MariaDB