How to monitor MySQL space?

Question

I downloaded a VM image of a web application that uses MySQL.

How can I monitor its space consumption and know when additional space must be added?

score 30 · Accepted Answer · edited Jun 20 '20 at 09:12

I have some great big queries to share:

Run this to get the Total MySQL Data and Index Usage By Storage Engine

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;

Run this to get the Total MySQL Data and Index Usage By Database

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);

Run this to get the Total MySQL Data and Index Usage By Database and Storage Engine

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;

CAVEAT

In each query, you will see (SELECT 3 pw). The pw stands for the Power Of 1024 to display the results.

(SELECT 0 pw) will Display the Report in Bytes
(SELECT 1 pw) will Display the Report in KiloBytes
(SELECT 2 pw) will Display the Report in MegaBytes
(SELECT 3 pw) will Display the Report in GigaBytes
(SELECT 4 pw) will Display the Report in TeraBytes
(SELECT 5 pw) will Display the Report in PetaBytes (please contact me if you run this one)

Here is a report query with a little less formatting:

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;

Trust me, I made these queries over 4 years ago and still use them today.

UPDATE 2013-06-24 15:53 EDT

I have something new. I have changed the queries so that you do not have to set the pw parameter for different unit displays. Each unit display is calculated for you.

Report By Storage Engine

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;

Report By Database

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;

Report By Database / Storage Engine

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;

You probably should edit your question and put 4 leading spaces in front of each line that should show up as code. — 700 Software, Dec 18 '10 at 17:34
@Bhathiya I use 17 because of the output of the `FORMAT` function. The number one trillion has 13 digits. Running `FORMAT(1000000000000,0)` displays `1,000,000,000,000`. Notice that 4 commas are added. Thus, I use 17 to accommodate small display columns next to larger display columns, giving the report a uniform look. You can experiment with width values other then 17 since it does not affect disk space computation. — RolandoMySQLDBA, Jun 09 '13 at 11:31
I've tried queries like these, and I'm trying these specific queries, and I've always run into the issue that they take a long time to run. Is there any way to optimize these queries to run faster? — JDS, Jun 24 '13 at 19:26
@JDS I have two questions for you: 1) is all your data InnoDB ? 2) Are you using innodb_file_per_table ? — RolandoMySQLDBA, Jun 24 '13 at 19:37
1) Yes. 2) Yes. "Long time to run" is three to five minutes for a medium (10GB on disk) database — JDS, Jun 25 '13 at 21:14
@JDS there is the problem. If all the tables were inside ibdata1 and innodb_file_per_table was disabled, the query would be dynamite fast because of polling the data dictionary and data/index pages inside one file. In your situation, each access of a table with ibdata1's data dictionary results in a file handle being open on each corresponding `.ibd` file. I have a client DB with 112,000+ tables (900GB) and it takes 2 hours. I have another client with 41000+ tables (2.5TB) and it takes 15 seconds (Not a Typo, I said 15 sec). The difference was innodb_file_per_table. — RolandoMySQLDBA, Jun 25 '13 at 21:17
ok, thanks. file_per_table is the only tenable solution for us, because of the way we manage databases for our customers. if innodb was able to release space back to the OS, we'd probably use a monolithic data file. thanks — JDS, Jun 26 '13 at 14:06

score 4 · Answer 2 · answered Oct 08 '08 at 20:24

If only MySQL is available, use the SHOW TABLE STATUS command, and look at the Data_length column for each table, which is in bytes.

If you have other languages available on the machine, a script in any of them that runs regularly (cron), checks disk free space or size of database directory, and updates you over e-mail or otherwise. There are far too many options to suggest a particular solution -- it depends on your situation.

score 1 · Answer 3 · answered May 12 '14 at 08:01

1

You can refer MONyog which has Disk Info feature which lets you find out Disk space analysis at server level, Database level and at Table level

answered May 12 '14 at 08:01

Glenn McKay

11
1

1

Hi, as this is your first answer, please first check if your answer already exists, as [it does](http://stackoverflow.com/a/23289446/2844473). In that case it is better to __upvote__ that answer instead of saying the exact same thing. – MarijnS95 May 12 '14 at 08:29

score 0 · Answer 4 · answered Oct 08 '08 at 21:05

0

For MyISAM tables, I usually check the size of the /var/lib/mysql/mydatabasename/ directory. InnoDB tables use monolithic files, so you have to use SHOW TABLE STATUS.

answered Oct 08 '08 at 21:05

Javier

60,510
8
78
126

score 0 · Answer 5 · answered Nov 08 '15 at 08:39

0

du -s /var/lib/mysql/* | sort -nr

Result

34128   /var/lib/mysql/db_name1
33720   /var/lib/mysql/db_name2
29744   /var/lib/mysql/db_name3
26624   /var/lib/mysql/db_name4
16516   /var/lib/mysql/db_name5

Thsi will show as descending order in kb

answered Nov 08 '15 at 08:39

wpdevramki

171
1
2

score 0 · Answer 6 · answered Nov 19 '10 at 20:23

Since you have VM and you don't really care how the space is used, I think the simplest way is to check the size of MySQL data dir. By default it is /var/lib/mysql. Also it`ll be nice to cleanup the mysql binary logs (if possible) before checking data dir size.

How to monitor MySQL space?

6 Answers6

UPDATE 2013-06-24 15:53 EDT

Linked