Rt-2-1-85: mysql performance?

Carlos_Canau · March 13, 2003, 7:39pm

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

Jesse_Vincent · March 13, 2003, 8:52pm

Carlos,
What queries was it blocking on? What mysql are you running?
Does it have support for innodb tables? That sounds like an awful lot
of indices that you might need. Did you use mysql’s ‘EXPLAIN’
to see that your indices were all being used more efficiently?

Thanks,
jesseOn 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@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

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

Michael_K_Brown · March 13, 2003, 9:39pm

Sorry for the HTML mail… it was the easiest way to show the data.
We’re having similar performance issues with rt-2-1-88. The following
is a process list query in phpMyAdmin, after I had hit “Create a new
ticket in”. The create page only turns up after I kill the mysql
process below, or after it runs it’s course. It usually runs for more
than 200s. Below that is the imported info from rt2, into the rt3
database. rt2 is on the same server, and it’s lightening fast compared
to rt3. Is there any way to improve the performance?

New install of rt3, import of dumpfile via the 1.5 version of import
script. MySQL 3.23.45, perl 5.8, all dependencies satisfied.

/Mike

75843 rt_user localhost rt3 Query 196 Copying
to tmp table SELECT DISTINCT main.* FROM Users main, Principals
Principals_1, CachedGroupMembers CachedGroupMembe

Database
<http://labnetmgr1/phpMyAdmin/db_details.php?lang=en-iso-8859-1&convchar
set=iso-8859-1&server=2&db=rt3> rt3 running on labs

Table Records Type Size
ACL 64 MyISAM 8.9 KB
Attachments 2,238 MyISAM 3.7 MB
CachedGroupMembers 8,107 MyISAM 748.4 KB
CustomFieldValues 325 MyISAM 23.2 KB
CustomFields 21 MyISAM 4.4 KB
GroupMembers 2,651 MyISAM 93.7 KB
Groups 3,129 MyISAM 442.8 KB
Links 83 MyISAM 28.7 KB
Principals 3,453 MyISAM 130.4 KB
Queues 3 MyISAM 4.4 KB
ScripActions 15 MyISAM 3.7 KB
ScripConditions 9 MyISAM 2.9 KB
Scrips 22 MyISAM 6.6 KB
Templates 9 MyISAM 5.0 KB
TicketCustomFieldValues 2,532 MyISAM 146.5 KB
Tickets 695 MyISAM 149.7 KB
Transactions 8,005 MyISAM 628.2 KB
Users 323 MyISAM 97.1 KB
sessions 1 MyISAM 9.9 KB
19 table(s) 31,685 – 6.1 MB

Jesse_Vincent · March 13, 2003, 9:50pm

Is there any way to improve the performance?

New install of rt3, import of dumpfile via the 1.5 version of import
script. MySQL 3.23.45, perl 5.8, all dependencies satisfied.

Well, not exactly all dependencies. RT3 requires InnoDB tables if
you’re using mysql. You’re very clearly using MyISAM tables. There’s a
massive performance hit when you use MyIASM tables. And since they don’t
support row-level locking, one bad query will hurt everyone.

Table Records Type Size
ACL 64 MyISAM 8.9 KB
Attachments 2,238 MyISAM 3.7 MB
CachedGroupMembers 8,107 MyISAM 748.4 KB
CustomFieldValues 325 MyISAM 23.2 KB
CustomFields 21 MyISAM 4.4 KB
GroupMembers 2,651 MyISAM 93.7 KB
Groups 3,129 MyISAM 442.8 KB
Links 83 MyISAM 28.7 KB
Principals 3,453 MyISAM 130.4 KB
Queues 3 MyISAM 4.4 KB
ScripActions 15 MyISAM 3.7 KB
ScripConditions 9 MyISAM 2.9 KB
Scrips 22 MyISAM 6.6 KB
Templates 9 MyISAM 5.0 KB
TicketCustomFieldValues 2,532 MyISAM 146.5 KB
Tickets 695 MyISAM 149.7 KB
Transactions 8,005 MyISAM 628.2 KB
Users 323 MyISAM 97.1 KB
sessions 1 MyISAM 9.9 KB
19 table(s) 31,685 – 6.1 MB

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

Michael_K_Brown · March 13, 2003, 10:01pm

Well, I guess I’ll have to upgrade MySQL to support InnoDB tables then!
DOH!

Thanks for the help! Jesse, it’s a compliment to you and your work on
RT that I’m going through this much pain to upgrade to rt3! If you
have a minute, could you point out to us listers the best way to support
LDAP with rt3? That should probably be in the docs that get released
with 3.0.

Thanks again, and great job on rt3! It rocks!

/MikeFrom: Jesse Vincent [mailto:jesse@bestpractical.com]
Sent: Thursday, March 13, 2003 4:50 PM
To: Michael K. Brown
Cc: ‘Rt-Users’
Subject: Re: [rt-users] rt-2-1-85: mysql performance ?

Is there any way to improve the performance?

New install of rt3, import of dumpfile via the 1.5 version of import
script. MySQL 3.23.45, perl 5.8, all dependencies satisfied.

Well, not exactly all dependencies. RT3 requires InnoDB tables if
you’re using mysql. You’re very clearly using MyISAM tables. There’s a
massive performance hit when you use MyIASM tables. And since they don’t
support row-level locking, one bad query will hurt everyone.

Table Records Type Size
ACL 64 MyISAM 8.9 KB
Attachments 2,238 MyISAM 3.7 MB
CachedGroupMembers 8,107 MyISAM 748.4 KB
CustomFieldValues 325 MyISAM 23.2 KB
CustomFields 21 MyISAM 4.4 KB
GroupMembers 2,651 MyISAM 93.7 KB
Groups 3,129 MyISAM 442.8 KB
Links 83 MyISAM 28.7 KB
Principals 3,453 MyISAM 130.4 KB
Queues 3 MyISAM 4.4 KB
ScripActions 15 MyISAM 3.7 KB
ScripConditions 9 MyISAM 2.9 KB
Scrips 22 MyISAM 6.6 KB
Templates 9 MyISAM 5.0 KB
TicketCustomFieldValues 2,532 MyISAM 146.5 KB
Tickets 695 MyISAM 149.7 KB
Transactions 8,005 MyISAM 628.2 KB
Users 323 MyISAM 97.1 KB
sessions 1 MyISAM 9.9 KB
19 table(s) 31,685 – 6.1 MB

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

Carlos_Canau · March 13, 2003, 11:17pm

I’m sorry… I’m no SQL expert :-/

Carlos,
What queries was it blocking on?

Time: 030313 17:13:44

User@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

Carlos_Canau · March 14, 2003, 3:03pm

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,
</canauOn 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@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@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

–
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
rt-users mailing list
rt-users@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

Harald_Wagener · March 14, 2003, 3:07pm

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 ??

No! RedHat 8.0 is a rat’s nest for other problems (apache/perl/modperl
setup and versions don’t seem to play nice with RT3 at the moment. I
tend to blame RH for this these days…).

Regards,
Harald

Harald Wagener * FCB/Wilkens * An der Alster 42 * 20099 Hamburg

Rich_Lafferty · March 14, 2003, 3:56pm

No! RedHat 8.0 is a rat’s nest for other problems (apache/perl/modperl
setup and versions don’t seem to play nice with RT3 at the moment. I
tend to blame RH for this these days…).

Don’t blame Red Hat for using the wrong version. For RT3, I’m
pretty sure you want Apache 1.x and the stable mod_perl, not
Apache 2.x and the development mod_perl 1.99.

You should be able to install the RH7.3 Apache and mod_perl packages
on a RH8.0 system.

For the original poster, some ideas offhand:

Make sure you’re letting MySQL use sufficient resources. The default
RH /etc/my.cnf is pretty puny, IIRC – take a look at the examples
in /usr/share/mysql or /usr/share/doc/mysql-server-VERSION/. You
will probably want to base yours on the my-large.cnf example.

Also, check for the other MySQL basics – not doing full query logging,
not doing binlogging unless you need it, staying safely out of swap,
and so forth.

-Rich

Rich Lafferty --------------±----------------------------------------------
Ottawa, Ontario, Canada | Save the Pacific Northwest Tree Octopus!
http://www.lafferty.ca/ | Save The Pacific Northwest Tree Octopus
rich@lafferty.ca -----------±----------------------------------------------

Carlos_Canau · March 14, 2003, 4:08pm

Am Freitag, 14.03.03 um 16:03 Uhr schrieb 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.

Nopes… I was wrong. What I observe from “feeling” and looking into
the clock in the righthand of the screen:

Ticket browsing:

MyISAM (RH73-mysql-3.23.54a-3.73-default): +/- 10 sec
INNODB (RH73-mysql-3.23.54a-3.73): +/- 7 sec
INNODB + my INDEXes: +/- 5 sec

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 ??

No! RedHat 8.0 is a rat’s nest for other problems (apache/perl/modperl
setup and versions don’t seem to play nice with RT3 at the moment. I
tend to blame RH for this these days…).

Yikes!.. luckily RH will drop support for it on the end of the year
:->>>

regards,
</canau

Carlos_Canau · March 15, 2003, 10:09pm

Am Freitag, 14.03.03 um 16:03 Uhr schrieb 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.

Nopes… I was wrong. What I observe from “feeling” and looking into
the clock in the righthand of the screen:

Ticket browsing:

MyISAM (RH73-mysql-3.23.54a-3.73-default): +/- 10 sec
INNODB (RH73-mysql-3.23.54a-3.73): +/- 7 sec
INNODB + my INDEXes: +/- 5 sec

I’ve upgraded from 3.23.54a to MySQL 4.0.11 (binaries from
www.mysql.com): more or less 1-2 sec. improvement on ticket
browsing. I’m still on the 3-4 sec. per ticket change.

</canau