Safe Change Validation

This is a sample test plan, of the kind Sixta produces when a capacity assessment recommends a change and somebody sensibly asks to see the evidence before engineering time is committed to it. It is a follow-up to a Sixta Yield report for a customer running Aurora MySQL on a db.r8g.24xlarge writer, 96 vCPU and 768 GB of RAM, on engine version 8.0.mysql_aurora.3.09.0.

The point of the document is not the specific parameter. It is the shape: a change worth making, a cheap and reversible experiment that measures its upper bound, and a written record of exactly what will be done, what will be watched, and what would cause it to be stopped.

Why this test

The capacity assessment found that 65.5% of database load was being spent in wait/io/redo_log_flush: the time each COMMIT spends waiting for Aurora's distributed storage layer to confirm a durable write. AWS's own documentation for this wait event names the cause as too many commits, and prescribes application-level batching as the remedy.

The recommended fix is transaction batching in the application, grouping 50 to 100 inserts per COMMIT instead of committing each one. That is real engineering work. Before spending it, this test measures how much improvement is available, by temporarily removing the redo flush wait at the database level instead.

Setting innodb_flush_log_at_trx_commit=0 tells Aurora MySQL to return success to the client immediately on COMMIT, without waiting for storage acknowledgement. That removes precisely the wait event batching would reduce, so the measured improvement is the upper bound of what batching can achieve. Real batching at a batch size of 100 would deliver roughly 95% of the same improvement, with no durability trade-off at all.

What the test requires

Two parameter changes, a 90-minute window, and direct MySQL access on the writer.

Parameter changes, in order

1. innodb_trx_commit_allow_data_loss = 1. Aurora's safety gate. It must be set before innodb_flush_log_at_trx_commit=0 will take effect; without it Aurora ignores the setting and behaves as if it were 1. This should be dynamic in Aurora MySQL 3.x with no reboot required, but the database team should confirm by checking the Apply Type column in the parameter group. If it reads static, a reboot is required and the test logistics change significantly.

2. innodb_flush_log_at_trx_commit = 0. The test parameter. Dynamic, and takes effect immediately on all new transactions.

For a short test, prefer SET GLOBAL over modifying the parameter group. The change then reverts automatically on the next reboot and leaves no trace in the parameter group:

SET GLOBAL innodb_trx_commit_allow_data_loss = 1;
SET GLOBAL innodb_flush_log_at_trx_commit = 0;

Prerequisites

• A 90-minute window during representative traffic, ideally overlapping peak hours (21:00 to 23:00 UTC, based on the original assessment).
• Direct MySQL access on the writer instance, or a DBA willing to run the SET GLOBAL commands.
• Agreement to revert immediately if any anomaly is observed.
• CloudWatch console access, to capture the CommitLatency and CommitThroughput graphs.

Test procedure: 90 minutes, seven steps

[table-1]

T-30: baseline capture

Run both sixta-yield tools before anything changes, and record the exact UTC time.

sixta-yield aws \
  --instance=prod-primary-writer-0 \
  --region=us-east-1 \
  --role-arn=<ARN> --external-id=<ID> \
  --format=json > baseline-aws.json

sixta-yield diagnose \
  --instance=prod-primary-writer-0 \
  --region=us-east-1 \
  --role-arn=<ARN> --external-id=<ID> \
  --hours=1 --format=json > baseline-diagnose.json

T=0: apply the change

The database team runs the two SET GLOBAL statements above on the writer instance. Record the exact UTC time.

T+5: verify

SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
-- Expected: 0

SHOW GLOBAL VARIABLES LIKE 'innodb_trx_commit_allow_data_loss';
-- Expected: 1

If innodb_flush_log_at_trx_commit still reads 1, the allow_data_loss prerequisite has not taken effect. Abort the test.

T+10 to T+60: wait

Leave it running and change nothing else. Application traffic continues normally; nobody outside the database team needs to know the test is happening. Watch CloudWatch for anomalies: error rates should remain stable, connection count should remain stable, and no alerts should fire. There should be no visible change to the application. Operations should feel the same, or faster.

T+60: test capture

Run the same two sixta-yield commands, writing to test-aws.json and test-diagnose.json.

T+65: revert

SET GLOBAL innodb_flush_log_at_trx_commit = 1;

-- Optional; has no effect when flush=1, but leaves a clean state:
SET GLOBAL innodb_trx_commit_allow_data_loss = 0;

T+70: verify the revert

SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
-- Expected: 1

Expected measurements

Primary metrics, from sixta-yield

[table-2]

Secondary metrics, from CloudWatch

Screenshot or export these graphs covering the full test window plus thirty minutes either side.

[table-3]

What not to measure

Application-level latency, meaning API response times, may not change noticeably, and it is important to say so before the test rather than after it. The redo flush wait sits inside the database engine. It appears as part of query time, but each individual query is already fast, sub-millisecond at P50. The improvement is in throughput capacity under load, not in individual query latency at current utilisation. Do not set the expectation that API latency will visibly drop. The win is headroom, not current-state latency.

Three independent lines of evidence

1. Wait event profile shift, direct evidence

The before and after diagnose output shows the proportion of database time spent in each wait event. If io/redo_log_flush drops from 65% to near zero, that is direct proof the parameter eliminated the bottleneck. It is not a statistical inference; it is an observation of where the engine spent its time. The remaining profile also reveals the next bottleneck, most likely wait/io/table/sql/handler, the actual INSERT I/O, or CPU. That tells you the new ceiling.

2. USL parameter change, derived evidence

If the USL alpha parameter decreases, the system's serialisation fraction has decreased: more of each request's time is spent doing useful work rather than waiting for storage acknowledgement. The change in N_critical, the concurrency at which throughput peaks, quantifies how much additional load the system could absorb.

3. CloudWatch commit latency, independent corroboration

CommitLatency is an AWS-provided metric, independent of sixta-yield. If it drops by a factor of five to ten, it corroborates the Performance Insights wait event data and confirms the change had the expected mechanical effect.

Controlling for traffic variation

The test and the baseline should run during comparable traffic. A baseline captured at 21:00 UTC at peak, compared against a test run from 02:00 to 03:00 UTC when the system is quiet, is a weak comparison. Ideal timing is the same time of day on consecutive days, or both captures inside the same peak period: baseline at 20:00, parameter change at 21:00, test capture at 22:00.

If traffic levels differ despite best efforts, the comparison remains valid, for three reasons. The diagnose output reports proportions, the percentage of total load per wait event, which are comparable regardless of absolute load. The USL parameters alpha and beta are load-independent: they are structural properties of the workload. Only the absolute AAS numbers will differ.

The result is an upper bound, not a proposal

This test measures what happens when the redo flush wait is removed entirely. Real application-level batching would not remove it entirely; it would reduce it in proportion to the batch size. At a batch size of 100 the application performs roughly 99% fewer COMMITs, so redo flush time falls by roughly 99% — very nearly the same result as the test.

The test result is therefore the upper bound of what batching achieves. In practice batching gets you 90 to 95% of the way there while preserving full durability. The argument it lets you make is a short one: we temporarily removed the redo flush wait and measured an X-fold improvement in capacity ceiling; transaction batching achieves the same result durably, with no data loss risk; here are the numbers.

Risks: low, bounded, instantly reversible

The data loss window

During the test, if the Aurora MySQL database engine process crashes, transactions that were acknowledged to the application but not yet written to Aurora storage are lost. The window is approximately one second of recent commits.

Three distinctions matter here. This is not a storage failure risk: Aurora's storage layer is a separate distributed system with six-way replication across three availability zones, and the parameter changes when the engine waits for storage confirmation, not how durably storage holds data. The risk event is specifically the mysqld process crashing — an OOM kill, a bug — not hardware failure or an AZ outage. And Aurora process crashes are rare; across a one-hour window the probability is very low.

[table-4]

Worst realistic outcome

The Aurora engine crashes during the one-hour test window, which is very unlikely, and approximately one second of event updates is lost. Those records are stale for a few seconds until the next update arrives. The impact is comparable to a brief network partition between the application and the database: the app retries and life continues.

Deliverables

After the test, produce a before-and-after comparison containing the wait event profile from both diagnose runs; the USL parameters alpha, beta, N_critical and efficiency for both; the yield score for both; CloudWatch screenshots of CommitLatency and CommitThroughput spanning the test window; and a recommendation, in the form: transaction batching would deliver X of this improvement durably, at a recommended batch size of 50 to 100 rows per COMMIT for the event ingestion service.

Questions about this sample, or about a test plan for a change you are considering: ewen@sixta.ai.