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

[Carlos Canau]

I've  DROPped the  INDEXes  and  ALTERed all  tables  to type  INNODB.
Performance is  better but  it still takes  about 5 seconds  to change
between  tickets.

Should it be this way and  should I upgrade the hardware or can mysql,
innodb and rt3 be fine tuned to perform better ?

An upgrade from RH7.3 to RH8.0 would help ??

Anyone wants to share experiences ???

Best regards,
</canau


On Thu, Mar 13, 2003 at 11:17:17PM +0000, Carlos Canau wrote:
> 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.
> _______________________________________________
> 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