Warning regarding Generic Agent jobs (was RE: [otrs] Ticket history table size revisited)

[Jason B. Loven]

Following on to this issue I have looked a little deeper and I've found
that my ticket_history table contains ~57,000,000 records. Bear in mind
we have about 9900 (not 9400) tickets in the database. This seems a
little large to me in terms of ticket history lines to tickets.

 

After running a few queries against the database and taking a closer
look at what was going on I figured out what happened. I had configured
a couple of generic agent jobs that basically made sure tickets from
certain places ended up with the correct customer code. The problem was
the selection criteria wasn't specific enough and basically kept
repeatedly changing the tickets back to the same customer ID even if it
was already set correctly. This created a ticket_history entry for each
time the generic agent touched the ticket. Result: HUGE ticket_history
on many tickets. On the order of 21000+ records on some tickets. Ugh.
Bad thing. Sooo..I'm looking at the database now and I think I'm going
to simply try to prune any ticket history past some arbitrary count such
as 50. At least that'll maintain the history on the tickets I have and
get the database back down to a manageable size.

 

Live and learn. 

 

________________________________

From: otrs-bounces at otrs.org [mailto:otrs-bounces at otrs.org] On Behalf Of
Jason B. Loven
Sent: Thursday, March 01, 2007 12:36 PM
To: User questions and discussions about OTRS.org
Subject: [otrs] Ticket history table size revisited

 

I had mentioned a while back that I had hit a limit in MySQL with the
tablesize on my ticket_history table. I was able to get around it by
adjusting the table parameters. More of a concern is that the table hit
the limit at all. We process about 20-30 tickets a day and have been
using the system for just about a year. Certainly not heavy users. 

 

Is it reasonable to expect the ticket_history table to be this big for
approximately 9400 tickets? 

 

Is there a way to look deeper and determine if there's some attachments
or something like that which could be causing the table to be unusually
large?

 

Ticket_history data is as follows...

 

 

mysql> show table status like 'ticket_history'\G

*************************** 1. row ***************************

           Name: ticket_history

         Engine: MyISAM

        Version: 10

     Row_format: Dynamic

           Rows: 57025570

 Avg_row_length: 104

    Data_length: 5969466052

Max_data_length: 1099511627775

   Index_length: 6723551232

      Data_free: 0

 Auto_increment: 57044889

    Create_time: 2007-02-07 08:42:37

    Update_time: 2007-03-01 12:24:52

     Check_time: 2007-02-07 10:14:18

      Collation: latin1_swedish_ci

       Checksum: NULL

 Create_options: max_rows=4294967295 avg_row_length=106

        Comment:

1 row in set (0.00 sec)

 

 

 

 

Thank you,
Jason Loven
Manager - Technical Services Department

Computer Associates, Inc.
36 Thurber Blvd, Smithfield RI 02917
Phone: (401)232-2600, Fax: (401)232-7778
Email: jloven at cainetserv.com
Web: http://www.cainetserv.com/

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.otrs.org/pipermail/otrs/attachments/20070301/94221c22/attachment.html