Portal:Data Services/Admin/Wiki Replicas
This page holds most of the knowledge we have regarding Wiki Replicas for admins.
Service architecture by layers

The diagram above shows the 2 proxy layers that live in front of the replicas DB servers.
The proxy layer 1 are virtual machines in Cloud VPS, which abstract away port numbers for each database section.
The proxy layer 2 are physical servers in the wiki production realm that know the actual internal address of each replica DB server.
Physical database server layer
There are currently 8 servers in this cluster. Each of the nodes hosts two database sections. Since there are 8 sections in the scope of the Wikireplicas service, that means that they are able to function as sets of four that can act as redundant standby for one another. These are all Dell PowerEdge R440s, built to Data Persistence specs.
Web (realtime workloads)
Analytics (long-processing workloads)
DB layer
All database servers run MariaDB.
The multi-instance servers have a somewhat complex layout that is documented in each database server's host hiera file (eg. clouddb1013.yaml). They are multi-instance in the sense that each server has two mariadb database instances running at the same time on nonstandard ports (see: profile::mariadb::section_ports). Each instance is named for and hosts one database section.
Physical proxy layer
Two haproxy servers are the back proxy in front of the replica servers for the multi-instance hosts (behind the LVS layer).
- dbproxy1018 - by default, the analytics service
- uses the LVS IP of 208.80.154.242
- dbproxy1019 - by default, the web service
- uses the LVS IP of 208.80.154.243
VM proxies
The routing of user requests first hits a pair of VMs that each act as a proxy for one of the web or analytics services. The current proxies are:
- clouddb-wikireplicas-proxy-1.clouddb-services.eqiad1.wikimedia.cloud
- clouddb-wikireplicas-proxy-2.clouddb-services.eqiad1.wikimedia.cloud
Each is an haproxy server that holds 8 VIPs that are reserved as Neutron ports, connected to the VM via puppet and ends up in DNS via wikireplicas_dns.yaml in puppet. Each IP is the frontend for a section. Each section then routes to the LVS IP address for the upstream hardware proxy servers ( 208.80.154.242 for clouddb-wikireplicas-proxy-2.clouddb-services.eqiad1.wikimedia.cloud and 208.80.154.242 for clouddb-wikireplicas-proxy-1.clouddb-services.eqiad1.wikimedia at this time) on the appropriate port. The hardware proxies handle load balancing in the default configuration. This layer purely routes an IP address for a section at port 3306 to the section's port on the multi-instance cluster and is discoverable via the wiki database name in DNS.
DNS
main article Help:Toolforge/Database#Naming conventions
DNS is an important component of helping our users find their server in multi-instance. Each wiki database has a CNAME in the form of:
- ${WIKI-DATABASE}.{analytics,web}.db.svc.wikimedia.cloud. Example enwiki.web.db.svc.wikimedia.cloud
- s${SECTION_NUMBER}.{analytics,web}.db.svc.wikimedia.cloud. Example s1.web.db.svc.wikimedia.cloud
By connecting to the wiki database's name, the CNAME redirects the user to the appropriate section's DNS, which is a distinct IP address that will route to the appropriate port on the physical proxies. This way, instead of users needing to figure out that diqwiktionary
is on s5 (by checking dblists/s5.dblist perhaps) and then determining from puppet that s5 uses the port 3315 at the proxy and connecting to that, they just need to know that they are connecting to diqwiktionary and DNS plus two proxies handle the rest. This allows components behind this layer to be depooled and maintained simply without a need to provide lengthy explanations of our infrastructure for very basic usage.
More advanced users who wish to attempt a cross-wiki join could determine the section number of two wiki databases using the dig tool:
bstorm@tools-sgebastion-08:~$ dig +short diqwiktionary.analytics.db.svc.wikimedia.cloud
s5.analytics.db.svc.wikimedia.cloud.
172.16.0.220
bstorm@tools-sgebastion-08:~$ dig +short avkwiki.analytics.db.svc.wikimedia.cloud
s5.analytics.db.svc.wikimedia.cloud.
172.16.0.220
bstorm@tools-sgebastion-08:~$ dig +short eswiki.analytics.db.svc.wikimedia.cloud
s7.analytics.db.svc.wikimedia.cloud.
172.16.1.152
By running the above, they could determine that a cross-wiki SQL join between diqwiktionary and avkwiki is possible because they are both on s5, while they cannot be joined to eswiki because that is on s7. This is much faster, programmatically, than scanning noc.wikimedia.org, depending on the use case. That information can sometimes be gained from meta_p (which is hosted on s7), and always from the dblists files on noc.wikimedia.org if checking the section for what it contains rather than check where a database lives.
From production to wiki-replicas
Wiki databases from production are copied and sanitized to serve wiki-replicas.
Step 0: databases in production
The starting situation is that there are databases in production for wiki projects (like wikipedia, wikidata, wikictionary, and friends). We would like to provide this same databases for WMCS users. Due to privacy reasons, some data needs to be redacted or deleted. That's why users can't directly access this database.
So, we choose what databases to copy to wiki-replicas, which are all of them.
Every time a new database is created in production (for example, a new language for a wiki) we are in this step 0.
The identification of new database candidates for migrating to wiki-replicas is done under request by someone. Right now there aren't any mechanisms to notify pending migrations or the like.
Step 1: sanitization
Main article: MariaDB/Sanitarium and Labsdbs
The production database is copied to sanitarium boxes by means of MariaDB replication mechanisms (TODO: is this true? give more info if possible).
Each sanitarium host has a MariaDB instance to replicate each db shard. The replication into the sanitarium host uses triggers and filters to remove sensitive columns, tables and databases in the simple case where there are no conditions (e.g. Ensures user_password does not go into Cloud Services).
Having this redaction done on a separate host outside of Cloud Services helps isolate the security of the data and ensure a privilege escalation via the Cloud Services access does not compromise the most sensitive data in the db.
Some triggers are added by means of:
- modules/role/files/mariadb/redact_sanitarium.sh - main script
- modules/role/files/mariadb/filtered_tables.txt - configuration file, to what tables/columns add triggers to
This step is handled by main operations teams, DBAs.
Step 2: evaluation
Main article: Labsdb redaction
Once data is in sanitarium boxes, some cron jobs and manual scripts check whether data is actually redacted. For example, check that a given column is NULL.
Involved code:
- modules/role/files/mariadb/check_private_data.py - main checker script
- modules/role/files/mariadb/check_private_data_report - wrapper to report and send notification emails
- modules/profile/manifests/mariadb/check_private_data.pp - cron / systemd job to run wrapper daily
This evaluation also happens in the wiki-replica servers, and alerts in case some private data is detected.
The main production team, DBAs, are in charge of this step.
Step 3: filling up wiki-replicas
Data is finally copied to wiki-replica servers. The DB servers are currently using row-based replication from sanitization boxes to wiki-replicas.
This is done in real time by the DB daemons, so there is always a data flow, which should be sanitized due to previous steps.
Step 4: setting up GRANTs
At this point, database GRANTS are created by main operations team DBAs.
This is done by hand, no automation using puppet, but there is a file where GRANTS are being tracked: modules/role/templates/mariadb/grants/wiki-replicas.sql.
The content of the file is something like the following:
GRANTS tracking file |
---|
The following content has been placed in a collapsed box for improved usability. |
-- Initial grants and grants added at T178128 GRANT labsdbuser TO 'labsdbadmin'@'10.64.37.19' WITH ADMIN OPTION GRANT SELECT, INSERT, UPDATE ON `mysql`.* TO 'labsdbadmin'@'10.64.37.19' GRANT SELECT, SHOW VIEW ON `%\\_p`.* TO 'labsdbadmin'@'10.64.37.19' WITH GRANT OPTION GRANT SELECT, SHOW VIEW ON `%wik%`.* TO 'labsdbadmin'@'10.64.37.19' GRANT labsdbuser TO 'labsdbadmin'@'10.64.37.20' WITH ADMIN OPTION GRANT SELECT, INSERT, UPDATE ON `mysql`.* TO 'labsdbadmin'@'10.64.37.20' GRANT SELECT, SHOW VIEW ON `%wik%`.* TO 'labsdbadmin'@'10.64.37.20' GRANT SELECT, SHOW VIEW ON `%\\_p`.* TO 'labsdbadmin'@'10.64.37.20' WITH GRANT OPTION -- Labsdbuser is a role with privileges for all views like -- GRANT SELECT, SHOW VIEW ON `rowikiquote\_p`.* TO 'labsdbuser' GRANT USAGE ON *.* TO 'labsdbuser' -- maintainviews user used by cloud services team GRANT ALL PRIVILEGES ON `heartbeat\\_p`.* TO 'maintainviews'@'localhost' GRANT ALL PRIVILEGES ON `meta\\_p`.* TO 'maintainviews'@'localhost' GRANT ALL PRIVILEGES ON `centralauth\\_p`.* TO 'maintainviews'@'localhost' GRANT SELECT ON `centralauth`.* TO 'maintainviews'@'localhost' GRANT SELECT ON `heartbeat`.* TO 'maintainviews'@'localhost' GRANT ALL PRIVILEGES ON `%wik%\\_p`.* TO 'maintainviews'@'localhost' GRANT ALL PRIVILEGES ON `%\\_p`.* TO 'maintainviews'@'localhost' GRANT SELECT, DROP, CREATE VIEW ON `%wik%`.* TO 'maintainviews'@'localhost' GRANT SELECT (user, host) ON `mysql`.`user` TO 'maintainviews'@'localhost' -- viewmater user GRANT SELECT ON *.* TO 'viewmaster'@'%' -- quarry user granted 48 connections #T180141 GRANT USAGE ON *.* TO 's52788'@'%' WITH MAX_USER_CONNECTIONS 48; |
The above content has been placed in a collapsed box for improved usability. |
Related tickets:
- T178128 - Access to raw database tables on labsdb* for wmcs-admin users
- T166310 - Grant root access for Bryan Davis on labstore* and admin for maintain scripts for labsdb*
Step 5: setting indexes
Create indexes that only exist on the wiki replicas via the maintain-replica-indexes script. This script manages the indexes in an idempotent fashion (if you run it multiple times it only changes what doesn't match the configuration file here index-conf.yaml). This could use the ability to handle only a single database, but it skips tables it finds comply with the definitions in the config, so it is fairly quick to run, despite that it runs across the whole set.
On each wiki-replica server, run:
% sudo /usr/local/sbin/maintain-replica-indexes
Step 6: setting views
Create _p (public) views, which are intermediate views which leave out private data.
This is done by means of the maintain-views.py script.
In each wiki-replica server, it's executed like this:
% sudo maintain-views --databases $wiki --debug
This step is handled by the Data Engineering team.
TODO: elaborate info on what is this doing
Step 7: setting up metadata
Insert a new row in meta_p.wiki for the new wiki by running the maintain-meta_p script. This is only relevant for S7 databases.
The execution is like this:
% sudo /usr/local/sbin/maintain-meta_p --databases $wiki.
If the meta_p doesn't exist, you need to run it with --boostrap option.
For example, in case of a full bootstrap:
user@cumin1001:~$ sudo cumin "P{R:Profile::Mariadb::Section = 's7'} and P{P:wmcs::db::wikireplicas::mariadb_multiinstance}" "/usr/local/sbin/maintain-meta_p --all-databases --bootstrap"
3 hosts will be targeted:
clouddb[1014,1018,1021].eqiad.wmnet
[..]
This step is handled by the WMCS team.
TODO: elaborate info on what is this doing
Step 8: setting up DNS
From an openstack control server (for example cloudcontrol1004.wikimedia.org), run:
$ sudo /usr/local/sbin/wikireplica_dns --aliases --shard <sN>
To update the service IP used for the analytics or web servers:
- Edit modules/openstack/files/util/wikireplica_dns.yaml to change the service IP
- Run /usr/local/sbin/wikireplica_dns
root@cloudcontrol1007:~# /usr/local/sbin/wikireplica_dns -v --zone web.db.svc.eqiad.wmflabs. 2018-03-16T14:02:41Z mwopenstackclients.DnsManager INFO : Updating s8.web.db.svc.eqiad.wmflabs. 2018-03-16T14:02:42Z mwopenstackclients.DnsManager INFO : Updating s3.web.db.svc.eqiad.wmflabs. 2018-03-16T14:07:17Z mwopenstackclients.DnsManager INFO : Updating s2.web.db.svc.eqiad.wmflabs. 2018-03-16T14:07:23Z mwopenstackclients.DnsManager INFO : Updating s1.web.db.svc.eqiad.wmflabs. 2018-03-16T14:07:24Z mwopenstackclients.DnsManager INFO : Updating s7.web.db.svc.eqiad.wmflabs. 2018-03-16T14:07:28Z mwopenstackclients.DnsManager INFO : Updating s6.web.db.svc.eqiad.wmflabs. 2018-03-16T14:07:29Z mwopenstackclients.DnsManager INFO : Updating s5.web.db.svc.eqiad.wmflabs. 2018-03-16T14:07:30Z mwopenstackclients.DnsManager INFO : Updating s4.web.db.svc.eqiad.wmflabs.
This step is done by the WMCS team.
TODO: elaborate info on what is this doing
Step 9: all is done
All is done, wiki-replica contains a mirror of the production database. Finally WCMS users/tools/projects are able to query the database/tables.
This is usually done by using the sql wrapper script.
TODO: the benefits of using the sql script.
Admin guide
Docs to perform common tasks related to this service. As detailed as possible.
Who admins what
- main production cluster: main operations team, DBAs
- sanitarium cluster: main operations team, DBAs
- wiki-replicas cluster:
- WMCS has historically handled most hardware, end-user and view-level operations, Data Persistence (DBAs) handles database-level and replication matters
- Increasingly, the Data Engineering team is taking over the reponsibility for managing the wiki replicas from WMCS.
- wiki-replicas DNS: Data Engineering
Adding a wiki
- Documented at Add_a_wiki#Cloud_Services
User connection limits
There are two important aspects to this.
- The review process, which is that requests for expanding the number of connections per tool be discussed at the weekly WMCS planning meeting for approval with a requirement of a +1 from a member of the DBA team after WMCS approval. This should be a restrictive process that errs on the side of denial. Only tools with a pretty good reason and significant user base should use additional connections beyond the default limit of 10.
- Keeping a record of the change in modules/role/templates/mariadb/grants/wiki-replicas.sql. In the file as of this writing, line 38 provides a model for a comment (including the Phabricator task number and user) as well as the exact command that is run on the replicas. For consistency, the command should be run on all four replica hosts.
The process after approval is:
- Determine the mysql username of the tool, which should be in any approved request. This will be something similar to
s52788
and not something liketools.flubber
. It can be found in the tool's$HOME/replica.my.cnf
. - The clinic-duty person logs into the root mysql shell for each replica and runs
GRANT USAGE ON *.* TO '$mysql_username'@'%' WITH MAX_USER_CONNECTIONS $number;
Please note that you can always view grants on the server you are on withSHOW GRANTS FOR '$mysql_username';
- Record the change in modules/role/templates/mariadb/grants/wiki-replicas.sql
- To persist the change on rebuilds, add the new value to hieradata/role/common/wmcs/nfs/primary.yaml
- Communicate with the user that they should be all set!
De-pooling Replica Servers
See Portal:Data Services/Admin/Runbooks/Depool wikireplicas
Making significant changes to the views and the indexes
View and index scripts perform DDL commands on the databases, which can cause lock contention issues both for the scripts and the users of the replicas. Problems with the views can even cause data that the view system is supposed to keep out to be exposed. These are some guidelines to prevent problems.
- De-pool a host before doing index changes and view changes that are not simply adding a new wiki, table or database. This reduces production impact as well as fallout from mistakes.
- If there is a concern that the data may not be in a consistent or safely-scrubbed state, find a user who is trusted (as in NDA-signed) to take a look if you aren't able to come up with some good examples yourself to go after.
Updating views
- Confirm with DBAs that they don't have work in progress that might interfere
- Merge associated gerrit change, and force puppet runs on all affected replica servers
- On each replica server:
- If modifying an existing view, these steps on each replica server
- Depool
- Write, merge, apply hiera change (run sudo -i puppet agent -t on the dbproxy after merging)
- systemctl reload haproxy && echo "show stat" | socat /run/haproxy/haproxy.sock stdio
- wait for depool to take effect (check with
echo "show stat" | socat /run/haproxy/haproxy.sock stdio
)
- Kill running user processes on the clouddb* server you depooled if necessary. Processes holding locks on tables you are changing must finish or be killed to proceed or the maintain-views script will fail with a traceback about timing out waiting for a lock.
- Look at the processes running with a root mysql shell (
sudo mysql -S /var/run/mysqld/mysqld.s3.sock -u root
) by runningshow processlist;
. If you need to be able to see all of them, and they won't show on the screen, you can usepager less
to set the shell to show output in a pager. - Only usernames like u##### (a user) and s##### (a tool) should be killed. Other usernames are admin or system processes. Please don't kill those. You probably only need to kill queries against tables you are changing things about, and often you can just give it an hour after depooling to let people's work finish. Sometimes you need to kill more because of subqueries or DB connection pools holding table locks with connections that don't even seem to be doing anything. Never feel bad about killing an old, inactive connection because we don't allow that anyway.
- If you have identified a process to kill, copy the process ID and run
kill $pid;
. It might not die right away, but typically, unless things are in bad shape on that replica, it will die without too much delay after closing up tables.
- Look at the processes running with a root mysql shell (
- Depool
- If it is just one new table, update with:
sudo maintain-views --table <affected table> --all-databases
, otherwise, drop the --table argument to update all views. If this is for existing tables/views, you will want to use the--replace-all
argument to avoid typing "Y" 800 times. - Verify changes via the mysql commandline: sudo mysql -S /var/run/mysqld/mysqld.s3.sock -u root
- Repool
- Revert depooling hiera change
- systemctl reload haproxy
- Check that the original state is restored with
echo "show stat" | socat /run/haproxy/haproxy.sock stdio
- Repool
- If modifying an existing view, these steps on each replica server
Concrete example: apply 2 view changes to centralauth
[1]
As an example (and with an eye towards automating this entire procedure) the following steps were done to apply https://gerrit.wikimedia.org/r/c/operations/puppet/+/743948. The patch updates 2 views of the centralauth
database. centralauth
is in section s7
, so the relevant clouddb hosts are clouddb1014.eqiad.wmnet
and clouddb1018.eqiad.wmnet
.
- Merge the actual Gerrit patch which updates the view definitions[2], and
sudo puppet-merge
it onpuppetmaster1001.eqiad.wmnet
- Apply the changes to the views on
clouddb1014.eqiad.wmnet
. Before you begin, come up with a query that will confirm when the change has been applied. In this case a view was being modified, so I tested a new field and got the error I'd expect:MariaDB [centralauth_p]> select gu_hidden_level from globaluser limit 1; ERROR 1054 (42S22): Unknown column 'gu_hidden_level' in 'field list'
- Depool
clouddb1014.eqiad.wmnet
- Make and review a patch to depool
clouddb1014.eqiad.wmnet
fromdbproxy1019.eqiad.wmnet
by uncommenting the complementary serverclouddb1018.eqiad.wmnet
inhieradata/hosts/dbproxy1019.yaml
[3]- Explanation: since we're starting with
clouddb1014
, a "web" type database host (see Portal:Data Services/Admin/Wiki Replicas#Physical database server layer), we have to tell the "web" type db proxy (dbproxy1019
) to pool onlyclouddb1018
, which implicitly depoolsclouddb1014
. You should only have to uncomment lines from thehieradata/hosts/dbproxy*.yaml
.
- Explanation: since we're starting with
- Submit the patch, and on the puppetmaster, run
sudo puppet-merge
. - On
dbproxy1019.eqiad.wmnet
, runsudo puppet agent -t
. - On
dbproxy1019
, runsudo systemctl reload haproxy
and confirm that clouddb1014 has been removed by looking at the output ofecho "show stat" | socat /run/haproxy/haproxy.sock stdio
: in the lines starting withmariadb-s7
, there should be one forclouddb1018.eqiad.wmnet
but not one forclouddb1014.eqiad.wmnet
. It doesn't happen immediately; re-run the HAProxy status command until you see it change.
- Make and review a patch to depool
- Connect to MariaDB on
clouddb1014.eqiad.wmnet
usingsudo mysql -S /var/run/mysqld/mysqld.s7.sock
and clean up old connections- I ran
show processlist;
, and after waiting a couple minutes, I usedkill <id>;
to terminate any lingering connections started by users (u###
) and services (s###
).
- I ran
- Still on
clouddb1014.eqiad.wmnet
, runsudo maintain-views --databases centralauth --replace-all
.- I originally ran this with a single table,
sudo maintain-views --table globaluser --databases centralauth
but I realized there were 2 views in the patch, so I ended up running it against all tables anyways. In terms of automation, it should be fine to always run all tables. - It's a good idea to test the new view after running
maintain-views
; in this case I ran sudo mysql -S /var/run/mysqld/mysqld.s7.sock centralauth_p -e 'select gu_hidden_level from globaluser limit 1'
- The other view only changed a filter on the
localuser
table so there was no column to query; I ransudo mysql -S /var/run/mysqld/mysqld.s7.sock centralauth_p -e 'show create table localuser \G'
and inspected the output.
- I originally ran this with a single table,
- Repool
clouddb1014.eqiad.wmnet
- Make a patch to repool
clouddb1014.eqiad.wmnet
(you can use the gerrit ui to revert the original patch)[4] - Submit the change, run puppet-merge on the puppetmaster, and run
sudo puppet agent -t
ondbproxy1019.eqiad.wmnet
- Reload HAProxy on
dbproxy1019
usingsudo systemctl reload haproxy
. Wait a few seconds and confirm thatclouddb1014
is back:echo "show stat" | socat /run/haproxy/haproxy.sock stdio
should have both clouddb servers.
- Make a patch to repool
- Depool
- Update clouddb1018.eqiad.wmnet, the other host for s7.
Update a wiki's section
Although it is a rare operation, sometimes production wikis get moved from one section to a different one. For example, if two wikis get moved from s3 to s5, the following steps need to taken on the wikireplicas:
- Update
modules/profile/files/openstack/base/pdns/recursor/labsdb.zone
, follow this example: https://gerrit.wikimedia.org/r/619627 - Run
/usr/local/sbin/maintain-meta_p
on each wikireplica host- Double check that the slices look good and the new wikis are on the correct slice, in this case, s5:
sql meta_p select dbname, slice from wiki where dbname in ('muswiki', 'mhwiktionary');
- From a cloudcontrol node (like cloudcontrol1004.wikimedia.org) run:
wmcs-wikireplica-dns --aliases --shard s3 wmcs-wikireplica-dns --aliases --shard s5
- Double check that the wikis appear on their new section using https://replag.toolforge.org/
History
Before September 2017 we had a cluster composed of three physical servers: labsdb1001, labsdb1002 and labsdb1003. Each host contained a copy of all database sections combined into a single database service which also allowed users to add their own custom tables to each wiki's namespace. DNS names for connecting to these servers used a ${wikidb}.labsdb naming convention. These legacy service names survive as CNAME pointers to the appropriate current (2020) s${SECTION_NUMBER}.analytics.db.svc.wikimedia.cloud name. Records are only maintained for wikis which existed at the time of the migration to the 2017 system.
In 2017 we deployed a new cluster of three physical servers with faster hardware, improved replication, and new service names. Each host still contained a merged view of all database sections under a single MariaDB process. User created tables were not allowed in this redesign to improve the ability to shift request traffic from one instance to another. This redesign also introduced the "web" vs "analytics" split for performance tuning. Read more details in the New Wiki Replica servers ready for use blog post from the launch announcement.
In 2021 we deployed the "2020" cluster of 8 physical servers with a new "multiinstance" design. Multiinstance separates database sections into distinct MariaDB instances with two instances hosted on each physical server. See News/Wiki Replicas 2020 Redesign for more information about the user facing changes this required.
See also
- End user docs: Wiki Replicas
- Help:Toolforge/Database
- MariaDB/Sanitarium and Labsdbs
- Labsdb redaction
- Add a wiki#Cloud Services
- Portal:Data Services/Admin/Wiki Replica DNS
- phab:T180513 "Document wiki-replicas architecture for future automation"
- News/Wiki Replicas 2020 Redesign
- 2017 cluster user announcement
- ↑ https://phabricator.wikimedia.org/T298505
- ↑ https://gerrit.wikimedia.org/r/c/operations/puppet/+/743948/
- ↑ https://gerrit.wikimedia.org/r/c/operations/puppet/+/751779
- ↑ https://gerrit.wikimedia.org/r/c/operations/puppet/+/751823
- ↑ https://gerrit.wikimedia.org/r/c/operations/puppet/+/751824
- ↑ https://gerrit.wikimedia.org/r/c/operations/puppet/+/751840