Diagnosing query overload


#1

Hello,

I’m using adonis-acl package to manage user permissions and I noticed that there are some 60+ queries when the application initially loads. I noticed this by going adding:

//start/hooks.js
  const Database = use('Database')
  Database.on('query', console.log)

What I see is a barrage of queries, but I’m trying to figure out what they mean. For example,

{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 1 ],
  __knexQueryUid: '7dfb68a9-7c18-4125-9844-ee08e99a4119',
  sql: 'select `permissions`.*, `permission_role`.`permission_id` as `pivot_permission_id`, `permission_role`.`role_id` as `pivot_role_id` from `permissions` inner join `permission_role` on `permissions`.`id` = `permission_role`.`permission_id` where `permission_role`.`role_id` = ?' }
{ __knexUid: '__knexUid1',
  __knexTxId: undefined,
  method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 1 ],
  __knexQueryUid: '7dfb68a9-7c18-4125-9844-ee08e99a4119',
  sql: 'select `permissions`.*, `permission_role`.`permission_id` as `pivot_permission_id`, `permission_role`.`role_id` as `pivot_role_id` from `permissions` inner join `permission_role` on `permissions`.`id` = `permission_role`.`permission_id` where `permission_role`.`role_id` = ?' }
{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 2 ],
  __knexQueryUid: '866cc759-941b-4ce5-b33d-e0b0365cb83b',
  sql: 'select `permissions`.*, `permission_role`.`permission_id` as `pivot_permission_id`, `permission_role`.`role_id` as `pivot_role_id` from `permissions` inner join `permission_role` on `permissions`.`id` = `permission_role`.`permission_id` where `permission_role`.`role_id` = ?' }
{ __knexUid: '__knexUid1',
  __knexTxId: undefined,
  method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 2 ],
  __knexQueryUid: '866cc759-941b-4ce5-b33d-e0b0365cb83b',
  sql: 'select `permissions`.*, `permission_role`.`permission_id` as `pivot_permission_id`, `permission_role`.`role_id` as `pivot_role_id` from `permissions` inner join `permission_role` on `permissions`.`id` = `permission_role`.`permission_id` where `permission_role`.`role_id` = ?' }

Questions:

  1. As you can see there are 4 total objects. The first 2 and the second 2 seem to be part of the same query, except the second in the series has __knexUid & __knexTxId. Are these two separate queries or the same?
  2. I’m using Adonis Authentication and It seems that with every request, it’s querying 'select * from `users` where `id` = ? limit ?' 5-6 times. This seems excessive. It’s only calling 2 routes. No middleware. Calling auth.getUser() once.

Any help would be appreciated.


#2

I am not sure about the adonis-acl package, since I don’t maintain it. The auth package makes a single query and that is to verify that the user in the session does exists in the database


#3

@virk - I’m noticing that auth package makes 1 query upon request and then another query when the response is sent back. For instance, I have the following Route:

  Route.get('somePath', async ({session, auth, response}) => {

    // do some stuff - grab data

    response.send(someData); // if I comment this out, then only 1 call is made.
  });

Before response.send() only one auth call ('select * from `users` where `id` = ? limit ?') , but after it triggers, another call is made and I don’t know where it’s coming from.

Any idea why this might be?


#4

I don’t think this is possible. Sending of response has nothing to do with auth. Maybe you can share some repo that I can use to reproduce the issue.