June 19, 2013

Comment: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE

April 4, 2013 By 17 Comments

… everyone I am using wordpress and drupal, and i converted databases from MyISAM to InnoDB, mysql tunning script gives below results for temp table… your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables Note! BLOB…

Filed Under: Insight for DBAs Tagged With: ,

Post: Moving from MyISAM to Innodb or XtraDB. Basics

November 22, 2010 By 2 Comments

… webinar on migrating MyISAM to Innodb or some other reason but recently I see a lot of questions about migration from MyISAM to Innodb. Webinar will… be stored in MyISAM but would not fit to Innodb, though this is an exception. Space Innodb Tables tend to be larger. Again converting schema will…

Filed Under: Events and Announcements, MySQL Tagged With: , ,

Post: Quick tip: how to convert tables to InnoDB

July 30, 2008 By 5 Comments

… have a bazillion tables to convert from MyISAM to InnoDB, but they are mixed in with other tables that are already InnoDB, or are another storage engine that you don’t want to touch. mk-find –engine MyISAM –exec “ALTER TABLE %D.%N ENGINE=INNODB… like user_123_456_friends. I wanted to add an index to them — but not to the ones named friends_123_456_user…

Filed Under: Insight for DBAs Tagged With:

Post: Pitfalls of converting to InnoDB

February 25, 2007 By 16 Comments

We often recommend to our clients to convert their current database from MyISAM tables to InnoDB. The transfer by itself in most cases is almost plain… “Converting table $t” mysql -u$DBUSER -p$DBPWD -e “alter table $t type=InnoDB” $DBNAME; done There is the standard script mysql_convert…

Filed Under: Insight for DBAs Tagged With: ,

Post: A recovery trivia or how to recover from a lost ibdata1 file

June 3, 2011 By 8 Comments

… creating fake Innodb tables Once the Innodb tablespace id is minus one of a customer table alter the table definition from MyISAM to Innodb Discard… modified schema, the recovery completed. The customer was able to download the tables in MyISAM format and then he just convert them back to InnoDB.

Filed Under: MySQL

Post: What to do with MySQL Full Text Search while migrating to Innodb ?

September 10, 2009 By 12 Comments

It is rather typical for systems to start as MyISAM but as system growths to move to Innodb. The reason of the move could be just desire for better data consistency guaranty or being bitten repairing multiple GB MyISAM… Innodb tables. So what can you do ? Leave Tables as MyISAM The beauty of MySQL storage engines is you do not have to convert…

Filed Under: Insight for Developers Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

… there are likely going to be people who believe that switching to InnoDB FTS is simply a matter of upgrading to 5.6 and running ALTER TABLE foo ENGINE=InnoDB. In theory… possible to take the MyISAM full-text parser code, convert it to a plugin, and use it for InnoDB FT indexes where you’re expecting MyISAM…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Recovering Innodb table Corruption

July 4, 2008 By 22 Comments

… data in MyISAM table so all you have to do is to drop old table and convert new table back to Innodb after restarting without innodb_force_recovery option. You can also rename the old table in case you will need to look… to get your data back from simple Innodb Table Corruption. In more complex cases you may need to use higher innodb_force_recovery modes to…

Filed Under: Insight for DBAs Tagged With:

Post: MySQL Upgrade Webinar Questions Followup

June 28, 2012 By 7 Comments

…, you still need to keep into account possible query changes, replication etc. Q: Is it possible to move MyISAM to Innodb through plain ALTER TABLE… be making MySQL Replication to work. You might need to inject MySQL 5.0 in between so it will convert binary log events… to Innodb in MySQL 5.5, meaning you might need to set storage_engine=MYISAM if you’re relaying on tables to be created as MyISAM…

Filed Under: Insight for DBAs, MySQL

Post: Innodb Table Locks

July 31, 2012 By 4 Comments

…between MyISAM and Innodb when it comes to Table Level Locks. For MyISAM tables running UPDATE query on the table is essentially equivalent to locking…it straight after. Not so for Innodb. Unless table is being locked explicitly Innodb “converts” table lock to “no lock” hence eliminating conflicts on…

Filed Under: Insight for DBAs, MySQL
« Older Posts