0

Here is my table:

+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| id          | int(11)       | NO   | PRI | NULL    | auto_increment |
| tckno       | bigint(20)    | YES  |     | NULL    |                |
| adid        | mediumint(9)  | NO   |     | NULL    |                |
| soyadid     | mediumint(9)  | NO   |     | NULL    |                |
| anaadid     | mediumint(9)  | NO   |     | NULL    |                |
| babaadid    | mediumint(9)  | NO   |     | NULL    |                |
| cinsiyet    | enum('E','K') | NO   |     | NULL    |                |
| dogumyeri   | mediumint(9)  | NO   |     | NULL    |                |
| dogumtarihi | date          | NO   |     | NULL    |                |
| nufusil     | tinyint(4)    | NO   |     | NULL    |                |
| nufusilce   | smallint(9)   | NO   |     | NULL    |                |
+-------------+---------------+------+-----+---------+----------------+

Now, according to https://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html my row size is

id(4) + tckno(8) + next 4 rows (4*3) + cinsiyet(1) + dogumyeri(3) + dogumtarihi(3) + nufusil(1) + nufusilce(2) = 34 bytes

I should have 48166478 (row count) * 34 = 1637660252 bytes database. But when I navigate to mysql/data/<database name> folder, I see that <table name>.idb file is 2826960896 bytes on disk. There is a discrepency of 2826960896 bytes - 1637660252 bytes ≈ 1.1GB. What is this extra data?

Here is what I get from exporting said table

CREATE TABLE `optimumtablo` (
  `id` int(11) NOT NULL,
  `tckno` bigint(20) DEFAULT NULL,
  `adid` mediumint(9) NOT NULL,
  `soyadid` mediumint(9) NOT NULL,
  `anaadid` mediumint(9) NOT NULL,
  `babaadid` mediumint(9) NOT NULL,
  `cinsiyet` enum('E','K') NOT NULL,
  `dogumyeri` mediumint(9) NOT NULL,
  `dogumtarihi` date NOT NULL,
  `nufusil` tinyint(4) NOT NULL,
  `nufusilce` smallint(9) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `optimumtablo` ADD PRIMARY KEY (`id`);

ALTER TABLE `optimumtablo` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

This is show table status output for said table;

+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name         | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| optimumtablo | InnoDB |      10 | Compact    | 47621825 |             57 |  2759852032 |               0 |            0 |   4194304 |       48166479 | 2015-09-11 20:53:45 | NULL                | NULL                | utf8_general_ci   |     NULL |                |         |
+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
  • Can you add the CREATE TABLE statement? Are there any indexes on the table? Check show table status like '<tablename>';for data and index size as reported by MySQL. – jkavalik Sep 12 '15 at 8:03
  • @jkavalik added. – yasar Sep 12 '15 at 8:18
1

In InnoDB, each row has, at least, the following overhead:

  • A 5 byte row header,

  • A 6 byte transaction ID,

  • A 7 byte roll pointer, which points to the segment in the redo log that contains the necessary information to reconstruct an earlier version of the row

  • A variable-length bit flag vector of size CEIL(nullable_column_count/8) which indicates whether each nullable column for that row is null (in this table, this would require 1 byte per row).

https://dev.mysql.com/doc/refman/5.6/en/innodb-physical-record.html

These are going to account for much of your storage space, since your rows themselves are very short. On this particular table, the overhead is ~19 bytes per row, which is >50% of the size of the actual stored data. If you had more columns or longer columns the overhead would not grow much if at all, and would be much smaller (proportionally) when compared to the actual data.

InnoDB also tends to leave gaps at the end of each data page, reducing (ideally) the need for page splits, later. The size of the gap varies by whether rows are inserted in primary key me order, or not. With an AUTO_INCREMENT primary key, pages should fill to somewhere around 15/16ths.

https://dev.mysql.com/doc/refman/5.6/en/innodb-physical-structure.html

1

Part of the difference can be explained by:

InnoDB stores the table as B-tree inside its Primary key (primary key is clustering index for the table). And B-tree has some storage overhead (pointers on each node and internal nodes - for your row count the tree might have say 3-5 levels). + each secondary index would add some space too but you do not have any. Not sure if it accounts for the entire 1.1GB difference but it will be a big part of it.

Another thing is that the file does not automatically shrink on deletes. Only on optimize/alter which rebuilds the entire table. But it would be reported in data free column of the table status.

  • Good points, here, but I suspect the InnoDB row structures are accountable for most of the unexplained space. – Michael - sqlbot Sep 12 '15 at 19:18
  • 1
    It did seem a lot more than I expected, but I had no idea the per-row overhead you described is that much. With those 19B accounted for we get a difference of 2826960896-(48166478*51) = ~370MB (some 15%) which is much more reasonable number for a B-tree storage overhead. Definitely +1 to your answer. – jkavalik Sep 12 '15 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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