RT3 Speed Info

Okay … lets play spot the difference:

Table RT2 Data Rows: RT3 Imported Data Rows:

GroupMembers 35 116012
Groups 15 171333

And this was created with the import tool … so this could well be
where the problem lies ??

Hopefully Jesse can provide a nod in the right direction here ? I don’t
know, maybe RT3 needs 100’s of thousands of groups ? But I doubt it ;-).

Cheers,

Simon.

Okay … lets play spot the difference:

Table RT2 Data Rows: RT3 Imported Data Rows:

GroupMembers 35 116012
Groups 15 171333

And this was created with the import tool … so this could well be
where the problem lies ??

RT’s data model changed somewhat between RT2 and RT3. The 'watchers’
system in RT2 was replaced with a more flexible groups system in rt3.
Most of those groups and group members are actually queue and ticket
roles. It might be possible to re-cast the code to lazy-create them, but
unless someone can show me proof that this is what’s killing some folks
performance, I’m not motivated to make the change.

j

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

Hi Jesse,

RT’s data model changed somewhat between RT2 and RT3. The 'watchers’
system in RT2 was replaced with a more flexible groups system in rt3.
Most of those groups and group members are actually queue and ticket
roles. It might be possible to re-cast the code to lazy-create them, but
unless someone can show me proof that this is what’s killing some folks
performance, I’m not motivated to make the change.

If thats how its meant to be, thats fine. I’m just trying to go through
everything I can find thats different or stands out as being a potential
cause of the huge problems we are experiencing.

The main problem is that I’m getting close to a point now where I can’t keep
justifying using RT3, I’ve been pouring through code / mysql docs and everything
for the last few days, and all I am getting from staff “is can’t we go back to
the old one, because that worked”, and its not going to be long before I
get ordered to do it, so I’m basically running out of time.

The system is just that slow.

Cheers,

Simon.

Hi Jesse,

The main problem is that I’m getting close to a point now where I can’t keep
justifying using RT3, I’ve been pouring through code / mysql docs and everything
for the last few days, and all I am getting from staff “is can’t we go back to
the old one, because that worked”, and its not going to be long before I
get ordered to do it, so I’m basically running out of time.

Understood. I’m sorry that you’re having performance issues.
Unfortunately, I’m on holiday in Europe and have already been spending
way too much time in netcafes with rt-related stuff. If you’re having
issues you can’t resolve, the community support isn’t enough to make it
go and for whatever reason, commercial support isn’t an option,
you’re probably far better off running something that will make your
coworkers happy in production and debugging the issue on a test server
until you’re satisfied that you’ve resolved things.

Jesse

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

Hi Jesse,

Understood. I’m sorry that you’re having performance issues.
Unfortunately, I’m on holiday in Europe and have already been spending
way too much time in netcafes with rt-related stuff. If you’re having
issues you can’t resolve, the community support isn’t enough to make it
go and for whatever reason, commercial support isn’t an option,
you’re probably far better off running something that will make your
coworkers happy in production and debugging the issue on a test server
until you’re satisfied that you’ve resolved things.

Also understood. Everyone needs there holidays :slight_smile:

… but since you’re over here … (grin).

Seriously, yeah, I am aware of my options, its just venting ;-). Trouble is
I wish I had the time to go through and debug RT, if only that was all I had
to worry about, but try explaining that to manglement types !!

Ho Hum. Cheers anyways.

I take it from the sheer silence on the list that no-one else has any thoughts /
ideas or input. I’d greatly appreciate any if you have, otherwise I think its
downgrade and wait until the time is available to try again or test.

Cheers,

Simon.

Okay … lets play spot the difference:

Table RT2 Data Rows: RT3 Imported Data Rows:

GroupMembers 35 116012
Groups 15 171333

And this was created with the import tool … so this could well be
where the problem lies ??

Well, if you’ve got a spare box to try it on, I’d suggest knocking up an rt3
install from scratch, not importing your old data and creating a bunch of
users/tickets. From that I’d suggest that you could judge the raw performance
of rt2 vs rt3 and judge whether or not its

  1. just that rt3 is slow
  2. the speeds are comparable but it slows after you migrate the data.
  3. rt2 handles the number of tickets you’ve got better than rt3 does (do this
    by restoring/migrating a certain number of tickets at a time)

Of course if you stick a basic rt2 installation on the same box then the
comparison becomes much easier. I guess there’ll be some point when the speed
difference becomes apparant.

cheers
john

Jesse Vincent wrote:

Table RT2 Data Rows: RT3 Imported Data Rows:

GroupMembers 35 116012
Groups 15 171333

FWIW, I’ve just embarked on a 2.0->3.0 upgrade, and:

Table RT2 Data Rows: RT3 Imported Data Rows:

GroupMembers 210 87718
Groups 25 96932
CachedGroupMembers 259639

(~20k tickets, 9k users)

RT’s data model changed somewhat between RT2 and RT3. The 'watchers’
system in RT2 was replaced with a more flexible groups system in rt3.
Most of those groups and group members are actually queue and ticket
roles. It might be possible to re-cast the code to lazy-create them, but
unless someone can show me proof that this is what’s killing some folks
performance, I’m not motivated to make the change.

I have no proof as yet, but with the above dataset I’m seeing the
same kind of performance issues as have been reported here.
There is no one query that bottoms out on this; however I do see
several (20-30) seconds of nothing but ACL queries[0] every time I
visit certain pages (the “home” page, and “ModifyAll” page are good
indicators here.) Each query completes in under a second, however
when you add 56 of those queries together, the response time does
blow out – around a minute for the homepage, and half that for a
ModifyAll (jumbo) page display.

[0] “SELECT ACL.id from ACL, Groups, Principals, CachedGroupMembers …”

Phil Homewood, Systems Janitor, www.SnapGear.com
pdh@snapgear.com Ph: +61 7 3435 2810 Fx: +61 7 3891 3630
SnapGear - Custom Embedded Solutions and Security Appliances

Hi All,

I’ve been getting MySQL to log “slow queries” for the last 24 hours now
and typically its queries such as:

SELECT DISTINCT main.* FROM Tickets main, Groups Groups_1, Principals
Principals_2, CachedGroupMembers CachedGroupMembers_3, Principals
Principals_4, Users Users_5 WHERE ((main.EffectiveId = main.id)) AND
((main.Type = ‘ticket’)) AND ( ( ( (Users_5.EmailAddress = ‘’)AND(Groups_1.Domain = ‘RT::Ticket-Role’)AND(Groups_1.Type =
‘Requestor’)AND(Principals_2.PrincipalType = ‘Group’) ) ) AND (
(main.Status = ‘new’)OR(main.Status = ‘open’) ) ) AND Groups
_1.Instance = main.id AND Groups_1.id = Principals_2.ObjectId AND
Principals_2.id = CachedGroupMembers_3.GroupId AND
CachedGroupMembers_3.MemberId = Principa ls_4.id AND
Principals_4.ObjectId = Users_5.id ORDER BY main.Priority DESC LIMIT
10;

And also:

SELECT DISTINCT main.* FROM Users main, Principals Principals_1, Groups
Groups_2, Principals Principals_3, Principals Principals_4, ACL ACL_5,
CachedGroupMembers CachedGroupMembers_6, CachedGroupMembers
CachedGroupMembers_7 WHERE ((ACL_5.RightName =
‘SuperUser’)OR(ACL_5.RightName = ‘OwnTicket’)) AND ((CachedGroupMem
bers_7.GroupId = ‘4’)) AND ((Principals_1.Disabled = ‘0’)) AND
((Principals_1.PrincipalType = ‘User’)) AND ((Principals_3.PrincipalType
= ‘User’)) AND (ACL_5.ObjectType = ‘RT::System’ OR (ACL_5.ObjectType =
‘RT::Queue’ AND ACL_5.ObjectId= ‘18’) ) AND ( (ACL_5.PrincipalId =
Principals_4.Id AND Principals_4.id = Groups_2.Id AND
ACL_5.PrincipalType = ‘Group’ AND (Groups_2.Domain = 'SystemInternal’
OR Groups_2.Domain = ‘UserDefined’ OR Groups_2.Domain
=‘ACLEquivalence’)) OR( ( (Groups_2.Domain = ‘RT::Queue-Role’ AND
Groups_2.Instance = ‘18’) OR ( Groups_2.Domain = ‘RT::Ticket-Role’ AND
Groups_2.Instance = ‘39574’) ) AND Groups_2.Type = ACL_5.PrincipalType
AND Groups_2.Id = Principals_4.id AND Principals_4.PrincipalType =
‘Group’) ) AND main.id = Principals_1.id AND Principals_1.id =
CachedGroupMembers_7.MemberId AND main.id = Principals_3.id AND
CachedGroupMembers_6.MemberId = Principals_3.Id
ANDCachedGroupMembers_6.GroupId = Principals_4.Id ORDER BY main.Name
ASC;

Which seem to crop up quite a lot. All with Query times ranging from
13-26 seconds a time.

However that is nothing in comparison to the Apache::Session module
who’s SELECT GET_LOCK(‘Apache-Session-<session’, 3600); requests appear
to take on average 20-90 seconds, and I have even seen one at 1185, but
I’ll class that as an anomaly.

However hope this helps with the tracking … I’d be interested to see
if other people seeing these speed problems are noticing a similar
problem.

Cheers,

Simon.

Hi,

Should have mentioned …

I am also seeing similar amounts of ACL queries to Phil, however as there
aren’t being logged by the slow queries log, I wasn’t convinced they were
the problem.

Cheers,

Simon.

Hi All,

Scrub all that. I’ve been watching the data in real time and I think the
slow queries are red herrings.

I am definitely of the opinion now that Phil is right. It appears to be
the long list of ACL[0] lookups that are causing the delay. Watching the
MySQL log file you can tell when someone has clicked a link and can say
when the page will come up, just by watching the queries. The Slow
queries still exist, but are no where near common enough to be the main
cause of this problem.

I’ll see where this now gets me.

Cheers,

Simon.

[0] Queries just as Phil described.

hi list,
hi simon,

I take it from the sheer silence on the list that no-one else has any

thoughts /

ideas or input. I’d greatly appreciate any if you have, otherwise I think

its

downgrade and wait until the time is available to try again or test.

my silence is caused by my boss, wanting me to dig into things that make us
money, not into things that make us trouble, which rt3 definitly does.

therefore i had no time to investigate what causes the performance problems
in our rt3 install, but i definetly will. probably in my holidays…

since rt2 is still a GREAT tool (not able to thank jesse and coworkers
enough for it) which improved our customer support and
internal-communication a lot, we will stick to it untill the rt3 performace
issue is solved.

jesse:
stay away from open netcafes.
this is supposed to be your holiday :wink:

cu
andreas

Andreas Wahlfeldt
subshell GmbH
Weidenallee 1
20357 Hamburg

t +49.40. 431 362-25
f +49.40. 431 362-29
e awahlfeldt@subshell.com

since rt2 is still a GREAT tool (not able to thank jesse and coworkers
enough for it)

I couldn’t agree with this more.

I think I shall have to join you in returning to RT2 over the weekend, to get
the office working again on Monday and leave RT3 until I have the time to look
at this further.

To be honest I have been spending far too much time on this, and as Andreas
says, I should also be concentrating on other things too. I just don’t like
being beaten, even if its only temporary :wink:

Cheers,

Simon.

Okay … lets play spot the difference:

Table RT2 Data Rows: RT3
Imported Data Rows:

GroupMembers 35 116012
Groups 15 171333

And this was created with the import tool … so this could well be
where the problem lies ??

RT’s data model changed somewhat between RT2 and RT3. The 'watchers’
system in RT2 was replaced with a more flexible groups system in rt3.
Most of those groups and group members are actually queue and ticket
roles. It might be possible to re-cast the code to lazy-create them, but
unless someone can show me proof that this is what’s killing some folks
performance, I’m not motivated to make the change.

Would manually dropping watchers/groups/etc either in an RT2 replica or
directly in the dump files and rebuilding them directly in RT3 avoid this
problem?

Thanks,
Christian

Christian Gilmore
Technology Leader
GeT Support Application Development
IBM Software Group

RT’s data model changed somewhat between RT2 and RT3. The 'watchers’
system in RT2 was replaced with a more flexible groups system in rt3.
Most of those groups and group members are actually queue and ticket
roles. It might be possible to re-cast the code to lazy-create them, but
unless someone can show me proof that this is what’s killing some folks
performance, I’m not motivated to make the change.

Would manually dropping watchers/groups/etc either in an RT2 replica or
directly in the dump files and rebuilding them directly in RT3 avoid this
problem?

I don’t think so. That wouldn’t change anything at all. That should
essentially be what the import tool does.

Thanks,
Christian


Christian Gilmore
Technology Leader
GeT Support Application Development
IBM Software Group

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