… at information_schema to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where… which temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my…
Comment: Solving INFORMATION_SCHEMA slowness
April 20, 2012 By 16 Comments
… about one hour just to finish this query: “SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE …” After setting innodb_stats_on… to the rest of the percona tools that use the MySQL INFORMATION_SCHEMA ) allowing it to momentarily set innodb_stats_on_metadata=0…
Comment: Finding out largest tables on MySQL Server
April 24, 2012 By 24 Comments
…example a table that looks like this from the query in this article: +—————————————+———+——–+——–+————+———+ | CONCAT(table_schema, ‘.’, table_name) | rows … 1 mysql mysql 58G 2012-04-24 13:41 tablename.ibd On disk vs the information_schema query…
Comment: Which Linux distribution for a MySQL database server? A specific point of view.
March 15, 2012 By 37 Comments
…_for_crashed_tables‘ function (found in /usr/share/mysql/debian-start.inc.sh) which, among other things, does nasty select from information_schema. On an instance having thousands+ of tables it…
Post: Solving INFORMATION_SCHEMA slowness
December 23, 2011 By 16 Comments
… the table first time it opens it. Here are some numbers from my test box: mysql> select count(*),sum(data_length) from information_schema.tables; +———-+——————+ | count(*) | sum(data_length) | +———-+——————+ | 130 | 2856365892 | +———-+——————+ 1 row in set (1.08 sec) mysql> set…
Post: INFORMATION_SCHEMA tables in the InnoDB pluggable storage engine
May 24, 2008 By Leave a Comment
… in this InnoDB release is INFORMATION_SCHEMA tables that show some status information about InnoDB. Here are the tables: mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE ‘INNODB%’; +—————————————-+ | Tables_in_INFORMATION_SCHEMA (INNODB%) | +—————————————-+ | INNODB_CMP | | INNODB_CMP_RESET | | INNODB_CMPMEM | | INNODB_CMPMEM_RESET | | INNODB_LOCK_WAITS | | INNODB_LOCKS | | INNODB_TRX | +—————————————-+ The _CMP tables…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
… MySQL and InnoDB plugin’s manual: Metadata commands like SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES (or their corresponding queries from INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.STATISTICS) When 1 / 16th of the table or 2Billion…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… transaction is blocking others. There are some INFORMATION_SCHEMA tables that make this simple. Just query the tables, and if something is blocked, find… exit 1 fi host=$(mysql -ss -e ‘SELECT p.HOST FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… ? Right now none of such information is available which makes it hard to understand how exactly MySQL Query Cache is working in… Cache content and operation can be presented as some INFORMATION_SCHEMA tables. Self Refresh MySQL Query Cache causes not uniform performance as you get…
Post: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
… an instance of MySQL with 100 tables and the table_definition_cache set to 1000. My understanding of this is that MySQL won’t… 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…
