Hi Raed,<div>do you have some more details about your setup with partitioning of your mysql installation? </div><div><br></div><div>From which table size it makes sense?</div><div><br></div><div><br></div><div>Torsten<br><br>
<div class="gmail_quote">2010/4/19 Raed El-Hames <span dir="ltr"><<a href="mailto:rfh@vialtus.com">rfh@vialtus.com</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
Ronald;<br>
<br>
I don't think you stated what version of mysql you are using?<br>
If its less than version 5 then I would recommend upgrading , as far as I know more recent versions of mysql have better query optimisers.<br>
Once you are on version 5.x have a look at table partitioning, in our system the only big table is the Attachments table, 80G or so , and a year or so ago we suffered performance issues with tickets listing/displaying , one of the things we did to improve that was to partition the Attachment table by range based on id, and once we done that we noticed a massive improvement in performance.<br>
Partitioning will only benefit you if the active set of data is a percentage of the table, but with 1.2 million tickets I would have guessed the active tickets possibly 100000 or so ?; partitioning your Tickets/Attachments/Groups may help you.<br>
<br>
Have a look at <a href="http://dev.mysql.com/doc/refman/5.1/en/partitioning.html" target="_blank">http://dev.mysql.com/doc/refman/5.1/en/partitioning.html</a><br>
<br>
<br>
Hope it helps;<br>
<br>
Regards;<br>
Roy<div><div></div><div class="h5"><br>
<br>
<br>
<br>
<br>
ronald.higgins wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Greetings fellow list members.<br>
<br>
I'm hoping some more experienced members might be able to shed some light on<br>
some performance issues<br>
we have been having with Request Tracker 3.8.7, it really is terribly slow<br>
loading anything from the DB side without the Server itself being<br>
constrained for resources.<br>
<br>
The RT instance is running under VMWare VSphere (ESX4.0) with the following<br>
resources assigned<br>
<br>
8 vCPU's<br>
24GB RAM<br>
500GB disk on SAN (the SAN is idling so it's definately not disk I/O)<br>
<br>
O.S is Centos 5.4<br>
<br>
The database itself (ibdata1) is 213GB in size. The database stores a lot of<br>
images (faxes) sent from customers,<br>
hence the size of the DB. The Tickets table contains about 1.2 million<br>
records.<br>
<br>
Once logged into RT the (RT @ a Glance & queues takes about 10->15 seconds<br>
too load.<br>
Pages like Configuration loads instantaneously leading me to believe it's<br>
anything being queried out of the DB.<br>
<br>
So any guidance on InnoDB tweaks to try would be appreciated as well.<br>
<br>
##################<br>
#MySQL related Info#<br>
##################<br>
<br>
#######################################################################################<br>
<br>
mysql> show engine innodb status\G;<br>
*************************** 1. row ***************************<br>
Status:<br>
=====================================<br>
100419 11:04:18 INNODB MONITOR OUTPUT<br>
=====================================<br>
Per second averages calculated from the last 15 seconds<br>
----------<br>
SEMAPHORES<br>
----------<br>
OS WAIT ARRAY INFO: reservation count 11363775, signal count 4598538<br>
Mutex spin waits 0, rounds 1511018468, OS waits 3297606<br>
RW-shared spins 12329291, OS waits 6064081; RW-excl spins 7564941, OS waits<br>
1214997<br>
------------<br>
TRANSACTIONS<br>
------------<br>
Trx id counter 0 1347315994<br>
Purge done for trx's n:o < 0 1347314768 undo n:o < 0 0<br>
History list length 18<br>
Total number of lock structs in row lock hash table 0<br>
LIST OF TRANSACTIONS FOR EACH SESSION:<br>
---TRANSACTION 0 0, not started, process no 5371, OS thread id 1183050048<br>
MySQL thread id 924, query id 6013662 localhost root<br>
show engine innodb status<br>
---TRANSACTION 0 1347315694, not started, process no 5371, OS thread id<br>
1173416256<br>
MySQL thread id 923, query id 6012423 localhost rt_user<br>
---TRANSACTION 0 1347315808, not started, process no 5371, OS thread id<br>
1171409216<br>
MySQL thread id 921, query id 6013433 localhost rt_user<br>
---TRANSACTION 0 1347315807, not started, process no 5371, OS thread id<br>
1176828224<br>
MySQL thread id 920, query id 6013424 localhost rt_user<br>
---TRANSACTION 0 1347314770, not started, process no 5371, OS thread id<br>
1180440896<br>
MySQL thread id 919, query id 6008500 localhost rt_user<br>
---TRANSACTION 0 1347315776, not started, process no 5371, OS thread id<br>
1180641600<br>
MySQL thread id 918, query id 6013312 localhost rt_user<br>
---TRANSACTION 0 1347315924, not started, process no 5371, OS thread id<br>
1176426816<br>
MySQL thread id 917, query id 6013592 localhost rt_user<br>
---TRANSACTION 0 1347315841, not started, process no 5371, OS thread id<br>
1174018368<br>
MySQL thread id 916, query id 6013495 localhost rt_user<br>
---TRANSACTION 0 1347315301, not started, process no 5371, OS thread id<br>
1177631040<br>
MySQL thread id 914, query id 6008465 localhost rt_user<br>
---TRANSACTION 0 1347315993, not started, process no 5371, OS thread id<br>
1187264832<br>
MySQL thread id 913, query id 6013661 localhost rt_user<br>
---TRANSACTION 0 1347315752, not started, process no 5371, OS thread id<br>
1184254272<br>
MySQL thread id 840, query id 6013216 localhost rt_user<br>
---TRANSACTION 0 1347315768, not started, process no 5371, OS thread id<br>
1181043008<br>
MySQL thread id 834, query id 6013268 localhost rt_user<br>
---TRANSACTION 0 1347315684, not started, process no 5371, OS thread id<br>
1185659200<br>
MySQL thread id 830, query id 6012355 localhost rt_user<br>
---TRANSACTION 0 1347315775, not started, process no 5371, OS thread id<br>
1083808064<br>
MySQL thread id 813, query id 6013356 localhost rt_user<br>
---TRANSACTION 0 1347315773, not started, process no 5371, OS thread id<br>
1186261312<br>
MySQL thread id 811, query id 6013273 localhost rt_user<br>
---TRANSACTION 0 1347315723, not started, process no 5371, OS thread id<br>
1185458496<br>
MySQL thread id 807, query id 6013245 localhost rt_user<br>
---TRANSACTION 0 1347314764, not started, process no 5371, OS thread id<br>
1186462016<br>
MySQL thread id 806, query id 6008386 localhost rt_user<br>
---TRANSACTION 0 1347315541, not started, process no 5371, OS thread id<br>
1175423296<br>
MySQL thread id 802, query id 6013293 localhost rt_user<br>
---TRANSACTION 0 1347315790, not started, process no 5371, OS thread id<br>
1081203008<br>
MySQL thread id 754, query id 6013327 localhost rt_user<br>
---TRANSACTION 0 1347315801, ACTIVE 2 sec, process no 5371, OS thread id<br>
1079327040 starting index read, thread declared inside InnoDB 283<br>
mysql tables in use 4, locked 0<br>
MySQL thread id 841, query id 6013346 localhost rt_user Copying to tmp table<br>
SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_2 JOIN Principals<br>
Principals_1 ON ( Principals_1.id = <a href="http://main.id" target="_blank">main.id</a> ) JOIN CachedGroupMembers<br>
CachedGroupMembers_3 ON ( CachedGroupMembers_3.MemberId = Principals_1.id )<br>
WHERE (Principals_1.Disabled = '0') AND (ACL_2.PrincipalId =<br>
CachedGroupMembers_3.GroupId) AND (Principals_1.id != '1') AND<br>
(ACL_2.PrincipalType = 'Group') AND (Principals_1.PrincipalType = 'User')<br>
AND (ACL_2.RightName = 'OwnTicket') AND ((ACL_2.ObjectType = 'RT::Queue') OR<br>
(ACL_2.ObjectType = 'RT::System')) ORDER BY main.Name ASC<br>
Trx read view will not see trx with id >= 0 1347315802, sees < 0 1347315215<br>
---TRANSACTION 0 1347315215, ACTIVE 14 sec, process no 5371, OS thread id<br>
1096284480 starting index read, thread declared inside InnoDB 10<br>
mysql tables in use 3, locked 0<br>
MySQL thread id 912, query id 6007074 localhost rt_user Copying to tmp table<br>
SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN<br>
Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (<br>
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = <a href="http://main.id" target="_blank">main.id</a> ) JOIN<br>
CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId<br>
= Users_3.id ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE<br>
(Users_3.id = '1241833') AND (main.Status != 'deleted') AND ( (<br>
CachedGroupMembers_2.id IS NOT NULL ) AND ( main.Status = 'new' OR<br>
main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type =<br>
'ticket') AND (main.EffectiveId = <a href="http://main.id" target="_blank">main.id</a><br>
Trx read view will not see trx with id >= 0 1347315216, sees < 0 1347314360<br>
--------<br>
FILE I/O<br>
--------<br>
I/O thread 0 state: waiting for i/o request (insert buffer thread)<br>
I/O thread 1 state: waiting for i/o request (log thread)<br>
I/O thread 2 state: waiting for i/o request (read thread)<br>
I/O thread 3 state: waiting for i/o request (write thread)<br>
Pending normal aio reads: 0, aio writes: 0,<br>
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0<br>
Pending flushes (fsync) log: 0; buffer pool: 0<br>
2769582 OS file reads, 93462 OS file writes, 33046 OS fsyncs<br>
0.07 reads/s, 16384 avg bytes/read, 3.00 writes/s, 1.80 fsyncs/s<br>
-------------------------------------<br>
INSERT BUFFER AND ADAPTIVE HASH INDEX<br>
-------------------------------------<br>
Ibuf: size 1, free list len 5, seg size 7,<br>
825 inserts, 825 merged recs, 120 merges<br>
Hash table size 17700857, used cells 13967099, node heap has 40615 buffer(s)<br>
416299.25 hash searches/s, 127302.78 non-hash searches/s<br>
---<br>
LOG<br>
---<br>
Log sequence number 60 1215858005<br>
Log flushed up to 60 1215857995<br>
Last checkpoint at 60 1215430296<br>
0 pending log writes, 0 pending chkp writes<br>
27847 log i/o's done, 1.53 log i/o's/second<br>
----------------------<br>
BUFFER POOL AND MEMORY<br>
----------------------<br>
Total memory allocated 9551415920; in additional pool allocated 16775936<br>
Buffer pool size 524288<br>
Free buffers 1<br>
Database pages 483672<br>
Modified db pages 108<br>
Pending reads 0<br>
Pending writes: LRU 0, flush list 0, single page 0<br>
Pages read 9488089, created 12107, written 82568<br>
0.07 reads/s, 1.60 creates/s, 1.87 writes/s<br>
Buffer pool hit rate 1000 / 1000<br>
--------------<br>
ROW OPERATIONS<br>
--------------<br>
2 queries inside InnoDB, 0 queries in queue<br>
3 read views open inside InnoDB<br>
Main thread process no. 5371, id 1170004288, state: sleeping<br>
Number of rows inserted 41669, updated 32489, deleted 2390, read 9639755901<br>
4.20 inserts/s, 2.73 updates/s, 0.13 deletes/s, 973193.79 reads/s<br>
----------------------------<br>
END OF INNODB MONITOR OUTPUT<br>
============================<br>
<br>
1 row in set (0.11 sec)<br>
<br>
ERROR:<br>
No query specified<br>
<br>
#######################################################################################<br>
<br>
mysql> show indexes from Tickets;<br>
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<br>
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |<br>
Cardinality | Sub_part | Packed | Null | Index_type | Comment |<br>
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<br>
| Tickets | 0 | PRIMARY | 1 | id | A |<br>
1339471 | NULL | NULL | | BTREE | |<br>
| Tickets | 1 | Tickets1 | 1 | Queue | A |<br>
18 | NULL | NULL | | BTREE | |<br>
| Tickets | 1 | Tickets1 | 2 | Status | A |<br>
18 | NULL | NULL | YES | BTREE | |<br>
| Tickets | 1 | Tickets2 | 1 | Owner | A |<br>
18 | NULL | NULL | | BTREE | |<br>
| Tickets | 1 | Tickets6 | 1 | EffectiveId | A |<br>
1339471 | NULL | NULL | | BTREE | |<br>
| Tickets | 1 | Tickets6 | 2 | Type | A |<br>
1339471 | NULL | NULL | YES | BTREE | |<br>
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+<br>
<br>
<br>
And some sample queries out of the mysqld-slow log:<br>
<br>
# Query_time: 38 Lock_time: 0 Rows_sent: 10 Rows_examined: 1570956<br>
SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN<br>
Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (<br>
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = <a href="http://main.id" target="_blank">main.id</a> ) JOIN<br>
CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId<br>
= Users_3.id ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE<br>
(Users_3.id = '1241833') AND (main.Status != 'deleted') AND ( (<br>
CachedGroupMembers_2.id IS NOT NULL ) AND ( main.Status = 'new' OR<br>
main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type =<br>
'ticket') AND (main.EffectiveId = <a href="http://main.id" target="_blank">main.id</a>) ORDER BY main.Priority DESC<br>
LIMIT 10;<br>
# Time: 100419 11:06:04<br>
# User@Host: rt_user[rt_user] @ localhost []<br>
# Query_time: 10 Lock_time: 0 Rows_sent: 10 Rows_examined: 839782<br>
SELECT main.* FROM Tickets main WHERE (main.Status != 'deleted') AND<br>
(main.Owner = '10' AND ( main.Status = 'new' OR main.Status = 'open' ) )<br>
AND (main.Type = 'ticket') AND (main.EffectiveId = <a href="http://main.id" target="_blank">main.id</a>) ORDER BY<br>
main.Created DESC LIMIT 10;<br>
# Time: 100419 11:06:05<br>
# User@Host: rt_user[rt_user] @ localhost []<br>
# Query_time: 9 Lock_time: 0 Rows_sent: 1 Rows_examined: 839772<br>
SELECT count(<a href="http://main.id" target="_blank">main.id</a>) FROM Tickets main WHERE (main.Status != 'deleted')<br>
AND (main.Owner = '10' AND ( main.Status = 'new' OR main.Status = 'open' )<br>
) AND (main.Type = 'ticket') AND (main.EffectiveId = <a href="http://main.id" target="_blank">main.id</a>);<br>
# User@Host: rt_user[rt_user] @ localhost []<br>
# Query_time: 40 Lock_time: 0 Rows_sent: 10 Rows_examined: 1570956<br>
SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN<br>
Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (<br>
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = <a href="http://main.id" target="_blank">main.id</a> ) JOIN<br>
CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId<br>
= Users_3.id ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE<br>
(Users_3.id = '1241833') AND (main.Status != 'deleted') AND ( (<br>
CachedGroupMembers_2.id IS NOT NULL ) AND ( main.Status = 'new' OR<br>
main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type =<br>
'ticket') AND (main.EffectiveId = <a href="http://main.id" target="_blank">main.id</a>) ORDER BY main.Priority DESC<br>
LIMIT 10;<br>
# Time: 100419 11:06:06<br>
<br>
#######################################################################################<br>
<br>
#########<br>
#System#<br>
#########<br>
<br>
On the O.S side everything looks good, O.S is nippy:<br>
<br>
mpstat -P ALL<br>
Linux 2.6.18-164.15.1.el5 04/19/2010<br>
<br>
11:09:59 AM CPU %user %nice %sys %iowait %irq %soft %steal<br>
%idle intr/s<br>
11:09:59 AM all 8.99 0.01 1.36 1.32 0.06 0.29 0.00<br>
87.99 555.61<br>
11:09:59 AM 0 8.95 0.03 1.64 4.62 0.05 0.23 0.00<br>
84.48 138.93<br>
11:09:59 AM 1 8.89 0.00 1.07 0.50 0.00 0.02 0.00<br>
89.50 0.00<br>
11:09:59 AM 2 8.88 0.00 1.06 0.35 0.00 0.02 0.00<br>
89.69 0.00<br>
11:09:59 AM 3 9.01 0.00 1.06 0.19 0.00 0.02 0.00<br>
89.72 0.00<br>
11:09:59 AM 4 9.62 0.01 1.17 0.33 0.00 0.02 0.00<br>
88.85 0.00<br>
11:09:59 AM 5 9.25 0.00 1.08 0.33 0.00 0.02 0.00<br>
89.31 2.33<br>
11:09:59 AM 6 9.00 0.01 2.07 3.51 0.07 0.15 0.00<br>
85.19 22.36<br>
11:09:59 AM 7 8.28 0.00 1.73 0.70 0.32 1.79 0.00<br>
87.18 391.98<br>
<br>
<br>
iostat -x 10<br>
<br>
avg-cpu: %user %nice %system %iowait %steal %idle<br>
74.12 0.00 4.54 0.03 0.00 21.32<br>
<br>
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz<br>
avgqu-sz await svctm %util<br>
sda 0.00 249.85 1.10 47.75 8.79 2381.62 48.93<br>
0.27 5.58 1.06 5.19<br>
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00<br>
0.00 0.00 0.00 0.00<br>
sda2 0.00 249.85 1.10 47.75 8.79 2381.62 48.93<br>
0.27 5.58 1.06 5.19<br>
<br>
Best Regards<br>
<br>
Ronald Higgins<br>
--<br>
View this message in context: <a href="http://old.nabble.com/WebRT-3.8.7-Slow-Performance-tp28287648p28287648.html" target="_blank">http://old.nabble.com/WebRT-3.8.7-Slow-Performance-tp28287648p28287648.html</a><br>
Sent from the Request Tracker - User mailing list archive at Nabble.com.<br>
<br>
<br>
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.<br>
Buy a copy at <a href="http://rtbook.bestpractical.com" target="_blank">http://rtbook.bestpractical.com</a><br>
<br>
</blockquote>
<br>
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.<br>
Buy a copy at <a href="http://rtbook.bestpractical.com" target="_blank">http://rtbook.bestpractical.com</a><br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br>MFG<br><br>Torsten Brumm<br><br><a href="http://www.brumm.me">http://www.brumm.me</a><br><a href="http://www.elektrofeld.de">http://www.elektrofeld.de</a><br>
</div>