article_plain table management

[josegomes]

Hi, I'm currently managing an old otrs instance where my DB has grown a bit out of control. I have a article_plain table growing larger each day and I need solution, it's now around 147GB.

Top management has allowed me to delete all attachments older then 1 year, which I'm doing, so article_attachment is controled, however aticle_plain is not. Given that I don't care about attachments, but I still want to keep the texts, can I safely delete the contents of article_plain where create_time if less than 1 year ago and I would still see what people wrote in the tickets?

[skullz]

For article attachment, consider using ArticleStorageFS (File Storage based)..
This should be able to trim you database size..

https://doc.otrs.com/doc/manual/admin/6 ... rs-storage

Then, later if you didn't need it, let say more than 3 year old ticket (attachment), you can just transfer it out from otrs server to another one / delete..

This 3y > ticket will be in text/plain mode and no attachment.

[jojo]

I also suggest to have tha attachments on disc. This will also include the plain part which contains the full mail (headers and all mime parts).

You can delete the atachments then year by year or just move them to a cheaper and slower disc space

[josegomes]

I had seen people sugesting disc storage, but I would not like to follow that path for now as I don't have that much free space on the VM and the VM itself is on a physical machine who does not have much space left. Also I've read reports of converting large databases (remember I'm dealing with 147GB) can take a looot of time. Just quering the article_plain table for a single ticket can take me around 15 minutes.

I have made some tests on old test tickets and keeping in mind I had already deleted all entries on article_attachment for those tickes, I saw no diference after also deleting their entries for article_plain, so, I guess my question is, what's the point on keeping article_plain?

When I implemented the mechanism that deletes data from article_attachment I knew eventually I'd have to deal with article_plain and now I estimate I have 6 months of space left, as article_plain grows 4gb/month.

Bottom line, if deleting entries from article_plain would let me browsewhat people wrote on the tickets would be the easier solution for me (remember >1yr old tickets no longer hold any attachments)

Anyway, thanks for already replying so fast , on a Sunday!

[josegomes]

Ok, so now I really don't get the point of article_plain. I have created a new ticket, added a couple attachments and deleted all the entries on article_plain related to that ticket.

I can read the ticket fine and even the attachments are there (which makes sense as I did not touch article_attachment).

[jojo]

on a VM you alwas can attach additional "drives" for the disc space. Using the article storage on disc instead of database will keep the database small and fast...