Reducing the database size or trimming attachments
Our OTRS database has grown to over 1.5 GB. We are eager to trim it down without losing the tickets. Many of our tickets have very large attachments that we don't need anymore. We would like to delete the attachments on tickets that are more than 90 days old, but keep the tickets themselves. This change would allow our customers to respond to old tickets and allow us to see the history, without saving the unnecessary attachments. We have attempted to do this a couple of times, but each time we end up deleting the tickets or losing other data inside the tickets. Is there a prescribed way to complete this task? Is there another way to significantly reduce the database size without losing the data? Thank you for your help. Travis Murdock Cloudmark System details: OTRS 1.1.1 mySQL database
Hi Travis, On Wed, Sep 08, 2004 at 10:42:09AM -0700, Travis Murdock wrote:
Our OTRS database has grown to over 1.5 GB. We are eager to trim it down without losing the tickets. Many of our tickets have very large attachments that we don't need anymore. We would like to delete the attachments on tickets that are more than 90 days old, but keep the tickets themselves. This change would allow our customers to respond to old tickets and allow us to see the history, without saving the unnecessary attachments.
We have attempted to do this a couple of times, but each time we end up deleting the tickets or losing other data inside the tickets.
Is there a prescribed way to complete this task?
Is there another way to significantly reduce the database size without losing the data?
If you use the database backend for your attachments, the the attachments stored in article_attachment table. In this table is also a create_time column. So you can just delete attachments older then... by using SQL link "DELETE FROM article_attachment WHERE create_time ...". PS: In my opinion, it's always good to have the attachments longer. Maybe a few years. So I would use the filesystem backend, the the attachments are stored in the file system and normally the file system is big enough. Read more: http://doc.otrs.org/1.2/en/html/performance-tuning.html#PERFORMANCE-TUNING-O...
Travis Murdock
Cloudmark
Martin Edenhofer -- ((otrs.de)) :: OTRS GmbH :: Norsk-Data-Str. 1 :: 61352 Bad Homburg http://www.otrs.de/ :: Manage your communication!
On 8/9/04 7:06 pm, Martin Edenhofer
On Wed, Sep 08, 2004 at 10:42:09AM -0700, Travis Murdock wrote:
Is there another way to significantly reduce the database size without losing the data?
If you use the database backend for your attachments, the the attachments stored in article_attachment table. In this table is also a create_time column. So you can just delete attachments older then... by using SQL link "DELETE FROM article_attachment WHERE create_time ...".
Has anyone got a backend which keeps the entire message in an IMAP store? That would IMHO be quite a neat feature :-) Cheers, Chris
On Wed, Sep 08, 2004 at 07:22:08PM +0100, Chris Ridd wrote:
Is there another way to significantly reduce the database size without losing the data?
If you use the database backend for your attachments, the the attachments stored in article_attachment table. In this table is also a create_time column. So you can just delete attachments older then... by using SQL link "DELETE FROM article_attachment WHERE create_time ...".
Has anyone got a backend which keeps the entire message in an IMAP store? That would IMHO be quite a neat feature :-)
It would be a technical nice feature. But in this case a IMAP box with 1,5 GB isn't fine for the mail server. In my opinion. :) And a database/filesystem is faster then IMAP.
Cheers,
Chris
Martin Edenhofer -- ((otrs.de)) :: OTRS GmbH :: Norsk-Data-Str. 1 :: 61352 Bad Homburg http://www.otrs.de/ :: Manage your communication!
Martin Edenhofer wrote:
On Wed, Sep 08, 2004 at 07:22:08PM +0100, Chris Ridd wrote:
Is there another way to significantly reduce the database size without losing the data?
If you use the database backend for your attachments, the the attachments stored in article_attachment table. In this table is also a create_time column. So you can just delete attachments older then... by using SQL link "DELETE FROM article_attachment WHERE create_time ...".
Has anyone got a backend which keeps the entire message in an IMAP store? That would IMHO be quite a neat feature :-)
It would be a technical nice feature. But in this case a IMAP box with 1,5 GB isn't fine for the mail server. In my opinion. :)
And a database/filesystem is faster then IMAP.
If you've already got your messages in an IMAP store then it would make a lot of sense to keep them there. I only want one copy of 1.5GB of mail, thank you :-) The additional protocol overhead of an IMAP connection might be a concern, but might be similar to a DB connection if the store and DB are co-located. Is writing new ArticleStorage classes documented anywhere? The existing ArticleStorage classes don't seem to share a superclass or anything. Cheers, Chris
Hi Chris, On Thu, Sep 09, 2004 at 09:57:43AM +0100, Chris Ridd wrote:
It would be a technical nice feature. But in this case a IMAP box with 1,5 GB isn't fine for the mail server. In my opinion. :)
And a database/filesystem is faster then IMAP.
If you've already got your messages in an IMAP store then it would make a lot of sense to keep them there. I only want one copy of 1.5GB of mail, thank you :-)
The additional protocol overhead of an IMAP connection might be a concern, but might be similar to a DB connection if the store and DB are co-located.
Is writing new ArticleStorage classes documented anywhere? The existing ArticleStorage classes don't seem to share a superclass or anything.
Sure, there are classes. Kernel/System/Ticket/ArticleStorageDB.pm and Kernel/System/Ticket/ArticleStorageFS.pm And Kernel::System::Ticket documentation is availabe at http://dev.otrs.org/. OTRS Dev documentation is availabe at http://doc.otrs.org/1.2/en/html/dev.html and http://doc.otrs.org/1.2/en/html/dev-custom-modules.html.
Chris
Martin Edenhofer -- ((otrs.de)) :: OTRS GmbH :: Norsk-Data-Str. 1 :: 61352 Bad Homburg http://www.otrs.de/ :: Manage your communication!
participants (3)
-
Chris Ridd -
Martin Edenhofer -
Travis Murdock