Flow Control, Certification Conflicts, Auto-Increment, Hot Rows, and Workload Constraints

Learning outcomes

ServiceHub now has three healthy writable nodes, but the workload is not automatically three times faster. Two dispatch workers update the same queue counters from different nodes; bulk imports create large write sets; one reporting node sometimes applies changes more slowly. Galera exposes these problems as certification conflicts, receive queues, and flow control rather than hiding them behind replication lag.

1. Start a clean three-node lab and create the hot-row fixture

services:  n1:    image: mariadb:12.3.2    container_name: mdb15-n1    environment:      MARIADB_ROOT_PASSWORD: labroot    command:      - --bind-address=0.0.0.0      - --binlog-format=ROW      - --default-storage-engine=InnoDB      - --innodb-autoinc-lock-mode=2      - --wsrep-on=ON      - --wsrep-provider=/usr/lib/galera/libgalera_smm.so      - --wsrep-cluster-name=servicehub15      - --wsrep-cluster-address=gcomm://n2,n3      - --wsrep-node-name=n1      - --wsrep-sst-method=rsync      - --wsrep-new-cluster    ports:      - "33151:3306"  n2:    image: mariadb:12.3.2    container_name: mdb15-n2    environment:      MARIADB_ROOT_PASSWORD: labroot    command:      - --bind-address=0.0.0.0      - --binlog-format=ROW      - --default-storage-engine=InnoDB      - --innodb-autoinc-lock-mode=2      - --wsrep-on=ON      - --wsrep-provider=/usr/lib/galera/libgalera_smm.so      - --wsrep-cluster-name=servicehub15      - --wsrep-cluster-address=gcomm://n1      - --wsrep-node-name=n2      - --wsrep-sst-method=rsync    ports:      - "33152:3306"  n3:    image: mariadb:12.3.2    container_name: mdb15-n3    environment:      MARIADB_ROOT_PASSWORD: labroot    command:      - --bind-address=0.0.0.0      - --binlog-format=ROW      - --default-storage-engine=InnoDB      - --innodb-autoinc-lock-mode=2      - --wsrep-on=ON      - --wsrep-provider=/usr/lib/galera/libgalera_smm.so      - --wsrep-cluster-name=servicehub15      - --wsrep-cluster-address=gcomm://n1      - --wsrep-node-name=n3      - --wsrep-sst-method=rsync    ports:      - "33153:3306"

docker compose down -v --remove-orphans# Bootstrap exactly one node for a brand-new disposable cluster.docker compose up -d n1# Wait until n1 reports wsrep_ready=ON, then join the other nodes.docker compose up -d n2 n3# Verify all three nodes before creating application data.docker exec mdb15-n1 mariadb -uroot -plabroot -e "SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size'; SHOW GLOBAL STATUS LIKE 'wsrep_cluster_status';"docker exec mdb15-n2 mariadb -uroot -plabroot -e "SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment';"docker exec mdb15-n3 mariadb -uroot -plabroot -e "SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment';"

CREATE DATABASE IF NOT EXISTS servicehub_galera_lab;CREATE TABLE IF NOT EXISTS servicehub_galera_lab.tickets (  ticket_id BIGINT PRIMARY KEY AUTO_INCREMENT,  customer_id BIGINT NOT NULL,  state VARCHAR(24) NOT NULL,  priority INT NOT NULL DEFAULT 0,  updated_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)             ON UPDATE CURRENT_TIMESTAMP(6)) ENGINE=InnoDB;CREATE TABLE IF NOT EXISTS servicehub_galera_lab.counters (  counter_name VARCHAR(64) PRIMARY KEY,  counter_value BIGINT NOT NULL) ENGINE=InnoDB;INSERT INTO servicehub_galera_lab.counters(counter_name,counter_value)VALUES ('dispatch',0)ON DUPLICATE KEY UPDATE counter_value=VALUES(counter_value);INSERT INTO servicehub_galera_lab.tickets(customer_id,state,priority) VALUES (501,'open',1),(502,'open',1);

SHOW GLOBAL STATUS WHERE Variable_name IN ( 'wsrep_local_cert_failures','wsrep_local_bf_aborts', 'wsrep_local_recv_queue_avg','wsrep_flow_control_paused', 'wsrep_flow_control_sent','wsrep_cert_deps_distance');

2. A deterministic mental model for certification conflicts

Open two clients: Session A on n1 and Session B on n2. Both transactions update the same primary-key row while neither has yet committed. There is no single distributed row lock that prevents both sessions from executing. The write sets meet at certification. One ordering wins; the conflicting transaction must roll back/retry.

START TRANSACTION;UPDATE servicehub_galera_lab.countersSET counter_value=counter_value+1WHERE counter_name='dispatch';-- do not COMMIT yet

START TRANSACTION;UPDATE servicehub_galera_lab.countersSET counter_value=counter_value+10WHERE counter_name='dispatch';-- do not COMMIT yet

Commit Session A, then commit Session B. Depending on exact scheduling, either side can be the victim, but the important result is that the two conflicting commits cannot both be accepted as independent histories.

-- Session ACOMMIT;-- Session BCOMMIT;-- Then on both nodesSHOW GLOBAL STATUS WHERE Variable_name IN ('wsrep_local_cert_failures','wsrep_local_bf_aborts');

3. Retry belongs to the application contract

wsrep_retry_autocommit can retry autocommitted statements a limited number of times, but multi-statement transactions still require application reasoning. A safe retry repeats a business operation whose inputs and external side effects are controlled. Retrying “charge card, write database, send email” as a single blind loop can duplicate non-database effects.

SHOW GLOBAL VARIABLES LIKE 'wsrep_retry_autocommit';

4. AUTO_INCREMENT is coordinated for uniqueness, not gaplessness

With wsrep_auto_increment_control=ON, Galera adjusts auto_increment_increment and auto_increment_offset as membership changes so simultaneous inserts from several nodes are less likely to collide. The consequence is expected gaps. Business logic must never interpret consecutive IDs as “no missing tickets.”

SHOW VARIABLES WHERE Variable_name IN ('wsrep_auto_increment_control','auto_increment_increment','auto_increment_offset');

INSERT INTO servicehub_galera_lab.tickets(customer_id,state,priority) VALUES (601,'open',1);SELECT LAST_INSERT_ID();

SELECT ticket_id,customer_idFROM servicehub_galera_lab.ticketsWHERE customer_id>=600ORDER BY ticket_id;

Do not predict exact IDs in the lesson; offsets depend on membership and prior allocation. Verify uniqueness and replication, not consecutiveness.

5. Flow control is deliberate cluster-wide backpressure

Every node receives ordered write sets, but local apply can lag. If a receive queue grows beyond provider thresholds, that node sends flow-control pause messages. Writers across the Primary Component slow down so the lagging node can catch up. That protects bounded queues and consistency at the cost of cluster throughput.

Counters are cumulative/interval-sensitive. Record a start point or use FLUSH STATUS only when you understand which counters it resets. Correlate with CPU, storage latency, lock waits, query workload, and network evidence.

6. Create pressure without claiming a universal result

The following workload creates many small commits from n1 while n3 executes a local CPU/I/O-heavy query. On some machines it will raise queues/flow-control metrics; on others the cluster remains fast enough. Both outcomes are valid local observations.

SELECT BENCHMARK(2000000, SHA2('servicehub',256));

for i in $(seq 1 500); do docker exec mdb15-n1 mariadb -uroot -plabroot -e "UPDATE servicehub_galera_lab.counters SET counter_value=counter_value+1 WHERE counter_name='dispatch';"; done

SHOW GLOBAL STATUS WHERE Variable_name IN ('wsrep_local_recv_queue_avg','wsrep_flow_control_paused','wsrep_flow_control_sent','wsrep_cert_deps_distance');

If flow control stays near zero, do not tune anything. If one node consistently sends flow control, find why that node cannot apply at the cluster rate before increasing provider queue thresholds.

7. Wrong tuning approach: hide the slow node with larger queues

Provider options such as gcs.fc_limit change when flow control engages. Raising them can postpone backpressure but increases queued work and memory requirements; it does not make the slow disk, hot row, or blocked applier faster. Likewise, blindly increasing wsrep_slave_threads/wsrep_applier_threads can stop helping when dependencies or storage contention dominate.

Use a controlled baseline: one workload, one variable, same dataset, same cluster membership, same cache/warm-up conditions. Accept a change only if throughput/latency improves without unacceptable conflict, queue, memory, or durability consequences.

8. Transaction size and hot-row design

• Prefer moderate transactions. Very large write sets consume memory, increase certification/apply work, and can amplify recovery/SST consequences.
• Partition business contention logically: avoid one global “next number” row or single mutable aggregate when independent shards/counters can exist.
• Use primary keys on replicated InnoDB tables. Galera's row identity/certification depends on stable keys.
• Multi-primary capability is optional. Many teams route writes to one preferred node to reduce conflict while retaining fast failover to another node.

9. Production judgment

Galera is strongest when transactions are short, keyed, and mostly independent. Treat certification aborts as a workload signal, not merely an exception to suppress. Treat flow control as a safety mechanism telling you the cluster's sustainable write rate is bounded by the slowest relevant member.

Next, separate those database guarantees from the client-routing problem: a node can be running and reachable over TCP while being Non-Primary, Joining, Donor/Desynced, or otherwise unsuitable for application traffic.

docker compose down -v --remove-orphans