… very helpful article! It is just what I needed to convert a 330GB MyISAM database to InnoDB with reasonable effort. I have tested your Perl… seems it doesn’t handle binary data correctly, you need to add these lines: if (length($line) > 1) { while (substr($line… commands in the loop (second shell), I added –show-warnings to the command line, because otherwise things may go wrong unnoticed…
Comment: How to load large files safely into InnoDB with LOAD DATA INFILE
March 29, 2012 By 28 Comments
Comment: Percona Toolkit 2.1 with New Online Schema Change Tool
April 9, 2012 By 9 Comments
Quick question… does the pt-online-schema-change tool work at tables using the MyISAM storage engine? Would it work to convert a table from MyISAM to InnoDB?
Post: Moving from MyISAM to Innodb or XtraDB. Basics
November 22, 2010 By 2 Comments
… are some feature differences between MyISAM and Innodb though well it is typically easily spotted by converting tables to Innodb on restored backup. Full text… 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…
Post: A recovery trivia or how to recover from a lost ibdata1 file
June 3, 2011 By 7 Comments
… tablespace;” sleep 1 echo “switching to MyISAM” mysql -u root -e “alter table recover.$CURRENT_TABLENAME engine=MyISAM;flush tables;” sleep 1 let… 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.
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… OK. This means You Can’t Trust CHECK TABLE in Innodb to be sure your tables are good. In this simple corruption… 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… crashes it does not store all data it could recover to MyISAM table. Using series of queries with LIMIT can be handly…
Post: Quick tip: how to convert tables to InnoDB
July 30, 2008 By 4 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…
Post: InnoDB in self-compiled MySQL 5.1
September 13, 2007 By 9 Comments
… InnoDB from previous version: mysql testdatabase < dump.sql I bet you will not notice all your tables now is MyISAM. Why? Welcome to… are equal to be not included by default. It is not too hard to fix, you just need to use –with-plugins=innodb (or… what I would want to see is BIG Warning or even Error that InnoDB table can’t be created instead of calm converting to MyISAM
Post: Using MyISAM in production
June 17, 2006 By 35 Comments
…application (storing billions of rows in tens of thousands tables) Innodb was better choice mainly because of thouse other behaviors … well…performance for small application. As load increases you might convert certain tables to MyISAM and other storage engines for performance reasons…. of course…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
… per second while converting table to MyISAM I get about 600 deletes per second and about 4000 deletes per second for Innodb (with log… be surprised to see MyISAM being significantly slower than Innodb – in this case the table is small and fully fits in Innodb Buffer Pool…, meaning Innodb could delay writes and do all the changes directly in memory. MyISAM however had to perform number of random writes to the…
Post: Using VIEW to reduce number of tables used
September 28, 2007 By 12 Comments
… which can become really inefficient. It is especially inefficient with Innodb tables both in terms of space (some tables would keep… page in Innodb), keeping all tables open in Innodb dictionary and number of other challenges in IO management and recovery. For MyISAM it… post123 table which keeps posts for user number 123 etc. Converting it to the views we can have “post” table which has…
