… caching of data at the filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM…_tmp_storage_engine=myisam tmpdir=/dev/shm innodb_undo_logs=32 innodb_old_blocks_time=0 table_open_cache=2048 table_definition_cache=16384 innodb_flush…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
…> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test…, PRIMARY KEY (`id`), KEY `col2` (`col2`), KEY `col2_2` (`col2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec…> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.36 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test…
Post: Migrating between MySQL schemas with Percona Xtrabackup
June 10, 2013 By 2 Comments
…-ddl.sql SELECT CONCAT(‘ALTER TABLE `’, table_name, ‘` DISCARD TABLESPACE;’) AS _ddl FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=’orig’ AND ENGINE=’InnoDB‘; EOF mysql -N -B…-ddl.sql SELECT CONCAT(‘ALTER TABLE `’, table_name, ‘` IMPORT TABLESPACE;’) AS _ddl FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=’orig’ AND ENGINE=’InnoDB‘; EOF For full disclosure…
Comment: Implications of Metadata Locking Changes in MySQL 5.5
March 20, 2013 By 9 Comments
For my test,server version : 5.1.48-community-log ,table engine=innodb My steps : session1 session2 mysql> start transaction; Query OK, 0… alter table t1 add column title2 varchar(30) default ‘ook’; (hang) mysql> commit; Query OK, 0 rows affected (0.01 sec) mysql> alter table… /* xid=18 */ use `bruce`; alter table t1 add column title3 varchar(30) default ‘oook’ As you see,alter table is logged after update,that…
Comment: Percona Server 5.5.30 with TokuDB for MySQL
April 28, 2013 By 26 Comments
… InnoDB table to TokuDB: “ERROR 1030 (HY000): Got error -1 from storage engine” The table has approximately 1.6 billion rows and I used “ALTER TABLE ENGINE… to that, I was successful in converting a 10 GB table with a similar schema.
Comment: Implications of Metadata Locking Changes in MySQL 5.5
March 27, 2013 By 9 Comments
… ALTER: — session 1 start transaction; select * from t1 where id=3 for update; — session 2 set innodb_lock_wait_timeout=1; alter table t1 engine=innodb; And the alter will timeout after approximately a second with…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… by SHOW TABLE STATUS versus 265 MB index size with the optimization disabled. OPTIMIZE TABLE OPTIMIZE TABLE is mapped to ALTER TABLE … ENGINE=InnoDB for InnoDB tables and thus… foreign keys because InnoDB requires a full table rebuild on foreign key changes. So adding them back with a separate ALTER TABLE after restoring…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
…TABLE `huge_table`\G *************************** 1. row *************************** Table: huge_table Create Table: CREATE TABLE `huge_table` ( `id` int(6) NOT NULL, `text` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB…
Post: Why ALTER TABLE shows as two transactions in SHOW ENGINE INNODB STATUS
July 1, 2012 By 4 Comments
When executing an ALTER TABLE, InnoDB (and XtraDB) will create two InnoDB transactions: One transaction is created when the table being ALTERed is locked by the… show up as something like “TABLE LOCK table `schema`.`table_name` trx id XXXX lock mode S” in SHOW ENGINE INNODB STATUS. Another is created…
Post: Quick tip: how to convert tables to InnoDB
July 30, 2008 By 5 Comments
… 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…$’ –exec ‘ALTER TABLE %D.%N ADD KEY(site_id)’ Boy, is that a lot easier than adding indexes to 90k tables by hand!
