….table_schema,’.',d.table_name,’ drop index ‘,group_concat(index_name separator ‘,drop index ‘),’;') stmt from (SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics s LEFT JOIN information_schema.index_statistics…
Post: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
… code paths in the server: DROP TABLE RENAME TABLE DROP VIEW open table filling INFORMATION_SCHEMA tables (I think it is actually the TABLES table, but didn’t look closely) An example of how this is used is that in the DROP TABLE code path…
Post: Announcing Percona XtraDB Storage Engine: a Drop-in Replacement for Standard InnoDB
December 16, 2008 By 25 Comments
… with standard InnoDB, so you can use it as a drop-in replacement in your current environment. It is designed to… is a list of new features and enhancements: INFORMATION_SCHEMA.XTRADB_ENHANCEMENTS. This table contains information about the differences between the Percona XtraDB and…
Post: SHOW OPEN TABLES - what is in your table cache
December 14, 2008 By 3 Comments
… table lock for this table to lock it. Name_locked shows whenever the name for this table is locked. It is used for DROP or RENAME TABLE… lock right now. It also deserves to be converted to INFORMATION_SCHEMA table – so it would be easily to operate it using SQL…
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
…information_schema.table_constraints a INNER JOIN information_schema.key_column_usage b ON a.constraint_name = b.constraint_name AND a.table_schema = b.table_schema AND a.table…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… heavily loaded, I may even do some INFORMATION_SCHEMA queries to help me find the biggest tables and so forth. If the server is heavily loaded or has a lot of data, touching the INFORMATION_SCHEMA can… that there are InnoDB tables whose .frm files exist but have been dropped from InnoDB, for example. (The INFORMATION_SCHEMA query will surely trigger…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… InnoDB schema from one shard. The schema is duplicated 20 times (but not the ontime_fact data): DROP TABLE IF EXISTS `dim_airport`; CREATE TABLE…` (`DayOfWeek`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=’Contains the date information from the BTS ontime flight data. Note dates may not…
Post: Connecting orphaned .ibd files
May 13, 2011 By 12 Comments
…information_scheme database in Percona Server though). The structure of these tables is following: SYS_TABLES: CREATE TABLE `SYS_TABLES…table test/t2 In this case you need to drop table…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
… you get it right. Performance of sysbench OLTP on small table drops more than 150 times when heavy mysqldump is running concurrently… at the buffer pool contents. mysql [localhost] {msandbox} (information_schema) > select concat_ws(‘.’, t.schema, t.name, i.name) as index_name, sum…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 7 Comments
… must not drop, truncate or alter the schema of the table after the backup has been taken. The variable innodb_file_per_table must… table was altered or truncated in the meanwhile. Though this only works if table was “exported” with Xtrabackup as this exports essential information from main tablespace which is not stored in .ibd file. innodb_import_table…
