mysql+空间监控_如何监控MySQL空间？

我有一些伟大的大问题要分享：

运行它来获取存储引擎的总MySQL数据和索引使用量

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','performance_schema') AND

engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,

(SELECT 3 pw) A ORDER BY TSize;

运行它来获取数据库的MySQL数据和索引总数

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','performance_schema')) AAA

GROUP BY DB WITH ROLLUP) AA,(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

运行它来获取数据库和存储引擎的MySQL数据和索引总数

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','performance_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;

警告

在每个查询中,您将看到(SELECT 3 pw). pw代表1024的幂显示结果.

>(SELECT 0 pw)将以字节显示报告

>(SELECT 1 pw)将以千字节显示报告

>(SELECT 2 pw)将以MegaBytes显示报告

>(SELECT 3 pw)将以GigaBytes显示报告

>(SELECT 4 pw)将以TeraBytes显示报告

>(SELECT 5 pw)将以PetaBytes显示报告(如果您运行此操作,请与我联系)

这是一个报告查询,格式化少一些：

SELECT IFNULL(db,'Total') "Database",

datsum / power(1024,pw) "Data Size",

ndxsum / power(1024,pw) "Index Size",

totsum / power(1024,pw) "Total"

FROM (SELECT db,SUM(dat) datsum,SUM(ndx) ndxsum,SUM(dat+ndx) totsum

FROM (SELECT table_schema db,data_length dat,index_length ndx

FROM information_schema.tables WHERE engine IS NOT NULL

AND table_schema NOT IN ('information_schema','mysql')) AA

GROUP BY db WITH ROLLUP) A,(SELECT 1 pw) B;

相信我,4年前我提出了这些疑问,今天仍然使用它们.

更新2013-06-24 15:53 EDT

我有新的东西我已经更改了查询,因此您不必为不同的单位显示设置pw参数.每个单位显示是为您计算的.

存储引擎报告

SELECT

IFNULL(ENGINE,'Total') "Storage Engine",

LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',

SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",

LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',

SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",

LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',

SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"

FROM

(

SELECT ENGINE,DAT,NDX,TBL,

IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3

FROM

(SELECT *,

FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,

FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,

FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz

FROM

(SELECT

ENGINE,

SUM(data_length) DAT,

SUM(index_length) NDX,

SUM(data_length+index_length) TBL

FROM

(

SELECT engine,data_length,index_length FROM

information_schema.tables WHERE table_schema NOT IN

('information_schema','performance_schema','mysql')

AND ENGINE IS NOT NULL

) AAA GROUP BY ENGINE WITH ROLLUP

) AAA ) AA) A,(SELECT ' BKBMBGBTB' units) B;

数据库报告

SELECT

IFNULL(DB,'Total') "Database",

LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',

SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",

LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',

SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",

LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',

SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"

FROM

(

SELECT DB,DAT,NDX,TBL,

IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3

FROM

(SELECT *,

FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,

FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,

FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz

FROM

(SELECT

DB,

SUM(data_length) DAT,

SUM(index_length) NDX,

SUM(data_length+index_length) TBL

FROM

(

SELECT table_schema DB,data_length,index_length FROM

information_schema.tables WHERE table_schema NOT IN

('information_schema','performance_schema','mysql')

AND ENGINE IS NOT NULL

) AAA GROUP BY DB WITH ROLLUP

) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;

数据库/存储引擎报告

SELECT

IF(ISNULL(DB)+ISNULL(ENGINE)=2,'Database Total',

CONCAT(DB,' ',IFNULL(ENGINE,'Total'))) "Reported Statistic",

LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',

SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",

LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',

SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",

LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',

SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"

FROM

(

SELECT DB,ENGINE,DAT,NDX,TBL,

IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3

FROM

(SELECT *,

FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,

FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,

FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz

FROM

(SELECT

DB,ENGINE,

SUM(data_length) DAT,

SUM(index_length) NDX,

SUM(data_length+index_length) TBL

FROM

(

SELECT table_schema DB,ENGINE,data_length,index_length FROM

information_schema.tables WHERE table_schema NOT IN

('information_schema','performance_schema','mysql')

AND ENGINE IS NOT NULL

) AAA GROUP BY DB,ENGINE WITH ROLLUP

) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;