MySQL and MariaDB read replicas #

This page is for database administrators who run MySQL or MariaDB with replicas and want Exponential to use them. It answers: which settings send reads to a replica, how the replica is chosen, what lag means for the site, and how the other engines compare. MySQL and MariaDB are the only engines with built-in read splitting in Exponential. Source: Replicas and settings/site.ini.

Contents #

1. The settings
2. How a replica is chosen
3. Replication lag
4. Accounts on the replicas
5. Do you need replicas at all
6. Other engines
7. Checklist

The settings #

The keys are in site.ini [DatabaseSettings]; the shipped file has them commented out:

# settings/override/site.ini.append.php
[DatabaseSettings]
UseSlaveServer=enabled
SlaveServerArray[]
SlaveServerArray[]=replica1.example.internal
SlaveServerArray[]=replica2.example.internal
SlaveServerPort[]
SlaveServerPort[]=3306
SlaveServerPort[]=3306
SlaverServerUser[]
SlaverServerUser[]=exponential_ro
SlaverServerUser[]=exponential_ro
SlaverServerPassword[]
SlaverServerPassword[]=<the password>
SlaverServerPassword[]=<the password>
SlaverServerDatabase[]
SlaverServerDatabase[]=exponential
SlaverServerDatabase[]=exponential

The keys for user, password and database really are spelled Slaver...; that is the name the code reads. The lists are parallel: the second entry of each list belongs to the second replica. The empty Key[] line first resets the list, so that only the entries of this file count.

Clear the INI cache, reload PHP-FPM and restart Velocity after a change (Reading and changing database settings).

How a replica is chosen #

With UseSlaveServer=enabled, eZDB::instance() picks one replica at random (mt_rand() in lib/ezdb/classes/ezdb.php) when it creates the connection: per request under PHP-FPM. Reads go to that replica; writes always go to Server. Setup > System information, part Database connection, shows the read replica in use (System information).

Replication lag #

A page may show content a few seconds old while a replica catches up. In practice:

• an editor who publishes and immediately views the page may see the previous version for as long as the lag lasts;
• the caches (view cache, Velocity's response cache) may then keep that older rendering until their next refresh.

Keep lag low and monitor it on the replicas with your usual tools (for example SHOW REPLICA STATUS on MySQL 8.0.22 and later, SHOW SLAVE STATUS on older releases and MariaDB). Exponential itself does not measure lag or take a lagging replica out of rotation; that is the job of your replication monitoring or a proxy in front of the replicas.

Accounts on the replicas #

A replica account needs read privileges; the kernel creates temporary tables for some read operations, so give it CREATE TEMPORARY TABLES as well, or test the read paths you rely on (search, lists) with a strict read-only account on staging. Keep the replica password in settings/override/ like the main one.

Do you need replicas at all #

Most page views do not reach the database: Velocity's response cache, the content view cache and the template-block cache answer first, and the SQL query cache can answer repeated SELECTs (Performance and scaling). Measure first with exp:benchmark and the SQL profiler (Finding slow queries). Replicas are worth their operation when uncached reads (signed-in editors, search, the first views after a clear) really load the primary.

Other engines #

Source: 9.7 Comparison.

Checklist #

• To do: The lists of hosts, ports, users, passwords and databases have the same length.
• To do: The replica account can read every table and create temporary tables.
• To do: Lag is monitored outside Exponential.
• To do: Editors know that a fresh publish may show with a short delay.

Related pages #

• Setting up MySQL and MariaDB
• Several web nodes
• The DatabaseSettings reference
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.