We are using myisam primarily for full text on mysql 5.5 ,However as 5.6+ innodb supports full text i want to know any performance or other bottleneck ,Please suggest ,we have huge data set in millions and currently tables are mix of myisam and innodb.
MySQL 5.7 – Disadvantages of Shifting from MyISAM to InnoDB
myisamMySQLmysql-5.5mysql-5.6mysql-5.7
Related Solutions
To disable InnoDB, add this to /etc/my.cnf on the MySQL 5.5. slave:
[mysqld]
skip-innodb
and run this on the Slave:
service mysql restart
According to MySQL Documentation on CREATE TABLE :
You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement:
CREATE TABLE new_tbl SELECT * FROM orig_tbl; For more information, see Section 13.1.17.1, “CREATE TABLE ... SELECT Syntax”.
Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table:
CREATE TABLE new_tbl LIKE orig_tbl;
The copy is created using the same version of the table storage format as the original table. The SELECT privilege is required on the original table.
You must specify the ENGINE upon creation
CREATE TEMPORARY TABLE ... ENGINE=MyISAM SELECT ...
to get around this problem
If you need concurrency of heavy UPDATEs and INSERTs, you will want InnoDB
If you need deadlock resolution, you will want InnoDB
If you want a storage engine that caches both data and indexes, you will want InnoDB
If you want to access multiple CPUs effective, you will want InnoDB (and tune it to do so)
Please refer to my past articles on InnoDB:
Feb 12, 2011
: How do you tune MySQL for a heavy InnoDB workload?Apr 14, 2011
: What are the main differences between InnoDB and MyISAM?Aug 04, 2011
: Optimizing InnoDB default settingsOct 05, 2011
: Query runs a long time in some newer MySQL versionsDec 19, 2011
: Which first: upgrade mysql version or convert storage engine?Jan 09, 2012
: Any gotchas at all with converting from MyISAM to InnoDB?Jan 27, 2012
: importing myisam 5.0 database into a 5.5 innodb serverJan 18, 2012
: Insert-heavy InnoDB table won't use all my CPUJan 21, 2012
: Is InnoDB Engine up to speed against Memory Engine?Jul 16, 2012
: decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5
You said you heard that MySQL 5.5 is faster than MySQL 5.0. Out of the box, that's not true. YOU MUST TUNE MySQL 5.5 TO GET GREAT PERFORMANCE OUT OF IT !!! Here are my past articles on that subject:
Jun 19, 2011
: How do I properly perform a MySQL bake-off?Nov 24, 2011
: Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)Jul 18, 2012
: decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5
You mentioned MyISAM being faster than InnoDB. If there are a lot of wide VARCHAR columns in the MyISAM with the default ROW_FORMAT=Dynamic (Default), that can degrade MyISAM performance. Saying one storage engine is faster than the other depends on the application, read/write I/O ratio, storage engine tuning.
See my posts around this subject
Sep 20, 2011
: Best of MyISAM and InnoDBApril 12, 2012
: Mysql: Insert performance INNODB vs MYISAM (Not My Post, but good answers are given)May 02, 2012
: Which mysql storage engine to choose?May 03, 2012
: Which is faster, InnoDB or MyISAM?
Addressing your issue of whether to convert now or later to InnoDB:
In the fifth link I provided ( Which first: upgrade mysql version or convert storage engine? ), I recommend converting first. However, in your particular case, I would recommend converting later. The reason is simple: You are moving the data to a different server. MyISAM tables are very easy to port. Doing an rsync or scp of /var/lib/mysql is more expedient (faster) that converting to InnoDB because the data will be slightly (sometimes significantly) big when existing as InnoDB.
There are two things you will need to do:
NEED #1
If you rysnc/scp /var/lib/mysql
you will overwrite /var/lib/mysql/mysql. Therefore, I propose you move your data like this:
- STEP01) On the MySQL 5.5 machine,
mkdir /root/mysql_schema
- STEP02) On the MySQL 5.5 machine,
cp /var/lib/mysql/mysql/* /root/mysql_schema/.
- STEP03) rsync or scp
/var/lib/mysql
from MySQL 5.0 Server to MySQL 5.5 Server - STEP04) On the MySQL 5.5 machine,
cp /root/mysql_schema/* /var/lib/mysql/mysql/.
STEP05) Get the grants from MySQL 5.0 machine as pure SQL and save the file as MySQL50Grants.sql
Jul 26, 2011
: Restoring an old backup to latest MySQL release (See the CAVEAT)Jan 27, 2012
: importing myisam 5.0 database into a 5.5 innodb serverJun 13, 2012
: Fastest way to move a database from one server to another
- STEP06) Execute the MySQL50Grants.sql on the MySQL 5.5. Server
NEED #2
You need a straightforward script to convert MyISAM to InnoDB. Please run the following on the MySQL 5.5 DB Server :
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
less /root/ConvertMyISAMToInnoDB.sql
When you are satisfied with the conversion script, login to mysql on MySQL 5.5 and run
mysql> source /root/ConvertMyISAMToInnoDB.sql
Give it a Try !!!
Related Question
- MySQL – Migrating MyISAM Tables from 5.5 to 5.6
- MySQL Versions – Comparing MySQL 5.5 vs 5.6 with WordPress
- Mysql – Different execution with same query
- Mysql – Zero Date Values & Converting Entire DB From MyISAM to InnoDB
- Mysql – Changing storage engine to InnoDb from MyIsam is dropping records from table
- MySQL InnoDB Insertion Faster Than MyISAM
Best Answer
Oracle's goal seems to be to get rid of MyISAM. Version 5.7 has virtually all the improvements to eliminate the 'desire' to use MyISAM. The next version, 8.0, is attempting to finish the task -- including getting rid of the MyISAM tables from the
mysql
database. (cf "Data Dictionary").The only thing that remains is disk space. Any InnoDB table will continue to take 2x-3x the disk space of a comparable MyISAM table. But disk space is cheap and SSDs are prevalent, so this is not a big deal.
I strongly encourage to make the shift. See my article for some rare gotchas that might bite you in the conversion. (Which reminds; I should update it to say more about the status of 8.0.)