Skip to content

Commit 5cced0e

Browse files
committed
perf(server): enable WAL and NORMAL sync for the SQLite store
On-disk SQLite stores ran with sqlx defaults: rollback journal (`journal_mode=delete`) and `synchronous=FULL`. Every autocommit write paid several fsyncs and blocked readers while it held the lock, so gateway hot paths made of many small writes serialized on disk latency. The clearest case is `openshell forward service`, which mints and revokes an SSH session token around every forwarded TCP connection: two commits per connection, tens of milliseconds each on a virtual disk, wall clock linear in the number of concurrent connections, and enough queueing that bursts hit the per-sandbox connection cap and get refused. Switch on-disk databases to WAL with `synchronous=NORMAL`. The mode change runs once on a single connection before the pool opens: entering WAL needs exclusive access to the file, so doing it up front means pool connections only ever re-apply the pragma to a file already in WAL mode, and a failure surfaces as one clear connect error. The first start after upgrading an existing database therefore needs the file to be otherwise unopened. `synchronous` is applied through the connect options on every pooled connection. In-memory databases keep their defaults. A crash can now roll back the most recent transactions without corrupting the database, which is the standard WAL trade-off and fits the single-node scope of the SQLite backend. Tests cover a fresh store, an existing rollback-journal file that must be switched on connect, sidecar permissions, and concurrent readers under a burst of insert-then-update writes. Architecture, configuration and Helm docs describe the durability trade-off, the sidecar files, and the local filesystem requirement. Signed-off-by: Jason T. Greene <[email protected]>
1 parent 24706c1 commit 5cced0e

7 files changed

Lines changed: 299 additions & 8 deletions

File tree

‎architecture/gateway.md‎

Lines changed: 14 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -648,11 +648,23 @@ Gateway and Sandbox Protocol token responses follow the same convention: a
648648
present expiration timestamp carries the absolute deadline, while absence means
649649
the issued token does not expire.
650650

651+
On-disk SQLite databases run in WAL journal mode with `synchronous=NORMAL`.
652+
The adapter switches the file to WAL on a single connection before the pool
653+
opens, then applies both settings to every pooled connection. WAL lets readers
654+
proceed while a writer commits, and `NORMAL` removes the per-commit `fsync`,
655+
which matters because gateway hot paths such as SSH session issuance and
656+
revocation are many small autocommit writes. The trade-off is that a power
657+
loss or kernel crash can roll back the most recent transactions; the database
658+
remains consistent. Deployments that need stronger durability or multiple
659+
replicas use Postgres. WAL requires a local filesystem with working shared
660+
memory, so the SQLite file must not live on a network mount, and backups must
661+
use `sqlite3 .backup` or `VACUUM INTO` rather than copying the main file alone.
662+
651663
The SQLite adapter tightens the on-disk database file to mode `0o600` on every
652664
connect so that provider API keys, SSH session tokens, and sandbox metadata are
653665
not readable by other local users on shared hosts. The same restriction is
654-
reapplied to the `<db>-wal` and `<db>-shm` sidecars (created by SQLite's
655-
default WAL journal mode), which mirror the same sensitive contents.
666+
reapplied to the `<db>-wal` and `<db>-shm` sidecars that WAL mode creates,
667+
which mirror the same sensitive contents.
656668

657669
Persisted state includes sandboxes, providers, provider profiles, provider
658670
credential refresh state, SSH sessions, policy revisions, settings, deployment

‎crates/openshell-server/src/persistence/sqlite.rs‎

Lines changed: 70 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,9 @@ use openshell_core::SetResourceVersion;
1515
use openshell_core::paths::set_file_owner_only;
1616
use openshell_core::proto::Sandbox;
1717
use prost::Message;
18-
use sqlx::sqlite::{SqliteConnectOptions, SqliteConnection, SqlitePoolOptions};
18+
use sqlx::sqlite::{
19+
SqliteConnectOptions, SqliteConnection, SqliteJournalMode, SqlitePoolOptions, SqliteSynchronous,
20+
};
1921
use sqlx::{Connection, QueryBuilder, Row, Sqlite, SqlitePool};
2022
use std::path::{Path, PathBuf};
2123
use std::str::FromStr;
@@ -70,6 +72,69 @@ pub(super) async fn replace_pool_connection(store: &SqliteStore) -> PersistenceR
7072
connection.close().await.map_err(|e| map_db_error(&e))
7173
}
7274

75+
/// Apply the on-disk journal settings and switch the database file to WAL
76+
/// once, before the pool opens its connections.
77+
///
78+
/// The gateway's hot paths (SSH-session tokens minted and revoked around every
79+
/// forwarded connection, sandbox status updates) are many small autocommit
80+
/// writes. `SQLite`'s default rollback journal makes each of those commits pay
81+
/// several `fsync` calls and blocks readers while a writer holds the lock, so
82+
/// under a burst of forwarded connections the whole store serializes on disk
83+
/// latency. WAL mode removes the reader/writer exclusion and, combined with
84+
/// `synchronous=NORMAL`, drops the per-commit `fsync`: a power loss or kernel
85+
/// crash may roll back the most recent transactions, but the database stays
86+
/// consistent. That is the standard WAL configuration and matches the
87+
/// single-node scope of the `SQLite` backend; deployments that need stronger
88+
/// durability guarantees use the Postgres backend.
89+
///
90+
/// `journal_mode=WAL` is persistent in the database file, but switching into
91+
/// it needs exclusive access: if another connection holds the file open, the
92+
/// switch waits out `busy_timeout` and then fails. Doing it up front on one
93+
/// connection means the pool connections only ever re-apply the pragma to a
94+
/// file that is already in WAL mode, which never blocks, and a failure
95+
/// surfaces as a single clear connect error instead of a pool error later.
96+
/// The first start after upgrading a rollback-journal database therefore needs
97+
/// the file to be otherwise unopened. `synchronous` is a per-connection
98+
/// setting and is applied through the options on every connection.
99+
///
100+
/// In-memory databases are left on their defaults: WAL is meaningless there
101+
/// and the shared-cache keepalive connection already provides their lifetime
102+
/// guarantees.
103+
async fn configure_on_disk_durability(
104+
options: SqliteConnectOptions,
105+
) -> PersistenceResult<SqliteConnectOptions> {
106+
let options = options
107+
.journal_mode(SqliteJournalMode::Wal)
108+
.synchronous(SqliteSynchronous::Normal);
109+
let wal_error = |e: &sqlx::Error| {
110+
PersistenceError::Database(format!(
111+
"failed to switch SQLite database {} to WAL journal mode (the switch needs \
112+
exclusive access; close other connections to the file and retry): {}",
113+
options.get_filename().display(),
114+
map_db_error(e)
115+
))
116+
};
117+
let connection = SqliteConnection::connect_with(&options)
118+
.await
119+
.map_err(|e| wal_error(&e))?;
120+
connection.close().await.map_err(|e| wal_error(&e))?;
121+
Ok(options)
122+
}
123+
124+
#[cfg(test)]
125+
pub(super) async fn journal_settings(store: &SqliteStore) -> PersistenceResult<(String, i64)> {
126+
let mut connection = store.pool.acquire().await.map_err(|e| map_db_error(&e))?;
127+
let journal_mode: String = sqlx::query_scalar("PRAGMA journal_mode")
128+
.fetch_one(&mut *connection)
129+
.await
130+
.map_err(|e| map_db_error(&e))?;
131+
let synchronous: i64 = sqlx::query_scalar("PRAGMA synchronous")
132+
.fetch_one(&mut *connection)
133+
.await
134+
.map_err(|e| map_db_error(&e))?;
135+
Ok((journal_mode, synchronous))
136+
}
137+
73138
impl SqliteStore {
74139
/// Closes the connection pool.
75140
#[cfg(test)]
@@ -107,6 +172,10 @@ impl SqliteStore {
107172
// so we can restrict the permissions after the database is connected.
108173
let db_path = (!is_in_memory).then(|| options.get_filename().to_path_buf());
109174

175+
if !is_in_memory {
176+
options = configure_on_disk_durability(options).await?;
177+
}
178+
110179
let in_memory_keepalive = if is_in_memory {
111180
let connection = SqliteConnection::connect_with(&options)
112181
.await

‎crates/openshell-server/src/persistence/tests.rs‎

Lines changed: 206 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -324,14 +324,217 @@ async fn sqlite_connect_tightens_existing_db_file_permissions() {
324324
assert_eq!(mode, 0o600, "expected 0600, got {mode:04o}");
325325
}
326326

327+
fn on_disk_store_url(db_path: &std::path::Path) -> String {
328+
format!("sqlite:{}?mode=rwc", db_path.display())
329+
}
330+
331+
async fn connect_on_disk_sqlite(url: &str) -> super::SqliteStore {
332+
match Store::connect(url).await.expect("connect to sqlite") {
333+
Store::Sqlite(store) => store,
334+
Store::Postgres(_) => unreachable!("sqlite URL must select the SQLite store"),
335+
}
336+
}
337+
338+
async fn file_journal_mode(db_path: &std::path::Path) -> String {
339+
use sqlx::{Connection, SqliteConnection};
340+
341+
let mut connection = SqliteConnection::connect(&format!("sqlite:{}", db_path.display()))
342+
.await
343+
.expect("open database file directly");
344+
let journal_mode: String = sqlx::query_scalar("PRAGMA journal_mode")
345+
.fetch_one(&mut connection)
346+
.await
347+
.expect("read journal_mode");
348+
connection.close().await.expect("close direct connection");
349+
journal_mode
350+
}
351+
352+
#[tokio::test]
353+
async fn sqlite_connect_enables_wal_and_normal_synchronous_on_disk() {
354+
let tmp = tempfile::tempdir().expect("tempdir");
355+
let db_path = tmp.path().join("openshell.db");
356+
let url = on_disk_store_url(&db_path);
357+
358+
let store = connect_on_disk_sqlite(&url).await;
359+
360+
let (journal_mode, synchronous) = super::sqlite::journal_settings(&store)
361+
.await
362+
.expect("read journal settings through the pool");
363+
assert_eq!(journal_mode, "wal", "on-disk stores must run in WAL mode");
364+
assert_eq!(
365+
synchronous, 1,
366+
"on-disk stores must run with synchronous=NORMAL (1), got {synchronous}"
367+
);
368+
369+
// Force a write so the WAL sidecars exist on disk, then confirm they are
370+
// owner-only like the main file.
371+
store
372+
.put(
373+
"sandbox",
374+
"wal-probe",
375+
"wal-probe",
376+
"default",
377+
b"payload",
378+
None,
379+
)
380+
.await
381+
.expect("write through the store");
382+
let [wal_path, shm_path] = super::sqlite::sqlite_sidecar_paths(&db_path);
383+
assert!(wal_path.exists(), "WAL sidecar should exist after a write");
384+
assert!(shm_path.exists(), "SHM sidecar should exist after a write");
385+
#[cfg(unix)]
386+
{
387+
use std::os::unix::fs::PermissionsExt;
388+
for path in [&db_path, &wal_path, &shm_path] {
389+
let mode = std::fs::metadata(path)
390+
.expect("sidecar metadata")
391+
.permissions()
392+
.mode()
393+
& 0o777;
394+
assert_eq!(
395+
mode,
396+
0o600,
397+
"{}: expected 0600, got {mode:04o}",
398+
path.display()
399+
);
400+
}
401+
}
402+
403+
store.close().await;
404+
assert_eq!(
405+
file_journal_mode(&db_path).await,
406+
"wal",
407+
"WAL must persist in the database file after the store closes"
408+
);
409+
}
410+
411+
#[tokio::test]
412+
async fn sqlite_connect_switches_existing_rollback_journal_database_to_wal() {
413+
use sqlx::sqlite::SqliteConnectOptions;
414+
use sqlx::{Connection, SqliteConnection};
415+
use std::str::FromStr;
416+
417+
let tmp = tempfile::tempdir().expect("tempdir");
418+
let db_path = tmp.path().join("openshell.db");
419+
let url = on_disk_store_url(&db_path);
420+
421+
// A database created by an older gateway, or by any sqlx 0.8 default
422+
// connection, is in rollback-journal (`delete`) mode.
423+
{
424+
let options = SqliteConnectOptions::from_str(&url)
425+
.expect("parse url")
426+
.create_if_missing(true);
427+
let mut connection = SqliteConnection::connect_with(&options)
428+
.await
429+
.expect("create legacy database");
430+
sqlx::query("CREATE TABLE legacy_probe (x INTEGER)")
431+
.execute(&mut connection)
432+
.await
433+
.expect("write legacy database");
434+
connection.close().await.expect("close legacy connection");
435+
}
436+
assert_eq!(file_journal_mode(&db_path).await, "delete");
437+
438+
let store = connect_on_disk_sqlite(&url).await;
439+
let (journal_mode, _) = super::sqlite::journal_settings(&store)
440+
.await
441+
.expect("read journal settings");
442+
assert_eq!(
443+
journal_mode, "wal",
444+
"connect must switch existing files to WAL"
445+
);
446+
store.close().await;
447+
assert_eq!(file_journal_mode(&db_path).await, "wal");
448+
}
449+
450+
#[tokio::test(flavor = "multi_thread", worker_threads = 4)]
451+
async fn sqlite_file_backed_reads_and_writes_proceed_concurrently() {
452+
let tmp = tempfile::tempdir().expect("tempdir");
453+
let db_path = tmp.path().join("openshell.db");
454+
let url = on_disk_store_url(&db_path);
455+
let store = std::sync::Arc::new(Store::connect(&url).await.expect("connect to sqlite"));
456+
457+
store
458+
.put("sandbox", "seed", "seed", "default", b"seed", None)
459+
.await
460+
.expect("seed object");
461+
462+
// Mirror the forward-service pattern: many independent autocommit writes
463+
// (one insert and one update per "connection") while readers keep
464+
// fetching. Every operation must complete; no caller may observe a
465+
// "database is locked" error even though the writes contend for the
466+
// single SQLite writer.
467+
let writers = (0..8).map(|writer| {
468+
let store = store.clone();
469+
tokio::spawn(async move {
470+
for index in 0..25 {
471+
let id = format!("session-{writer}-{index}");
472+
store
473+
.put_if(
474+
"ssh_session",
475+
&id,
476+
&id,
477+
"default",
478+
b"issued",
479+
None,
480+
super::WriteCondition::MustCreate,
481+
)
482+
.await
483+
.expect("insert session");
484+
store
485+
.put_if(
486+
"ssh_session",
487+
&id,
488+
&id,
489+
"default",
490+
b"revoked",
491+
None,
492+
super::WriteCondition::MatchResourceVersion(1),
493+
)
494+
.await
495+
.expect("revoke session");
496+
}
497+
})
498+
});
499+
let readers = (0..4).map(|_| {
500+
let store = store.clone();
501+
tokio::spawn(async move {
502+
for _ in 0..100 {
503+
let record = store
504+
.get("sandbox", "seed")
505+
.await
506+
.expect("read while writers are active")
507+
.expect("seed object present");
508+
assert_eq!(record.payload, b"seed");
509+
}
510+
})
511+
});
512+
513+
for handle in writers.chain(readers) {
514+
handle.await.expect("task completed");
515+
}
516+
517+
let sessions = store
518+
.list("ssh_session", "default", 1000, 0)
519+
.await
520+
.expect("list sessions");
521+
assert_eq!(sessions.len(), 200, "every session write must be durable");
522+
assert!(
523+
sessions.iter().all(|record| record.payload == b"revoked"),
524+
"every session must have been revoked by its follow-up write"
525+
);
526+
}
527+
327528
// The next three tests cover `restrict_db_file_permissions` against the
328529
// WAL/SHM sidecars at increasing levels of fidelity:
329530
//
330531
// 1. `_tightens_main_and_wal_and_shm_files`: synthetic empty files, proves
331532
// the chmod loop walks all three paths.
332-
// 2. `_skips_missing_sidecars`: proves the `exists()` guard, which is the
333-
// actual production path today (sqlx 0.8 doesn't default to WAL and
334-
// doesn't accept `journal_mode` as a URL parameter).
533+
// 2. `_skips_missing_sidecars`: proves the `exists()` guard for databases
534+
// that have not been written yet or were created before the adapter
535+
// enabled WAL (sqlx 0.8 doesn't default to WAL and doesn't accept
536+
// `journal_mode` as a URL parameter; `SqliteStore::connect` opts in
537+
// through the builder API).
335538
// 3. `_handles_real_sqlite_wal_files`: opens a real sqlx pool with
336539
// `SqliteJournalMode::Wal` via the builder API so SQLite materializes
337540
// real `-wal` and `-shm` files, then checks the helper tightens them.

‎deploy/helm/openshell/README.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -281,7 +281,7 @@ discovery endpoint or its TLS CA.
281281
| server.credentialDrivers.vault.timeoutSecs | string | `""` | HTTP request timeout in seconds. Empty = driver default. |
282282
| server.credentialDrivers.vault.tokenPath | string | `""` | Mounted token file path when authMethod is token_file. |
283283
| server.credentialStorage.existingSecret | string | `""` | Name of a pre-existing Secret containing the key-encryption key. When set, the chart does NOT generate a new Secret; it references this one instead. The Secret must contain a key named "key-encryption-key" with a base64-encoded 32-byte value. Required for GitOps workflows that render manifests with `helm template` (where `lookup` is unavailable). |
284-
| server.dbUrl | string | `"sqlite:/var/openshell/openshell.db"` | Gateway database URL (used for the default SQLite backend). |
284+
| server.dbUrl | string | `"sqlite:/var/openshell/openshell.db"` | Gateway database URL (used for the default SQLite backend). SQLite runs in WAL mode and needs a local block-backed volume, not NFS or other network filesystems. |
285285
| server.defaultRuntimeClassName | string | `""` | Default Kubernetes runtimeClassName for sandbox pods. Applied when a CreateSandbox request does not specify one. Empty (default) = omit the field, using the cluster's default RuntimeClass. Set to a RuntimeClass name (e.g. "kata-containers", "nvidia") to apply it to all sandboxes that don't explicitly override it. |
286286
| server.disableTls | bool | `false` | Disable TLS entirely - the server listens on plaintext HTTP. Set to true when a reverse proxy / tunnel terminates TLS at the edge. |
287287
| server.drivers.kubernetes.operatorNamespaceFile | string | `""` | Path to a JSON file containing an array of namespace names allowed in operator mode. Hot-reloaded on change. |

‎deploy/helm/openshell/values.yaml‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -206,7 +206,9 @@ server:
206206
# -- Namespace where sandbox pods are created. Defaults to the Helm release
207207
# namespace (.Release.Namespace) when left empty.
208208
sandboxNamespace: ""
209-
# -- Gateway database URL (used for the default SQLite backend).
209+
# -- Gateway database URL (used for the default SQLite backend). SQLite runs
210+
# in WAL mode and needs a local block-backed volume, not NFS or other network
211+
# filesystems.
210212
dbUrl: "sqlite:/var/openshell/openshell.db"
211213
# -- Name of a pre-existing Opaque Secret containing a PostgreSQL
212214
# connection URI (key: uri). When set, the gateway reads OPENSHELL_DB_URL

‎deploy/rpm/CONFIGURATION.md‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -229,6 +229,9 @@ overrides that persist across package upgrades.
229229

230230
The database URL is not accepted in TOML. When `OPENSHELL_DB_URL` is unset,
231231
the gateway uses `sqlite:$XDG_STATE_HOME/openshell/gateway/openshell.db`.
232+
The SQLite database runs in WAL mode with `synchronous=NORMAL`, so
233+
`openshell.db-wal` and `openshell.db-shm` sit next to it and must be kept
234+
together with it; back it up with `sqlite3 openshell.db ".backup <copy>"`.
232235

233236
### Driver TOML settings
234237

‎docs/reference/gateway-config.mdx‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,8 @@ Gateway CLI flag > gateway OPENSHELL_* env var > TOML file > built-in defa
1818

1919
`database_url` is env-only. The loader rejects it when it appears in the file. When `OPENSHELL_DB_URL` is unset, the gateway stores its SQLite database under `$XDG_STATE_HOME/openshell/gateway/openshell.db`.
2020

21+
On-disk SQLite databases run in WAL mode with `synchronous=NORMAL`: the gateway keeps `openshell.db-wal` and `openshell.db-shm` next to the database file, the file must live on a local filesystem, and a backup must use `sqlite3 openshell.db ".backup <copy>"` rather than copying `openshell.db` on its own. A power loss can roll back the most recent transactions without corrupting the database.
22+
2123
`name` assigns an operator-facing identity to the gateway installation. Set it with `[openshell.gateway].name`, `--name`, or `OPENSHELL_GATEWAY_NAME`. It defaults to `openshell`; the Helm chart defaults it to the chart fullname so all replicas in one installation share a name. Chart fullnames are only unique within their Kubernetes namespace, so set `server.name` explicitly when one collector receives telemetry from multiple namespaces or clusters. This identity is independent of client-side gateway aliases, TLS names, and `gateway_jwt.gateway_id`.
2224

2325
## Package-Managed Locations

0 commit comments

Comments
 (0)