Users::WhoHaveRight and Sybase

[Audley_Christopher]

We have been pushing with all our might to get RT3 deployed on Sybase. We have had it up and running for a while but the performance just didn’t cut it. The most glaring problem was trying to create a new ticket. The ticket submission form would take 5-10 minutes to come up. All of this time was spent in the Users::WhoHaveRight, even after upgrading to rt 3.0.3.

The bulk of this problem has been caused by our inability to find a solution to the distinct problem. Like Oracle, Sybase won’t take a distinct on main.* for Users because of the TEXT fields. Unlike Oracle, there doesn’t seem to be a cute trick to get around the problem. We have compensated by modifying SearchBuild::_DoQuery to discard duplicates in the query results. We are forced to consider the distinct clause is a nice but unnecessary optimization.

However, this kills performance in Users::WhoHaveRight. If I take the generated query and limit the select to main.Name and add distinct, the query time drops to 20 seconds. However, even twenty seconds is too slow, users won’t use the system if thats the best we can do.

The solution we found is to break WhoHaveRight into two steps. First, get the groups that have the right, via a new method Groups::WithRight, followed by a query for the members of a set of groups, Users::WhoBelongToGroups.

Using this split query, we get access times down to less than 300ms. I’ve attached the local/lib/RT/Users_Local.pm, local/lib/RT/Groups_Local.pm and local/html/Elements/SelectOwner that we are using, I’m curious if this method improves access times with other database engines.

Chris

Groups_Local.pm (2.7 KB)

Users_Local.pm (1.31 KB)

SelectOwner (1.95 KB)

[Jesse_Vincent]

We have been pushing with all our might to get RT3 deployed on Sybase. We have had it up and running for a while but the performance just didn’t cut it. The most glaring problem was trying to create a new ticket. The ticket submission form would take 5-10 minutes to come up. All of this time was spent in the Users::WhoHaveRight, even after upgrading to rt 3.0.3.

Chris:
Thanks very much for the patch. I’ll definitely be having a good
hard look at it in the next couple days.

jesse

Chris

Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.

[Harald_Wagener]

Chris, Jesse,

This seems to help me with my MySQL slowness very much. Query time is
down to sub second values again.

One problem with my setup might be that I upgraded from RT 2 with an old
version of the import tool (about 6 to 8 weeks ago) and some db
weirdness might come from that. Is CachedGroupMembers supposed to have
around 40,000 rows with only 4700 tickets, 200 requestors without
special rights and less than 10 privileged users?

Regards,
Harald Wagener

[Ron]

I am working on the RT (arch 3.0.2) User interface trying to make it RTL
(right to left).
The idea is to (just like in l10n efforts) read the proffered language
and then display the user interface in a right to left direction if the
language setting is that of a RTL language (namely hebrew and arabic).
my question is; where are the sessions language setting stored? can I
read this information from a variable?
also, I have noticed that the Elements/TitleBoxStart object holds two
places for a right and left title. how do I specify a right title?
where is that used? and would it be OK to leach on the right title for
an RTL environment?

Regards
Ron.

[Paul_Wagland]

Audley, Christopher wrote

Sent: Wednesday, June 25, 2003 20:46
Subject: [rt-devel] Users::WhoHaveRight and Sybase

We have been pushing with all our might to get RT3 deployed on
Sybase. We have had it up and running for a while but the
performance just didn’t cut it. The most glaring problem was
trying to create a new ticket. The ticket submission form would
take 5-10 minutes to come up. All of this time was spent in the
Users::WhoHaveRight, even after upgrading to rt 3.0.3.

[…]

The solution we found is to break WhoHaveRight into two steps.
First, get the groups that have the right, via a new method
Groups::WithRight, followed by a query for the members of a set
of groups, Users::WhoBelongToGroups.

Using this split query, we get access times down to less than
300ms. I’ve attached the local/lib/RT/Users_Local.pm,
local/lib/RT/Groups_Local.pm and local/html/Elements/SelectOwner
that we are using, I’m curious if this method improves access
times with other database engines.

Well, I have tested this and have come up with the following results (all
times are averaged over ten runs):

Page before time after time
Ticket/Create.html 13.6 0.4
Ticket/ModifyPeople.html 9.5 0.6
Search/Bulk.html ~4 ~2

These times come from running wget 10 times on these URLS, and then dividing
the cumlative (second) times by ten, so they might be a little rough… That
said however, they are still significantly faster!

This will be going into our production environment, so if we see any
problems I will let you know.

Thanks Christopher for the great work!

Cheers,
Paul

[Autrijus_Tang]

The solution we found is to break WhoHaveRight into two steps. First,
get the groups that have the right, via a new method
Groups::WithRight, followed by a query for the members of a set of
groups, Users::WhoBelongToGroups.

What about explicit UserRight against that object, i.e. not via
group rights? Did you take it into account?

Thanks,
/Autrijus/

[Audley_Christopher]

There are special groups in RT called ACLEquivalence groups that
represent users in a one-to-one relationship. When you assign rights to
an individual User, you are actually granting the right to that users
ACLEquivalence group.

Cheers
Chris-----Original Message-----
From: Autrijus Tang [mailto:autrijus@autrijus.org]
Sent: Thursday, June 26, 2003 12:31 PM
To: Audley, Christopher
Cc: rt-devel@lists.fsck.com
Subject: Re: [rt-devel] Users::WhoHaveRight and Sybase

On Wed, Jun 25, 2003 at 02:46:02PM -0400, Audley, Christopher wrote:

The solution we found is to break WhoHaveRight into two steps. First,
get the groups that have the right, via a new method
Groups::WithRight, followed by a query for the members of a set of
groups, Users::WhoBelongToGroups.

What about explicit UserRight against that object, i.e. not via
group rights? Did you take it into account?

Thanks,
/Autrijus/

[Autrijus_Tang]

There are special groups in RT called ACLEquivalence groups that
represent users in a one-to-one relationship. When you assign rights to
an individual User, you are actually granting the right to that users
ACLEquivalence group.

Yeah, just been pointed out to me. Sorry, me stupid.

Thanks,
/Autrijus/

[Jesse_Vincent]

I am working on the RT (arch 3.0.2) User interface trying to make it RTL
(right to left).
The idea is to (just like in l10n efforts) read the proffered language
and then display the user interface in a right to left direction if the
language setting is that of a RTL language (namely hebrew and arabic).
my question is; where are the sessions language setting stored? can I
read this information from a variable?

I believe autrijus and I had talked about a pseudo-string in the
localized message catalog called “TextDirection” that would default to
LtoR but could be set to RtoL for languages that needed that. One
thing I’m really just not sure how to handle within RT is what happens
if most of the UI is RtoL, but you’ve got embedded english text that
should be LtoR.

How much experience do you have with standards based translation

of apps into something that will work properly RtoL? I’d be curious to
hear what techniques you’ll be using.

Jesse

also, I have noticed that the Elements/TitleBoxStart object holds two
places for a right and left title. how do I specify a right title?

That’s so there can be buttons in the titlebar. It’s really, really not
what you want here.

Best,
Jesse

Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.

[stoffel]

Harald> This seems to help me with my MySQL slowness very much. Query
Harald> time is down to sub second values again.

Harald> One problem with my setup might be that I upgraded from RT 2
Harald> with an old version of the import tool (about 6 to 8 weeks
Harald> ago) and some db weirdness might come from that. Is
Harald> CachedGroupMembers supposed to have around 40,000 rows with
Harald> only 4700 tickets, 200 requestors without special rights and
Harald> less than 10 privileged users?

To me, it sounds like this cache is never expiring old data, which is
what a cache should do! Or the design of the ACL stuff isn’t done
right here…

Ok, I’ll shutup and sit back and watch the discussion again,
interesting stuff though!

John

[Jesse_Vincent]

Harald> This seems to help me with my MySQL slowness very much. Query
Harald> time is down to sub second values again.

Harald> One problem with my setup might be that I upgraded from RT 2
Harald> with an old version of the import tool (about 6 to 8 weeks
Harald> ago) and some db weirdness might come from that. Is
Harald> CachedGroupMembers supposed to have around 40,000 rows with
Harald> only 4700 tickets, 200 requestors without special rights and
Harald> less than 10 privileged users?

To me, it sounds like this cache is never expiring old data, which is
what a cache should do! Or the design of the ACL stuff isn’t done
right here…

Nope. This Cache is Caching recursive group membership, which would
otherwise hurt REALLY badly (O(n) SQL queries just as bad as the current
query per acl question)

Ok, I’ll shutup and sit back and watch the discussion again,
interesting stuff though!

John

Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.

[stoffel]

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

[Jesse_Vincent]

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?

Jeff is a member of the sysadmin group. the sysadmin group is a memeber
of the operations team, etc. This sort of functionality is critical in a
number of organizations.

John

---

rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.

[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@lists.fsck.com
[mailto:rt-devel-admin@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@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@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

[Jesse_Vincent]

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@lists.fsck.com
[mailto:rt-devel-admin@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@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@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

---

rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.

[Autrijus_Tang]

The idea is to (just like in l10n efforts) read the proffered language
and then display the user interface in a right to left direction if the
language setting is that of a RTL language (namely hebrew and arabic).
my question is; where are the sessions language setting stored? can I
read this information from a variable?

$session{CurrentUser}->LanguageHandle->language_tag

Thanks,
/Autrijus/

[Aidan_Mountford]

<… Lots Deleted …>

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.

Several options - Either a local semaphore or a file (Could even be
/tmp/LastACLChange

Or a field in the DB that can be queried once per page display … One
fast query being better than 10 slow ones

The first issue obviously does not work too well if you have a clustered
environment unless the /tmp/ is on a shared mountpoint.

If you were in more of a butchers mood, this file could be generated by a
“background” process and simply read by processes that require it (ie when a
session starts - or the file’s timestamp changes) - thereby reducing the
load
on the DB and keeping the whole cache local to the webserver.

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.

Yes and no. At any given time - a single user has only 1 effective access
list
to a nominated object. This “effective” list is a function of your business
rules, the ACL’s specified against the object for this user, and the roles
that is being played by that user.

Even a cache that say had roles as well we could go “Aidan@oz.to” as “Owner”
has
the right to “go to the pub and drink”

That would still present a “static” list that would not change ticket to
ticket.

The down side - is that as the number of users grows - you need 5 rows per
user…

But then again - ram is cheap (oh no - slap me - i just did a
Microsoft.)

A

[Harald_Wagener]

With rt 3.0.3 and MySQL 4.0.13, the query below runs for more than 10
minutes without returning. It is done when searching for more than one
requestor email address.

Anybody share some insight as how to speed this up?

Regards,
Harald

SELECT count(DISTINCT main.id)
FROM Tickets main, Groups Groups_1, Principals Principals_2,
CachedGroupMembers
CachedGroupMembers_3, Principals Principals_4, Users Users_5,
Groups Groups_6, Principals
Principals_7, CachedGroupMembers CachedGroupMembers_8, Principals
Principals_9, Users Users_10
WHERE ((main.EffectiveId = main.id)) AND ((main.Type = ‘ticket’)) AND
((((Users_5.EmailAddress
LIKE ‘%behle%’)AND(Groups_1.Domain =
‘RT::Ticket-Role’)AND(Groups_1.Type =
‘Requestor’)AND(Principals_2.PrincipalType = ‘Group’)) OR
((Users_10.EmailAddress LIKE
‘%wagener%’)AND(Groups_6.Domain =
‘RT::Ticket-Role’)AND(Groups_6.Type =
‘Requestor’)AND(Principals_7.PrincipalType = ‘Group’))) AND
((main.Owner = ‘10’)OR(main.Owner
= ‘330’)) AND ((main.Status = ‘new’)OR(main.Status = ‘open’)) AND
((main.Queue = ‘5’))) AND
Groups_1.Instance = main.id AND Groups_1.id = Principals_2.ObjectId
AND Principals_2.id =
CachedGroupMembers_3.GroupId AND CachedGroupMembers_3.MemberId =
Principals_4.id AND
Principals_4.ObjectId = Users_5.id AND Groups_6.Instance = main.id
AND Groups_6.id =
Principals_7.ObjectId AND Principals_7.id =
CachedGroupMembers_8.GroupId AND
CachedGroupMembers_8.MemberId = Principals_9.id AND
Principals_9.ObjectId = Users_10.id

[Dean_Kao]

Using this split query, we get access times down to less than 300ms. I’ve attached the local/lib/RT/Users_Local.pm, local/lib/RT/Groups_Local.pm and local/html/Elements/SelectOwner that we are using, I’m curious if this method improves access times with other database engines.

Verified that this has increased speeds tremendously on certain
queries. Setup is rt 3.0.3+mysql+fastcgi. We had the same
problems with RT locking up as well.

Dean

[Dean_Kao]

The following query has locked up the database for an hour.

Copying to tmp table | SELECT DISTINCT main.* FROM ( ( Tickets main LEFT
JOIN TicketCustomFieldValues as TicketCustomFieldValues_1 ON
((TicketCustomFieldValues_1.CustomField = ‘1’)) AND ( main.id =
TicketCustomFieldValues_1.Ticket)) LEFT JOIN TicketCustomFieldValues as
TicketCustomFieldValues_2 ON ((TicketCustomFieldValues_2.CustomField =
‘2’)) AND ( main.id = TicketCustomFieldValues_2.Ticket)) WHERE
((main.EffectiveId = main.id)) AND ((main.Type = ‘ticket’)) AND ( ( (
(TicketCustomFieldValues_1.Content LIKE ‘%%01Components%%’) ) OR (
(TicketCustomFieldValues_2.Content LIKE ‘%%023Store23%%’) ) ) AND (
(main.Status = ‘resolved’) ) ) ORDER BY main.id ASC LIMIT 50 |

Dean