RT Database pruning before moving to new server

RT Users
1

Hi,

I am new to the RT lists as well as new to the administration of the RT
product. My company has used RT for many years but was maintained by
another admin.

I have been asked to upgrade our RT system from version 3.8.1 running on
CentOS 5.9 to version 4.2.4 running on CentOS 6.5.

Our current RT3 database is over 18 gigs and I was told that our admins
have never pruned or purged any of the old data. Are there any
utilities or scripts in RT that I can use to purge old data cleanly or
is all of that done directly through the database, ie mysql in my case?

Thanks,

Dave

2

I’m interested to hear from everyone too, as one of these days I hope to drop out some unnecessary attachments from the database.

One thing that I found in our environment is that the sessions table was absolutely huge and accounted for more than 80% of our database size.

You can use the sbin/rt-clean-sessions script to help prune that if you find that your sessions table is also very large.

If you are migrating to another database server, you can also leave out the sessions table.
For example, with MySQL:
mysqldump -u$RootDBuser -p$RootDBpassword -h$DBhost rt4 --ignore-table rt4.sessions > db_backup.sql
(or just truncate it after importing into the new server)

Hi,

I am new to the RT lists as well as new to the administration of the RT product. My company has used RT for many years but was maintained by another admin.

I have been asked to upgrade our RT system from version 3.8.1 running on CentOS 5.9 to version 4.2.4 running on CentOS 6.5.

Our current RT3 database is over 18 gigs and I was told that our admins have never pruned or purged any of the old data. Are there any utilities or scripts in RT that I can use to purge old data cleanly or is all of that done directly through the database, ie mysql in my case?

Thanks,

Dave
RT Training - Boston, September 9-10

3

I’m interested to hear from everyone too, as one of these days I hope to drop out some unnecessary attachments from the database.
One thing that I found in our environment is that the sessions table was absolutely huge and accounted for more than 80% of our database size.

Standard response:
http://bestpractical.com/docs/rt/latest/rt-shredder.html

You can use the sbin/rt-clean-sessions script to help prune that if you find that your sessions table is also very large.

This should be a daily cron job on every production RT server,
otherwise your session table just grows without bounds.

Our current RT3 database is over 18 gigs and I was told that our
admins have never pruned or purged any of the old data. Are there
any utilities or scripts in RT that I can use to purge old data
cleanly or is all of that done directly through the database, ie
mysql in my case?

Look at the shredder docs above.

Make sure you apply the documented indexes. You may find that
shredder performs better on 4.2 than on 3.8, test.

-kevin

4

On my current production system it looks like the majority of the space
is being taken up by the attachments table and then the sessions table.

| CONCAT(table_schema, ‘.’, table_name) | rows | DATA | idx |
total_size | idxfrac |
| rt3.Attachments | 3.91M | 14.32G | 0.10G |
14.42G | 0.01 |
| rt3.sessions | 0.16M | 2.26G | 0.01G |
2.27G | 0.00 |

So I guess I need a query to delete any attachments that I no longer
need. I am guessing it would be any attachments (and associated
resolved tickets) past a certain date.

Unfortunately we do not have a data retention policy in place so we have
at least 6 or 7 years worth of closed tickets in the database.On 6/5/2014 10:04 AM, Parish, Brent wrote:

I’m interested to hear from everyone too, as one of these days I hope to drop out some unnecessary attachments from the database.

One thing that I found in our environment is that the sessions table was absolutely huge and accounted for more than 80% of our database size.

You can use the sbin/rt-clean-sessions script to help prune that if you find that your sessions table is also very large.

If you are migrating to another database server, you can also leave out the sessions table.
For example, with MySQL:
mysqldump -u$RootDBuser -p$RootDBpassword -h$DBhost rt4 --ignore-table rt4.sessions > db_backup.sql
(or just truncate it after importing into the new server)

  • Brent

-----Original Message-----
From: rt-users [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Lists
Sent: Wednesday, June 04, 2014 8:21 PM
To: rt-users@lists.bestpractical.com
Subject: [rt-users] RT Database pruning before moving to new server

Hi,

I am new to the RT lists as well as new to the administration of the RT product. My company has used RT for many years but was maintained by another admin.

I have been asked to upgrade our RT system from version 3.8.1 running on CentOS 5.9 to version 4.2.4 running on CentOS 6.5.

Our current RT3 database is over 18 gigs and I was told that our admins have never pruned or purged any of the old data. Are there any utilities or scripts in RT that I can use to purge old data cleanly or is all of that done directly through the database, ie mysql in my case?

Thanks,

Dave

RT Training - Boston, September 9-10
http://bestpractical.com/training

5

Thanks Kevin, this may do the trick. Still being new to RT, I missed this.On 6/5/2014 11:27 AM, Kevin Falcone wrote:

I’m interested to hear from everyone too, as one of these days I hope to drop out some unnecessary attachments from the database.
One thing that I found in our environment is that the sessions table was absolutely huge and accounted for more than 80% of our database size.
Standard response:
rt-shredder - RT 5.0.5 Documentation - Best Practical

You can use the sbin/rt-clean-sessions script to help prune that if you find that your sessions table is also very large.
This should be a daily cron job on every production RT server,
otherwise your session table just grows without bounds.

Our current RT3 database is over 18 gigs and I was told that our
admins have never pruned or purged any of the old data. Are there
any utilities or scripts in RT that I can use to purge old data
cleanly or is all of that done directly through the database, ie
mysql in my case?
Look at the shredder docs above.

Make sure you apply the documented indexes. You may find that
shredder performs better on 4.2 than on 3.8, test.

-kevin