MYSQL Optimize tables on Innodb in production environment advice

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.

InnoDB for MySQL 5.1 exhibits single-threaded behavior because the number of read and write I/O threads are fixed at 4 each. I was just reminded that versions of MySQL before 5.1.38 has only one thread for read and one for write. Therefore, scaling up hardware will not improve performance at all.

You need to upgrade to MySQL 5.5/5.6 because there are settings that will enable InnoDB to engage multiple CPUs and multiple cores (such as innodb_read_io_threads and innodb_write_io_threads).

• Sep 20, 2012 : Multi cores and MySQL Performance
• Apr 26, 2012 : Is the CPU performance relevant for a database server?
• May 26, 2011 : About single threaded versus multithreaded databases performance

CAVEAT : You must tune MySQL 5.5/5.6 properly because left unconfigured MySQL 5.1 is faster than 5.5/5.6 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

Give it a Try !!!