Upgrading a gigantic 3.4.4 database to 4.0?

Hi List,
Now that RT 4.0 is out, I’ve finally been granted resources to move forward from our existing RT 3.4.4 install. The challenge we’ve been having with that existing install is that it’s huge - over 17GB on disk, with on the order of 250,000 tickets in 71 active queues, and roughly a hojillion attachments.

At this point I’m working with a new server and a clone of the 3.4.4 database, trying to get through the RT 4.0 schema update steps, and the ‘ALTER TABLE Attachments…’ query is running for many hours and then dying, apparently unsuccessfully; I presume the issue is the number and size of blobs to be processed.

Do the fine folks of this list have any suggestions for how to move forward? I have been instructed that all of our ticket and transaction history must be preserved, so I can’t just arbitrarily shred old items. I had been considering the idea of splitting RT into several smaller instances by organizational unit - could I use rt-shredder on a clone of the 3.4 database to delete queues, tickets and attachments not required for a given instance, then upgrade to 4.0 from there on the smaller database? I am hopeful for any suggestions you might have.

Cheers,
Jeff Albert
Senior UNIX Systems Administrator
University of Victoria
jralbert@uvic.camailto:jralbert@uvic.ca

Now that RT 4.0 is out, I’ve finally been granted resources to move forward from our existing
RT 3.4.4 install. The challenge we’ve been having with that existing install is that it’s huge

  • over 17GB on disk, with on the order of 250,000 tickets in 71 active queues, and roughly a
    hojillion attachments.

I’ve successfully converted larger Attachments tables, so you should be fine.

At this point I’m working with a new server and a clone of the 3.4.4 database, trying to get
through the RT 4.0 schema update steps, and the `ALTER TABLE Attachments…’ query is running
for many hours and then dying, apparently unsuccessfully; I presume the issue is the number
and size of blobs to be processed.

You should be checking the mysql error logs to find out why it failed,
or running the output of upgrade-mysql-schema.pl through mysql -v to
get some more diagnostics.

Without knowing why it failed, it’s hard to offer suggestions.

-kevin

Hey Kevin,
Thanks for the quick reply. MySQL’s error log suggests it’s failing to allocate memory past about 2.5GB; I’ll work with our DBAs to see what we can do with MySQL in this area.

Generally I’m curious about the feasibility of continuing to run a single large instance of RT; presumably at some point the query times will just become untenable (bringing up the query builder in our existing 3.4 instance can take up to a minute even now). Would you recommend separating into smaller instances, or a different approach?

Cheers,
JeffFrom: rt-devel-bounces@lists.bestpractical.com [mailto:rt-devel-bounces@lists.bestpractical.com] On Behalf Of Kevin Falcone
Sent: Wednesday, May 04, 2011 1:54 PM
To: rt-devel@lists.bestpractical.com
Subject: Re: [Rt-devel] Upgrading a gigantic 3.4.4 database to 4.0?

Now that RT 4.0 is out, I’ve finally been granted resources to move forward from our existing
RT 3.4.4 install. The challenge we’ve been having with that existing install is that it’s huge

  • over 17GB on disk, with on the order of 250,000 tickets in 71 active queues, and roughly a
    hojillion attachments.

I’ve successfully converted larger Attachments tables, so you should be fine.

At this point I’m working with a new server and a clone of the 3.4.4 database, trying to get
through the RT 4.0 schema update steps, and the `ALTER TABLE Attachments…’ query is running
for many hours and then dying, apparently unsuccessfully; I presume the issue is the number
and size of blobs to be processed.

You should be checking the mysql error logs to find out why it failed, or running the output of upgrade-mysql-schema.pl through mysql -v to get some more diagnostics.

Without knowing why it failed, it’s hard to offer suggestions.

-kevin

Thanks for the quick reply. MySQL’s error log suggests it’s failing to
allocate memory past about 2.5GB; I’ll work with our DBAs to see what
we can do with MySQL in this area.

This is likely because you’re running a 32-bit kernel, and MySQL is
unable to access more memory because of that. Running a 64-bit kernel
will allow you to allocate more memory to MySQL.

Generally I’m curious about the feasibility of continuing to run a
single large instance of RT; presumably at some point the query times
will just become untenable (bringing up the query builder in our
existing 3.4 instance can take up to a minute even now). Would you
recommend separating into smaller instances, or a different approach?

There are existing RT instances with terabytes of data. Proper tuning,
indexing, and provisioning of your database will go a long way.

  • Alex

Lowering some mysql options helps as well. However, if you are going to use
this server for mysql and can give it more than 2gb then 64bits is your way.

Regards, Ruslan. From phone.

написал:> On Wed, 2011-05-04 at 14:06 -0700, Jeff Albert wrote:

Thanks for the quick reply. MySQL’s error log suggests it’s failing to
allocate memory past about 2.5GB; I’ll work with our DBAs to see what
we can do with MySQL in this area.

This is likely because you’re running a 32-bit kernel, and MySQL is
unable to access more memory because of that. Running a 64-bit kernel
will allow you to allocate more memory to MySQL.

Generally I’m curious about the feasibility of continuing to run a
single large instance of RT; presumably at some point the query times
will just become untenable (bringing up the query builder in our
existing 3.4 instance can take up to a minute even now). Would you
recommend separating into smaller instances, or a different approach?

There are existing RT instances with terabytes of data. Proper tuning,
indexing, and provisioning of your database will go a long way.

  • Alex

List info:
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

Jeff:

As suggested I would also recommend a 64-bit kernel, but I would also recommend you consider partitioning the Attachments table, table partitioning is available in mysql versions 5.x

Roy