I am also in the process of altering tables from MyISAM to INNODB. I
have one big problem - a very large table (> 5 million rows) with 5
indices/indexes on it, including a couple that are UNIQUE but with NULL
values allowed. Creating the last of these indices is taking an absurd
amount of time on my test system, which makes it impossible for me to
recommend this change for production as our systems would be down for
many days.
Here are the steps I took (note that after each failure, I recreated the
original data structure)
1) I tried altering the table directly to be Innodb. I stopped this
after 4 days, when all it said in the process list was 'copying to tmp
table' - after 4 days!
2) Then I tried mysqldumping the table structure, and changing MyISAM ->
INNODB, dropped the table, and ran the mysqldump output back into the
database. The INSERTS ran horribly slowly - something like 50,000
inserts in 4 hours. This extrapolates to a query that would run for
several weeks on 5 million rows, so I stopped it again.
3) Then I tried dropping all the indices, altering the table to INNODB
and re-creating the indicies. This worked pretty quickly until the final
index creation, which is still running after more than 4 days.
What am I doing wrong? This seems like an unacceptable length of time to
convert a valid MyISAM table to Innodb, yet I REALLY need the row-level
locking ASAP!
Any help would be appreciated.
Table structure is shown below, as is the current mysqladmin processlist.
Thanks,
John
________________________________________________________________
CREATE TABLE Subscriber (
SubscriberID int(11) NOT NULL auto_increment,
Email varchar(255) NOT NULL default '',
Zipcode varchar(5) default NULL,
InitialContactDate date NOT NULL default '0000-00-00',
FirstName varchar(20) default NULL,
LastName varchar(20) default NULL,
OrigDate date NOT NULL default '0000-00-00',
AlertWhenAvailable char(1) default NULL,
Gender char(1) default NULL,
DOB date default NULL,
Password varchar(25) default NULL,
SubscriberTypeID mediumint(9) default NULL,
Attempts tinyint(4) NOT NULL default '0',
Username varchar(255) default NULL,
Address varchar(255) default NULL,
City varchar(100) default NULL,
State char(2) default NULL,
Married char(1) default NULL,
Age varchar(5) default NULL,
Income varchar(20) default NULL,
optin int(11) default NULL,
Country char(2) default 'US',
SourcePartnerID int(11) default '0',
Occupation char(2) default NULL,
PRIMARY KEY (SubscriberID),
UNIQUE KEY AK1Subscriber (Email),
UNIQUE KEY XAK2Subscriber (Username),
KEY xie2Subscriber (optin,Gender,DOB),
KEY xie3Subscriber (Zipcode)
) TYPE=MyISAM;
______________________________________________
+----+-------+-----------+------------+---------+--------+-------------------+-------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State
| Info
|
+----+-------+-----------+------------+---------+--------+-------------------+-------------------------------------------------------------------+
| 87 | jkemp | localhost | streetmail | Query | 250195 | copy to tmp
table | alter table Subscriber add unique index XAK2Subscriber (Username) |
| 95 | jkemp | localhost | | Query | 0 |
| show processlist
|
+----+-------+-----------+------------+---------+--------+-------------------+-------------------------------------------------------------------+
______________________________________________
Robert P Ricci wrote:
> I've recently converted from using MyISAM to InnoDB tables, and am
> pleased with InnoDB so far. However, the inability to use 'ALTER TABLE'
> with InnoDB tables is very problematic, as our software is still under
> heavy development and our database schema changes frequently. I'm
> working on a way to automate the drop/create/repopulate process to alter
> tables, but am running into a few problems:
>
> Is there a way to prevent clients from noticing that the table in
> question is temporarily gone? Something like locking the entire database
> for writing?
>
> Second, mysqldump doesn't give foreign key constraints in its table
> creation commands. Is there any way to retrieve these?
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
