data_size_per_schema
NAME
data_size_per_schema: Present with data size measurements per schemaTYPE
ViewDESCRIPTION
data_size_per_schema provides with an analysis of number and size of tables, views & engines per schema. It is useful in diagnosing an unfamiliar server, in checking up on the different engines, quickly recognizing largest tables.
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_schema; +--------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+---------------------+------+-----+---------+-------+ | TABLE_SCHEMA | varchar(64) | NO | | | | | count_tables | decimal(23,0) | YES | | NULL | | | count_views | decimal(23,0) | YES | | NULL | | | distinct_engines | 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:
- TABLE_SCHEMA: name of schema
- count_tables: number of tables in this schema
- count_views: number of views in this schema
- distinct_engines: number of distinct storage engines of tables in this schema
- data_size: approximate data size of schema's tables
- index_size: approximate index size of schema's tables
- total_size: sum of data_size and index_size
- largest_table: name of largest table in this schema
- largest_table_size: total size in bytes of largest_table
EXAMPLES
Show dimensions per schema:
mysql> SELECT * FROM common_schema.data_size_per_schema; +---------------+--------------+-------------+------------------+-----------+------------+------------+----------------------+--------------------+ | TABLE_SCHEMA | count_tables | count_views | distinct_engines | data_size | index_size | total_size | largest_table | largest_table_size | +---------------+--------------+-------------+------------------+-----------+------------+------------+----------------------+--------------------+ | common_schema | 1 | 27 | 1 | 28672 | 35840 | 64512 | numbers | 64512 | | google_charts | 1 | 1 | 1 | 16384 | 0 | 16384 | chart_data | 16384 | | mycheckpoint | 13 | 50 | 2 | 3022602 | 88064 | 3110666 | status_variables | 2654208 | | mysql | 23 | 0 | 2 | 3259223 | 2551808 | 5811031 | time_zone_transition | 4297362 | | sakila | 16 | 7 | 2 | 4297536 | 2761728 | 7059264 | rental | 2850816 | | test | 6 | 0 | 2 | 80232 | 45056 | 125288 | t | 49152 | | world | 3 | 0 | 2 | 510355 | 28672 | 539027 | City | 409600 | +---------------+--------------+-------------+------------------+-----------+------------+------------+----------------------+--------------------+