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 <[email protected]> > 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 >> # u...@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 >> # u...@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); >> # u...@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
Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
