…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…
Comment: Finding out largest tables on MySQL Server
April 24, 2012 By 24 Comments
…table that looks like this from the query in this article: +—————————————+———+——–+——–+————+———+ | CONCAT(table_schema, ‘.’, table_name) | rows | DATA | idx | total_size….ibd On disk vs the information_schema query, there is around 9GB …
Post: Researching your MySQL table sizes
March 17, 2008 By 26 Comments
…’) total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES WHERE table_name like “%performance_log%”; +——–+———+———+——-+————+———+ | tables | rows | data | idx | total_size…),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_size | idxfrac…
Post: Getting History of Table Sizes in MySQL
December 8, 2010 By 13 Comments
… surprisingly few people have is the history of the table sizes. Projection of data growth is very important component for…INSERT INTO stats.TABLES SELECT DATE(NOW()), TABLE_SCHEMA, TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES; I …
Post: Solving INFORMATION_SCHEMA slowness
December 23, 2011 By 16 Comments
… this slowness is not opening and closing tables, which can be solved with decent table cache size, and which is very fast for… 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: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
…’) total_size, ROUND(index_length / 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_size | idxfrac…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
… 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… # Bytes_sent: 34187 Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 0 SET timestamp=1316767697; show table status from `db1`; As…
Post: InnoDB TABLE/INDEX stats
March 20, 2010 By 5 Comments
…INFORMATION_SCHEMA tables available in XtraDB: It is INNODB_TABLE_STATS INNODB_INDEX_STATS These table show statistics about InnoDB tables…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
… index size (assuming table sizes are relatively static) To get accurate information about table usage I will use Percona Patches: mysql> select * from information_schema.table_statistics where table_schema…) For table sizes we can use traditional TABLES table: mysql> select table_schema,table_name,index_length from information_schema.tables where table_schema=’test’ and table_name in (‘a’,'b’); +————–+————+————–+ | table_schema | table_name | index…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 11 Comments
…tables to Information Schema which makes this information possible. It is just few queries away: SELECT `schema` AS table_schema, innodb_sys_tables.name AS table…
