…(data_free / ( 1024 * 1024 ), 2), ‘MB’)FREE from information_schema.TABLES where TABLE_SCHEMA=’xyz’ and table_name=’abc’ and Data_free > 0 limit 10; and…(data_free / ( 1024 * 1024 ), 2), ‘MB’)FREE from information_schema.TABLES where TABLE_SCHEMA=’xyz’ and table_name=’abc’ and Data_free > 0 limit 10; still…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… 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… 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…
Comment: Solving INFORMATION_SCHEMA slowness
April 20, 2012 By 16 Comments
…’ve just hit the INFORMATION_SCHEMA slowness today when I began the tests to integrate the pt-online-schema-change tool into our… 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…
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 …41 tablename.ibd On disk vs the information_schema query, there is around 9GB discrepancy. …
Comment: Which Linux distribution for a MySQL database server? A specific point of view.
March 15, 2012 By 37 Comments
…_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 has to be disabled in order…
Post: INFORMATION_SCHEMA tables in the InnoDB pluggable storage engine
May 24, 2008 By Leave a Comment
… 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: Solving INFORMATION_SCHEMA slowness
December 23, 2011 By 16 Comments
… table first time it opens it. Here are some numbers from my test box: mysql> select count(*),sum(data_length) from information_schema.tables… (0.00 sec) mysql> select count(*),sum(data_length) from information_schema.tables; +———-+——————+ | count(*) | sum(data_length) | +———-+——————+ | 130 | 2856365892 | +———-+——————+ 1 row in set…
Post: Userstats patches with information schema support
August 4, 2008 By 16 Comments
Recently, we added information schema support to Google’s userstats patch. There are three information schema tables added: user_statistics, table_statistics, index_statistics. One can now use select * from information_schema.user_statistics along with…
Post: Researching your MySQL table sizes
March 17, 2008 By 26 Comments
…(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES WHERE table_name like “%performance_log%”; +——–+———+———+——-+————+———+ | tables | rows | data | idx | total_size | idxfrac | +——–+———+———+——-+————+———+ | 120…_length),2) idxfrac FROM information_schema.TABLES GROUP BY table_schema ORDER BY sum(data_length+index_length) DESC LIMIT 10; +——–+——————–+——-+——-+——-+————+———+ | tables | table_schema | rows | data | idx | total…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
… is quite handy in a way it presents information: SELECT CONCAT(table_schema, ‘.’, table_name), CONCAT(ROUND(table_rows / 1000000, 2), ‘M’) rows, CONCAT(ROUND… / data_length, 2) idxfrac FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10; +————————————-+——–+——–+——–+————+———+ | concat(table_schema,’.',table_name) | rows | data | idx | total…
