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@otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Jason B. Loven
Sent: Thursday, March 01, 2007
12:36 PM
To:
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,
Manager -
Technical Services Department
Computer Associates, Inc.
Phone: (401)232-2600, Fax: (401)232-7778
Email: jloven@cainetserv.com
Web: http://www.cainetserv.com/