Re: [rt-users] find time span between dates
Hi Steven, I have done this to measure the time (in days) between Created and Resolved by extending the ticket column_map this way: For 3.6.x place a file under local/html/Callbacks/MyCallbackname/Elements/RT__Ticket/ColumnMap/ColumnMap For 3.8.x this would be local/html/Callbacks/MyCallbackname/Elements/RT__Ticket/ColumnMap/Once with the following content: %ARGS $COLUMN_MAP = {} /%ARGS %INIT $COLUMN_MAP-{DaysCreatedToResolved} = { title = 'DaysCreatedToResolved', value = sub { my $Created = $_[0]-CreatedObj-SetToMidnight; my $Resolved = $_[0]-ResolvedObj-SetToMidnight; return '' unless ( $Resolved 0 ); return ' 1' if ( $Resolved == $Created ); return ($Resolved-$Created)/60/60/24; } } /%INIT Use it as a guide for your way ;-) Chris Am 16.04.2010 14:11, schrieb Steven Platt: Hi, Is there a way to report the difference between RT dates (Created Started), either in the standard or advanced search? I’m looking to generate a report to reflect how rapidly requests are addressed by our support staff. Many thanks Steve [RT v3.6.2] Dr Steven Platt Bioinformatician Statistics, Modelling and Bioinformatics Health Protection Agency Centre for Infections London ___www.hpa.org.uk/bioinformatics_ http://www.hpa.org.uk/bioinformatics * ** The information contained in the EMail and any attachments is confidential and intended solely and for the attention and use of the named addressee(s). It may not be disclosed to any other person without the express authority of the HPA, or the intended recipient, or both. If you are not the intended recipient, you must not disclose, copy, distribute or retain this message or any part of it. This footnote also confirms that this EMail has been swept for computer viruses, but please re-sweep any attachments before opening or saving. HTTP://www.HPA.org.uk ** * 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
[rt-users] force use index when constructing the sql from Tickets_Overlay
Hi; rt-3.8.7 mysql 5.1 The following query : 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 ) LEFT JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Users_3.id ) AND ( CachedGroupMembers_2 .GroupId = Groups_1.id ) WHERE (Users_3.EmailAddress LIKE '%blah.gov.uk%') AND (main.Status != 'deleted') AND ( ( main.Subject LIKE '%blah%' OR main.Subject LIKE '%blah CC%' OR main .Subject LIKE '%blah County%' OR ( CachedGroupMembers_2.id IS NOT NULL ) ) AND ( main.Created '2008-12-31 00:00:00' AND main.Created '2009-03-01 00:00:00' ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) ORDER BY main.id ASC LIMIT 100; Takes over 3 minutes to run (from sql client), the web interface takes ~ 4 to 5 minutes to come back, an explain show that its using index Groups2 (Type, Instance) , when joining the Groups table. However: SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN Groups Groups_1 *use index (Groups1)* ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND ( Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) LEFT JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Users_3.id ) AND ( CachedGroupMembers_2 .GroupId = Groups_1.id ) WHERE (Users_3.EmailAddress LIKE '%blah.gov.uk%') AND (main.Status != 'deleted') AND ( ( main.Subject LIKE '%blah%' OR main.Subject LIKE '%blah CC%' OR main .Subject LIKE '%blah County%' OR ( CachedGroupMembers_2.id IS NOT NULL ) ) AND ( main.Created '2008-12-31 00:00:00' AND main.Created '2009-03-01 00:00:00' ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) ORDER BY main.id ASC LIMIT 100; This returns the results in ~ 20 seconds. So the first question is; is there any why I can add the use index statement when the sql is constructed my guess some where in Tickets_Overlay::_WatcherLimit?? Second question if the answer to the first question is no ; then is there any other way I can optimise the original query ?? I can send the explain out put if required ; Regards; Roy Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Queues not showing/updating in QuickCreate box
When a queue is just created, it is not shown sometime. Try to log out (perhaps close your browser) and then log in. Cheers, Carlos Nick Irvine wrote: I've just changed the name of the General queue to Triage, but this is not reflected in the Quick Create box in RT at a Glance, nor in the New Ticket in [Combo box] at the top. Also, I can't select any other Queues in either of these. If I create a new ticket, it does in fact end up in the Triage Queue. Using 3.8 in from Ubuntu Karmic. -- ___ ___ | __ __ | Carlos García MontoroIngeniero Informático |_\_Y_/_| Instituto de Física Corpuscular Centro Mixto CSIC - UV |\_] [_/| Servicios Informáticos | [_] | Edificio Institutos de Investigacióncgar...@ific.uv.es |C S I C| Apartado de Correos 22085 E-46071 Valencia Tel: +34 963543706 |___| España / Spain Fax: +34 963543488 attachment: cgarcia.vcf Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
[rt-users] 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 )
Re: [rt-users] WebRT 3.8.7 Slow Performance
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. 3) If this system is not 64bit then mysql may have problems allocating more than 2G when you have plenty of room. 4) 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.higg...@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. Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] WebRT 3.8.7 Slow Performance
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 10 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
Re: [rt-users] WebRT 3.8.7 Slow Performance
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 r...@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 10 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
Re: [rt-users] TransactionBatch - AddCustomFieldValue
Thanks for your answers. Actually, I wanna change a custom field based in a change of another one. So, trying what Raed said, I wrote a Custom Condition to verify if it is a custom condition change. But I had the same problem.. When I update via Web interface, the Custom Field is changed twice, in TransactionCreate, and in Transactionbatch the scrip is not executed. Any ideas? 2010/4/16 Kenneth Crocker kfcroc...@lbl.gov Sergio, Your scrips have different conditions, so they are completely separate transaction. Look at any scrips that are triggered by the status changing to resolved and see what is happening to the Custom Fields AND check the stage to see if it is TransactionBatch. Hope this helps. Kenn LBNL On Fri, Apr 16, 2010 at 10:12 AM, Sergio Charpinel Jr. sergiocharpi...@gmail.com wrote: Yes, that was happening in TransactionCreate. So, I found people with similar problems that solved this using TransactionBatch, because the scrip is executed later. But, when I change status to resolved, the CustomField does not get changed (in TransactionCreate it was twice). I dont know why this is happening. 2010/4/16 Raed El-Hames r...@vialtus.com Are you creating the ticket from the web interface and that custom field exist in the form; Most likely the script is changing it to the value you want, then its changing again to the value given in the form while processing the form ?? Is that what is happening?? If its , I would suggest you change your scrip condition to Userdefined and look for Custom Field create or change something similar to http://wiki.bestpractical.com/view/OnCustomFieldValueChange Regards; Roy Sergio Charpinel Jr. wrote: Hi, I'm trying to change a CustomField value in OnCreate event. I cant use TransactionCreate stage, because when I resolve the ticket via Web Interface, it changes again my custom field value to what it was. So, I'm trying to use TransactionBatch stage, but the field is not changed. And the script is get executed. What am I doing wrong?? Thanks for any help. -- Sergio Roberto Charpinel Jr. -- Sergio Roberto Charpinel Jr. Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com -- Sergio Roberto Charpinel Jr. Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] WebRT 3.8.7 Slow Performance
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 40 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 r...@vialtus.com mailto:r...@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 10 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
Re: [rt-users] TransactionBatch - AddCustomFieldValue
Sergio Charpinel Jr. wrote: Thanks for your answers. Actually, I wanna change a custom field based in a change of another one. So, trying what Raed said, I wrote a Custom Condition to verify if it is a custom condition change. But I had the same problem.. When I update via Web interface, the Custom Field is changed twice, in TransactionCreate, and in Transactionbatch the scrip is not executed. Do you know why the (Transactionbatch) scrip is not executed ?? is there any perl errors ? have you checked the logs Roy Any ideas? Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Installing: make fixdeps can't find Calendar::Simple
I've been building RT 387 of late and use cpans Bundle::RT install using cpan. All this comes down and istalls OK (takes ages), but for the libgd library. To get round that problem I use the ./configure of:- ./configure --prefix=/usr/local/Rt/rt-3.8.7 \ --disable-graphviz \ --disable-gd \ --with-web-handler=modperl2 \ I have built RT into Sun's VirtualBox guesting FreeBSD_8, not that that should make any difference. Try Cpan, that got me over several problems I was having with the RT installing procedure(s). Then your problems re-appear when setting up apache - yet another hurdle. The internet is full of RT's this un that and all are old and almost useless for todays procedures. . Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Installing: make fixdeps can't find Calendar::Simple
I've been building RT 387 of late and use cpans Bundle::RT install using cpan. All this comes down and istalls OK (takes ages), but for the libgd library. To get round that problem I use the ./configure of:- ./configure --prefix=/usr/local/Rt/rt-3.8.7 \ --disable-graphviz \ --disable-gd \ --with-web-handler=modperl2 \ I have built RT into Sun's VirtualBox guesting FreeBSD_8, not that that should make any difference. Try Cpan, that got me over several problems I was having with the RT installing procedure(s). Then your problems re-appear when setting up apache - yet another hurdle. The internet is full of RT's this un that and all are old and almost useless for todays procedures. . Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
[rt-users] at_123 configure Makefile no good
The Asset Tracker 123 ./configure --prefix=/usr/local/at-1.2.3 does NOT create a good Makefile. The prefix is corrupt as I get:- sudo make Password: mkdir -p //opt/rt3/local/etc/AssetTracker/ cp etc/AssetTracker/AT_Config.pm //opt/rt3/local/etc/AssetTracker/AT_Config.pm [ -f //opt/rt3/local/etc/AssetTracker/AT_SiteConfig.pm ] || cp etc/AssetTracker/AT_SiteConfig.pm //opt/rt3/local/etc/AssetTracker/AT_SiteConfig.pm chgrp rt //opt/rt3/local/etc/AssetTracker/AT_Config.pm chgrp: rt: Invalid argument *** Error code 1 Stop in /usr/home/rt_dev/Rt/at-1.2.3. RT 387 running OK inf FreeBSD_8. Had a quick look round and changing Makefile to reflect the correct PREFIX, but just makes things worse. . Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
[rt-users] at_123 configure Makefile no good
The Asset Tracker 123 ./configure --prefix=/usr/local/at-1.2.3 does NOT create a good Makefile. The prefix is corrupt as I get:- sudo make Password: mkdir -p //opt/rt3/local/etc/AssetTracker/ cp etc/AssetTracker/AT_Config.pm //opt/rt3/local/etc/AssetTracker/AT_Config.pm [ -f //opt/rt3/local/etc/AssetTracker/AT_SiteConfig.pm ] || cp etc/AssetTracker/AT_SiteConfig.pm //opt/rt3/local/etc/AssetTracker/AT_SiteConfig.pm chgrp rt //opt/rt3/local/etc/AssetTracker/AT_Config.pm chgrp: rt: Invalid argument *** Error code 1 Stop in /usr/home/rt_dev/Rt/at-1.2.3. RT 387 running OK inf FreeBSD_8. Had a quick look round and changing Makefile to reflect the correct PREFIX, but just makes things worse. . Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] at_123 configure Makefile no good
Hi, try out the new at 1.24b1 from google code! Torsten - Originalnachricht - Kuehne + Nagel (AG Co.) KG, Geschaeftsleitung: Hans-Georg Brinkmann (Vors.), Dirk Blesius, Reiner Heiken, Bruno Mang, Alfred Manke, Christian Marnetté, Mark Reinhardt, Jens Wollesen, Rainer Wunn, Sitz: Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persoenlich haftende Gesellschaft: Kuehne Nagel A.G., Sitz: Contern/Luxemburg Geschaeftsfuehrender Verwaltungsrat: Klaus-Michael Kuehne Von: rt-users-boun...@lists.bestpractical.com rt-users-boun...@lists.bestpractical.com An: rt-users@lists.bestpractical.com rt-users@lists.bestpractical.com Gesendet: Mon Apr 19 16:58:15 2010 Betreff: [rt-users] at_123 configure Makefile no good The Asset Tracker 123 ./configure --prefix=/usr/local/at-1.2.3 does NOT create a good Makefile. The prefix is corrupt as I get:- sudo make Password: mkdir -p //opt/rt3/local/etc/AssetTracker/ cp etc/AssetTracker/AT_Config.pm //opt/rt3/local/etc/AssetTracker/AT_Config.pm [ -f //opt/rt3/local/etc/AssetTracker/AT_SiteConfig.pm ] || cp etc/AssetTracker/AT_SiteConfig.pm //opt/rt3/local/etc/AssetTracker/AT_SiteConfig.pm chgrp rt //opt/rt3/local/etc/AssetTracker/AT_Config.pm chgrp: rt: Invalid argument *** Error code 1 Stop in /usr/home/rt_dev/Rt/at-1.2.3. RT 387 running OK inf FreeBSD_8. Had a quick look round and changing Makefile to reflect the correct PREFIX, but just makes things worse. . 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
Re: [rt-users] at_123 configure Makefile no good
This looks like that often seen at the rear of a cow! The install has dropdb in it! Ummm - a naming problem ! Now - where 1.24b1 downloadable from ? Still getting horrible make install errors. I ONLY have the latest RTFM on top of RT387 though. Ian From: Brumm, Torsten / Kuehne + Nagel / Ham MI-ID torsten.br...@kuehne-nagel.com To: ipel...@yahoo.com; rt-users@lists.bestpractical.com Sent: Mon, 19 April, 2010 17:01:39 Subject: AW: [rt-users] at_123 configure Makefile no good Hi, try out the new at 1.24b1 from google code! Torsten - Originalnachricht - Von: rt-users-boun...@lists.bestpractical.com rt-users-boun...@lists.bestpractical.com An: rt-users@lists.bestpractical.com rt-users@lists.bestpractical.com Gesendet: Mon Apr 19 16:58:15 2010 Betreff: [rt-users] at_123 configure Makefile no good The Asset Tracker 123 ./configure --prefix=/usr/local/at-1.2.3 does NOT create a good Makefile. The prefix is corrupt as I get:- sudo make Password: mkdir -p //opt/rt3/local/etc/AssetTracker/ cp etc/AssetTracker/AT_Config.pm //opt/rt3/local/etc/AssetTracker/AT_Config.pm [ -f //opt/rt3/local/etc/AssetTracker/AT_SiteConfig.pm ] || cp etc/AssetTracker/AT_SiteConfig.pm //opt/rt3/local/etc/AssetTracker/AT_SiteConfig.pm chgrp rt //opt/rt3/local/etc/AssetTracker/AT_Config.pm chgrp: rt: Invalid argument *** Error code 1 Stop in /usr/home/rt_dev/Rt/at-1.2.3. RT 387 running OK inf FreeBSD_8. Had a quick look round and changing Makefile to reflect the correct PREFIX, but just makes things worse. . Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com Kühne + Nagel (AG Co.) KG, Geschäftsleitung: Hans-Georg Brinkmann (Vors.), Dirk Blesius, Reiner Heiken, Bruno Mang, Alfred Manke, Christian Marnetté, Mark Reinhardt, Jens Wollesen, Rainer Wunn, Sitz: Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persönlich haftende Gesellschaft: Kühne Nagel A.G., Sitz: Contern/Luxemburg, Geschäftsführender Verwaltungsrat: Klaus-Michael Kühne Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] at_123 configure Makefile no good
Search for asset tracker4rt inside google! The you grab lastest! Torsten Von: Ian Pellew ipel...@yahoo.com An: Brumm, Torsten / Kuehne + Nagel / Ham MI-ID Cc: RT List rt-users@lists.bestpractical.com Gesendet: Mon Apr 19 17:54:43 2010 Betreff: Re: AW: [rt-users] at_123 configure Makefile no good This looks like that often seen at the rear of a cow! The install has dropdb in it! Ummm - a naming problem ! Now - where 1.24b1 downloadable from ? Still getting horrible make install errors. I ONLY have the latest RTFM on top of RT387 though. Ian From: Brumm, Torsten / Kuehne + Nagel / Ham MI-ID torsten.br...@kuehne-nagel.com To: ipel...@yahoo.com; rt-users@lists.bestpractical.com Sent: Mon, 19 April, 2010 17:01:39 Subject: AW: [rt-users] at_123 configure Makefile no good Hi, try out the new at 1.24b1 from google code! Torsten - Originalnachricht - Von: rt-users-boun...@lists.bestpractical.com rt-users-boun...@lists.bestpractical.com An: rt-users@lists.bestpractical.com rt-users@lists.bestpractical.com Gesendet: Mon Apr 19 16:58:15 2010 Betreff: [rt-users] at_123 configure Makefile no good The Asset Tracker 123 ./configure --prefix=/usr/local/at-1.2.3 does NOT create a good Makefile. The prefix is corrupt as I get:- sudo make Password: mkdir -p //opt/rt3/local/etc/AssetTracker/ cp etc/AssetTracker/AT_Config.pm //opt/rt3/local/etc/AssetTracker/AT_Config.pm [ -f //opt/rt3/local/etc/AssetTracker/AT_SiteConfig.pm ] || cp etc/AssetTracker/AT_SiteConfig.pm //opt/rt3/local/etc/AssetTracker/AT_SiteConfig.pm chgrp rt //opt/rt3/local/etc/AssetTracker/AT_Config.pm chgrp: rt: Invalid argument *** Error code 1 Stop in /usr/home/rt_dev/Rt/at-1.2.3. RT 387 running OK inf FreeBSD_8. Had a quick look round and changing Makefile to reflect the correct PREFIX, but just makes things worse. . Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com http://rtbook.bestpractical.com/ Kühne + Nagel (AG Co.) KG, Geschäftsleitung: Hans-Georg Brinkmann (Vors.), Dirk Blesius, Reiner Heiken, Bruno Mang, Alfred Manke, Christian Marnetté, Mark Reinhardt, Jens Wollesen, Rainer Wunn, Sitz: Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persönlich haftende Gesellschaft: Kühne Nagel A.G., Sitz: Contern/Luxemburg, Geschäftsführender Verwaltungsrat: Klaus-Michael Kühne Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] TransactionBatch - AddCustomFieldValue
Sorry, It is getting executed, but the custom field is changing twice too. I need my scrip executing after the custom fields updates. Is this possible? Or is there another way to do this? 2010/4/19 Raed El-Hames r...@vialtus.com Sergio Charpinel Jr. wrote: Thanks for your answers. Actually, I wanna change a custom field based in a change of another one. So, trying what Raed said, I wrote a Custom Condition to verify if it is a custom condition change. But I had the same problem.. When I update via Web interface, the Custom Field is changed twice, in TransactionCreate, and in Transactionbatch the scrip is not executed. Do you know why the (Transactionbatch) scrip is not executed ?? is there any perl errors ? have you checked the logs Roy Any ideas? -- Sergio Roberto Charpinel Jr. Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] TransactionBatch - AddCustomFieldValue
Can you send us the scrip so we can look at for you, include your condition and action .. Also can you reiterate what you are trying to achieve , from your previous thread it seems to me you want a custom field to always have the same value, if this is the case then I would hide it from the form to begin with, which would be the easiest way of dealing with this. Regards; Roy Sergio Charpinel Jr. wrote: Sorry, It is getting executed, but the custom field is changing twice too. I need my scrip executing after the custom fields updates. Is this possible? Or is there another way to do this? 2010/4/19 Raed El-Hames r...@vialtus.com mailto:r...@vialtus.com Sergio Charpinel Jr. wrote: Thanks for your answers. Actually, I wanna change a custom field based in a change of another one. So, trying what Raed said, I wrote a Custom Condition to verify if it is a custom condition change. But I had the same problem.. When I update via Web interface, the Custom Field is changed twice, in TransactionCreate, and in Transactionbatch the scrip is not executed. Do you know why the (Transactionbatch) scrip is not executed ?? is there any perl errors ? have you checked the logs Roy Any ideas? -- Sergio Roberto Charpinel Jr. Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
[rt-users] Setting Priority via Create New Ticket In...
I've searched and found no previous thread on the topic. Before I start digging around in RT 3.8.7 files... Priority does not show up in our RT when using 'Create New Ticket In... and we'd like it to. Is something broken, or is it correct and we'll need to make local mods? Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Setting Priority via Create New Ticket In...
A. Thanks. On 4/19/2010 1:21 PM, Jeff Voskamp wrote: On 04/19/2010 01:12 PM, Jeff Blaine wrote: I've searched and found no previous thread on the topic. Before I start digging around in RT 3.8.7 files... Priority does not show up in our RT when using 'Create New Ticket In... and we'd like it to. Is something broken, or is it correct and we'll need to make local mods? Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com Take a good look for Show details in the upper right of the Create a new ticket page. Jeff Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
[rt-users] Large file uploads
Hi, I am hoping to use RT to help manage a printshop, but am having severe performance issues when I upload a 76M file. My hardware has 2 CPUs and 4GB of ram. The upload was ok, but when I try to look at the ticket, the system just locks up with almost 100% system cpu use and uses up all ram and starts in on swap. Has anyone else used RT with large files (100 - 200MB). Does RT's design allow for this or should I just set up links to an FTP site that can be accessed from the RT ticket? Thanks in advance. cheers, ski -- When we try to pick out anything by itself, we find it connected to the entire universeJohn Muir Chris Ski Kacoroski, ckacoro...@nsd.org, 206-501-9803 or ski98033 on most IM services Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] at_123 configure Makefile no good
Try this one. http://code.google.com/p/asset-tracker-4rt/ 2010/4/19 Ian Pellew ipel...@yahoo.com This looks like that often seen at the rear of a cow! The install has dropdb in it! Ummm - a naming problem ! Now - where 1.24b1 downloadable from ? Still getting horrible make install errors. I ONLY have the latest RTFM on top of RT387 though. Ian -- *From:* Brumm, Torsten / Kuehne + Nagel / Ham MI-ID torsten.br...@kuehne-nagel.com *To:* ipel...@yahoo.com; rt-users@lists.bestpractical.com *Sent:* Mon, 19 April, 2010 17:01:39 *Subject:* AW: [rt-users] at_123 configure Makefile no good Hi, try out the new at 1.24b1 from google code! Torsten - Originalnachricht - Von: rt-users-boun...@lists.bestpractical.com rt-users-boun...@lists.bestpractical.com An: rt-users@lists.bestpractical.com rt-users@lists.bestpractical.com Gesendet: Mon Apr 19 16:58:15 2010 Betreff: [rt-users] at_123 configure Makefile no good The Asset Tracker 123 ./configure --prefix=/usr/local/at-1.2.3 does NOT create a good Makefile. The prefix is corrupt as I get:- sudo make Password: mkdir -p //opt/rt3/local/etc/AssetTracker/ cp etc/AssetTracker/AT_Config.pm //opt/rt3/local/etc/AssetTracker/AT_Config.pm [ -f //opt/rt3/local/etc/AssetTracker/AT_SiteConfig.pm ] || cp etc/AssetTracker/AT_SiteConfig.pm //opt/rt3/local/etc/AssetTracker/AT_SiteConfig.pm chgrp rt //opt/rt3/local/etc/AssetTracker/AT_Config.pm chgrp: rt: Invalid argument *** Error code 1 Stop in /usr/home/rt_dev/Rt/at-1.2.3. RT 387 running OK inf FreeBSD_8. Had a quick look round and changing Makefile to reflect the correct PREFIX, but just makes things worse. . Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com Kühne + Nagel (AG Co.) KG, Geschäftsleitung: Hans-Georg Brinkmann (Vors.), Dirk Blesius, Reiner Heiken, Bruno Mang, Alfred Manke, Christian Marnetté, Mark Reinhardt, Jens Wollesen, Rainer Wunn, Sitz: Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persönlich haftende Gesellschaft: Kühne Nagel A.G., Sitz: Contern/Luxemburg, Geschäftsführender Verwaltungsrat: Klaus-Michael Kühne 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
Re: [rt-users] Trouble with CommandByMail
Hello Kenneth, As I understand you tested that disabling the plugin stops looping? On Fri, Apr 16, 2010 at 1:51 AM, Kenneth Crocker kfcroc...@lbl.gov wrote: To list, I have a bit of a problem. CommandByMail is working well with email from privileged users. However, when I send in an email from my yahoo account, it being non-LDAP and therefore doesn't exist in my USERS Table, it creates a never-ending mailer daemon loop and my yahoo account gets hundreds and hundreds of emails saying it couldn't get any user info on it. What I expected to happen was for RT to reject the email as unprivileged and an email notice stating that fact, but one-time only. Does anyone have a way around this problem? We definitely need to have CommanByMail but we don't want people to get endless error email either. Thanks. Kenn LBNL Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com -- Best regards, Ruslan. Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
[rt-users] Spreadsheet Custom Fields not Aligned.
Hi List, We just upgraded our RT system to 3.8.7 from 3.8.4. Now when we export a spreadsheet none of the custom fields are aligned with the proper field. Which is confusing to say the least. I was wondering if there something simple that we may have missed? Has anyone seen this before? I vaguely remember something on the list about this recently in the list, but searching for it has proved to be futile. Thanks, -- Bill Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com