WebRT 3.8.7 Slow Performance

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

Hello Ronald,

  1. I suspect missing index on CachedGroupMembers table. Show indexes
    for that table.

  2. Also one of the following indexes on tickets would help with
    queries you showed:

  • (Status)
  • (Owner, Status)
  • (Status, Owner)

I don’t know which one you should use as I don’t know distribution of
Status and Owner columns, as well, I don’t know if there are more
queries with different signatures in the slow log.

  1. If this system is not 64bit then mysql may have problems allocating
    more than 2G when you have plenty of room.

  2. Major variable to tune for InnoDB is innodb_buffer_pool_size.
    However, read below.

Considering all of the above you should take the following steps:

  1. Improve performance of slow queries using indexes. For example you
    have queries that return “Rows_sent: 1”, but to do that look at
    “Rows_examined: 839772”, so this query pollute buffers with info from
    1M records to recieve only one.

  2. Once you have no slow queries in the log or ration between examined
    and sent records is small enough, at this moment you can move to
    tunning mysql’s options. You restart mysql server or flush stats,
    waits some time to gather fresh stats and uses mysqltuner.pl at first
    and then advance tutorials on optimizing mysql/InnoDB.On Mon, Apr 19, 2010 at 3:38 PM, ronald.higgins ronald.higgins@gmail.com 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.

Best regards, Ruslan.

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:

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 :slight_smile:

RonaldOn Mon, Apr 19, 2010 at 2:18 PM, Raed El-Hames rfh@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@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@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@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

Hi Raed,
do you have some more details about your setup with partitioning of your
mysql installation?

From which table size it makes sense?

Torsten2010/4/19 Raed El-Hames rfh@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 ) 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@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@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@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

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

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: