… caching of data at the filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM… user=justin innodb_buffer_pool_size=64G innodb_log_file_size=4G innodb_file_per_table innodb_stats_on_metadata=off innodb_file_format=barracuda innodb_log…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
… new features that MySQL 5.6 brought: GTID-based replication, InnoDB Fulltext, Memcached integration, a more complete performance schema, online DDL…> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test…> 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…-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…
Comment: Implications of Metadata Locking Changes in MySQL 5.5
March 27, 2013 By 9 Comments
… InnoDB, then you can further clarify to yourself that the ALTER in your test case is waiting on InnoDB row locks by setting innodb… the 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…
Post: Percona XtraBackup 2.0.7 for MySQL available for download
May 6, 2013 By 1 Comment
… pool dump files). Percona XtraBackup has implemented support for the InnoDB Buffer Pool Preloading introduced in MySQL 5.6. Starting with… be imported by ALTER TABLE IMPORT TABLESPACE on MySQL and Percona Server 5.6 as described in Exporting and Importing Tables guide. Bugs… all InnoDB error or diagnostic messages are never printed by xtrabackup_56. Bug fixed #1169971. innobackupex would still run with FLUSH TABLES…
Post: Percona XtraBackup 2.1.0 'release candidate' for MySQL available for download
May 7, 2013 By Leave a Comment
… pool dump files). Percona XtraBackup has implemented support for the InnoDB Buffer Pool Preloading introduced in MySQL 5.6. Starting with… be imported by ALTER TABLE IMPORT TABLESPACE on MySQL and Percona Server 5.6 as described in Exporting and Importing Tables guide. Bugs… all InnoDB error or diagnostic messages are never printed by xtrabackup_56. Bug fixed #1169971. innobackupex would still run with FLUSH TABLES…
Comment: Percona Server 5.5.30 with TokuDB for MySQL
April 28, 2013 By 26 Comments
… to convert 240 GB 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=TokuDB” to do the conversion… to that, I was successful in converting a 10 GB table with a similar schema.
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 server. This will show up as something like “TABLE… of MVCC. It’s not ACID). The largest part of ALTER TABLE not being completely crash safe in MySQL is the MySQL…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… remove auto_increment from 100G table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE `huge_table` CHANGE `id` `id` int(6) NOT NULL and then wait hours for table rebuild… work for changing table comment as well, however – changing a comment with a help of ALTER TABLE does not rebuild the table, so we…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… and index size: mysql> show table status like “sbtest” \G *************************** 1. row *************************** Name: sbtest Engine: InnoDB Version: 10 Row_format: … If you’re having large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable …
