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)
Nenhum comentário:
Postar um comentário