Yes, sadly MySQL is silly and doesn't understand that the ALTER TABLE that you are doing is simple enough and is only changing the default for a column or NOT NULL to NULL.
Before you can do such copy operations, increasing the READ_BUFFER_SIZE will help increase speed of the operation --- Regards, Saptarshi PURKAYASTHA My Tech Blog: http://sunnytalkstech.blogspot.com You Live by CHOICE, Not by CHANCE On 19 August 2011 18:53, Jeremy Keiper <[email protected]> wrote: > Thanks, Saptarshi ... I'll keep that in mind. Meanwhile, it seems that the > indexing sped up but modifying the table was as slow as ever. This > statement may take days to complete: > > ALTER TABLE `obs` MODIFY `location_id` INT NULL; > > For now, we have skipped that changeset manually and plan on dumping the > table, modifying the structure and reimporting it. Hopefully the fast > indexes won't slow down the import process like they have before. It seems > ridiculous that a table structure change (especially from NOT NULL to NULL) > should require a `copy to tmp table` step in MySQL. Have you seen this > before and found a way to tweak MySQL to use resources better so it can > complete faster? > > > Jeremy Keiper > OpenMRS Core Developer > AMPATH / IU-Kenya Support > > > On Wed, Aug 17, 2011 at 7:21 PM, Saptarshi Purkayastha > <[email protected]>wrote: > >> Hi Jeremy, >> >> I completely agree that InnoDB-plugin does perform better than plain >> InnoDB tables. >> That's why Innobase (now Oracle) has been silently providing services to >> improving MySQL performance for years. >> Also with InnoDB-plugin you can intelligently set the KEY_BLOCK_SIZE and >> get more data in-memory in ur buffer_pool_size >> >> But from what u'll see I've mentioned earlier that if you are performing >> IO intensive tasks, XtraDB (another drop-in replacement for InnoDB) provides >> even better performance, with the same reliability. >> >> --- >> Regards, >> Saptarshi PURKAYASTHA >> >> My Tech Blog: http://sunnytalkstech.blogspot.com >> You Live by CHOICE, Not by CHANCE >> >> >> On 17 August 2011 23:11, Jeremy Keiper <[email protected]> wrote: >> >>> Has anyone used the MySQL InnoDB plugin [0] for performance enhancement >>> (faster indexing, etc)? >>> >>> I just tried it out in our dev environment for upgrading the AMPATH >>> dataset from version 1.6.2 to 1.8.2. Our original upgrade was stuck on >>> adding an index to the obs table for 4+ days. After installing the plugin, >>> the same statement completed in 24 minutes. I would like to continue using >>> it, but need to know if anyone else has experienced problems with it. I >>> will post a blog entry with my findings. Thanks! >>> >>> [0]: http://dev.mysql.com/doc/innodb-plugin/1.0/en/index.html >>> >>> Jeremy Keiper >>> OpenMRS Core Developer >>> AMPATH / IU-Kenya Support >>> ------------------------------ >>> Click here to >>> unsubscribe<[email protected]?body=SIGNOFF%20openmrs-implement-l>from >>> OpenMRS Implementers' mailing list >>> >> >> ------------------------------ >> Click here to >> unsubscribe<[email protected]?body=SIGNOFF%20openmrs-implement-l>from >> OpenMRS Implementers' mailing list >> > > ------------------------------ > Click here to > unsubscribe<[email protected]?body=SIGNOFF%20openmrs-implement-l>from > OpenMRS Implementers' mailing list > _________________________________________ To unsubscribe from OpenMRS Implementers' mailing list, send an e-mail to [email protected] with "SIGNOFF openmrs-implement-l" in the body (not the subject) of your e-mail. [mailto:[email protected]?body=SIGNOFF%20openmrs-implement-l]
