Jump to content

MariaDB

From Wikitech

For emergencies, check the subpage about MariaDB troubleshooting.

MariaDB is the main database management system used to run the Wikimedia sites.

For a general overview, check the MySQL@Wikipedia (2015) slides (MariaDB is a drop-in replacement for MySQL, which we used to use).

Categories and clusters

Our MariaDB databases can be grouped into roughly seven main categories. The database tree and replication status for most clusters can be viewed in Orchestrator (NDA only).

category clusters use steward
Core s1-8 main MediaWiki databases SRE/Data Persistence
External storage es1-7 Full content of MediaWiki page revisions SRE/Data Persistence
Parser cache pc1-7 Database component of parser cache SRE/Data Persistence
Extension storage x1, x3 Cross-wiki storage generally used by MediaWiki extensions SRE/Data Persistence
MainStash ms1-3 Semi-persistent store for user state (like whether Echo notification have been read) SRE/Data Persistence
Miscellaneous m1-3, m5

db_inventory

Auxiliary services such as Phabricator and VRTS SRE/Data Persistence
Cloud Services — public replicas of core (MediaWiki) databases Cloud Services
Data Platform — private replicas of core and x1 databases, backend storage for analytics tools Data Platform Engineering
Diagram of sections and how they get read/write

Core (MediaWiki databases)

There are 8 clusters which host the main MediaWiki databases. Each cluster is responsible for a particular set of wikis.

The canonical list of core clusters can be viewed at noc.wikimedia.org/db.php, but here is a summary:

  • s1: English Wikipedia
  • s2: 17 large wikis, including Chinese, Italian, Dutch, Polish, and Portuguese Wikipedias and English Wiktionary
  • s3: Most small wikis (~900)
  • s4: Commons, Testcommons
  • s5: German Wikipedia, some other large wikis, and most new wikis since mid-2020 (T259438)
  • s6: French, Japanese, and Russian Wikipedias, and Wikitech
  • s7: CentralAuth, Meta, 10 large Wikipedias, and French Wiktionary
  • s8: Wikidata
Standardized weights
Type Main API vslow dump
primary
main 500
api 300 100
vd 300 100 100

External storage

The external storage servers host the full text of all page revisions in a compressed format.

  • es1: read-only
  • es2: read-only
  • es3: read-only
  • es4: read-only
  • es5: read-only
  • es6: read/write
  • es7: read/write

Parser cache

The parser cache servers (clusters pc1-8) store the largest, disk-based part of the parser cache (for example, temporary parsed HTML output sent to users). While the service is very important for performance optimization, individually, and in general, rows can be lost, as they will be automatically inserted back when re-parsed, on next cache miss.

Because data loss is not a concern, but read only could cause application errors, the parser cache hosts are started by default in read-write mode (unlike most production databases), so they are writable by the application at all times.

These clusters use a multi-primary topology and they don't have any replicas in each section.

Extension storage

See also: Debugging in production#Debugging databases

x1

The x1 cluster is used by MediaWiki at WMF for databases that are "global" or "cross-wiki" in nature, and are typically associated with a MediaWiki extension. Apart from being used across wikis, it is otherwise configured, replicated, and queried in the same way as "core" databases.

Tables that don't need to join with core tables can (and should) live in x1, there is a wiki db for per-wiki tables.

Feature Database Phabricator project Steward
BounceHandler wikishared.bounce_*

#MediaWiki-extensions-BounceHandler

CampaignEvents wikishared.campaign_events, wikishared.ce_*, [wiki].campaign_events, [wiki].ce_*

#CampaignEvents

Campaigns Product Team
CheckUser [wiki].cusi_*, wikishared.cuci_*

#CheckUser #CheckUser-SuggestedInvestigations

Product Safety & Integrity
Cognate cognate_wiktionary.*

#Cognate

CommunityRequests wikishared.communityrequests_*

#MediaWiki-extensions-CommunityRequests

Community Tech
ContentTranslation wikishared.cx_*

#ContentTranslation

Language and Product Localization
Echo wikishared.echo_*, and

[wiki].echo_*

#Notifications

Growth Team
Flow flowdb.*

#StructuredDiscussions

Growth Team
GrowthExperiments [wiki].growthexperiments_*

#MediaWiki-extensions-GrowthExperiments

Growth Team
LoginNotify wikishared.loginnotify_*

#MediaWiki-extensions-LoginNotify

MediaModeration [wiki].mediamoderation_*

#MediaModeration

ReadingLists wikishared.reading_list_*

#Reading List Service

UrlShortener wikishared.urlshortcodes

#MediaWiki-extensions-UrlShortener

WikimediaCampaignEvents wikishared.wikimedia_campaign_events_grant, [wiki].wikimedia_campaign_events_grant

#WikimediaCampaignEvents

Campaigns Product Team
WikimediaEditorTasks wikishared.wikimedia_editor_tasks_*

#Product-Infrastructure-Team-Backlog

x3

The x3 cluster is used to store Wikidata's term store tables (wbt_*), which were split out of s8 in 2025 (T351820).

x4

The x4 cluster is planned to store the *links tables for Commonswiki (T398709).

MainStash

Main article: mw:MainStash

The ms-prefixed clusters back the MainStash feature of MediaWiki, which represents a replicated and persistent cache (similar to ParserCache), in constract with Memcached which is ephemeral and LRU-evicted (Least Recently Used). Currently there are ms1, ms2, and ms3. Each cluster is read-write in all data centers and replicated in both directions.

The replication topology is primary-primary, meaning the eqiad primary and codfw primary replicate from each other and they are both writable. The ms-clusters do not use the replicas for reads, everything is read from the primaries.

Like ParserCache, MainStash clusters store data sharded by cache key, and are shared by all wikis.

To depool the primary of a MainStash section, you need to depool both primaries in both DCs (see the guide).

These hosts use STATEMENT based replication and they have Innodb-flush-log-at-trx-commit = 0 for better performance as data consistency isn't critical.

History:

  • Launched in 2018 as the x2 cluster (T212129).
  • In 2025, the x2 cluster was replaced by the ms- clusters (T383327).

Miscellaneous

Main article: MariaDB/misc

The miscellaneous clusters host databases for various auxiliary services.

Database creation template

If you need to request a new database please create a task with the DBA tag and fill out the following fields if you know them (or can estimate them)

  • QPS:
  • Size: (An estimation is fine)
  • DB Name:
  • User:
  • Accessed from server (s):
  • Backup Policy: Needed? Frequency?
  • Grants needed:

Cloud Services

Replicas of the core databases with private data removed are part of the Data Services hosted by Wikimedia Cloud Services for bot, tool, and analysis use. The redaction is done during an intermediate stop on Sanitarium.

These replicas run in multiple hosts (clouddb1013-1020), with multiple processes. See Portal:Data Services/Admin/Wiki Replicas for more details.

To depool a Wiki Replica host, see Portal:Data Services/Admin/Runbooks/Depool wikireplicas.

Data Platform

The Data Platform includes a variety of MariaDB databases:

Architecture

Replicas

The primary database is the primary server for a given shard. If the primary database server fails, then a replica can be promoted to be a new primary. See Primary database switchover for more information.

A replica database is another MariaDB or MySQL instance that replicates data from the primary database. Most often, we add replicas in production for the purpose of load-balancing read queries, and for backup/failover purposes.

Storage engines

We use InnoDB for storage engine.

In 2015, TokuDB was trialled but was found to be buggy.

Puppet

The main module for DBAs on the operations/puppet tree is "mariadb", which is in its own repo operations/puppet/mariadb. Remember to update the subrepo when committing changes to the mariadb module, otherwise it will not be caught by palladium, strontium, CI, etc.

Despite all the previous, there are mariadb-related files on the main repo- shared among the classes. Those are the puppet/templates/mariadb/ files, where the main configuration and grants lie.

There is a forth place where you will find mariadb-related files, and that is the private repository, but I suppose that requires no explanation (passwords, etc.).

Other DBA related repos are:

  • operations/software/dbtools: for scripts used for maintenance
  • operations/software/redactatron: labs filtering
  • operations/software/wmfmariadbpy
  • operations/software/schema-changes

Backups

Main article: MariaDB/Backups

In 2017, multiple hardware and scalability issues led to a complete redesign of the database backup architecture, leading to the current one explained at MariaDB/Backups, with focus on full coverage, validation and testing, and full automation recovery.

Testing servers

As of today (Dec 2022) there are two testing hosts on a shard called test-s4:

  • DB primary (writable): db1124.eqiad.wmnet 256GB RAM + RAID10
  • DB replica: (read-only): db1125.eqiad.wmnet 256GB RAM + RAID10

Whilst these hosts are normally up, it is not guaranteed they will always be available or up.

These hosts have static data, and are not updated with production data. More tables or wikis can be requested if needed by filing a ticket to DBAs). It is not guaranteed that they'll be included, but they can be studied and some expectations about ETA and if it is possible to have that data will be given on the ticket.

Access to these hosts is granted individually to those who already have an NDA and cluster access if really needed. Please file a ticket and tag DBA to let us know why you need access so we can start a discussion about it.

People with access:

  • Data Persistence Members

HAProxy

Main page containing : HAProxy

dbproxy1XXX boxes run HAProxy. Besides making for easier Ops server rotations simply by having a proxy IP in the mix, there are two modes for other stuff: load balancing for replicas and failover for primaries. So far, only Miscellaneous primary clusters and the Cloud Services replicas use it via dbproxy1XXX.

We also have codfw proxies (dbproxy2XXX) serving misc services.

Failover

See role::mariadb::proxy::master

Load Balancing

See role::mariadb::proxy::slaves

Monitoring

See MariaDB/monitoring.

Long running queries

Main article: /troubleshooting#Overload_due_to_long_running_queries

You can identify long running queries on logstash. The query killer should stop read queries running longer than 60 seconds, but may not function properly under high load. db-kill can be used in emergencies to kill any query running for longer than 10 seconds.

Replication lag

See also: Map of database maintenance and Tool:Replag

Production replication lag can be checked on https://orchestrator.wikimedia.org/web/clusters (an NDA is needed to access it).

See MariaDB/troubleshooting#Replication lag and MySQL#Replication lag on how to handle replication lag.

Table drift between code and production

There are known differences between table definitions in MW and the live version in production. We have historically worked hard to fix the most important ones, but there are still lots of minor ones that need to be solved.

There is a dashboard where those can be checked/observed: https://drift-tracker.toolforge.org/report/core/

Admin guides

Main article: MariaDB/Administration



Retrieved from "https://wikitech.wikimedia.org/w/index.php?title=MariaDB&oldid=2356311"