Repeated queries and scalability

I chose this plugin for an intranet I’m working on. It seemed simple and clear, and in general to be doing what I needed.

I added the groups I needed, just short of 40, then categories to match them, and parent pages for each. Roughly 40 categories and top-level pages plus another 10 or so subpages.

Since the groups aren’t hierarchical, a lot of pages and categories have more than group assigned to them, on average two.

I now started adding posts to a couple of the categories, about 70 posts in all.

While I was working the system got slower and slower, not so much in the admin area as in the front.

So I installed debug objects to see wtf was going on. And noted that on the same page where I had 18 (eighteen) queries without UAM, i had 10302 (ten thousand threehundred and two queries I sh*t you not!) with the plugin active.

Total query time: 1 392,1ms for 10302 queries (1,392127990722656s)
Page generated in 4 000,0ms; (4,6721889972686767578125s); 65,20% PHP; 34,80% MySQL

In reality that means going from a page served in less than half a second to about four seconds on localhost and eight seconds on my actual host. Activating W3 total cache, the time got down to seven seconds, so that is still faaaaar too slow.

I sat down and started to analyze the reported queries, to try to get why the sh*t hit the fan that way, and it turns out it is probably an iteration/recursivity problem.

That is, rather than doing a single query and using the result to filter what is accessible or not, it asks for each and every restrictable object. In some cases it makes the same query three or more times per object almost consecutively.

Like this:

Time: 0.2ms (0.00022101402282715s)
Query: SELECT object_id as id
FROM wp_uam_accessgroup_to_object
WHERE group_id = 1
AND object_type = ‘category’
Function: UamUserGroup->getObjectsFromType()

Time: 0.2ms (0.00017690658569336s)
Query: SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy = ‘category’ AND t.term_id = 1 LIMIT 1
Function: get_term()

Time: 0.2ms (0.00018811225891113s)
Query: SELECT object_id as id
FROM wp_uam_accessgroup_to_object
WHERE group_id = 1
AND object_type = ‘category’
Function: UamUserGroup->_getAssignedObjects()

Time: 0.1ms (0.00014400482177734s)
Query: SELECT COUNT(*)
FROM wp_term_relationships AS tr,
wp_term_taxonomy AS tt
WHERE tr.object_id = ‘247’
AND tt.term_id = ‘1’
AND tr.term_taxonomy_id = tt.term_taxonomy_id
AND tt.taxonomy = ‘category’
Function: UamUserGroup->_isPostInCategory()

Time: 0.2ms (0.00021100044250488s)
Query: SELECT object_id as id
FROM wp_uam_accessgroup_to_object
WHERE group_id = 1
AND object_type = ‘post’
Function: UamUserGroup->_getAssignedObjects()

And so on… for ever and ever and ever.

So my choices are to accept the performance hit, go through and debug the 4000 lines of code in the plugin, or start over with another plugin.

Alternative one is probably a deal-breaker, since either the customer or the customers host will flog me if I let this go live in its current condition.

Alternative two would be doable if I can be fairly sure of success. The cost would be time, which I can pay if it works and not if it doesn’t.

The third alternative means starting over, which is very unappealing but might be what it comes down to in the end.

What I would like is for the plugin author to test and see if he can recreate the problem, and share what might be the offending method, if there are any workarounds and whether it is a simple fix or means a total rewrite.

If it is something he can recreate and if it is doable to fix it within a reasonable time-frame, I am available to help.

http://wordpress.org/extend/plugins/user-access-manager/