Performance Problems with User Permissions on bigger Datasets

Hi,

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.

Best regards,
Roman