By the way after replying to you... a google search pointed to this book: http://books.google.co.in/books?id=BL0NNoFPuAQC&lpg=PA145&ots=COQMAwwG9Q&dq=speeding%20up%20alter%20table%20mysql&pg=PA146#v=onepage&q=speeding%20up%20alter%20table%20mysql&f=false
--- Regards, Saptarshi PURKAYASTHA My Tech Blog: http://sunnytalkstech.blogspot.com You Live by CHOICE, Not by CHANCE On 19 August 2011 23:23, Saptarshi Purkayastha <[email protected]> wrote: > 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]
