MySQL Forums
Forum List  »  General

How much space InnoDB really uses for rows?
Posted by: Dimitris Dimitropoulos
Date: October 19, 2004 07:59AM

Hi,

I have looked the MySQL documentation and still did n't manage to find a detailed
explanation about how the InnoDB engine allocate disk space to store rows.

I have seen the information at
http://dev.mysql.com/doc/mysql/en/Storage_requirements.html
http://dev.mysql.com/doc/mysql/en/Dynamic_format.html

but does not even come close to the space requirements that I am experiencing.
I am running mysql 4.1.5 on solaris
----------------------------------------------------
$mysqld --help
mysqld Ver 4.1.5-gamma-standard for sun-solaris2.8 on sparc (Official MySQL-standard binary),
----------------------------------------------------
my my.cnf file is
----------------------------------------------------
innodb_data_file_path = ibdata1:1000M
innodb_data_home_dir = /u09/mysql/ibdata
set-variable = innodb_mirrored_log_groups=1
innodb_log_group_home_dir = /u09/mysql/iblogs
set-variable = innodb_log_files_in_group=2
set-variable = innodb_log_file_size=50M
set-variable = innodb_log_buffer_size=500K
innodb_flush_log_at_trx_commit=1
innodb_log_arch_dir = /u09/mysql/iblogs
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=500M
set-variable = innodb_additional_mem_pool_size=50M
set-variable = innodb_file_io_threads=2
set-variable = innodb_lock_wait_timeout=50
set-variable = lower_case_table_names=1
----------------------------------------------------
and I start mysqld as
./mysqld --console --default-storage-engine=InnoDB --character-set-server=ascii --collation-server=ascii_bin &
----------------------------------------------------
I run the following create table statement(no indexes etc)
----------------------------------------------------
create table t1(v varchar(30)) CHARACTER SET ascii;
----------------------------------------------------
and then I run the following 4921 times:
----------------------------------------------------
insert into t1 values('12345678901234567890');


mysql> describe t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| v | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+


show table status like 't1';
+------+--------+---------+------------+------+----------------+-------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |
+------+--------+---------+------------+------+----------------+-------------+
| t1 | InnoDB | 9 | Dynamic | 4892 | 60 | 294912 |
--------------+--------------+-----------+----------------+-------------
Max_data_length | Index_length | Data_free | Auto_increment | Create_time |
-----------------+--------------+-----------+----------------+-------------
NULL | 0 | 0 | NULL | NULL |
-------------+------------+------------------+----------+----------------+
Update_time | Check_time | Collation | Checksum | Create_options |
-------------+------------+------------------+----------+----------------+
NULL | NULL | ascii_general_ci | NULL |
+-------------------------+
Comment |
+-------------------------+
| InnoDB free: 1003520 kB |


mysql> select count(1) from t1;
+----------+
| count(1) |
+----------+
| 4921 |
+----------+

---------------------------------------------------------
So the average row length is estimated to be 60 bytes and
more accurately is 294912/4921 very close to 60 bytes.
Based on the formula

3
+ (number of columns + 7) / 8
+ (number of char columns)
+ (packed size of numeric columns)
+ (length of strings)
+ (number of NULL columns + 7) / 8

it should to be something like 3+1+1+20+1=25
so were are the rest 35 bytes wasted?
We want to have very large tables and I have seen a similar disk space usage
pattern even when the number of rows increases (up to several millions)
so our disk estimates requirements of 100GB seem to go up closer to 300GB
I could n't find any messages relating to disk usage on the mailing lists etc. so
I am wondering what is it that I am doing so terribly wrong.

Thank's in advance for any replies

Options: ReplyQuote


Subject
Written By
Posted
How much space InnoDB really uses for rows?
October 19, 2004 07:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.