跟踪数据库查询时间-书架/knex [英] Tracking DB querying time - Bookshelf/knex

问题描述

我想监视API数据库上的查询所花费的时间.因此，我使用书架插件bookshelf-signals创建了以下功能. :

I would like to monitor the time taken by a query on my API's db. I so created the following function, using bookshelf-signals, a Bookshelf plugin. :

bookshelf.on('fetching', () => {
  server.app.fetching = new Date().valueOf();
});

bookshelf.on('counting', () => {
  server.app.fetching = new Date().valueOf(); 
});

bookshelf.on('fetched', () => {
  server.statsd.gauge('db_query', new Date().valueOf() - server.app.fetching);
});

...这样我就可以在获取/计数之前和之后获取时间；我对Delete-deleted和Saving-saved做了同样的事情.

... so that I can retrieve the time just before and just after a fetch/count; I did the same with deleting-deleted and saving-saved.

我想我不明白的是什么时候应该触发获取和获取...当我试图查看何时触发了获取和获取时，基本上它最终是这样的:

What I think I fail to understand is when fetching and fetched are supposed to be triggered... When I tried to to see when fetching and fetched were triggered, basically it ended up with this :

'fetching event A'
'fetching event B'
'fetching event C'
'fetched event C'
'fetched event B'
'fetched event A'

导致计时器明显返回错误的值，您有任何线索/线索吗?

Resulting in the timers returning wrong values obliviously, do you have any lead/clue ?

我还看到有人可以在Knex上触发查询"事件，并想到了将其用作替代解决方案.但是，似乎只有在我指定查询表的情况下，它才有效:

I also saw that one could trigger 'query' events on Knex, and thought of using this as an alternative solution. However, it seems that it only works if I specify the table where I query, ie :

 knex('whatever_table').on('query', () => {///});

在我要在每个模型上应用事件处理程序的情况下，使它不可行... 我认为我应该坚持使用书架，但是如何处理事件呢?

Making it impracticable in the case where I want to apply an event handler on every model... I think I should stick with Bookshelf, but how can I do with the way the events are handled?

提前谢谢！

推荐答案

我刚刚编写了一些小的测试代码，如何使用knex跟踪事务持续时间.

I just wrote some small test code how to trace transaction duration with knex.

https://runkit.com/embed/679qu91ylu4w

/**
 * Calculate transaction durations in knex
 * 
 */
require('sqlite3');
var knex = require("knex")({
  client: 'sqlite', 
  connection: ':memory:', 
  pool: { min: 1, max: 10 }
});

function isTransactionStart(querySpec) {
  return querySpec.sql === 'BEGIN;';
}

function isTransactionEnd(querySpec) {
  return querySpec.sql === 'COMMIT;' || querySpec.sql === 'ROLLBACK;';
}

const transactionDurations = {};

knex.on('query', querySpec => {
  console.log('On query', querySpec);

  if (isTransactionStart(querySpec)) {
    if (transactionDurations[querySpec.__knexUid]) {
      console.error('New transaction started, before earlier was ended');
      return;
    }
    transactionDurations[querySpec.__knexUid] = new Date().getTime();
  }

  if (isTransactionEnd(querySpec)) {
    const startTime = transactionDurations[querySpec.__knexUid];
    if (!startTime) {
      console.error('Transaction end detected, but start time not found');
    }
    const endTime = new Date().getTime();
    transactionDurations[querySpec.__knexUid] = null;
    console.log('TRANSACTION DURATION', endTime - startTime);
  }
}); 

// just as an example of other available events to show when they are called
knex.on('query-response', (res, querySpec) => {
  // console.log('On query response', res, querySpec);
}); 

knex.on('query-error', (err, querySpec) => {
  // console.log('On query error', err, querySpec);
}); 

try {
    a = await Promise.all([
      knex.transaction(trx => {
        return trx.raw('select 1');
      }),
      knex.transaction(trx => {
        return trx.raw('select 2');
      }),
      knex.transaction(trx => {
        return trx.raw('error me');
      })
    ]);
} catch (e) {
  console.log('Got ERROR:', e);
}

相同的方法之王也应适用于查询时间.为了防止计时器记帐泄漏内存，您应该添加一些清除代码.

The same king of approach should work also for query timing. To prevent timer bookkeeping from leaking memory you should add some cleanup code though.

查询持续时间计时器应在query事件中启动，并在query-response或query-error中停止，具体取决于哪个首先触发.

Query duration timer should be started in query event and stopped in query-response or query-error depending which one triggers first.

为了能够匹配query-query-response对querySpec.__knexQueryUid属性，可以使用.

To be able to match query - query-response pair querySpec.__knexQueryUid attribute can be used.

这篇关于跟踪数据库查询时间-书架/knex的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！