Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can you determine how much disk space a particular MySQL table is taking up?

Tags:

mysql

People also ask

How do you know the size of the each table of the database?

This can be accomplished easily with the following query: SELECT TABLE_SCHEMA AS `Database`, TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM information_schema.

How do you check if the MySQL DB is full?

Analyze the total space used on the DB instance (user-created databases) To find the size of each user-created database, run the following query: mysql> SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024/1024,2) "size in GB" FROM information_schema.


For a table mydb.mytable run this for:

BYTES

SELECT (data_length+index_length) tablesize
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

KILOBYTES

SELECT (data_length+index_length)/power(1024,1) tablesize_kb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

MEGABYTES

SELECT (data_length+index_length)/power(1024,2) tablesize_mb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

GIGABYTES

SELECT (data_length+index_length)/power(1024,3) tablesize_gb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

GENERIC

Here is a generic query where the maximum unit display is TB (TeraBytes)

SELECT 
    CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',SUBSTR(units,pw1*2+1,2)) DATSIZE,
    CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',SUBSTR(units,pw2*2+1,2)) NDXSIZE,
    CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',SUBSTR(units,pw3*2+1,2)) TBLSIZE
FROM
(
    SELECT DAT,NDX,TBL,IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM 
    (
        SELECT data_length DAT,index_length NDX,data_length+index_length TBL,
        FLOOR(LOG(IF(data_length=0,1,data_length))/LOG(1024)) px,
        FLOOR(LOG(IF(index_length=0,1,index_length))/LOG(1024)) py,
        FLOOR(LOG(IF(data_length+index_length=0,1,data_length+index_length))/LOG(1024)) pz
        FROM information_schema.tables
        WHERE table_schema='mydb'
        AND table_name='mytable'
    ) AA
) A,(SELECT 'B KBMBGBTB' units) B;

Give it a Try !!!


Quick bit of SQL to get the top 20 biggest tables in MB.

SELECT table_schema, table_name,
  ROUND((data_length+index_length)/POWER(1024,2),2) AS tablesize_mb
FROM information_schema.tables
ORDER BY tablesize_mb DESC LIMIT 20;

Hope that's useful to somebody!


This won't be accurate for InnoDB tables. The size on disk is actually bigger than that reported via query.

Please see this link from Percona for more information.

https://www.percona.com/blog/2008/12/16/how-much-space-does-empty-innodb-table-take/


In linux with mysql installed by default:

[you@yourbox]$ ls -lha /var/lib/mysql/<databasename>

based on NIXCRAFT's mysql db location


Based on the RolandMySQLDBA's answer I think we can use the above to get the size of each schema in a table:

SELECT table_schema, SUM((data_length+index_length)/power(1024,1)) tablesize_kb 
    FROM information_schema.tables GROUP BY table_schema;

Really liked it!


Taken from How do I check how much disk space my database is using?

You can check MySQL table size either by looking at phpMyAdmin in your control panel by clicking on the database name in the left frame and reading the size for the tables in there in the right frame.

The below query will as well help to get the same information in bytes

select SUM(data_length) + SUM(index_length) as total_size 
from information_schema.tables 
where table_schema = 'db_name' 
and table_name='table_name';