June 19, 2013

Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency

May 22, 2013 By Leave a Comment

… is faster than InnoDB when the database is cold, but the opposite is true when the database is … at the filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation (merge …_buffer_size=8M default_tmp_storage_engine=myisam tmpdir=/dev/shm innodb_undo_logs=32 innodb_old_blocks…

Filed Under: Benchmarks, MySQL Tagged With: , , , , , , ,

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

March 4, 2013 By 7 Comments

…innodb_myisam_stopword\G *************************** 1. row *************************** Table: innodb_myisam_stopword Create Table: CREATE TABLE `innodb_myisam_stopword` ( `value` varchar(18) NOT NULL DEFAULT ” ) ENGINE=InnoDB DEFAULT…

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

Post: mysql_upgrade and Innodb Tables

May 14, 2010 By 8 Comments

… upgrade path of dumping and reloading complete database confused by this error message, which of …. Another solution is to simply run ALTER TABLE tbl ENGINE=INNODB which will rebuild table with new … way. With Oracle intentions to make Innodb default storage engine in next MySQL release I’m hopeful …

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

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

…OFF by default. Let’s look at each of the above cases in more detail. ALTER TABLE … OPTIMIZE TABLE OPTIMIZE TABLE is mapped to ALTER TABLE … ENGINE=InnoDB for InnoDB tables and thus, is just a special…dump with a dump created with –innodb-optimize-keys (the test database contained only the table I …

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…DEFAULT NULL, `details` text, PRIMARY KEY (`id`), FULLTEXT KEY `full_name` (`full_name`,`details`) ) ENGINE=InnoDB DEFAULT…InnoDB tablespace files in our database directory: -rw-rw—-. 1 mysql mysql 8632 Feb 20 15:54 dir_test_innodb… is generated: mysql> alter table dir_test_innodb ADD FULLTEXT KEY …

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

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

June 3, 2011 By 8 Comments

…100) DEFAULT NULL, `spaceid` int(11) NOT NULL, PRIMARY KEY (`spaceid`) ) ENGINE=MyISAM DEFAULT…1 mysql -u root “create database if not exists filler;” while […Innodb ” mysql -u root -e “alter table recover.$CURRENT_TABLENAME engine=Innodb;” sleep 1 echo “discarding tablespace ” mysql -u root -e “alter…

Filed Under: MySQL

Post: Copying InnoDB tables between servers

July 31, 2009 By 11 Comments

…23:22:43 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files… InnoDB: Doing …i_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Next step is to remove created .ibd file, it’s done by command: alter table order_line…

Filed Under: Percona Software Tagged With: , , , , ,

Post: Should you move from MyISAM to Innodb ?

January 12, 2009 By 59 Comments

… number of tables, slow data load and ALTER TABLE and others. Another big one is … complicated. I prefer to pick one storage engine (typically Innodb) and when use other tables when …defaults. I’ve seen hundreds of GB databases ran with MyISAM with default settings and it worked reasonably. Innodb…

Filed Under: Insight for DBAs Tagged With: , ,

Post: Best kept MySQLDump Secret

March 23, 2012 By 13 Comments

… –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases it works, … ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; — – Dumping data for table `C` — LOCK TABLES `C` WRITE; /*!40000 ALTER…

Filed Under: Insight for DBAs, MySQL

Post: Be productive with the MySQL command line

December 21, 2012 By 14 Comments

…FANTASIA | | ALIEN CENTER | | ALLEY EVOLUTION | | ALONE TRIP | | ALTER VICTORY | | AMADEUS HOLY | –… mysql> show engine innodb status\Gselect sleep(60);show engine innodb status\G…default text editor with the text of the last query. The default… startup with -A Database changed mysql> select count(*) …

Filed Under: Insight for DBAs, MySQL
« Older Posts