… current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984 | +——————————-+ 1 row in…
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 | … 1 mysql mysql 58G 2012-04-24 13:41 tablename.ibd On disk vs the information_schema query…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
…://dev.mysql.com/doc/refman/5.5/en/innodb-physical-record.html You can also get the table row format from the Information Schema: mysql (information_schema) > SELECT ROW_FORMAT from TABLES WHERE TABLE_SCHEMA=’employees’ AND TABLE_NAME=’salaries’; +————+ | ROW_FORMAT | +————+ | Compact | +————+ ~/recovery-tool…
Post: Solving INFORMATION_SCHEMA slowness
December 23, 2011 By 16 Comments
… 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…, 0 rows affected (0.00 sec) mysql> select count(*),sum(data_length) from information_schema.tables; +———-+——————+ | count(*) | sum(data_length) | +———-+——————+ | 130 | 2856365892 | +———-+——————+ 1 row in set…
Post: INFORMATION_SCHEMA tables in the InnoDB pluggable storage engine
May 24, 2008 By Leave a Comment
…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…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
…INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.STATISTICS) When 1 / 16th of the table or 2Billion rows has been modified, whichever comes first. ./row/row0mysql.c:row…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
…, for which tracking exact rows used from query syntax may be hard. Query Cache Storage Engines MySQL Query Cache now stores queries… 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: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… foreign key. mysql> SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IS NOT NULL\G *************************** 1. row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: test CONSTRAINT… LIKE INFORMATION_SCHEMA.KEY_COLUMN_USAGE; mysql> INSERT INTO test.MY_KEY_COLUMN_USAGE SET CONSTRAINT_SCHEMA = ‘ecommerce’, CONSTRAINT_NAME = ‘fk_lineitems’, TABLE_SCHEMA = ‘ecommerce’, TABLE_NAME…
Post: Researching your MySQL table sizes
March 17, 2008 By 26 Comments
…information_schema.TABLES GROUP BY table_schema ORDER BY sum(data_length+index_length) DESC LIMIT 10; +——–+——————–+——-+——-+——-+————+———+ | tables | table_schema | rows…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
…tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema…FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10; +————————————-+——–+——–+——–+————+———+ | concat(table_schema,’.',table_name) | rows |…
