9

I'm trying to figure out storage requirements for different storage engines. I have this table:

CREATE TABLE  `mytest` (
  `num1` int(10) unsigned NOT NULL,
  KEY `key1` (`num1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

When I insert some values and then run show table status; I get the following:

+----------------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| 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 |
+----------------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| mytest         | InnoDB |      10 | Compact    | 1932473 |             35 |    67715072 |                0 |     48840704 |   4194304 |           NULL | 2010-05-26 11:30:40 | NULL                | NULL       | latin1_swedish_ci |     NULL |                |         |

Notice avg_row_length is 35. I am baffled that InnoDB would not make better use of space when I'm just storing a non-nullable integer.

I have run this same test on myISAM and by default myISAM uses 7 bytes per row on this table. When I run

ALTER TABLE mytest MAX_ROWS=50000000, AVG_ROW_LENGTH = 4;

causes myISAM to finally correctly use 5-byte rows.

When I run the same ALTER TABLE statement for InnoDB the avg_row_length does not change.

Why would such a large avg_row_length be necessary when only storing a 4-byte unsigned int?

Air
  • 8,274
  • 2
  • 53
  • 88
alessandro ferrucci
  • 1,261
  • 2
  • 24
  • 48
  • I just read that InnoDB uses the tablespace for both the data and the index. this makes sense and seems like this would be the reason why I'm seeing such a large avg_row_length... maybe. I also found out that each leaf node stores the transaction ID and rollback pointer. Well I'm not making use of transactions per-se and so have no use for this data. is there any way to not store these values? Any way I can use InnoDB but make a little bit better use of the storage? thanks! – alessandro ferrucci May 26 '10 at 15:50
  • @alessandro: yes, transaction support does add some overhead too. The fact that you're not making use of transactions does not mean they are not used: for instance, a thread killed during a long `UPDATE` operation will rollback correctly in `InnoDB` but not in `MyISAM`. Transaction support is the whole point of using `InnoDB`, if you don't need it, just use `MyISAM`. – Quassnoi May 26 '10 at 16:15
  • @Quassnoi: I got the impression that MyISAM is not as "mature" or production ready as InnoDB ... maybe it's an unfounded fear. Are there any hardships that MyISAM brings to the table when it comes to backing up databases besides the fact that MyISAM requires a full table lock to guarantee consistency. I do not require transactions and I will be storing lots of data. Are there any known issues with MyISAM that would cause you not to use it? – alessandro ferrucci May 26 '10 at 16:30
  • @alessandro: what exactly do you mean by "do not require transactions?" Which part of http://en.wikipedia.org/wiki/ACID you don't need? – Quassnoi May 26 '10 at 16:34
  • This will be a read-only database once I have it loaded. There will be a one-time massive load process, after that it will be all reads, all the time. I will do a 1-time backup. The only reason we are not seriously thinking about MyISAM is because we've never used it before in a production environment, we have always used InnoDB. – alessandro ferrucci May 26 '10 at 17:05
  • @alessandro: for a read-only database `MyISAM` will most probably be a better solution. – Quassnoi May 26 '10 at 21:45
  • @Quassnoi: that is what I was thinking, thank you. Have you deployed a MyISAM database in production and are there any hints/tips that you can recommend if any? thanks again. – alessandro ferrucci May 26 '10 at 22:56
  • 1
    @alessandro: yes, I have deployed hundreds of MyISAM databases in production. As for the hints, there are too many of them to recommend in a single post without knowing you requirements. Read http://mysqlperformanceblog.com for tips on system administration and my blog, http://explainextended.com/category/mysql/, on efficient `SQL`. – Quassnoi May 26 '10 at 23:06
  • (A decade later...) InnoDB has improved. Most operations with MyISAM are no faster than with InnoDB. MyISAM is probably going away. I don't recommend using MyISAM for anything. – Rick James Oct 18 '20 at 19:41

4 Answers4

11

InnoDB tables are clustered, that means that all data are contained in a B-Tree with the PRIMARY KEY as a key and all other columns as a payload.

Since you don't define an explicit PRIMARY KEY, InnoDB uses a hidden 6-byte column to sort the records on.

This and overhead of the B-Tree organization (with extra non-leaf-level blocks) requires more space than sizeof(int) * num_rows.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • I know this answer is more then 10 years old, but still I wonder. The mentioned discrepancy from the OP is 30 Byte. So does it mean `6` byte takes hidden column, `4` the integer and the remaining `25` byte belong all to the `B-Tree`? Isn't `71%` too much for the `B-Tree`? – Adam Oct 18 '20 at 18:38
  • 1
    @Adam: `avg_row_length` is number of pages * page size / number of records. The number of pages (relative to the number of records) depends on the B-Tree depth and fragmentation. The fragmentation happens with deletes, inserts out of PK order or updates which increase the row size (the latter two operations cause page splits). There is also some small intrinsic overhead caused by paged storage (page headers etc.) If your MERGE_THRESHOLD is low enough and you are doing lots of deletes on your table, 71% overhead is a plausible value – Quassnoi Oct 18 '20 at 18:55
4

Here is some more info you might find useful.

InnoDB allocates data in terms of 16KB pages, so 'SHOW TABLE STATUS' will give inflated numbers for row size if you only have a few rows and the table is < 16K total. (For example, with 4 rows the average row size comes back as 4096.)

The extra 6 bytes per row for the "invisible" primary key is a crucial point when space is a big consideration. If your table is only one column, that's the ideal column to make the primary key, assuming the values in it are unique:

CREATE TABLE `mytest2`
       (`num1` int(10) unsigned NOT NULL primary key)
ENGINE=InnoDB DEFAULT CHARSET=latin1;

By using a PRIMARY KEY like this:

  1. No INDEX or KEY clause is needed, because you don't have a secondary index. The index-organized format of InnoDB tables gives you fast lookup based on the primary key value for free.
  2. You don't wind up with another copy of the NUM1 column data, which is what happens when that column is indexed explicitly.
  3. You don't wind up with another copy of the 6-byte invisible primary key values. The primary key values are duplicated in each secondary index. (That's also the reason why you probably don't want 10 indexes on a table with 10 columns, and you probably don't want a primary key that combines several different columns or is a long string column.)

So overall, sticking with just a primary key means less data associated with the table + indexes. To get a sense of overall data size, I like to run with

set innodb_file_per_table = 1;

and examine the size of the data/database/*table*.ibd files. Each .ibd file contains the data for an InnoDB table and all its associated indexes.

To quickly build up a big table for testing, I usually run a statement like so:

insert into mytest
select * from mytest;

Which doubles the amount of data each time. In the case of the single-column table using a primary key, since the values had to be unique, I used a variation to keep the values from colliding with each other:

insert into mytest2
select num1 + (select count(*) from mytest2) from mytest2;

This way, I was able to get average row size down to 25. The space overhead is based on the underlying assumption that you want to have fast lookup for individual rows using a pointer-style mechanism, and most tables will have a column whose values serve as pointers (i.e. the primary key) in addition to the columns with real data that gets summed, averaged, and displayed.

Yaroslav
  • 6,476
  • 10
  • 48
  • 89
Max Webster
  • 181
  • 6
1

IN addition to Quassnoi's very fine answer, you should probably try it out using a significant data set.

What I'd do is, load 1M rows of simulated production data in, then measure the table size and use that as a guide.

That's what I've done in the past anyway

MarkR
  • 62,604
  • 14
  • 116
  • 151
  • Yes, thank you MarkR, I had done this with a data set of 50M rows. InnoDB used more than 3 times more space at 3GIGs – alessandro ferrucci May 27 '10 at 02:12
  • Based on my study (see link above), this is typical; you can use less space by using the plugin and turning compression on. See my blog post for some data. – MarkR May 27 '10 at 15:01
0

MyISAM

MyISAM, except in really old versions, uses a 7-byte "pointer" for locating a row, and a 6-byte pointer inside indexes. These defaults lead to a huge max table size. More details: http://mysql.rjweb.org/doc.php/limits#myisam_specific_limits . The kludgy way to change those involves the ALTER .. MAX_ROWS=50000000, AVG_ROW_LENGTH = 4 that you discovered. The server multiplies those values together to compute how many bytes the data pointer needs to be. Hence, you stumbled on how to shrink the avg_row_length.

But you actually needed to declare a table with fewer than 7 bytes to hit it! The pointer size shows in multiple places:

  • Free space links in the .MYD default to 7 bytes. So, when you delete a row, a link is provided to the next free spot. That link needs to be 7 bytes (by default), hence the row size was artificially extended from the 4-byte INT to make room for it! (There are more details having to do with whether the column is NULLable , etc.

  • FIXED vs DYNAMIC row -- When the table is FIXED size, the "pointer" is a row number. For DYNAMIC, it is a byte offset into the .MYD.

  • Index entries must also point to data rows with a pointer. So your ALTER should have shrunk the .MYI file as well!

There are more details, but MyISAM is likely to go away, so this ancient history is not likely to be of concern to anyone.

InnoDB

https://stackoverflow.com/a/64417275/1766831

Rick James
  • 135,179
  • 13
  • 127
  • 222