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

[Aidan Mountford]

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.

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)

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

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

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.

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.

This should also present a constant load - regardless of the type of
database in use
(Noting that MySQL seems to do merge joins - faster than Pg).

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 :(

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