Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


quinta-feira, 12 de julho de 2018

Obtendo o tamanho do schemas de bancos de dados de uma instância do MySQL

Por Eduardo Legatti

Olá,

Neste artigo irei compartilhar as instruções SQL que normalmente utilizo para obter informações de tamanho e quantidade de tabelas e índices dos schemas de bancos de dados de uma instância MySQL. Todas as consultas são baseadas nas tabelas TABLES e STATISTICS do schema de banco de dados information_schema, conforme a seguir.

O comando abaixo mostra os schemas de bancos de dados existentes na instância do MySQL.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bd01               |
| bd02               |
| bd03               |
| bd04               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
8 rows in set (0.00 sec)

A consulta abaixo retorna o tamanho total dos bancos de dados da instância do MySQL na qual o (DB Size) é a soma dos dados de tabelas (Data Size) e dos índices (Index Size).

mysql> SELECT
    -> ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) "DB Size (MB)",
    -> ROUND(SUM(data_length) / 1024 / 1024, 2) "Data Size (MB)",
    -> ROUND(SUM(index_length) / 1024 / 1024, 2) "Index Size (MB)"
    -> FROM information_schema.tables;
+--------------+----------------+-----------------+
| DB Size (MB) | Data Size (MB) | Index Size (MB) |
+--------------+----------------+-----------------+
|      1531.17 |        1372.30 |          158.87 |
+--------------+----------------+-----------------+
1 row in set (0.05 sec)

A consulta abaixo retorna informações de tamanho de um schema de banco de dados específico.

mysql> SELECT
    -> TABLE_NAME,
    -> table_rows,
    -> ROUND(((data_length) / 1024 / 1024),2) "data_size (MB)",
    -> ROUND(((index_length) / 1024 / 1024),2) "index_size (MB)",
    -> ROUND(((data_length + index_length) / 1024 / 1024),2) "Total_size (MB)"
    -> FROM information_schema.TABLES
    -> WHERE table_schema = "bd04"
    -> ORDER BY 5 DESC;
+----------------------------------+------------+----------------+-----------------+-----------------+
| TABLE_NAME                       | table_rows | data_size (MB) | index_size (MB) | Total_size (MB) |
+----------------------------------+------------+----------------+-----------------+-----------------+
| wp_c6abf3642d_posts              |        106 |           2.52 |            0.06 |            2.58 |
| wp_c6abf3642d_options            |        212 |           1.52 |            0.02 |            1.53 |
| wp_c6abf3642d_postmeta           |        840 |           0.13 |            0.11 |            0.23 |
| wp_c6abf3642d_comments           |          0 |           0.02 |            0.08 |            0.09 |
| wp_c6abf3642d_users              |          2 |           0.02 |            0.05 |            0.06 |
| wp_c6abf3642d_commentmeta        |          0 |           0.02 |            0.03 |            0.05 |
| wp_c6abf3642d_usermeta           |         41 |           0.02 |            0.03 |            0.05 |
| wp_c6abf3642d_terms              |         23 |           0.02 |            0.03 |            0.05 |
| wp_c6abf3642d_termmeta           |          0 |           0.02 |            0.03 |            0.05 |
| wp_c6abf3642d_term_taxonomy      |         23 |           0.02 |            0.03 |            0.05 |
| wp_c6abf3642d_links              |          0 |           0.02 |            0.02 |            0.03 |
| wp_c6abf3642d_term_relationships |         14 |           0.02 |            0.02 |            0.03 |
+----------------------------------+------------+----------------+-----------------+-----------------+
12 rows in set (0.00 sec)

A consulta abaixo retorna a quantidade de tabelas e índices bem como o tamanho total dos bancos de dados da instância do MySQL.

mysql> SELECT a.table_schema "DB Name",
    -> COUNT(a.table_schema) "tables",
    -> indexes,
    -> ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) "DB Size (MB)"
    -> FROM information_schema.tables a,
    ->      (SELECT table_schema,COUNT(table_schema) "indexes" FROM information_schema.STATISTICS GROUP  BY table_schema) b
    -> WHERE a.table_schema = b.table_schema
    -> GROUP BY a.table_schema;
+---------------+--------+---------+--------------+
| DB Name       | tables | indexes | DB Size (MB) |
+---------------+--------+---------+--------------+
| bd01          |     18 |      33 |       119.73 |
| bd02          |     61 |     122 |       131.23 |
| bd03          |     66 |     151 |      1302.61 |
| bd04          |     12 |      45 |         4.80 |
| mysql         |     31 |      69 |         2.60 |
| sys           |    101 |       1 |         0.02 |
+---------------+--------+---------+--------------+
6 rows in set (0.05 sec)

A consulta abaixo retorna o tamanho total da instância do MySQL agrupados por ENGINE de tabelas de bancos de dados.

mysql> SELECT
    -> ENGINE,
    -> COUNT(table_schema) "tables",
    -> ROUND(SUM(data_length)/1024/1024,2) AS "data_size (MB)",
    -> ROUND(SUM(index_length)/1024/1024,2) AS "index_size (MB)",
    -> ROUND(SUM(data_length+index_length)/1024/1024,2) AS "total (MB)"
    -> FROM information_schema.TABLES
    -> GROUP BY ENGINE;
+--------------------+--------+----------------+-----------------+------------+
| ENGINE             | tables | data_size (MB) | index_size (MB) | total (MB) |
+--------------------+--------+----------------+-----------------+------------+
| NULL               |    100 |           NULL |            NULL |       NULL |
| CSV                |      2 |           0.00 |            0.00 |       0.00 |
| InnoDB             |    185 |        1259.23 |          120.73 |    1379.97 |
| MEMORY             |     51 |           0.00 |            0.00 |       0.00 |
| MyISAM             |     12 |         104.05 |           36.10 |     140.15 |
| PERFORMANCE_SCHEMA |     87 |           0.00 |            0.00 |       0.00 |
+--------------------+--------+----------------+-----------------+------------+
6 rows in set (0.03 sec)

A consulta abaixo retorna o tamanho total da instância do MySQL agrupados por ENGINE de tabelas de bancos de dados e por schemas de bancos de dados.

mysql> SELECT * FROM
    -> (
    -> SELECT
    -> table_schema,
    -> COUNT(table_schema) "tables",
    -> ROUND(SUM(data_length)/1024/1024,2) AS "data_size (MB)",
    -> ROUND(SUM(index_length)/1024/1024,2) AS "index_size (MB)",
    -> ROUND(SUM(data_length+index_length)/1024/1024,2) AS "total (MB)",
    -> ENGINE
    -> FROM information_schema.TABLES
    -> GROUP BY table_schema,ENGINE
    -> ) v1
    -> WHERE TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema','sys');
+---------------+--------+----------------+-----------------+------------+--------+
| table_schema  | tables | data_size (MB) | index_size (MB) | total (MB) | ENGINE |
+---------------+--------+----------------+-----------------+------------+--------+
| bd01          |     18 |         116.00 |            3.73 |     119.73 | InnoDB |
| bd02          |     60 |          49.00 |           13.19 |      62.19 | InnoDB |
| bd02          |      1 |          51.00 |           18.04 |      69.04 | MyISAM |
| bd03          |     65 |        1087.66 |          103.16 |    1190.81 | InnoDB |
| bd03          |      1 |          52.75 |           18.02 |      70.77 | MyISAM |
| bd04          |     12 |           4.30 |            0.50 |       4.80 | InnoDB |
+---------------+--------+----------------+-----------------+------------+--------+
8 rows in set (0.09 sec)

Google+

Nenhum comentário:

Postagens populares