Converting MyISAM to InnoDB tables for upgrades

A while back (approximately 16-24 months ago) we installed RT 3.0.1,
with MySQL as the database engine.

Subsequently we experienced some of the performance problems
which at the time were occurring with that version when MySQL and
Apache were not tuned properly after the RT upgrade or install.

This engendered much unpleasantness among the staff using RT here,
leading to frenzied efforts to find a rapid fix.

So, after a reading some posts to this list recommending it, I converted
RT’s tables from InnoDB to MyISAM. The table type for our RT install has
remained MyISAM ever since.

The best thing that I can say about this fix is that it didn’t make
matters worse;
and in any case, after employing additional measures we eventually
obtained
the sort of performance originally anticipated.

However, we are now committed to upgrading RT to the latest version.

While making this upgrade, plainly we should get back on the right foot
with respect
to the MySQL table type that RT prefers (or perhaps now requires) and so
I have two
questions for the list:

  1. Will the database upgrade scripts to be run after “make upgrade” , e.g.,

/opt/rt3/sbin/rt-setup-database --action schema
–datadir etc/upgrade/

automatically convert from MyISAM to InnoDB?

  1. If not, has anyone any advice or caveats about effecting such a
    conversion using the mysql client?

Mike Kukla
Web Programmer/Assistant Unix Administrator
College of Art and Sciences, Educational Technology
414 Clemens Hall
645-6000 x1156
kukla@caset.buffalo.edu

So, after a reading some posts to this list recommending it,
I converted
RT’s tables from InnoDB to MyISAM. The table type for our
RT install has
remained MyISAM ever since.

Mike Kukla
Web Programmer/Assistant Unix Administrator
College of Art and Sciences, Educational Technology
414 Clemens Hall
645-6000 x1156
kukla@caset.buffalo.edu

The conversion from MyISAM back to InnoDB should be just as easy as the
original conversion from InnoDB to MyISAM - just a matter of a simple
"alter table" statement for each table (can be a perl one-liner on the
unix CL).

Eric Schultz
United Online

A while back (approximately 16-24 months ago) we installed RT 3.0.1,
with MySQL as the database engine.

Subsequently we experienced some of the performance problems
which at the time were occurring with that version when MySQL and
Apache were not tuned properly after the RT upgrade or install.

This engendered much unpleasantness among the staff using RT here,
leading to frenzied efforts to find a rapid fix.

So, after a reading some posts to this list recommending it, I converted
RT’s tables from InnoDB to MyISAM. The table type for our RT install has
remained MyISAM ever since.

The best thing that I can say about this fix is that it didn’t make
matters worse;
and in any case, after employing additional measures we eventually
obtained
the sort of performance originally anticipated.

However, we are now committed to upgrading RT to the latest version.

While making this upgrade, plainly we should get back on the right foot
with respect
to the MySQL table type that RT prefers (or perhaps now requires) and so
I have two
questions for the list:

  1. Will the database upgrade scripts to be run after “make upgrade” , e.g.,

/opt/rt3/sbin/rt-setup-database --action schema
–datadir etc/upgrade/

automatically convert from MyISAM to InnoDB?
No, because InnoDB is required since RT-3.0 release, recent versions
even abort new installations if you have no InnoDB support.

  1. If not, has anyone any advice or caveats about effecting such a
    conversion using the mysql client?
    No caveats, use query recommended by mysql docs, easy to find with
    search on the mysql site.


Mike Kukla
Web Programmer/Assistant Unix Administrator
College of Art and Sciences, Educational Technology
414 Clemens Hall
645-6000 x1156
kukla@caset.buffalo.edu


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

We’re hiring! Come hack Perl for Best Practical: http://bestpractical.com/about/jobs.html

Best regards, Ruslan.