45

I am finding what is the best way to estimate size of a table for that I have studied lot of blogs and forums but unable to find any accurate answer

For example, we have a table City with InnoDB engine,lets say in future (in next 1 year) it will have 1 million of records so what will be the estimated data size and index size of that table in this period.

mysql> desc City;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.03 sec)

UPDATE

What will be the estimated upper bound (Max size of table) with 1 million records and how can we estimate it.

1
  • This is great. but is it possible to get the index size column wise. What is mean is if you have a table with (say) 5 columns indexed. Can we get the index size of each one of them? I will ask this as another question. thanks
    – Sushil
    Sep 11, 2013 at 10:04

5 Answers 5

67

Given the table description, I see

  • 66 bytes per row of data
  • 4 bytes per row for the primary key
  • 7 bytes per row for country code index
    • 3 bytes for the country
    • 4 bytes for Clustered Key attached to the country code
  • Total of 77 bytes of data and keys
  • This does not factoring housekeeping for BTREEs or Tablespace Fragmentation

For a million rows, that would 77,000,000 bytes (73.43 MB)

As for measuring the table, for a given table mydb.mytable, you can run this query

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

To measure all tables grouped by Database and 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;

Run these queries and you can track changes in database/engine disk usage.

Give it a Try !!!

5
  • The CHAR lengths need to be multiplied by 3 if you have CHARSET utf8. The entire overhead can be estimated by doubling or tripling the computation.
    – Rick James
    Aug 18, 2015 at 23:27
  • @RolandoMySQLDBA , do you know if is possible to calculate the "real" row size of a table with the objective to compare with the real size (compressed table) and get the ratio of compress?
    – ceinmart
    Jun 19, 2019 at 20:26
  • @ceinmart innodb_page_size is fixed (16K or 16384 by default) and becomes the boundary where rows and grouped or split. Changing innodb_page_size can alter storage of data for good or bad.Based on the how filled or sparse a row is (especially with the presence of TEXT/BLOB/VARCHAR). At best, you should compare the the size of .ibd file to what the schema report to estimate a ratio. You may also need to perform a NULL ALTER TABLE (ALTER TABLE ... ENGINE=InnoDB;)to get an accurate ratio. Effort may not be worth it. Jun 19, 2019 at 21:13
  • @ceinmart Keep in mind hat changing innodb_page_size is not a table-by-table setting. You would need to do a full export of the data (See mariadb.com/kb/en/library/how-to-change-innodb_page_size) Jun 19, 2019 at 21:15
  • @RolandoMySQLDBA: Any idea how to know amount of data added to all tables of a database daily based on certain column like created_at ? Will be useful for telling beforehead when to add more capacity to server once we know how much data amount is added daily.
    – Sarfraz
    Jul 28, 2021 at 7:43
5

If you are using InnoDB tables, you can get the size for data/individual indexes from mysql.innodb_index_stats. The 'size' stat contains the answer, in pages, so you have to multiply it by the page-size, that is 16K by default.

select database_name, table_name, index_name, stat_value*@@innodb_page_size
from mysql.innodb_index_stats where stat_name='size';

The index PRIMARY is the data itself.

2
  • 1
    This assumes you have data in the table; seems like the OP wants to estimate before populating.
    – Rick James
    Nov 12, 2019 at 6:25
  • could you elaborate on "The index PRIMARY is the data itself.", please? I thought that only accounted for the primary index size
    – asgs
    Aug 1, 2022 at 13:24
1

If you don't have data yet, here are some tips. The following applies to InnoDB. (MyISAM is much simpler, and smaller.)

Don't use CHAR for variable-length columns. What CHARACTER SET are you using? Ascii needs one byte per character; utf8mb4 needs between 1 and 4.

4 bytes per INT
35 for CHAR(35), if ascii or latin1; varchar is probably less
3 for the country code; it is fixed length
etc

Total = about 80 bytes.

Multiply the 80 by between 2 and 3 to account for various overheads. Most likely the 1M row table will be between 160MB and 240MB.

To measure a single index, for say CountryCode of 3 bytes:

3 bytes data
4 bytes for the PK (implicitly included with any secondary key)
25 bytes basic overhead
32 total
times 1.5 -- overhead for BTree that was randomly inserted into
48MB -- total for 1M rows.

Notes:

  • Only the leaf nodes (of BTrees) need to be computed; the overhead for the non-leaf nodes is typically 1%.

  • The PRIMARY KEY is "clustered" with the data, so there is no need to compute it.

  • If you do not have an explicit PK, then you need to add 6 bytes to the row size to allow for the fabricated PK.

  • ROW_FORMAT = COMPRESSED gives you about a 2:1 shrinkage. (This is not as good as typical zip (etc) compression rate of 3:1.)

  • SHOW TABLE STATUS LIKE "tablename"; is the quick way to compute the 'actual' size. See Data_length for data and PK; Index_length for secondary indexes, and Data_free for some other stuff.

  • It is rare for Index_length to exceed Data_length. However it is not "wrong" for that to happen.

0
SELECT  Table_NAME "tablename",
           data_length   "table data_length in Bytes",
           index_length  "table index_length in Bytes",
           data_free  "Free Space in Bytes"
    FROM  information_schema.TABLES  where  Table_schema = 'databasename';

by executing this query you can get size used for Data and Index of a table , You can check this size against # of rows and predict for 1 million rows

2
  • 1
    I am not sure but will this give some what accurate results ? have you tested this ever ? Jul 10, 2013 at 12:18
  • Actually I am testing this query result periodically to see growth (%) w.r.t. size Jul 10, 2013 at 12:40
0

It's tedious. But the details are in the docs.

To be as accurate as possible, which is rarely necessary, you'll need to read about the table structure and index structure, too.

If I were in your shoes, I'd build the table, populate it with a million rows of test data, and measure the change in size. Depending on your application, you might need to take the size of transaction log files into account, too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.