We are long Term PIMCORE users and recently stumbled upon a peformance problem on one of our bigger installations. Access-restricted users see severe slowdowns (load times of 30 seconds +) when opening trees and list-views though server load is generally pretty low on their instances.
Watching the SQL queries while working with the system we noticed, that the problematic ones seem to be the ones checking for access rights - e.g.
SELECT object_11.o_id as o_id, `object_11`.`o_type` FROM `object_11` WHERE (o_path = '/some/folder' OR o_path LIKE '/some/folder/%') AND ( ( select list from users_workspaces_object where userId in (3, 96) and LOCATE(CONCAT(o_path, o_key), cpath)= 1 ORDER BY LENGTH(cpath) DESC LIMIT 1)= 1 OR ( select list from users_workspaces_object where userId in (3, 96) and LOCATE(cpath, CONCAT(o_path, o_key))= 1 ORDER BY LENGTH(cpath) DESC LIMIT 1)= 1 ) AND object_11.o_type IN ('object', 'folder') ORDER BY `SomeindexedColumn` DESC LIMIT 200
The data set is a little bit bigger but not too extreme in our opinion:
- ~7 million objects in total
- ~350.000 objects in the list that is the most problematic
- just ~50 entries in user_workspaces_object
The setup we use:
- PIMCORE Instance - 8 cores xeon / 32 GB RAM, Load & RAM usage ~20-30%
- MySQL Db - 8 cores xeon / 16 GB RAM, Load ~20%, RAM usage ~50%, well configured according to mysqltuner
- PIMCORE Version still 5.8.x
Any help or hints would be greatly appreciated.