John, did you configure innodb_buffer_pool_size and InnoDB log files big? Look at the online manual on the recommended sizes.
If the table is very big, then UNIQUE secondary keys will cause a lot of disk i/o, because a disk read is required to check the constraint for each row, and the insert buffer cannot be used. Hmm... a fix would be to disable UNIQUE checking during the conversion. I could add an option where you can switch the UNIQUE checking off in my.cnf. That option would be used during big conversion procedures, if you already know that the data satisifies the constraint. Ok, I will introduce the my.cnf option innodb_no_unique_checks in 3.23.4x. Thank you for bringing this up :). About Robert Ricci's question: SHOW TABLE STATUS FROM ... reports FOREIGN KEY constraints in the table comment section. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB John Kemp wrote in message ... >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
