data_size_per_engine
NAME
data_size_per_engine: Present with data size measurements per storage engineTYPE
ViewDESCRIPTION
data_size_per_engine provides with an approximate data size in bytes per storage engine. It is useful in diagnosing an unfamiliar server, checking up on the different defined engines and the volumes they hold.
This view includes dimensions of the `mysql` schema, since this schema may also include user data such as stored routines. It does not consider INFORMATION_SCHEMA nor PERFORMANCE_SCHEMA.
STRUCTURE
mysql> DESC common_schema.data_size_per_engine; +--------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+---------------------+------+-----+---------+-------+ | ENGINE | varchar(64) | YES | | NULL | | | count_tables | bigint(21) | NO | | 0 | | | data_size | decimal(42,0) | YES | | NULL | | | index_size | decimal(42,0) | YES | | NULL | | | total_size | decimal(43,0) | YES | | NULL | | | largest_table | longtext | YES | | NULL | | | largest_table_size | bigint(20) unsigned | YES | | NULL | | +--------------------+---------------------+------+-----+---------+-------+
SYNOPSIS
Columns of this view:
- ENGINE: name of storage engine
- count_tables: number of tables of this engine
- data_size: approximate data size in bytes for all tables of this engine
- index_size: approximate index size in bytes for all tables of this engine
- total_size: sum of data_size and index_size: approximate total size on disk
- largest_table: fully qualified name of largest table of this engine
- largest_table_size: total size in bytes of largest_table
EXAMPLES
Show dimensions per storage engine on an InnoDB-dedicated server:
mysql> SELECT * FROM common_schema.data_size_per_engine; +--------+--------------+--------------+-------------+--------------+---------------------------+--------------------+ | ENGINE | count_tables | data_size | index_size | total_size | largest_table | largest_table_size | +--------+--------------+--------------+-------------+--------------+---------------------------+--------------------+ | CSV | 2 | 0 | 0 | 0 | `mysql`.`general_log` | 0 | | InnoDB | 172 | 252877864960 | 68769677312 | 321647542272 | `webdata`.`data_archive` | 150358507520 | | MyISAM | 21 | 573493 | 95232 | 668725 | `mysql`.`help_topic` | 442472 | | SPHINX | 1 | 0 | 0 | 0 | `webdata`.`sphinx_search` | 0 | +--------+--------------+--------------+-------------+--------------+---------------------------+--------------------+
In the above example the only MyISAM tables are those of the `mysql` schema.