[rt-users] WebRT 3.8.7 Slow Performance

ronald higgins ronald.higgins at gmail.com
Mon Apr 19 08:41:07 EDT 2010


Hi Ruslan & Raed/Roy,

Thanks very much for the feedback so far.

Yes, it is a 64bit installation and we are running MySQL Ver 5.0.77.

As requested see below indexes for CachedGroupMembers (apologies if
the format is whack):

mysql> show indexes from CachedGroupMembers;
+--------------------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table              | Non_unique | Key_name            | Seq_in_index
| Column_name       | Collation | Cardinality | Sub_part | Packed |
Null | Index_type | Comment |
+--------------------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| CachedGroupMembers |          0 | PRIMARY             |            1
| id                | A         |     7970018 |     NULL | NULL   |
  | BTREE      |         |
| CachedGroupMembers |          1 | DisGrouMem          |            1
| GroupId           | A         |     7970018 |     NULL | NULL   |
YES  | BTREE      |         |
| CachedGroupMembers |          1 | DisGrouMem          |            2
| MemberId          | A         |     7970018 |     NULL | NULL   |
YES  | BTREE      |         |
| CachedGroupMembers |          1 | DisGrouMem          |            3
| Disabled          | A         |     7970018 |     NULL | NULL   |
  | BTREE      |         |
| CachedGroupMembers |          1 | CachedGroupMembers3 |            1
| MemberId          | A         |     7970018 |     NULL | NULL   |
YES  | BTREE      |         |
| CachedGroupMembers |          1 | CachedGroupMembers3 |            2
| ImmediateParentId | A         |     7970018 |     NULL | NULL   |
YES  | BTREE      |         |
+--------------------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+

I'm going to have too read up on the innodb_buffer_pool_size before I
can comment any further.

I will definately look into partitioning the Tickets/Attachments
tables as you have suggested,

thanks :)

Ronald


On Mon, Apr 19, 2010 at 2:18 PM, Raed El-Hames <rfh at vialtus.com> wrote:
> 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