Hi,
I’ve opened another topic (this: Bad performance on page loading), but unfortunatly I could not respond in time, so I write you again for my issue (bad performance on Passbolt).
I have done some investigation, and I have some news about my performance problem.
I have isolated the query that take > 10 sec to be executed, this:
SELECT Resources.id AS Resources__id, Resources.name AS Resources__name, Resources.username AS Resources__username, Resources.uri AS Resources__uri, Resources.description AS Resources__description, Resources.deleted AS Resources__deleted, Resources.created AS Resources__created, Resources.modified AS Resources__modified, Resources.created_by AS Resources__created_by, Resources.modified_by AS Resources__modified_by, Permission.id AS Permission__id, Permission.aco AS Permission__aco, Permission.aco_foreign_key AS Permission__aco_foreign_key, Permission.aro AS Permission__aro, Permission.aro_foreign_key AS Permission__aro_foreign_key, Permission.type AS Permission__type, Permission.created AS Permission__created, Permission.modified AS Permission__modified, Favorites.id AS Favorites__id, Favorites.user_id AS Favorites__user_id, Favorites.foreign_key AS Favorites__foreign_key, Favorites.foreign_model AS Favorites__foreign_model, Favorites.created AS Favorites__created, Favorites.modified AS Favorites__modified FROM resources Resources INNER JOIN permissions Permission ON Resources.id = (Permission.aco_foreign_key) LEFT JOIN favorites Favorites ON (Favorites.user_id = ‘’ AND Resources.id = (Favorites.foreign_key)) WHERE (Resources.deleted = 0 AND Permission.id = (SELECT Permissions.id AS Permissions__id FROM permissions Permissions WHERE (Permissions.aco_foreign_key = Resources.id AND (Permissions.aro_foreign_key = ‘’ OR Permissions.aro_foreign_key in (SELECT Groups.id AS Groups__id FROM groups Groups INNER JOIN groups_users GroupsUsers ON Groups.id = (GroupsUsers.group_id) INNER JOIN users Users ON Users.id = (GroupsUsers.user_id) WHERE (Groups.deleted = 0 AND Users.id = ‘’))) AND Permissions.type in (1,7,15)) ORDER BY Permissions.type DESC LIMIT 1)) ORDER BY Resources.modified DESC;
I’m not a MySQL expert, and I don’t know why it could take this amount of time.
Could you please help me?
Could you execute that query with a EXPLAIN so we get a bit more info on what is mysql doing?
Something like:
EXPLAIN SELECT Resources.id AS Resources__id, Resources.name AS Resources__name, Resources.username AS Resources__username, Resources.uri AS Resources__uri, Resources.description AS Resources__description, Resources.deleted AS Resources__deleted, Resources.created AS Resources__created, Resources.modified AS Resources__modified, Resources.created_by AS Resources__created_by, Resources.modified_by AS Resources__modified_by, Permission.id AS Permission__id, Permission.aco AS Permission__aco, Permission.aco_foreign_key AS Permission__aco_foreign_key, Permission.aro AS Permission__aro, Permission.aro_foreign_key AS Permission__aro_foreign_key, Permission.type AS Permission__type, Permission.created AS Permission__created, Permission.modified AS Permission__modified, Favorites.id AS Favorites__id, Favorites.user_id AS Favorites__user_id, Favorites.foreign_key AS Favorites__foreign_key, Favorites.foreign_model AS Favorites__foreign_model, Favorites.created AS Favorites__created, Favorites.modified AS Favorites__modified FROM resources Resources INNER JOIN permissions Permission ON Resources.id = (Permission.aco_foreign_key) LEFT JOIN favorites Favorites ON (Favorites.user_id = ‘’ AND Resources.id = (Favorites.foreign_key)) WHERE (Resources.deleted = 0 AND Permission.id = (SELECT Permissions.id AS Permissions__id FROM permissions Permissions WHERE (Permissions.aco_foreign_key = Resources.id AND (Permissions.aro_foreign_key = ‘’ OR Permissions.aro_foreign_key in (SELECT Groups.id AS Groups__id FROM groups Groups INNER JOIN groups_users GroupsUsers ON Groups.id = (GroupsUsers.group_id) INNER JOIN users Users ON Users.id = (GroupsUsers.user_id) WHERE (Groups.deleted = 0 AND Users.id = ‘’))) AND Permissions.type in (1,7,15)) ORDER BY Permissions.type DESC LIMIT 1)) ORDER BY Resources.modified DESC;
Hi remy,
I don’t know what is the “www-data”, because if I run your command it says “user www-data does not exist”.
Anyway, without “www-data”, I got this response:
Cleanup shell (dry-run)
2 orphan records found in table GroupsUsers (hard deleted groups)
2 orphan records found in table Comments (soft deleted resources)
PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in /var/www/passbolt/vendor/cakephp/cakephp/src/Database/ValueBinder.php on line 54
PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in /var/www/passbolt/vendor/cakephp/cakephp/src/Utility/Hash.php on line 61
It seems you have some integrity issue with your database. It’s hard to tell why but your DB seem to be in a bad state. I have never seen the cleanup script not being able to run until the end. Do you modify manually the data using the API or directly in SQL?
The best would be for you to contact us on support@passbolt.com, so that we can have a closer look at your data.
