… caching of data at the filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM tests I used a 10GB key buffer. I used ALTER TABLE DISABLE KEYS and built the keys with sort via ALTER TABLE ENABLE KEYS. my.cnf [mysqld…
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: 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: 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: Using innodb_sys_tables and innodb_sys_indexes
June 15, 2012 By Leave a Comment
…information_schema tables we added in Percona Server. I was doing simple ALTER TABLE such as: “alter table sbtest… innodb_sys_tables and innodb_sys_indexes I discovered it is not the case: mysql> select * from innodb_sys_tables; +———-+——–+——————+——+——–+——-+ | TABLE…
