perf: disable WAL for transpiled source cache (#18084)

Disable Write-Ahead Log for the cached module source database.

This brings SQLite connection cost on startup from 2.5% to 1.6%.

5 files changed, 95 insertions, 131 deletions

diff --git a/cli/cache/check.rs b/cli/cache/check.rs
index c991c14b1..c8b5717ec 100644
--- a/cli/cache/check.rs
+++ b/cli/cache/check.rs
@@ -7,7 +7,7 @@ use deno_core::error::AnyError;
 use deno_runtime::deno_webstorage::rusqlite::params;
 use deno_runtime::deno_webstorage::rusqlite::Connection;
 
-use super::common::run_sqlite_pragma;
+use super::common::INITIAL_PRAGMAS;
 
 /// The cache used to tell whether type checking should occur again.
 ///
@@ -60,8 +60,7 @@ impl TypeCheckCache {
     conn: Connection,
     cli_version: String,
   ) -> Result<Self, AnyError> {
-    run_sqlite_pragma(&conn)?;
-    create_tables(&conn, cli_version)?;
+    initialize(&conn, cli_version)?;
 
     Ok(Self(Some(conn)))
   }
@@ -158,31 +157,24 @@ impl TypeCheckCache {
   }
 }
 
-fn create_tables(
-  conn: &Connection,
-  cli_version: String,
-) -> Result<(), AnyError> {
-  // INT doesn't store up to u64, so use TEXT
-  conn.execute(
-    "CREATE TABLE IF NOT EXISTS checkcache (
-      check_hash TEXT PRIMARY KEY
-    )",
-    [],
-  )?;
-  conn.execute(
-    "CREATE TABLE IF NOT EXISTS tsbuildinfo (
-        specifier TEXT PRIMARY KEY,
-        text TEXT NOT NULL
-      )",
-    [],
-  )?;
-  conn.execute(
-    "CREATE TABLE IF NOT EXISTS info (
-      key TEXT PRIMARY KEY,
-      value TEXT NOT NULL
-    )",
-    [],
-  )?;
+fn initialize(conn: &Connection, cli_version: String) -> Result<(), AnyError> {
+  // INT doesn't store up to u64, so use TEXT for check_hash
+  let query = format!(
+    "{INITIAL_PRAGMAS}
+  CREATE TABLE IF NOT EXISTS checkcache (
+    check_hash TEXT PRIMARY KEY
+  );
+  CREATE TABLE IF NOT EXISTS tsbuildinfo (
+    specifier TEXT PRIMARY KEY,
+    text TEXT NOT NULL
+  );
+  CREATE TABLE IF NOT EXISTS info (
+    key TEXT PRIMARY KEY,
+    value TEXT NOT NULL
+  );
+  ",
+  );
+  conn.execute_batch(&query)?;
 
   // delete the cache when the CLI version changes
   let data_cli_version: Option<String> = conn
diff --git a/cli/cache/common.rs b/cli/cache/common.rs
index 33623bb79..1e6c5aa92 100644
--- a/cli/cache/common.rs
+++ b/cli/cache/common.rs
@@ -2,9 +2,6 @@
 
 use std::hash::Hasher;
 
-use deno_core::error::AnyError;
-use deno_runtime::deno_webstorage::rusqlite::Connection;
-
 /// A very fast insecure hasher that uses the xxHash algorithm.
 #[derive(Default)]
 pub struct FastInsecureHasher(twox_hash::XxHash64);
@@ -47,19 +44,14 @@ impl FastInsecureHasher {
   }
 }
 
-/// Runs the common sqlite pragma.
-pub fn run_sqlite_pragma(conn: &Connection) -> Result<(), AnyError> {
-  // Enable write-ahead-logging and tweak some other stuff
-  let initial_pragmas = "
-    -- enable write-ahead-logging mode
-    PRAGMA journal_mode=WAL;
-    PRAGMA synchronous=NORMAL;
-    PRAGMA temp_store=memory;
-    PRAGMA page_size=4096;
-    PRAGMA mmap_size=6000000;
-    PRAGMA optimize;
-  ";
-
-  conn.execute_batch(initial_pragmas)?;
-  Ok(())
-}
+/// Disable write-ahead-logging and tweak some other stuff.
+/// We want to favor startup time over cache performance and
+/// creating a WAL is expensive on startup.
+pub static INITIAL_PRAGMAS: &str = "
+  PRAGMA journal_mode=OFF;
+  PRAGMA synchronous=NORMAL;
+  PRAGMA temp_store=memory;
+  PRAGMA page_size=4096;
+  PRAGMA mmap_size=6000000;
+  PRAGMA optimize;
+";
diff --git a/cli/cache/incremental.rs b/cli/cache/incremental.rs
index 985181c59..88520a031 100644
--- a/cli/cache/incremental.rs
+++ b/cli/cache/incremental.rs
@@ -12,8 +12,8 @@ use deno_runtime::deno_webstorage::rusqlite::Connection;
 use serde::Serialize;
 use tokio::task::JoinHandle;
 
-use super::common::run_sqlite_pragma;
 use super::common::FastInsecureHasher;
+use super::common::INITIAL_PRAGMAS;
 
 /// Cache used to skip formatting/linting a file again when we
 /// know it is already formatted or has no lint diagnostics.
@@ -174,8 +174,7 @@ impl SqlIncrementalCache {
     state_hash: u64,
     cli_version: String,
   ) -> Result<Self, AnyError> {
-    run_sqlite_pragma(&conn)?;
-    create_tables(&conn, cli_version)?;
+    initialize(&conn, cli_version)?;
 
     Ok(Self { conn, state_hash })
   }
@@ -238,26 +237,21 @@ impl SqlIncrementalCache {
   }
 }
 
-fn create_tables(
-  conn: &Connection,
-  cli_version: String,
-) -> Result<(), AnyError> {
-  // INT doesn't store up to u64, so use TEXT
-  conn.execute(
-    "CREATE TABLE IF NOT EXISTS incrementalcache (
-        file_path TEXT PRIMARY KEY,
-        state_hash TEXT NOT NULL,
-        source_hash TEXT NOT NULL
-      )",
-    [],
-  )?;
-  conn.execute(
-    "CREATE TABLE IF NOT EXISTS info (
-        key TEXT PRIMARY KEY,
-        value TEXT NOT NULL
-      )",
-    [],
-  )?;
+fn initialize(conn: &Connection, cli_version: String) -> Result<(), AnyError> {
+  // INT doesn't store up to u64, so use TEXT for source_hash
+  let query = format!(
+    "{INITIAL_PRAGMAS}
+  CREATE TABLE IF NOT EXISTS incrementalcache (
+    file_path TEXT PRIMARY KEY,
+    state_hash TEXT NOT NULL,
+    source_hash TEXT NOT NULL
+  );
+  CREATE TABLE IF NOT EXISTS info (
+    key TEXT PRIMARY KEY,
+    value TEXT NOT NULL
+  );"
+  );
+  conn.execute_batch(&query)?;
 
   // delete the cache when the CLI version changes
   let data_cli_version: Option<String> = conn
diff --git a/cli/cache/node.rs b/cli/cache/node.rs
index b19772229..e010c0dcd 100644
--- a/cli/cache/node.rs
+++ b/cli/cache/node.rs
@@ -13,7 +13,7 @@ use serde::Serialize;
 use std::path::PathBuf;
 use std::sync::Arc;
 
-use super::common::run_sqlite_pragma;
+use super::common::INITIAL_PRAGMAS;
 use super::FastInsecureHasher;
 
 // todo(dsherret): use deno_ast::CjsAnalysisData directly when upgrading deno_ast
@@ -155,8 +155,7 @@ impl NodeAnalysisCacheInner {
     conn: Connection,
     version: String,
   ) -> Result<Self, AnyError> {
-    run_sqlite_pragma(&conn)?;
-    create_tables(&conn, &version)?;
+    initialize(&conn, &version)?;
 
     Ok(Self { conn })
   }
@@ -260,41 +259,32 @@ impl NodeAnalysisCacheInner {
   }
 }
 
-fn create_tables(conn: &Connection, cli_version: &str) -> Result<(), AnyError> {
+fn initialize(conn: &Connection, cli_version: &str) -> Result<(), AnyError> {
   // INT doesn't store up to u64, so use TEXT for source_hash
-  conn.execute(
-    "CREATE TABLE IF NOT EXISTS cjsanalysiscache (
-        specifier TEXT PRIMARY KEY,
-        source_hash TEXT NOT NULL,
-        data TEXT NOT NULL
-      )",
-    [],
-  )?;
-  conn.execute(
-    "CREATE UNIQUE INDEX IF NOT EXISTS cjsanalysiscacheidx
-    ON cjsanalysiscache(specifier)",
-    [],
-  )?;
-  conn.execute(
-    "CREATE TABLE IF NOT EXISTS esmglobalscache (
-        specifier TEXT PRIMARY KEY,
-        source_hash TEXT NOT NULL,
-        data TEXT NOT NULL
-      )",
-    [],
-  )?;
-  conn.execute(
-    "CREATE UNIQUE INDEX IF NOT EXISTS esmglobalscacheidx
-      ON esmglobalscache(specifier)",
-    [],
-  )?;
-  conn.execute(
-    "CREATE TABLE IF NOT EXISTS info (
-        key TEXT PRIMARY KEY,
-        value TEXT NOT NULL
-      )",
-    [],
-  )?;
+  let query = format!(
+    "{INITIAL_PRAGMAS}
+  CREATE TABLE IF NOT EXISTS cjsanalysiscache (
+    specifier TEXT PRIMARY KEY,
+    source_hash TEXT NOT NULL,
+    data TEXT NOT NULL
+  );
+  CREATE UNIQUE INDEX IF NOT EXISTS cjsanalysiscacheidx
+  ON cjsanalysiscache(specifier);
+  CREATE TABLE IF NOT EXISTS esmglobalscache (
+    specifier TEXT PRIMARY KEY,
+    source_hash TEXT NOT NULL,
+    data TEXT NOT NULL
+  );
+  CREATE UNIQUE INDEX IF NOT EXISTS esmglobalscacheidx
+      ON esmglobalscache(specifier);
+  CREATE TABLE IF NOT EXISTS info (
+    key TEXT PRIMARY KEY,
+    value TEXT NOT NULL
+  );
+  "
+  );
+
+  conn.execute_batch(&query)?;
 
   // delete the cache when the CLI version changes
   let data_cli_version: Option<String> = conn
diff --git a/cli/cache/parsed_source.rs b/cli/cache/parsed_source.rs
index b6a80e82e..7b183ce86 100644
--- a/cli/cache/parsed_source.rs
+++ b/cli/cache/parsed_source.rs
@@ -19,7 +19,7 @@ use deno_graph::ParsedSourceStore;
 use deno_runtime::deno_webstorage::rusqlite::params;
 use deno_runtime::deno_webstorage::rusqlite::Connection;
 
-use super::common::run_sqlite_pragma;
+use super::common::INITIAL_PRAGMAS;
 use super::FastInsecureHasher;
 
 #[derive(Clone, Default)]
@@ -162,8 +162,7 @@ impl ParsedSourceCacheModuleAnalyzer {
     cli_version: String,
     sources: ParsedSourceCacheSources,
   ) -> Result<Self, AnyError> {
-    run_sqlite_pragma(&conn)?;
-    create_tables(&conn, cli_version)?;
+    initialize(&conn, cli_version)?;
 
     Ok(Self { conn, sources })
   }
@@ -288,27 +287,24 @@ impl deno_graph::ModuleAnalyzer for ParsedSourceCacheModuleAnalyzer {
   }
 }
 
-fn create_tables(
-  conn: &Connection,
-  cli_version: String,
-) -> Result<(), AnyError> {
-  // INT doesn't store up to u64, so use TEXT for source_hash
-  conn.execute(
-    "CREATE TABLE IF NOT EXISTS moduleinfocache (
-        specifier TEXT PRIMARY KEY,
-        media_type TEXT NOT NULL,
-        source_hash TEXT NOT NULL,
-        module_info TEXT NOT NULL
-      )",
-    [],
-  )?;
-  conn.execute(
-    "CREATE TABLE IF NOT EXISTS info (
-        key TEXT PRIMARY KEY,
-        value TEXT NOT NULL
-      )",
-    [],
-  )?;
+fn initialize(conn: &Connection, cli_version: String) -> Result<(), AnyError> {
+  let query = format!(
+    "{INITIAL_PRAGMAS}
+  -- INT doesn't store up to u64, so use TEXT for source_hash
+  CREATE TABLE IF NOT EXISTS moduleinfocache (
+    specifier TEXT PRIMARY KEY,
+    media_type TEXT NOT NULL,
+    source_hash TEXT NOT NULL,
+    module_info TEXT NOT NULL
+  );
+  CREATE TABLE IF NOT EXISTS info (
+    key TEXT PRIMARY KEY,
+    value TEXT NOT NULL
+  );
+  "
+  );
+
+  conn.execute_batch(&query)?;
 
   // delete the cache when the CLI version changes
   let data_cli_version: Option<String> = conn