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