Blog for October 2009

Monday, October 19th, 2009

Slow Acl->check() calls in CakePHP

For version 2 of one of our projects we made the decision to scrap the customized access control from version 1 and make use of the ACL functionality built-in to Cake. Which seemed like the right way to go, that way we’d benefit from improvements to this feature in Cake itself and would have one less thing to worry about. That was almost certainly the right decision, but we very quickly ran into issues with page load times. For most Cake applications the existing ACL functionality is probably fine, but in this case we wanted to know if a user had permission to access certain functionality before displaying a link to it. So we were running multiple calls to Acl->check() on every page load, which quickly bogged things down.

Looking into things made it pretty obvious we weren’t the only ones with this problem. There were suggestions for tuning that we implemented, but that didn’t solve the problem. So today I sat down to dig into this again. The root of the problem was with a particular slow query that (in the case of my development machine) took around 200 ms to run:

SELECT
	`Aco`.`id`, 
	`Aco`.`parent_id`,
	`Aco`.`model`,
	`Aco`.`foreign_key`,
	`Aco`.`alias`
FROM
	`acos` AS `Aco`
	LEFT JOIN `acos` AS `Aco0`
		ON (`Aco0`.`alias` = ’controllers’)
	LEFT JOIN `acos` AS `Aco1`
		ON (
			`Aco1`.`lft` > `Aco0`.`lft`
			AND `Aco1`.`rght` < `Aco0`.`rght`
			AND `Aco1`.`alias` = ’App’
		)
	LEFT JOIN `acos` AS `Aco2`
		ON (
			`Aco2`.`lft` > `Aco1`.`lft`
			AND `Aco2`.`rght` < `Aco1`.`rght`
			AND `Aco2`.`alias` = ’Menus’
		)
	LEFT JOIN `acos` AS `Aco3`
		ON (
			`Aco3`.`lft` > `Aco2`.`lft`
			AND `Aco3`.`rght` < `Aco2`.`rght`
			AND `Aco3`.`alias` = ’index’
		)
WHERE
	(
		(
			`Aco`.`lft` <= `Aco0`.`lft`
			AND `Aco`.`rght` >= `Aco0`.`rght`
		) OR (
			`Aco`.`lft` <= `Aco3`.`lft`
			AND `Aco`.`rght` >= `Aco3`.`rght`
		)
	)
ORDER BY
	`Aco`.`lft` DESC

In this query Cake’s ACL routine is looking for rules matching controllers/App/Menus/index to see whether or not the current user should be allowed in. My first thought was caching the results of these queries, but when I looked at what was running I found that it wasn’t the same query over and over, but rather a large number of similar queries that each took around 200ms to run. So I’d need to speed up the query the first time it ran, not the second time.

So I tried messing around with the SQL directly. Surprisingly, the first thing I tried seemed to work. I moved the lft and rght conditions out of the ON clauses and into the WHERE clause:

SELECT
	`Aco`.`id`,
	`Aco`.`parent_id`,
	`Aco`.`model`,
	`Aco`.`foreign_key`,
	`Aco`.`alias`
FROM
	`acos` AS `Aco`
	LEFT JOIN `acos` AS `Aco0`
		ON (`Aco0`.`alias` = ’controllers’)
	LEFT JOIN `acos` AS `Aco1`
		ON (`Aco1`.`alias` = ’App’)
	LEFT JOIN `acos` AS `Aco2`
		ON ( `Aco2`.`alias` = ’Menus’)
	LEFT JOIN `acos` AS `Aco3`
		ON (`Aco3`.`alias` = ’index’)
WHERE
	`Aco1`.`lft` > `Aco0`.`lft`
	AND `Aco1`.`rght` < `Aco0`.`rght`
	AND `Aco2`.`lft` > `Aco1`.`lft`
	AND `Aco2`.`rght` < `Aco1`.`rght`
	AND `Aco3`.`lft` > `Aco2`.`lft`
	AND `Aco3`.`rght` < `Aco2`.`rght`
	AND (
		(
			`Aco`.`lft` <= `Aco0`.`lft`
			AND `Aco`.`rght` >= `Aco0`.`rght`
		) OR (
			`Aco`.`lft` <= `Aco3`.`lft`
			AND `Aco`.`rght` >= `Aco3`.`rght`
		)
	)
ORDER BY
	`Aco`.`lft` DESC

So, now I knew what the problem was, but I didn’t yet know where it was coming from or how to solve the problem. A bit more digging revealed that the SQL was generated in cake/libs/model/db_acl.php in the node() method of class AclNode. This allowed me to add in a custom model MyAcl that did the following:

1. Defined custom MyAco and MyAro classes that extended Aco and Aro and redefined the node() method to use the faster SQL from above.
2. Defined a custom MyAcl class that extended DbAcl and redefined the constructor to use MyAco and MyAro instead of Aro and Aco.
3. Changed core.php to use MyAcl as Acl.classname. I added an App::import("model","MyAcl") before the Configure::write() statement to ensure the model would be available.

 With these changes in place things ran much, much faster. Now I just need to figure out some of proving to myself that my new SQL is actually equivalent to what was being generated before.