[rt-users] rt-2-1-85: mysql performance ?

[Carlos Canau]

I'm sorry... I'm no SQL expert :-/

On Thu, Mar 13, 2003 at 03:52:00PM -0500, Jesse Vincent wrote:
> 
> Carlos,
> 	What queries was it blocking on?

# Time: 030313 17:13:44
# User at Host: root[root] @ localhost []
# Query_time: 5  Lock_time: 0  Rows_sent: 0  Rows_examined: 313971
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 = '2108')AND(Groups_1.Domain = 'RT::Ticket-Role')AND(Groups_1.Type = 'Requestor')AND(Principals_2.PrincipalType = 'Group') )  ) AND ( (main.Status = 'open')OR(main.Status = 'new') ) )  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  ORDER BY main.Priority DESC LIMIT 25;


> What mysql are you running?

"plain" MySQL from RH 7.3 + all patches on update

> 	Does it have support for innodb tables?

:-) rh7.3 out of the box with mysql + patches :-)))) Then README from rt-2.1.85.

> That sounds like an awful lot
> 	of indices that you might need.

Yes... I ended thinking that the extra indices might slow down some more somewhere.

>  Did you use mysql's 'EXPLAIN'
> 	to see that your indices were all being used more efficiently?

No. Will have to read more about it... I've just tried to use the more
that I can "out-of-the-box" when  I've stumbled into the 10 sec. delay
between ticket browsing.

I'm upgrading from  rt2 in SPARC Solaris 7 to rt3  in x86 RH7.3... and
... it's slower with what would seem better hardware :-)

</canau

> 
> 	Thanks,
> 	jesse
> 
> On Thu, Mar 13, 2003 at 07:39:40PM +0000, Carlos Canau wrote:
> > 
> > Hi,
> > 
> > In my setup (*) I was waiting about 10 sec. to change from a ticket to
> > the next  on the  tickets menu. After  turning on  log-slow-queries in
> > mysqld I've tried the following indexes  which drop the wait time to 5
> > sec. (which is still ... a lot ??). Can it be further optimized ?
> > 
> > 
> > alter table Tickets add INDEX TypeINDEX(Type);
> > alter table Tickets add INDEX StatusINDEX(Status);
> > alter table Groups add INDEX DomainINDEX(Domain);
> > alter table Groups add INDEX InstanceINDEX(Instance);
> > alter table Groups add INDEX TypeINDEX(Type);
> > alter table Principals add INDEX PrincipalsINDEX(PrincipalType);
> > alter table CachedGroupMembers add INDEX GroupIdINDEX(GroupId);
> > alter table CachedGroupMembers add INDEX MemberIdINDEX(MemberId);
> > 
> > regards,
> > </canau
> > 
> > 
> > (*)
> > - rt-2-1-85
> > - 9000 tickets imported from rt2
> > - 2x450MHz Pentium II Compaq 3000 server
> > - RH 7.3 + last patches
> > _______________________________________________
> > rt-users mailing list
> > rt-users at lists.fsck.com
> > http://lists.fsck.com/mailman/listinfo/rt-users
> > 
> > Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm
> 
> -- 
> http://www.bestpractical.com/rt  -- Trouble Ticketing. Free.