2

I inherited a production mysql server at 5.0. It uses MyISAM exclusively. It is not a high volume database but it does have some large datasets.

I am planning on:

  1. upgrading to 5.5
  2. converting existing databases to innodb

I will only do one at a time:

upgrade --> wait --> convert

or

convert --> wait --> upgrade

What is best to do first?

  • Upgrade to 5.5
  • Convert to InnoDB?

2 Answers 2

2

Of the choices, I would update, then convert.

InnoDB received some massive performance boosts in 5.5, so by upgrading first you get some features that 5.5 introduces, and then you can convert to InnoDB to get full innodb benefit.

5.0 InnoDB performance was not great, so you might see some strange issues by converting to InnoDB prior to updating to 5.5

As always though, read through the MySQL 5.5 upgrade notes to make sure there's nothing new that would break your current implementation.

And, of course, test it out on a non-production database first.

1
  • That is a good point. I hadn't thought of performance issues, just operational issues so far. Commented Dec 19, 2011 at 21:10
1

IMHO the best option would be to Convert First to InnoDB then perform the mysql upgrade. I prefer this because performing a mysql upgrade mostly involves alter the grant tables. (This especially includes the mysql.user table since MySQL 5.0's mysql.user tables has 37 columns while MySQL 5.5's mysql.user tables has 42 columns) I would not want to mess with connectivity or SQL Grants issues first.

Given the following facts:

  • your database is 74G
  • your RAM is 4GB
  • assuming you did not optimize or configure InnoDB ahead of time

Here is how to go about doing this InnoDB conversion:

Step 01) Create the script to convert all MyISAM tables into InnoDB

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
MYSQL_CONN="-u... -p..."
mysql ${MYSQL_CONN} -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql

The script looks for all tables in all databases can converts them in size order starting with the smallest.

Step 02) service mysql stop

Step 03) rm -f /var/lib/mysql/ib_logfile* /var/lib/mysql/ibdata1

Step 04) Add the following to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_buffer_pool_size=2G
innodb_log_file_size=512M
innodb_log_buffer_size=64M
innodb_open_files=4096
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=65536
key_buffer_size=8M

CAVEATS

  • innodb_log_file_size should be 25% of innodb_buffer_pool_size
  • key_buffer_size can now be scaled back

Step 05) service mysql start

This will take awhile (5 min) because mysqld will create ib_logfile0, format it, create ib_logfile1, format it.

Step 06) Login to mysql and load the conversion script

mysql> source /root/ConvertMyISAMToInnoDB.sql

This will take the longest

CAVEAT : you do not have to run it all. You could cut and paste a few lines at a time into mysql and convert a few tables at a time.

If you would like to reverse InnoDB tables back to MyISAM, just run the process backwards:

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertInnoDBToMyISAM.sql
MYSQL_CONN="-u... -p..."
mysql ${MYSQL_CONN} -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') MyISAMConversionSQL FROM information_schema.tables WHERE engine='InnoDB' ORDER BY (data_length+index_length)" > /root/ConvertInnoDBToMyISAM.sql
mysql -u... -p... -A < /root/ConvertInnoDBToMyISAM.sql

Step 07) As a precaution, dump the MySQL Grants to a text file

MYSQL_CONN="-u... -p..."
mysql ${MYSQL_CONN} --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql ${MYSQL_CONN} -AN | sed 's/$/;/g' > /root/MySQLGrants.sql

Step 08) Perform mysql upgrade

Step 09) Test all mysql users' connectivity. If it fails, run this:

mysql> source /root/MySQLGrants.sql
3
  • Just curious...how does the facts make it better to convert first, then update? Commented Dec 19, 2011 at 22:02
  • The facts are the the sake of setting up my.cnf either way. Maybe I started the answer off a little clumsy. Commented Dec 19, 2011 at 22:09
  • OK I corrected the introduction Commented Dec 19, 2011 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.