0

I'm doing some benchmarks and I found a strange thing - real table size is in 7 times bigger than estimated size

I created a table with 1 int column (4 bytes)

CREATE TABLE `t_int` (
  `c_int` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And inserted 57M records with value 1. I expected to get table 228Mb (4 bytes * 57M records), but got 1.8Gb

           Name: t_int
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 57391085
 Avg_row_length: 36
    Data_length: 2089795584
Max_data_length: 0
   Index_length: 0
      Data_free: 5242880
 Auto_increment: NULL
    Create_time: 2017-01-12 22:42:23
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 

optimize tables has not changed the results

Why is that?

MySQL 5.7.11

  • is it 7 times You compare with real disk space? or it statistics from benchmark? – a_vlad Jan 12 '17 at 23:23
  • It's statistic from show table status;, I don't have access to real db file as I use AWS RDS – Alexey Jan 12 '17 at 23:31
  • if You make test as described - yes, it possible, will try now answer - why – a_vlad Jan 12 '17 at 23:35
  • most DBMS, not 100% sure about mysql, will add and generate a hidden unique index to unindexed or indexeds withour either clustered or unique tables, otherwise selecting just 1 record would be impossible. they will also always add the clustered/unique/primary keys to all non-unique indexes. try changing that c_int to unique and do 1,2,3 ..etc see if the size gets smaller. innodb is particularily. Edit: from innodb page If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. – Rostol Jan 13 '17 at 1:38
  • @Rostol - For InnoDB, it is a 6-byte number. – Rick James Jan 13 '17 at 6:06
1

I will give this a try :

So as per the below snapshot taken from this link : http://dev.mysql.com/doc/refman/5.7/en/show-table-status.html

For InnoDB Data_length = Clustered Index size in pages x InnoDb page size

enter image description here

Now according to below screen shot taken from this link : http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html the default InnoDb page size should be equal to 16 kbenter image description here

Now the individual index size can be found using the below query.

Source 1,2

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

So taking only the index size values for your table from above output and considering your InnoDb page size to be equal to 16 kb you can calculate and see the actual size or data_length.

Note: I don't know why or how but product of you expected output 228MB(233472 Bytes as calculated per the inputs 4 bytes * 57M records) with maximum row length for default innodb page size of 16 Kb which is 8000 bytes gives 1867776000 bytes which when converted to Gb is 1.867776 Gb which is almost equal to size you said you are getting ie. 1.8 Gb .

Note 2: Please also read http://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html for learning further about how row length can vary depending on the Row Formats,and if no primary key is defined as already indicated by @Rick James in his answer.

  • 1
    Here is another link that might be useful for calculating index sizes aadant.com/blog/2014/02/04/… – Shivam Kumar Jan 13 '17 at 1:12
  • I don't see how the 8000 is relevant. – Rick James Jan 13 '17 at 6:17
  • I think the calculation is being done as avg_row_length(36 here) X Rows(57391085) which gives output close to Data_length and vey near to Data_length if we add up the data_free value to it, as per the question here stackoverflow.com/questions/2914492/… the user corrected the row length in myISAM to use 5 bytes by using 'ALTER TABLE mytest MAX_ROWS=50000000, AVG_ROW_LENGTH = 4;' but the same query failed when used with Innodbthat makes me think that 8000 is relevant as its the row length for 16kb Innodb page see 2nd screenshot. – Shivam Kumar Jan 13 '17 at 14:46
  • The 8000 comes into play when the row has a lot of VARCHARs; there is a concrete example in the manual. Just one INT comes nowhere near the 8000. – Rick James Jan 13 '17 at 16:18
  • MAX_ROWS with MyISAM controls the size of the pointer into the data. It defaults to 6 bytes, but can be anything up to 7. This used to be important in asking for more than 4B rows (and the default pointer was 4 bytes). – Rick James Jan 13 '17 at 16:20
1

For each row InnoDB use additional disk space, such as:

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

Records in the clustered index contain fields for all user-defined columns. In addition, there is a 6-byte transaction ID field and a 7-byte roll pointer field.

so, when You test it as described - it will use 30+- bytes per row, and this is will be similar with result

additional source of information:

  • Also from notes section at dev.mysql.com/doc/refman/5.7/en/show-table-status.html For MEMORY tables, the Data_length, Max_data_length, and Index_length values approximate the actual amount of allocated memory. The allocation algorithm reserves memory in large amounts to reduce the number of allocation operations. – Shivam Kumar Jan 12 '17 at 23:51
  • MEMORY also turns VARCHAR into CHAR, thereby taking a lot more space than predicted. – Rick James Jan 13 '17 at 6:21
1

There is a lot of overhead, and the overhead dominates when all you have is on 4-byte column:

Note: The following refers only to InnoDB tables.

  • Length of the column - probably 1 byte in this case.
  • Overhead for each row - I've seen 29 bytes, but perhaps it is less.
  • NULL - one byte for each 8 nullable columns (I think).
  • BTree 'waste' - At least 1/16th of each block, 42% when randomly inserting rows. (OPTIMIZE TABLE would temporarily clean up the 42%.)
  • PRIMARY KEY - Since you did not specify an explicit PK, one was generated for you. It is 6 bytes per row.
  • Allocation units - Once the table gets more than tiny, space is allocated in chunks of 8(?) megabytes.
  • Non-leaf nodes for the PK - on the order of 1%.
  • Deleted rows may or may not recover space.
  • MVCC and transactions lead to multiple copies of rows; this may or may not be recovered from.
  • Large TEXT and BLOB columns are stored off-page; this gets quite complex to predict.
  • Maybe more.

So, I am surprised that you got only 7x. But it is a very unrealistic table. I have measured many InnoDB tables. Most are between 2x and 3x the expected size.

The overhead is there for a variety of good reasons, usually related to performance.

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.