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@ivesinc.com>
To: mysql@lists.mysql.com
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

Hi Rolando,

Thank you for your help.

I am on MySQL 5, and I have tried to do the conversion using ALTER TABLE
command. With the same very slow result.

Do you by any chance have specific suggestions how to tweak variables
related to this?

Here is what I got:

+---------------------------------+-------------------------------------
-+
| Variable_name | Value
|
+---------------------------------+-------------------------------------
-+
| innodb_additional_mem_pool_size | 52428800
|
| innodb_autoextend_increment | 8
|
| innodb_buffer_pool_awe_mem_mb | 0
|
| innodb_buffer_pool_size | 1073741824
|
| innodb_checksums | ON
|
| innodb_commit_concurrency | 0
|
| innodb_concurrency_tickets | 500
|
| innodb_data_file_path | ibdata1:2000M;ibdata2:10M:autoextend
|
| innodb_data_home_dir | /export/home/mysqldata/ibdata
|
| innodb_doublewrite | ON
|
| innodb_fast_shutdown | 1
|
| innodb_file_io_threads | 4
|
| innodb_file_per_table | OFF
|
| innodb_flush_log_at_trx_commit | 1
|
| innodb_flush_method |
|
| innodb_force_recovery | 0
|
| innodb_lock_wait_timeout | 50
|
| innodb_locks_unsafe_for_binlog | OFF
|
| innodb_log_arch_dir | /export/home/mysqldata/ibdata
|
| innodb_log_archive | OFF
|
| innodb_log_buffer_size | 1048576
|
| innodb_log_file_size | 5242880
|
| innodb_log_files_in_group | 2
|
| innodb_log_group_home_dir | /export/home/mysqldata/ibdata
|
| innodb_max_dirty_pages_pct | 90
|
| innodb_max_purge_lag | 0
|
| innodb_mirrored_log_groups | 1
|
| innodb_open_files | 300
|
| innodb_support_xa | ON
|
| innodb_sync_spin_loops | 20
|
| innodb_table_locks | ON
|
| innodb_thread_concurrency | 20
|
| innodb_thread_sleep_delay | 10000
|
+---------------------------------+-------------------------------------
-+

Best,

Mikhail Berman

-----Original Message-----
From: Rolando Edwards [mailto:redwards@swmx.com]
Sent: Wednesday, November 01, 2006 11:05 AM
To: Mikhail Berman
Cc: mysql@lists.mysql.com
Subject: Re: MyISAM to InnoDB conversion help

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@ivesinc.com>
To: mysql@lists.mysql.com
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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=mberman@ivesinc.com