search

passbolt / passbolt_api

Passbolt Community Edition (CE) API. The JSON API for the open source password manager for teams!
https://passbolt.com
GNU Affero General Public License v3.0
4.73k stars 311 forks source link

Request is taking to long #494

Closed grassdionera closed 1 year ago

grassdionera commented 1 year ago

Request is taking to long

What you did

I wanted to change the description of a specific entry directly on the right Info Panel. Here is an obfusced example of that entry

Name: asd/secret
URI: k8s://test1/test2/base
Username: IDENT_NAME_FOR_TESTING_L
Passwort: dummy
Description: IDENT_NAME_FOR_TESTING_L=test1test2test3test4test1test2test3test4test1test2test3test4test1test2test3test4

What happened

after click on saving, the UI Loads for about 60 Seconds and the i get the Error Message: There is an error while saving, because the Server is taking to long to respond. The nginx is sending an 504 Gateway Timeout. (because 60 seconds are the request timeout in nginx)

I can see in the Database Processes there are Running SQL Queries that are takting long. For about 150 to 200 Seconds. 

SELECT `ActionLogs`.`id` AS `ActionLogs__id`, `ActionLogs`.`user_id` AS `ActionLogs__user_id`, `ActionL
ogs`.`action_id` AS `ActionLogs__action_id`, `ActionLogs`.`context` AS `ActionLogs__context`, `ActionLogs`.`status` AS `ActionLogs__status`, `ActionLogs`.`created` AS `ActionLogs__created`, `
Actions`.`name` AS `Actions__name` FROM `action_logs` `ActionLogs` INNER JOIN ((SELECT `ActionLogs`.`id` AS `ActionLogs__id`, `Actions`.`name` AS `Actions__name` FROM `action_logs` `ActionLog
s` INNER JOIN `actions` `Actions` ON `Actions`.`id` = `ActionLogs`.`action_id` INNER JOIN `entities_history` `EntitiesHistory` ON `ActionLogs`.`id` = `EntitiesHistory`.`action_log_id` INNER J
OIN `permissions_history` `PermissionsHistory` ON `PermissionsHistory`.`id` = `EntitiesHistory`.`foreign_key` INNER JOIN `resources` `PermissionsHistoryResources` ON `PermissionsHistoryResour
ces`.`id` = `PermissionsHistory`.`aco_foreign_key` WHERE (`PermissionsHistoryResources`.`id` = 'c180c3f6-4e51-47ca-b09b-6580683c2082' AND `ActionLogs`.`status` = 1) GROUP BY `ActionLogs`.`id`
, `Actions`.`name` )\nUNION (SELECT `ActionLogs`.`id` AS `ActionLogs__id`, `Actions`.`name` AS `Actions__name` FROM `action_logs` `ActionLogs` INNER JOIN `actions` `Actions` ON `Actions`.`id`
 = `ActionLogs`.`action_id` INNER JOIN `entities_history` `EntitiesHistory` ON `ActionLogs`.`id` = `EntitiesHistory`.`action_log_id` INNER JOIN `resources` `Resources` ON `Resources`.`id` = `
EntitiesHistory`.`foreign_key` WHERE (`Resources`.`id` = 'c180c3f6-4e51-47ca-b09b-6580683c2082' AND `ActionLogs`.`status` = 1) GROUP BY `ActionLogs`.`id`, `Actions`.`name` )\nUNION (SELECT `A
ctionLogs`.`id` AS `ActionLogs__id`, `Actions`.`name` AS `Actions__name` FROM `action_logs` `ActionLogs` INNER JOIN `actions` `Actions` ON `Actions`.`id` = `ActionLogs`.`action_id` INNER JOIN
 `entities_history` `EntitiesHistory` ON `ActionLogs`.`id` = `EntitiesHistory`.`action_log_id` INNER JOIN `secret_accesses` `SecretAccesses` ON `SecretAccesses`.`id` = `EntitiesHistory`.`fore
ign_key` WHERE (`SecretAccesses`.`resource_id` = 'c180c3f6-4e51-47ca-b09b-6580683c2082' AND `ActionLogs`.`status` = 1) GROUP BY `ActionLogs`.`id`, `Actions`.`name` )\nUNION (SELECT `ActionLog
s`.`id` AS `ActionLogs__id`, `Actions`.`name` AS `Actions__name` FROM `action_logs` `ActionLogs` INNER JOIN `actions` `Actions` ON `Actions`.`id` = `ActionLogs`.`action_id` INNER JOIN `entiti
es_history` `EntitiesHistory` ON `ActionLogs`.`id` = `EntitiesHistory`.`action_log_id` INNER JOIN `secrets_history` `SecretsHistory` ON `SecretsHistory`.`id` = `EntitiesHistory`.`foreign_key`
 INNER JOIN `resources` `SecretsHistoryResources` ON `SecretsHistoryResources`.`id` = `SecretsHistory`.`resource_id` WHERE (`SecretsHistoryResources`.`id` = 'c180c3f6-4e51-47ca-b09b-6580683c2
082' AND `ActionLogs`.`status` = 1) GROUP BY `ActionLogs`.`id`, `Actions`.`name` )) `resourceActionLogs` ON `resourceActionLogs`.`ActionLogs__id` = `ActionLogs`.`id` INNER JOIN `users` `Users
` ON `Users`.`id` = `ActionLogs`.`user_id` INNER JOIN `profiles` `Profiles` ON `Users`.`id` = `Profiles`.`user_id` LEFT JOIN `entities_history` `EntitiesHistory` ON `ActionLogs`.`id` = `Entit
iesHistory`.`action_log_id` LEFT JOIN `permissions_history` `PermissionsHistory` ON `PermissionsHistory`.`id` = `EntitiesHistory`.`foreign_key` LEFT JOIN `users` `PermissionsHistoryUsers` ON
`PermissionsHistoryUsers`.`id` = `PermissionsHistory`.`aro_foreign_key` LEFT JOIN `resources` `PermissionsHistoryResources` ON `PermissionsHistoryResources`.`id` = `PermissionsHistory`.`aco_f
oreign_key` LEFT JOIN `groups` `PermissionsHistoryGroups` ON `PermissionsHistoryGroups`.`id` = `PermissionsHistory`.`aro_foreign_key` LEFT JOIN `folders` `PermissionsHistoryFolders` ON `Permi
ssionsHistoryFolders`.`id` = `PermissionsHistory`.`aco_foreign_key` LEFT JOIN `resources` `Resources` ON `Resources`.`id` = `EntitiesHistory`.`foreign_key` LEFT JOIN `secret_accesses` `Secret
Accesses` ON `SecretAccesses`.`id` = `EntitiesHistory`.`foreign_key` LEFT JOIN `resources` `SecretAccessResources` ON `SecretAccessResources`.`id` = `SecretAccesses`.`resource_id` LEFT JOIN `
secrets_history` `SecretsHistory` ON `SecretsHistory`.`id` = `EntitiesHistory`.`foreign_key` LEFT JOIN `users` `SecretsHistoryUsers` ON `SecretsHistoryUsers`.`id` = `SecretsHistory`.`user_id`
 LEFT JOIN `resources` `SecretsHistoryResources` ON `SecretsHistoryResources`.`id` = `SecretsHistory`.`resource_id` LEFT JOIN `folders_history` `FoldersHistory` ON `FoldersHistory`.`id` = `En
titiesHistory`.`foreign_key` LEFT JOIN `actions` `Actions` ON `Actions`.`id` = `ActionLogs`.`action_id` WHERE `ActionLogs`.`status` = 1 GROUP BY `ActionLogs`.`id`, `Actions`.`name`  ORDER BY
`ActionLogs`.`created` desc LIMIT 5 OFFSET 0
SELECT (COUNT(*)) AS `count` FROM (SELECT `ActionLogs`.`id` AS `ActionLogs__id`, `ActionLogs`.`user_id`
 AS `ActionLogs__user_id`, `ActionLogs`.`action_id` AS `ActionLogs__action_id`, `ActionLogs`.`context` AS `ActionLogs__context`, `ActionLogs`.`status` AS `ActionLogs__status`, `ActionLogs`.`c
reated` AS `ActionLogs__created`, `Actions`.`name` AS `Actions__name` FROM `action_logs` `ActionLogs` INNER JOIN ((SELECT `ActionLogs`.`id` AS `ActionLogs__id`, `Actions`.`name` AS `Actions__
name` FROM `action_logs` `ActionLogs` INNER JOIN `actions` `Actions` ON `Actions`.`id` = `ActionLogs`.`action_id` INNER JOIN `entities_history` `EntitiesHistory` ON `ActionLogs`.`id` = `Entit
iesHistory`.`action_log_id` INNER JOIN `permissions_history` `PermissionsHistory` ON `PermissionsHistory`.`id` = `EntitiesHistory`.`foreign_key` INNER JOIN `resources` `PermissionsHistoryReso
urces` ON `PermissionsHistoryResources`.`id` = `PermissionsHistory`.`aco_foreign_key` WHERE (`PermissionsHistoryResources`.`id` = 'c180c3f6-4e51-47ca-b09b-6580683c2082' AND `ActionLogs`.`stat
us` = 1) GROUP BY `ActionLogs`.`id`, `Actions`.`name` )\nUNION (SELECT `ActionLogs`.`id` AS `ActionLogs__id`, `Actions`.`name` AS `Actions__name` FROM `action_logs` `ActionLogs` INNER JOIN `a
ctions` `Actions` ON `Actions`.`id` = `ActionLogs`.`action_id` INNER JOIN `entities_history` `EntitiesHistory` ON `ActionLogs`.`id` = `EntitiesHistory`.`action_log_id` INNER JOIN `resources`
`Resources` ON `Resources`.`id` = `EntitiesHistory`.`foreign_key` WHERE (`Resources`.`id` = 'c180c3f6-4e51-47ca-b09b-6580683c2082' AND `ActionLogs`.`status` = 1) GROUP BY `ActionLogs`.`id`, `
Actions`.`name` )\nUNION (SELECT `ActionLogs`.`id` AS `ActionLogs__id`, `Actions`.`name` AS `Actions__name` FROM `action_logs` `ActionLogs` INNER JOIN `actions` `Actions` ON `Actions`.`id` =
`ActionLogs`.`action_id` INNER JOIN `entities_history` `EntitiesHistory` ON `ActionLogs`.`id` = `EntitiesHistory`.`action_log_id` INNER JOIN `secret_accesses` `SecretAccesses` ON `SecretAcces
ses`.`id` = `EntitiesHistory`.`foreign_key` WHERE (`SecretAccesses`.`resource_id` = 'c180c3f6-4e51-47ca-b09b-6580683c2082' AND `ActionLogs`.`status` = 1) GROUP BY `ActionLogs`.`id`, `Actions`
.`name` )\nUNION (SELECT `ActionLogs`.`id` AS `ActionLogs__id`, `Actions`.`name` AS `Actions__name` FROM `action_logs` `ActionLogs` INNER JOIN `actions` `Actions` ON `Actions`.`id` = `ActionL
ogs`.`action_id` INNER JOIN `entities_history` `EntitiesHistory` ON `ActionLogs`.`id` = `EntitiesHistory`.`action_log_id` INNER JOIN `secrets_history` `SecretsHistory` ON `SecretsHistory`.`id
` = `EntitiesHistory`.`foreign_key` INNER JOIN `resources` `SecretsHistoryResources` ON `SecretsHistoryResources`.`id` = `SecretsHistory`.`resource_id` WHERE (`SecretsHistoryResources`.`id` =
 'c180c3f6-4e51-47ca-b09b-6580683c2082' AND `ActionLogs`.`status` = 1) GROUP BY `ActionLogs`.`id`, `Actions`.`name` )) `resourceActionLogs` ON `resourceActionLogs`.`ActionLogs__id` = `ActionL
ogs`.`id` INNER JOIN `users` `Users` ON `Users`.`id` = `ActionLogs`.`user_id` INNER JOIN `profiles` `Profiles` ON `Users`.`id` = `Profiles`.`user_id` LEFT JOIN `entities_history` `EntitiesHis
tory` ON `ActionLogs`.`id` = `EntitiesHistory`.`action_log_id` LEFT JOIN `permissions_history` `PermissionsHistory` ON `PermissionsHistory`.`id` = `EntitiesHistory`.`foreign_key` LEFT JOIN `u
sers` `PermissionsHistoryUsers` ON `PermissionsHistoryUsers`.`id` = `PermissionsHistory`.`aro_foreign_key` LEFT JOIN `resources` `PermissionsHistoryResources` ON `PermissionsHistoryResources`
.`id` = `PermissionsHistory`.`aco_foreign_key` LEFT JOIN `groups` `PermissionsHistoryGroups` ON `PermissionsHistoryGroups`.`id` = `PermissionsHistory`.`aro_foreign_key` LEFT JOIN `folders` `P
ermissionsHistoryFolders` ON `PermissionsHistoryFolders`.`id` = `PermissionsHistory`.`aco_foreign_key` LEFT JOIN `resources` `Resources` ON `Resources`.`id` = `EntitiesHistory`.`foreign_key`
LEFT JOIN `secret_accesses` `SecretAccesses` ON `SecretAccesses`.`id` = `EntitiesHistory`.`foreign_key` LEFT JOIN `resources` `SecretAccessResources` ON `SecretAccessResources`.`id` = `Secret
Accesses`.`resource_id` LEFT JOIN `secrets_history` `SecretsHistory` ON `SecretsHistory`.`id` = `EntitiesHistory`.`foreign_key` LEFT JOIN `users` `SecretsHistoryUsers` ON `SecretsHistoryUsers
`.`id` = `SecretsHistory`.`user_id` LEFT JOIN `resources` `SecretsHistoryResources` ON `SecretsHistoryResources`.`id` = `SecretsHistory`.`resource_id` LEFT JOIN `folders_history` `FoldersHist
ory` ON `FoldersHistory`.`id` = `EntitiesHistory`.`foreign_key` LEFT JOIN `actions` `Actions` ON `Actions`.`id` = `ActionLogs`.`action_id` WHERE `ActionLogs`.`status` = 1 GROUP BY `ActionLogs
`.`id`, `Actions`.`name` ) `count_source`

This always happens when the Activity Tab on the Info panel is open, and it happens sometimes when the Activity Panel is not open.

What you expected to happen

I want that the Entry could be saved and the SQL Query should not take so long.

pabloelcolombiano commented 1 year ago

@grassdionera thank you for pointing this out. We are currently working on a command to purge the action_logs table, which will expectedly significantly reduce the time of the query you pointed.

How many entries do you have in your action_logs table, in order to have an rough idea?

Also note that opening issues on the passbolt forum is more convenient for the mainteners and the community. We can then keep you posted more easily when improvements related to your issue are published.

grassdionera commented 1 year ago

Hey @pabloelcolombiano thanks for the fast reply. We have currently 729761 entries in the action logs table.

a purge command for the action logs would be good to have, for cleaning up some of the old logs.

but i think the problem of the above sqls will remain and they become slower from time to time. We are a small company with not mutch entries. But someone with a lot more entries will also produce more action_logs. and so the sqls will become slower even if someone purges old logs

ishanvyas22 commented 1 year ago

Hey @grassdionera, with the v4.2.0 we've released few performance improvements related to SQL queries. You should try to upgrade and check if it reduces the loading time.

Also, we are in constant endeavour to improve performance with coming releases. Closing this issue for now but feel free to post in community forum if you face any problems or have questions.