Knex.js

Knex.js (opens new window) 是很讚的 SQL query builder

設定 statement_timeout

statement_timeout (opens new window) 這個設定是說：如果 statement 執行超過 X 秒就會直接中斷，這可以確保 statement 不會執行太久，避免佔用 connection 。

要設定在 connection.statement_timeout 裡面，單位是毫秒，所以要乘 1000 才會是秒。

const knex = require('knex')({
  connection: {
    statement_timeout: 5000
  }
})

超過時間就會收到 error: canceling statement due to statement timeout 錯誤。

error: canceling statement due to statement timeout
    at Parser.parseErrorMessage (/home/noderunner/node_project/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/home/noderunner/node_project/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/home/noderunner/node_project/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/home/noderunner/node_project/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (events.js:310:20)
    at addChunk (_stream_readable.js:286:12)
    at readableAddChunk (_stream_readable.js:268:9)
    at Socket.Readable.push (_stream_readable.js:209:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23) {
  length: 102,
  severity: 'ERROR',
  code: '57014',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'postgres.c',
  line: '2958',
  routine: 'ProcessInterrupts'
}

建議一定要設定 statement_timeout ，這樣可以抓出執行太久的 statement 來優化，一般院長會設定 5 秒。

查看 Pool 狀態

Knex 是用 tarn.js (opens new window) 實作 connectiion pool ，所以可以透過 knex.client.pool 取得 tarn 的 pool instance 就可以呼叫相關的 function 得知 pool 的狀態。

const pool = knex.client.pool
// returns the number of non-free resources
pool.numUsed()

// returns the number of free resources
pool.numFree()

// how many acquires are waiting for a resource to be released
pool.numPendingAcquires()

// how many asynchronous create calls are running
pool.numPendingCreates()

參考資料

• Get connection pool stats from Knex.js - Stack Overflow (opens new window)