[rt-devel] Users::WhoHaveRight and Sybase and Sudden MySQL slowness

[Jesse Vincent]

On Fri, Jun 27, 2003 at 10:17:59AM +0930, Aidan Mountford wrote:
> I hope no one minds if I inject a couple of cents worth of info
> re- RT
> 
> Having developed a large number of apps over the years that do (from
> a database standpoint) similar things to RT permissions/group structure.
> 
> I would like to offer a couple of suggestions.
> 
> Firstly - the current RT make a large number of very similar DB transctions
> on things like the "HOME" page.
> 
> The shear quantity of db queries whilst not a huge problem - could manifest
> itself on either slow networks/servers..  It may be better (and I know this
> breaks the "OO" design rules) to try and use a "select ... where a in
> (1,2,3)"
> for this part of the code.

Now that we've fixed a stupid bug, perf doesn't seem to be a huge issue
with page 1. and yes, I don't want to go down the handcoded sql for page
display unless death is the only other option.




> With respect to caching - and I can honestly say - I have looked very little
> into it's implementation in RT - It would probably be caching the effective
> rights - not the groups ??? Or is this what it is doing.
> 
> ie:  Queue X - Current User - has the following rights  (A B C D)

We looked at that style of cache, but there were some nasty issues with
roles. It's actually more of "user has right A for Queue x" as a boolean

> This done - having got itself up and running - it should be a rapid process
> to query this data.

Yes. 

> A local cache on the webserver would allow this data to be queried once per
> session or when the ACLs change .

How can you tell when another process changes the ACLs. Instead, we
expire the cache after a timeout or when any internal change happens to
the ACLs.

> The query itself could be a simple "Select * where user = X order by Queue"
> results
> converted from this "flat" format to a heirarchial hash structure for ease
> of
> implementation.

That's very difficult when you've got roles to worry about.

> The actual load of converting the complex multilevel user/group structure to
> this "flat" representation should be done only when the permissions change (maybe
> a > background task  -  when the permissions have stabilised) not when a user logs
> on.

Well, the complex multilevel user/group structure _is_ unrolled only
when membership changes. Doing what you suggest fully is something we
looked at during implementation for 3.0 and decided was too complex at
the implementation level and too likely to break things. It may be
revisited later.


> I have been playing around with index's queries etc on Pg and am making
> reasonable
> progress - and will publish the results so that everyone can tear me to
> shreads
> when I have got over this cold :(


Feel better. I'm excited to see your index work.

> 
> Cheers..
> 
> -----Original Message-----
> From: rt-devel-admin at lists.fsck.com
> [mailto:rt-devel-admin at lists.fsck.com]On Behalf Of John Stoffel
> Sent: Friday, 27 June 2003 5:32 AM
> To: Jesse Vincent
> Cc: John Stoffel; Harald Wagener; Audley, Christopher;
> rt-devel at lists.fsck.com
> Subject: Re: [rt-devel] Users::WhoHaveRight and Sybase and Sudden MySQL
> slowness
> 
> 
> 
> Jesse> Nope. This Cache is Caching recursive group membership, which
> Jesse> would otherwise hurt REALLY badly (O(n) SQL queries just as bad
> Jesse> as the current query per acl question)
> 
> Then the question I ask is *why* have recursive group membership at
> all?  It seems to me that it just expands exponentially the number of
> permutations to go through.
> 
> It would seem that it's simple enough, each user is a member is X
> groups.  Each group has Y privs.  Is this what you're trying to cache?
> 
> John
> _______________________________________________
> rt-devel mailing list
> rt-devel at lists.fsck.com
> http://lists.fsck.com/mailman/listinfo/rt-devel
> 
> _______________________________________________
> rt-devel mailing list
> rt-devel at lists.fsck.com
> http://lists.fsck.com/mailman/listinfo/rt-devel
> 

-- 
http://www.bestpractical.com/rt  -- Trouble Ticketing. Free.