DB Problems upgrading to RT 3.8.2 from 3.6.5

Hello,

I have a (happily) running RT 3.6.5 with approx 300,000 tickets in it and a database of size of around 9 Gig (lots of attachments sadly). I have the application upgraded, but when I try and run the schema upgrade in my test environment it is taking forever (for values of forever which equate to around 6 days anyway):

perl etc/upgrade/schema.mysql-4.0-4.1.pl rt3 root "password > ~/sql.queries

time mysql -p rt3 < ~/sql.queries

Enter password:

real 8727m23.622s
user 0m0.012s
sys 0m0.004s

Does anyone have any suggestions as to how I can make this faster? I have tried to optimise MySQL as much as possible, but I am not a db expert and obviously there is something wrong. Nb. The test environment is actually more powerful than the production one after having shutdown evey VM on the machine and ensured I was only running this for the time it took.
Cheers,
David

Hi Dave,

MySQL has to convert each one of those attachments to the new datatype.
Sadly this just takes time. Our DB is only miniscule compared to yours and
from memory it takes around an 1hour just to get through all of our
attachments.

I think it is highly likely that this could be normal due to the amount of
data it has to manipulate. But as it said in the docs you need to test
everything. especially those binary attachments namely .pdf,.xls etc. I
have some difficulty with my database for some reason or another where in
doing the schema upgrade it proceeds to corrupt them all…

Regards

AaronOn Mon, Mar 2, 2009 at 2:16 PM, David Hobley david.hobley@mionegroup.comwrote:

Hello,

I have a (happily) running RT 3.6.5 with approx 300,000 tickets in it and a
database of size of around 9 Gig (lots of attachments sadly). I have the
application upgraded, but when I try and run the schema upgrade in my test
environment it is taking forever (for values of forever which equate to
around 6 days anyway):

perl etc/upgrade/schema.mysql-4.0-4.1.pl rt3 root "password >

~/sql.queries

time mysql -p rt3 < ~/sql.queries

Enter password:

real 8727m23.622s
user 0m0.012s
sys 0m0.004s

Does anyone have any suggestions as to how I can make this faster? I have
tried to optimise MySQL as much as possible, but I am not a db expert and
obviously there is something wrong. Nb. The test environment is actually
more powerful than the production one after having shutdown evey VM on the
machine and ensured I was only running this for the time it took.

Cheers,
David


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

If that is the case, then RT is un-upgradeable. Which would be a problem obviously. I can’t take out our database for 6 days. Over a weekend, sure. But no more than that. Does anyone have any suggestions as to how I can optimise this at all?

Cheers,
David----- Original Message -----
From: “Aaron Guise” aaron@guise.net.nz
To: “David Hobley” david.hobley@mionegroup.com
Cc: rt-users@lists.bestpractical.com
Sent: Monday, 2 March, 2009 8:54:58 PM GMT +10:00 Brisbane
Subject: Re: [rt-users] DB Problems upgrading to RT 3.8.2 from 3.6.5

Hi Dave,

MySQL has to convert each one of those attachments to the new datatype. Sadly this just takes time. Our DB is only miniscule compared to yours and from memory it takes around an 1hour just to get through all of our attachments.

I think it is highly likely that this could be normal due to the amount of data it has to manipulate. But as it said in the docs you need to test everything. especially those binary attachments namely .pdf,.xls etc. I have some difficulty with my database for some reason or another where in doing the schema upgrade it proceeds to corrupt them all…

Regards

Aaron

On Mon, Mar 2, 2009 at 2:16 PM, David Hobley < david.hobley@mionegroup.com > wrote:

Hello,

I have a (happily) running RT 3.6.5 with approx 300,000 tickets in it and a database of size of around 9 Gig (lots of attachments sadly). I have the application upgraded, but when I try and run the schema upgrade in my test environment it is taking forever (for values of forever which equate to around 6 days anyway):

perl etc/upgrade/ schema.mysql-4.0-4.1.pl rt3 root "password > ~/sql.queries

time mysql -p rt3 < ~/sql.queries

Enter password:

real 8727m23.622s
user 0m0.012s
sys 0m0.004s

Does anyone have any suggestions as to how I can make this faster? I have tried to optimise MySQL as much as possible, but I am not a db expert and obviously there is something wrong. Nb. The test environment is actually more powerful than the production one after having shutdown evey VM on the machine and ensured I was only running this for the time it took.
Cheers,
David

http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Cheers,
David Hobley

IT Manager
Creators of Miessence, MiVitality and MiEnviron

Phone: +61 (7) 5582 7020
Fax: +61 (7) 5539 6719
USA Fax 1800 840 0827
Email : david.hobley@mionegroup.com
Website: www.mionegroup.com

If that is the case, then RT is un-upgradeable. Which would be a problem obviously. I can’t take out our database for 6 days. Over a weekend, sure. But no more than that. Does anyone have any suggestions as to how I can optimise this at all?

Hm. I’ve made this schema change to larger RT instances in much less time.
How heavily loaded is your database? How much IO contention is there?

First up, you should make sure you’ve tuned mysql a bit. 30 minutes with
mysqltuner can be a night-and-day change.

How long do your nightly MySQL backups of RT take? Another option might
be to dump, massage the schema and load.

Jesse,

Thanks for the suggestion - this is running on a separate test machine with nothing else running on it. Admittedly, it is running in a Xen VM, although this is the only VM on the underlying server and has been allocated both CPUs and all the RAM available. The file system is on the underlying disk, not an image on the underlying filesystem.

I will try with mysqltuner and get back to you.

The nightly backups are another option - the dump takes a couple of hours from memory, the reload takes 8. I’ll look into that if mysqltuner doesn’t help.

Cheers,
David----- Original Message -----
From: “Jesse Vincent” jesse@bestpractical.com
To: “David Hobley” david.hobley@mionegroup.com
Cc: rt-users@lists.bestpractical.com
Sent: Tuesday, 3 March, 2009 8:34:27 AM GMT +10:00 Brisbane
Subject: Re: [rt-users] DB Problems upgrading to RT 3.8.2 from 3.6.5

On Tue, Mar 03, 2009 at 08:23:31AM +1000, David Hobley wrote:

If that is the case, then RT is un-upgradeable. Which would be a problem obviously. I can’t take out our database for 6 days. Over a weekend, sure. But no more than that. Does anyone have any suggestions as to how I can optimise this at all?

Hm. I’ve made this schema change to larger RT instances in much less time.
How heavily loaded is your database? How much IO contention is there?

First up, you should make sure you’ve tuned mysql a bit. 30 minutes with
mysqltuner can be a night-and-day change.

How long do your nightly MySQL backups of RT take? Another option might
be to dump, massage the schema and load.

Cheers,
David Hobley

IT Manager
Creators of Miessence, MiVitality and MiEnviron

Phone: +61 (7) 5582 7020
Fax: +61 (7) 5539 6719
USA Fax 1800 840 0827
Email : david.hobley@mionegroup.com
Website: www.mionegroup.com