☰

Como analisar o espaço utilizado por tabelas, índices

tag-icon • tag-icon mysql
Como analisar o espaço utilizado por tabelas, índices

Temos um banco de dados que cresceu organicamente no ano passado. Como podemos analisar rapidamente o espaço utilizado por cada mesa? Gostaríamos de considerar os índices também.

Responder1

Quando estou usando tabelas innodb em meus bancos de dados, gosto de usar oinnodb_file_per_tablecontexto. Isso me permite ter uma ideia rápida do que está acontecendo com ls como o caossugerido.

Esta declaração pode lhe dar uma boa ideia sobre quanto espaço você está usando.

use information_schema;
SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `TABLE_ROWS`, `DATA_LENGTH`, 
       `INDEX_LENGTH`, `DATA_FREE` FROM `TABLES`

Responder2

Tenho algumas perguntas malucas para você usar. Escrevi isso há dois anos e ainda os uso hoje. Experimente-os !!!

Aqui está uma consulta para somar todos os dados em todos os mecanismos de armazenamento

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(
B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),
'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),
17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

Aqui está uma consulta para somar todos os dados em todos os bancos de dados

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(FORMAT(SXSize/
POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),
'B') "Total Size" FROM (SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,
SUM(XSize) SXSize,SUM(TSize) STSize FROM (SELECT table_schema DB,
data_length DSize,index_length XSize,data_length+index_length TSize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema')) AAA GROUP BY DB WITH ROLLUP) AA,
(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

Aqui está uma consulta para somar todos os dados em todos os bancos de dados agrupados por mecanismo de armazenamento

SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score,IF(ISNULL(table_schema)=1,
'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,
CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),
' ',SUBSTR(' KMGTP',pw+1,1),'B') DataSize,CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,CONCAT(LPAD(REPLACE(FORMAT(B.TSize/
POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') TableSize
FROM (SELECT table_schema,engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema') AND
engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 3 pw) A) AA ORDER BY schemaname,schema_score,engine_score;

CAVEAT: Você notará que no final de todas essas consultas há um SELECT embutido que se parece com isto:(SELECT 3 pw)

O número 3 faz com que o relatório saia em GigaBytes.
Na verdade, aqui está uma lista dos números e unidades do relatório que ele usa:

  • (SELECT 0 pw)Relatórios em Bytes
  • (SELECT 1 pw)Relatórios em Kilobytes
  • (SELECT 2 pw)Relatórios em Megabytes
  • (SELECT 3 pw)Relatórios em Gigabytes
  • (SELECT 4 pw)Relatórios em Terabytes
  • (SELECT 5 pw)Relatórios em Petabytes (nunca usei essa configuração, mas ela está lá caso você alcance esse número algum dia)

Aproveitar !!!

Responder3

Mysql tem algumas funcionalidades integradas

use <datbase name>;
show table status;

Responder4

Tente usar uma ferramenta como esta para visualizar sua instância do MySQL em um formato fácil de ler:http://www.aquafold.com/index-mysql.html

informação relacionada