☰

UNIX 系统上的 SQLite WAL 并发写入性能

tag-icon • tag-icon debian performance ext4 sqlite concurrency
UNIX 系统上的 SQLite WAL 并发写入性能

我有两个设置：一个在 Windows 10（ntfs 分区）上运行，另一个在 Debian（ext4 分区）上运行。R 源代码相同。主进程在 8 个 vcore 上启动 8 个子进程（P-SOCKS），它们都查询并写入同一个启用 WAL 的 sqlite 数据库。

在 Windows 10 上，我得到 100% 的 CPU 负载，分布在所有进程上。在 Debian 上，我几乎没有得到 25% 的 CPU 负载。监控 Debian 上的进程，我认为写入是瓶颈，因为我看到只有一个进程在其 vcore 上一次达到 100%。（其他进程可能正在等待写入。）

每个连接都使用PRAGMA busy_timeout = 60000;和PRAGMA journal_mode = WAL;。

我正在尝试调试这个问题。我曾尝试PRAGMA synchronous = OFF;认为这可能与有关fsync()，但我没有看到任何改善。还有其他建议吗？什么可能导致 Debian 性能低下？

编辑： SCSI 磁盘上似乎已启用写入缓存（已用 检查sdparm），并且调整 ext4 挂载选项（例如barrier=0和 ）data=writeback似乎没有任何效果。

基准测试

以下是一些用于基准测试并发写入的简单代码：

make.con <- function() {
  con <<- DBI::dbConnect(RSQLite::SQLite(), dbname = 'db.sqlite')
  DBI::dbExecute(con, 'PRAGMA journal_mode = WAL;')
  DBI::dbExecute(con, 'PRAGMA busy_timeout = 60000;')
  DBI::dbExecute(con, '
    CREATE TABLE IF NOT EXISTS tmp (
      id INTEGER NOT NULL,
      blob BLOB NOT NULL,
      PRIMARY KEY (id)
  )')
}
make.con()


fn <- function(x) {
  set.seed(x)
  # read
  random.blob.read <- RSQLite::dbGetQuery(con, 'SELECT blob FROM tmp WHERE id = (SELECT abs(random() % max(tm.id)) FROM tmp tm);')
  # write
  blob <- serialize(list(rand = runif(1000)), connection = NULL, xdr = FALSE)
  RSQLite::dbExecute(con, 'INSERT INTO tmp (blob) VALUES (:blob);', params = list('blob' = list(blob)))
}

n <- 30000L

parallel::setDefaultCluster(parallel::makeCluster(spec = 2L))
parallel::clusterExport(varlist = 'make.con')
invisible(parallel::clusterEvalQ(expr = {make.con()}))

microbenchmark::microbenchmark(
  lapply(1:n, fn),
  parallel::parLapplyLB(X = 1:n, fun = fn, chunk.size = 50L), 
  times = 2L
)

parallel::stopCluster(cl = parallel::getDefaultCluster())

代码只是读取和写入数据库的 blob。首先，进行一些虚拟运行，并允许数据库增加到几 GB。

在我的 Windows 10 笔记本电脑上，我得到了以下结果（6GB 数据库）：

Unit: seconds
                                                       expr      min       lq     mean   median       uq      max neval
                                            lapply(1:n, fn) 26.02392 26.02392 26.54853 26.54853 27.07314 27.07314     2
 parallel::parLapplyLB(X = 1:n, fun = fn, chunk.size = 50L) 15.73851 15.73851 16.44554 16.44554 17.15257 17.15257     2

我清楚地看到 1 个 vcore 达到 100%，然后 2 个 vcore 达到 100%。性能几乎快了一倍，这表明 2 个并发进程不会相互阻塞。

在 Debian 上我得到这个：

Unit: seconds
                                                       expr      min       lq     mean   median       uq      max neval  
                                            lapply(1:n, fn) 39.96850 39.96850 40.14782 40.14782 40.32714 40.32714     2
 parallel::parLapplyLB(X = 1:n, fun = fn, chunk.size = 50L) 43.34628 43.34628 44.85910 44.85910 46.37191 46.37191     2

两个 vcore 永远不会达到最大值。此外，使用 2 个进程时性能没有任何改善 —— 甚至更糟，因为它们似乎互相阻塞。最后，debian 的硬件更好（尽管是虚拟化的）。

答案1

在 Ubuntu 18.04 上确认，尚未在 Windows 上测试。

我简化了您的示例并添加了检测代码。第一个图显示了为每个子进程写入的 blob 数量。在第一个图中，平稳状态表示所有核心约 0.2 秒不活动，而急剧上升表示所有核心的突发写入。第二个图显示原始数据，最适用于 plotly，但在 StackOverflow 答案中不起作用。

启用后gc()运行时间会更长，但负载会更均匀，如下面的第二个图所示。

我不知道发生了什么。您可以复制并进一步试验此设置吗？如果您能在这里或在 RSQLite 问题跟踪器中提供反馈，我将不胜感激。

基本运行，无gc()

make.con <- function() {
  options(digits.secs = 6)

  con <<- DBI::dbConnect(RSQLite::SQLite(), dbname = "db.sqlite")
  DBI::dbExecute(con, "PRAGMA journal_mode = WAL;")
  DBI::dbExecute(con, "PRAGMA busy_timeout = 60000;")
  DBI::dbExecute(con, "PRAGMA synchronous = OFF;")
  DBI::dbExecute(con, "
    CREATE TABLE IF NOT EXISTS tmp (
      id INTEGER NOT NULL,
      blob BLOB NOT NULL,
      PRIMARY KEY (id)
  )")
}
make.con()
#> [1] 0

blob <- serialize(list(rand = runif(1000)), connection = NULL, xdr = FALSE)

fn <- function(x) {
  time0 <- Sys.time()
  rs <- DBI::dbSendQuery(con, "INSERT INTO tmp (blob) VALUES (:blob);")
  time1 <- Sys.time()
  DBI::dbBind(rs, params = list("blob" = list(blob)))
  time2 <- Sys.time()
  DBI::dbClearResult(rs)
  time3 <- Sys.time()
  # gc()
  time4 <- Sys.time()
  list(pid = unix::getpid(), time0 = time0, time1 = time1, time2 = time2, time3 = time3, time4 = time4)
}

n <- 1000L

parallel::setDefaultCluster(parallel::makeCluster(8L))
parallel::clusterExport(varlist = c("make.con", "blob"))
invisible(parallel::clusterEvalQ(expr = {
  make.con()
}))

data <- parallel::parLapply(X = 1:n, fun = fn, chunk.size = 50L)

parallel::stopCluster(cl = parallel::getDefaultCluster())

library(tidyverse)

tbl <-
  data %>%
  transpose() %>%
  map(unlist, recursive = FALSE) %>%
  as_tibble() %>%
  rowid_to_column() %>%
  pivot_longer(-c(rowid, pid), names_to = "step", values_to = "time") %>%
  mutate(time = as.POSIXct(time, origin = "1970-01-01")) %>%
  mutate(pid = factor(pid)) %>%
  arrange(time)

tbl %>%
  group_by(pid) %>%
  mutate(cum = row_number()) %>%
  ungroup() %>%
  ggplot(aes(x = time, y = cum, color = pid)) +
  geom_line()


p <-
  tbl %>%
  ggplot(aes(x = time, y = factor(pid), group = 1)) +
  geom_path() +
  geom_point(aes(color = step))

p


plotly::ggplotly(p)

（plotly 在 StackOverflow 上不起作用）

创建于 2020-01-30，作者为reprex 包（v0.3.0）

结果gc()

相关内容