High Replication Lag on Secondary under Write Load on Primary

MySQL & MariaDB MariaDB Server & Utilities
1

Hello,

I am experiencing an issue with my MariaDB Replication Cluster (11.4.4-MariaDB) consisting of 1 Primary and 2 Secondary servers. All servers are identically configured, and the cluster is monitored using PMM. Primary and Secondary 1 are located in our main data center (local 100 Gbit network), while Secondary 2 is in a backup data center connected via a 1 Gbit leased line.

All tables use the Aria engine. The problem occurs under high write load on the Primary:

  • Secondary 1 (same DC, high-speed network) experiences extreme replication lag, sometimes peaking at 10–20 minutes before dropping back to zero.
  • Secondary 2 (remote DC, slower network) only shows brief spikes of 5–20 seconds, then catches up quickly.
#log-bin
server_id=2
log-basename=master1
binlog-format=mixed
binlog_expire_logs_seconds=1209600

read_only=1

PMM hasn’t revealed any clear bottlenecks. All servers share the same configuration.

Has anyone encountered similar behavior? Are there tips or diagnostics you recommend to determine why Secondary 1’s replication lag spikes so high under these conditions?

Thank you for your assistance.

Best regards,
Andreas

Latest Event:
Secondary 1:

grafik
grafik2177×446 28.5 KB

grafik
grafik2175×870 100 KB

grafik
grafik2177×945 238 KB

grafik
grafik2180×884 172 KB

Secondary 2:

grafik
grafik2177×469 23.2 KB

grafik
grafik2175×1018 230 KB

grafik
grafik2170×904 113 KB

grafik
grafik2171×911 153 KB

2

You shouldn’t be using MIXED. You should be using ROW. MIXED defaults to STATEMENT in all cases except when MySQL decides the SQL is non-deterministic. STATEMENT replication completely re-executes all replicated SQL including temporary table, and table locks. ROW does none of that.

You can see in the PMM graphs provided, the high number of table locks, correlates near exactly to the replication spike.

The Aria engine uses full table locks. That means when trx1 starts and runs UPDATE/INSERT/DELETE on a table, the entire table is locked. No other trx can modify this table while this trx is running. This is obviously horrible for any type of write performance.

You should switch all your application tables to InnoDB, which uses row-level locking. Row-level locking means trx1, trx2, and trx3 can all execute UPDATE at the same time on the same table as long as they don’t update the same rows.

tldr; Change binlog_format=ROW, change all app tables to InnoDB engine, set innodb_buffer_pool_size equal to 80% of the system memory, and decrease any Aria-related memory settings to 128MB.

3

Hi @matthewb,
Thank you for your quick response! :slightly_smiling_face:

1) binlog-format=mixed
Since this is the new default, I assumed it was the optimal setting. However, I can switch to ROW. If I understand this documentation correctly, this change can be done live without issues, right? (We have Parallel Threads = 0). Is there a recommended order for changing the servers (Primary first, then Secondary)? Is it safe if the settings differ for a few seconds?

2) ARIA vs. InnoDB
Our SaaS CMS primarily performs read operations (99% reads / 1% writes in the last 48 hours, according to mysqltuner.pl). High write peaks occur only 1–2 times per week, e.g., when clients send really large newsletters (tens of thousands or more). Given this, ARIA seems suitable for read-heavy workloads. Am I missing something?
Additionally:

  • The Primary handles all read/write operations.
  • Secondary 1 (same DC) is read-only, used only for backups.
  • Secondary 2 (different DC) is read-only, a failover and idle otherwise.
    So, no concurrent access happens on the secondaries beyond replication.

3) Further ARIA/InnoDB Consideration
tuning-primer.sh shows:

TABLE LOCKING  
Current Lock Wait ratio = 1 : 437  
You may benefit from selective use of InnoDB.

Would it make sense to migrate certain tables to InnoDB? Can PMM help identify which tables would benefit most?
Also, I’d expect increased RAM usage due to configuring both innodb_buffer_pool_size and ARIA memory settings, correct?

4) slave_parallel_threads
Would enabling this make sense in my scenario?

Main Concern:
What confuses me is why Secondary 2 (in another DC with slower network) doesn’t suffer from the same replication lag as Secondary 1. Both are set up identically. Could something specific be negatively affecting Secondary 1? Any PMM metrics I should check to diagnose this?

Thank you again!
Best regards from sunny Tyrol,
Andy

Last 12 Hours:

grafik
grafik1920×4532 571 KB

grafik
grafik1920×993 116 KB

grafik
grafik3740×1033 334 KB

grafik
grafik1920×974 85.1 KB

4
  1. Yes, you can change binlog_format at any time. It does not matter which server you change first, and it is perfectly OK for the two servers to have different formats.
  2. On paper, your arguments for Aria sound good. My concerns are that Aria has no transactions/ACID compliance, and it relies on the OS disk cache for any corruption protection. If those aren’t a concern for you, then stick with Aria.
  3. I would migrate everything to InnoDB, then you don’t have to worry about the increase RAM usage. You would simply divert any RAM associated with Aria engine over to InnoDB. It’s best to keep your database using a single engine for this reason.
  4. That could help; I thought the default was 4, so I assumed this was already enabled.

Main concern

I’d be looking at every graph, side-by-side between source and replica 1, and then between replica 1 and replica 2. Look at CPU, disk IO, disk latency, table lock time, qps, etc. Try to find correlations: you should see X on source, and then the same effect on replica a few seconds/minute later.

1 Like