List:General Discussion« Previous MessageNext Message »
From:Rolando Edwards Date:November 1 2006 4:05pm
Subject:Re: MyISAM to InnoDB conversion help
View as plain text  
If you are do this in MySQL 5, try this:

ALTER TABLE <table-name> ENGINE = InnoDB;

That's all.

Let MySQL worry about conversion.
You may also want to tweek the innodb
system variables (show variables like 'innodb%)
for better InnoDB performance prior to trying this.

----- Original Message -----
From: Mikhail Berman <mberman@stripped>
To: mysql@stripped
Sent: Wednesday, November 1, 2006 10:31:13 AM GMT-0500 US/Eastern
Subject: MyISAM to InnoDB conversion help

Hi everyone,
 
I am hoping to get help with extremely slow performance of MyISAM to
InnoDB conversion. Or find out if this type of performance is usual
 
I have MyISAM table that contains - 3,299,509 rows and I am trying to
convert it to InnoDB for the use with row-level locking, and I am
getting insertion speed of 0.243 of a record a second while I am doing
"INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table.
 
Your help is appreciated.
 
Here is what my environment looks like.
 
Hardware:
 
SunBlade 2000 with 2GB processor connected to StorEdge A5200 with RAID5
on it.
 
OS:
 
root@*****/>uname -a
SunOS ***** 5.9 Generic_118558-19 sun4u sparc SUNW,Sun-Blade-1000

MySQL:
 
mysql> status;
--------------
mysql  Ver 14.12 Distrib 5.0.18, for sun-solaris2.9 (sparc) using
readline 5.0
 
InnoDB tables structure:
 
 DAILY_EDGAR_INNODB |CREATE TABLE `DAILY_EDGAR_INNODB` (
  `ftp_file_name_key` char(80) NOT NULL default '',
  `history_record` int(1) NOT NULL default '0',
  `description` char(100) NOT NULL default '',
  `company_fkey` char(10) NOT NULL default '',
  `company_name` char(100) NOT NULL default '',
  `subject_company_fkey` char(10) NOT NULL default '',
  `filer_description` char(10) NOT NULL default '',
  `form_fkey` char(20) NOT NULL default '',
  `file_accepted` char(20) NOT NULL default '',
  `been_evaluated` char(20) NOT NULL default '',
  `uport_evaluated` int(1) NOT NULL default '0',
  `file_date` char(10) NOT NULL default '',
  `file_size` char(10) NOT NULL default '50 KB',
  `accession_number` char(24) NOT NULL default '',
  `http_file_name_html` char(100) NOT NULL default '',
  `http_file_name_text` char(100) NOT NULL default '',
  `create_date` date NOT NULL default '0000-00-00',
  `change_date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`ftp_file_name_key`),
  KEY `company_idx` (`company_fkey`),
  KEY `filaccdx` (`file_accepted`),
  KEY `beendx` (`been_evaluated`),
  KEY `fidadx` (`file_date`),
  KEY `upevdx` (`uport_evaluated`),
  KEY `crdadx` (`create_date`),
  KEY `hiredx` (`history_record`),
  KEY `accession_number` (`accession_number`),
  KEY `fofkdx` (`form_fkey`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Procedure used to execute conversion:
 
root     27686  0.0  0.2 5840 3224 ?        S 14:08:23  0:00 mysql
-pxxxxxx xxxxxxx -e insert into DAILY_EDGAR_INNODB select * from
DAILY_EDGAR

my.cnf InnoDB section:
 
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /export/home/mysqldata/ibdata
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /export/home/mysqldata/ibdata
innodb_log_arch_dir = /export/home/mysqldata/ibdata
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 50M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
 
Best,
 
Mikhail Berman
 

Thread
MyISAM to InnoDB conversion helpMikhail Berman1 Nov
  • Re: MyISAM to InnoDB conversion helpRolando Edwards1 Nov
    • RE: MyISAM to InnoDB conversion helpMikhail Berman1 Nov
      • Re: MyISAM to InnoDB conversion helpRolando Edwards1 Nov
        • Re: MyISAM to InnoDB conversion helpRolando Edwards1 Nov
          • RE: MyISAM to InnoDB conversion helpMikhail Berman1 Nov