[rt-users] WebRT 3.8.7 Slow Performance
Raed El-Hames
rfh at vialtus.com
Mon Apr 19 09:17:35 EDT 2010
Torsten;
As per my previous mail the only table we partitioned is the Attachment
table (80G or so); our partitioning is a Range based on id (I think
400000 rows partitions). We could have instead done a Range based on
Created (dates basically).
I am not sure which table size partitioning would improve the
performance? possibly a mysql guru can give this advice, Obviously the
sooner you partition the better , because you will need to re-generate
the table, one thing to bare in mind though partitions can speed your
selects if the query require values within the defined range, selects
for values across multiple partitions can carry an overhead. We applied
it to just the Attachment table because we felt our hardware can cope
with the system growth with the exception of Attachments, and our
tickets tend to be small and resolved within a relatively short period.
I do not want to give the wrong advice, I would suggest you read the
mysql docs and see if it would useful and how for your environment.
Roy
Torsten Brumm wrote:
> Hi Raed,
> do you have some more details about your setup with partitioning of
> your mysql installation?
>
> From which table size it makes sense?
>
>
> Torsten
>
> 2010/4/19 Raed El-Hames <rfh at vialtus.com <mailto:rfh at vialtus.com>>
>
> 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 <http://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 <http://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 <http://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 <http://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 <http://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
> <http://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 <http://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
> <http://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 <http://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 <http://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
>
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>
>
>
>
> --
> MFG
>
> Torsten Brumm
>
> http://www.brumm.me
> http://www.elektrofeld.de
More information about the rt-users
mailing list