The InnoDB Durability Knob Most VPS Deployments Have Wrong
innodb_flush_log_at_trx_commit defaults to 1 for a reason. Most cheap VPS setups quietly change it and hope nothing bad happens.
I've audited a lot of MariaDB configs over the years, and there's one line I see tweaked on almost every budget VPS setup: innodb_flush_log_at_trx_commit = 2. It's in blog posts, DigitalOcean community tutorials, random Stack Overflow answers. Everyone recommends it for performance. Almost nobody explains what you're actually giving up.
The short version: you're trading crash durability for speed, and on a shared VPS with a hypervisor that can yank your instance at any time, that's a worse trade than it looks on paper.
What This Setting Actually Controls
InnoDB keeps a write-ahead log - the redo log - so it can recover committed transactions after a crash. The innodb_flush_log_at_trx_commit setting controls how aggressively MariaDB flushes that log to durable storage on every commit. There are three values:
- 1 (default): On every commit, write to the log buffer AND call
fsync()to flush to disk. Fully ACID-compliant. If the machine dies mid-flight, committed transactions survive. - 2: On every commit, write to the OS page cache but only
fsync()once per second. If MariaDB crashes, you're fine - the OS cache survives. If the whole machine loses power or the hypervisor kills the VM hard, you can lose up to a second of committed transactions. - 0: Write to the log buffer once per second, flush once per second. If MariaDB itself crashes, you can lose up to a second of commits. Worse than 2 in almost every scenario.
The performance difference between 1 and 2 is real. On a spinning disk or a cheap SSD with no battery-backed write cache, fsync() on every commit is expensive. I've seen it account for 30-40% of total write latency on underpowered VPS instances. So I understand why people flip this knob. I've been tempted myself.
But here's what most of those tutorials gloss over.
The VPS Problem Nobody Talks About
On bare metal with a RAID controller that has a battery-backed write cache (BBU), setting this to 2 is a reasonable compromise. The OS page cache effectively becomes durable because the hardware protects it through power loss. That's the environment most of the "just set it to 2" advice was written for.
A DigitalOcean Droplet, a Linode, a Vultr VPS - none of those give you BBU semantics. The hypervisor can live-migrate your instance, run out of memory and hard-kill your VM, or lose power itself. The OS page cache on a virtual machine is not durable storage. It evaporates when the VM does.
I learned this the hard way on a client project a few years back - an e-commerce shop running on a mid-tier Linode. Their managed MariaDB setup had innodb_flush_log_at_trx_commit = 2 baked in from some optimized config template they'd grabbed. During a routine host maintenance window, the hypervisor hard-restarted their VM with no warning. They came back up missing about 40 seconds of order records. Not a lot, but enough to cause a support nightmare and a manual reconciliation job against their payment processor logs.
Forty seconds of orders isn't the end of the world. But it also wasn't zero, and it was entirely preventable.
Checking Your Current Setting
First, see what you're actually running:
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
Then check whether it's set in your config file or just running as a default:
grep -r 'innodb_flush_log' /etc/mysql/
On Debian/Ubuntu-based systems, also check /etc/mysql/mariadb.conf.d/ - config is split across multiple files and it's easy to miss an override.
The PHP/Laravel Angle
If you're running a Laravel app that relies on database transactions for anything important - order creation, payment recording, user registration - you need to know what your database is actually guaranteeing you.
Here's a snippet I use to sanity-check MariaDB connection settings from a Laravel service provider during boot in non-production environments:
<?php
namespace App\Providers;
use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
class DatabaseSanityServiceProvider extends ServiceProvider
{
public function boot(): void
{
if (! app()->isProduction()) {
return;
}
$flushSetting = DB::selectOne(
"SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'"
);
$value = (int) ($flushSetting->Value ?? -1);
if ($value !== 1) {
Log::critical('innodb_flush_log_at_trx_commit is not 1', [
'value' => $value,
'risk' => 'committed transactions may not survive a VM crash',
]);
}
}
}
This runs once at boot, writes to your log if something is wrong, and costs essentially nothing. I've started dropping this into client projects as a quiet canary. You'd be surprised how often it fires on inherited infrastructure.
The Gotchas Beyond the Obvious
Config file wins are silent. If you have innodb_flush_log_at_trx_commit = 2 in /etc/mysql/mariadb.conf.d/50-server.cnf and you think you set it back to 1 in /etc/mysql/conf.d/my.cnf, the load order might not be what you think. Always verify with SHOW VARIABLES after a restart, not just by reading the file.
Managed database services lie to you. PlanetScale, AWS RDS, and similar managed offerings typically handle durability at the storage layer independently of this setting. But if you're running MariaDB on a VPS you manage yourself - even with something like Forge or Ploi doing the provisioning - you own the config.
The sync_binlog setting compounds this. If you're running replication, sync_binlog = 1 pairs with innodb_flush_log_at_trx_commit = 1 to give you full durability. Setting one but not the other leaves a gap. I've seen setups where someone hardened the InnoDB flush setting but left sync_binlog = 0 (the default), which means the binary log - used for point-in-time recovery and replication - still isn't synced on every commit.
SHOW VARIABLES LIKE 'sync_binlog';
If you care about durability, that should be 1 too.
fsync on cheap SSDs is still slow. Some NVMe SSDs on cloud providers have published fsync latencies in the 1-5ms range per operation. At 200 transactions per second, that's your bottleneck. The right answer there isn't to disable flushing - it's to batch work into fewer, larger transactions, use connection pooling properly, or move to a managed database with real hardware backing it.
When I'd Change It (and When I Wouldn't)
I would consider setting this to 2 in exactly one scenario: a development or staging environment where data loss is acceptable and I need to benchmark write-heavy workloads without the fsync overhead confounding the numbers. Even then I document it and revert before anything goes near production.
I would not change it from 1 on:
- Any production VPS, full stop
- Any system handling financial transactions, healthcare records, or anything with audit requirements
- Any primary database in a replication chain (your replica's durability is only as good as the primary's binlog integrity)
If write performance is genuinely a problem at the database level, the right places to look are: query optimization, schema indexing, connection pooling (pgBouncer has a MariaDB equivalent in ProxySQL), read replicas for read-heavy workloads, or upgrading to hardware with actual write cache protection. Flipping this knob is the fastest way to feel like you solved a performance problem while actually just borrowing against your disaster recovery budget.
Closing
The default of 1 isn't there to be conservative or slow. It's there because the MariaDB team knows what ACID actually means and what it costs to deliver it. If your VPS config is overriding it for "performance", you're not running a faster database - you're running a faster database that lies to you about what it committed. For most of the workloads I deal with, that's not a trade I'm willing to make.
Need help shipping something like this? Get in touch.