Roles performance revisited

(Peter Sheppard) #1

As many people who use roles are aware, it can become extremely slow, exponentially so the more roles which are assigned within the system. This can cause asset listings (especially related content), menus, search, and even the backend to take up to 3 minutes to load a simple page.


I have had quite a bit of success by introducing a sub-query (see elsewhere on the developers forum) to limit the size of the join to the roles table to just the relevant users/groups, and whilst this brought timings to an acceptable level for a while, the more we allocate roles to users, it's got back up to the 2-minute stage.



However, I now think I've found the problem, and a solution. Even with my sub-query, I was still seeing a nested loop join creating a 200,000 tuple table in memory. Then a sort being performed on said table. I couldn't work out where all these tuples were coming from - this was far more than the number of assets being listed. What's actually happening, is postgres is considering the whole table. The problem is "ON p.userid = r.roleid". This is creating a tuple for each relevant role for EVERY asset that role is granted access to! I have modified every occurance of this in our system to something along the lines of "ON p.userd = r.roleid AND r.assetid = a.assetid" (the latter table alias is different in some of hte queries). Performance is now much higher; I have seen it down to sub-second queries when testing this.



Any comments/thoughts are appreciated.

(Greg Sherwood) #2

Thanks, as always, Peter. We'll make this change and run our test suite to see if it causes any problems. If not, we'll commit this into the next stable releases on all branches.

(K Buttress) #3

Was this integrated into any release?


Are roles still very slow when there are large numbers of users?

(Peter Sheppard) #4

No; search, asset listings, menu generation and the asset map now all work the same speed on our system with a good 200+ users having roles on around 90,000 assets, as they do with the roles table empty. We have other performance issues, but they're not related to roles, and have a lot to do with some hardware (especially ram) issues. But the bottleneck is now on the web server and not the database server (which is seeing load averages of 1-2% most of the time)


These mods aren't in 3.16.9, as I still had to patch files after that upgrade. I don't think they're in 3.18 either, but I've got quite a bit of consolidation work to do before we can look at that upgrade.