Node.js中DuckDB多连接仍无法并发执行多查询的问题排查

问题描述 #

使用官方Node.js库操作DuckDB时，尝试通过建立多个连接并发执行查询，但无论是否使用多连接，所有查询均为串行执行，总耗时几乎相同。相关代码及执行结果如下：

测试代码 #

//////////
// 使用1个连接执行10个并行查询（queries为外部定义的查询数组）
const totalSingleDT = Date.now()
const queriesSingleConnection = _.map(queries, (query, i) => {
  const startDT = new Date()
  return config.db.all(query).then(rows => {
    console.log(`query single connection took (ms): ${Date.now() - startDT} rows: ${rows.length}`)
  })
})
await Promise.all(queriesSingleConnection)
console.log(`Single total result took (ms): ${Date.now() - totalSingleDT}`)
console.log("#############")


//////////
// 使用10个连接执行10个并行查询（queries为外部定义的查询数组）
const connections = await Promise.all(_.map(_.times(10), () => config.db.connect()))

const totalParallelDT = Date.now()
const queriesMultipleConnections = _.map(queries, (query, i) => {
  const startDT = new Date()
  return connections[i].all(query).then(rows => {
    console.log(`query parallel connection took (ms): ${Date.now() - startDT} rows: ${rows.length}`)
  })
})
await Promise.all(queriesMultipleConnections)
console.log(`Parallel total result took (ms): ${Date.now() - totalParallelDT}`)

执行结果 #

query single connection took (ms): 8 rows: 95
query single connection took (ms): 14 rows: 46
query single connection took (ms): 21 rows: 46
query single connection took (ms): 27 rows: 978
query single connection took (ms): 35 rows: 4397
query single connection took (ms): 39 rows: 135
query single connection took (ms): 45 rows: 328
query single connection took (ms): 51 rows: 35
query single connection took (ms): 58 rows: 45
query single connection took (ms): 63 rows: 71
Single total result took (ms): 63
#############
query parallel connection took (ms): 7 rows: 95
query parallel connection took (ms): 13 rows: 46
query parallel connection took (ms): 19 rows: 46
query parallel connection took (ms): 26 rows: 978
query parallel connection took (ms): 35 rows: 4397
query parallel connection took (ms): 39 rows: 135
query parallel connection took (ms): 45 rows: 328
query parallel connection took (ms): 51 rows: 35
query parallel connection took (ms): 57 rows: 45
query parallel connection took (ms): 63 rows: 71
Parallel total result took (ms): 64

问题原因 #

DuckDB的Node.js驱动默认以单线程模式运行，即便创建多个数据库连接，所有查询请求也会被调度到同一个线程中串行处理，因此多连接无法实现真正的并发执行。

解决方法 #

初始化DuckDB实例时，通过threads参数启用多线程模式，指定可用的线程数量（建议根据CPU核心数设置，比如4、8等）：

const duckdb = require('duckdb');
// 初始化数据库时启用多线程，指定线程数
const db = new duckdb.Database(':memory:', { threads: 4 });
// 如果是本地文件数据库，路径替换为你的文件路径
// const db = new duckdb.Database('./mydb.db', { threads: 4 });

启用多线程后，DuckDB会将不同连接的查询分配到不同线程并行处理，此时多连接查询的总耗时会接近单个查询的最长耗时，而非所有查询时间的总和。

额外注意事项：

• 确保查询之间没有依赖关系（比如后一个查询需要前一个的结果），否则依然无法并行。
• 部分简单查询因执行时间过短，并行效果可能不明显，建议用耗时较长的测试查询验证效果。

内容的提问来源于stack exchange，提问作者Geert-Jan