Hi,
I also did experience the same problem converting a 2 million + row table
from MYISAM to INNODB. It took 3 days! I had highlighted the problem to
Heikki. (thanks Heikki for the response previously :)).
In my case, there was no UNIQUE KEY for the table, but just an index on one
of the varchars. However, common to both schemas in your email and mine was
the primary key which happened to be an auto_increment :
>SubscriberID int(11) NOT NULL auto_increment,
>PRIMARY KEY (SubscriberID), I
>msgid bigint(20) unsigned NOT NULL auto_increment
>PRIMARY KEY (msgid)
Could this be a problem? Anyway, to get around this long conversion
process, I performed the conversion on a table on a slave to the master
MySQL instance, then let the replication update this new InnoDB table. On
the next scheduled downtime of that particular service, I did a switch of
this replicated InnoDB table to the master, and then moved the original
MyISAM to be the slave. I'm happy to report that I experienced the same
performance improvements with InnoDB and it's row-level locking. Needless
to say, the users of the service are a lot happier :)
Next thing would be to work on another table, but this one is about 100+ GB
so it might take a while :)
Any comments?
Cheers,
Geoffrey
__________________________________________________
Geoffrey Soh, Software Architect
Ufinity - http://www.ufinity.com
Leading Enterprise Access Management Software!
9 Scotts Road, Pacific Plaza, #06-01, Singapore 228210
Tel : +65 830-0341
Fax : +65 737-0213
__________________________________________________
> -----Original Message-----
> From: John Kemp [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, January 15, 2002 11:58 PM
> To: Heikki Tuuri
> Cc: [EMAIL PROTECTED]
> Subject: Re: Altering InnoDB tables
>
>
> Heikki,
>
> I didn't config the log that big, but the buffer pool is using 1GB of
> memory, and yes, the UNIQUE index is definitely causing a lot of disk I/O!
>
> I will be very happy to see your change, and thanks for (as usual) being
> so quick to make changes to an already very welcome addition to MySQL.
>
> I'll add this - we're running several tables that have 5-10 million
> rows, one logging table with 56 million rows on a web-based system that
> is used by 30 people at our company, editing our newsletters (which are
> stored in the database), and almost 1 million email subscribers, who
> receive 3 database-driven emails every week, and use our similarly
> data-driven website. So MySQL is dealing with a lot of roughly
> simultaneous requests. Row-level locking has improved the performance
> massively, and made my internal and external customers happier, which
> makes my day better (fewer complaints!)
>
> Thanks again,
>
> John Kemp
> Director, Software Development
> Streetmail
>
> Heikki Tuuri wrote:
>
> > 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
> >
> > .
> >
> >
>
>
>
> ---------------------------------------------------------------------
> 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
>
---------------------------------------------------------------------
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
