List:General Discussion« Previous MessageNext Message »
From:Leong Jern-Kuan Date:April 30 2003 1:31am
Subject:RE: Row length surprising
View as plain text  
You could check ur my.cnf

http://www.mysql.com/doc/en/InnoDB_start.html

My guess is that you are using a huge configuration,
thus the initial size is 2.5G

Hope this helps.

-----Original Message-----
From: Ware Adams [mailto:rwalists@stripped]
Sent: Wednesday, April 30, 2003 12:34 AM
To: mysql@stripped
Subject: Row length surprising


I have an InnoDB table that is about 60 million records long with what I
thought was a 42 byte row.  However show status shows the rows are 42
bytes, and the table takes up a lot more space than I expected.

Here's the show create table output:

CREATE TABLE table_name (
  dt date NOT NULL default '0000-00-00',
  hr tinyint(4) NOT NULL default '0',
  highlight tinyint(4) default NULL,
  id mediumint(8) unsigned NOT NULL default '0',
  price float default '0',
  PRIMARY KEY  (dt,hr,id),
  KEY hr (hr,id)
) TYPE=InnoDB

which I figured would give a row size of:

3 bytes for a date
1 byte for a tinyint
1 byte for a tinyint
3 bytes for a mediumint
4 bytes for a float

=12 bytes of data per row

But show table status gives:

+--------+--------+------------+----------+----------------+-------------+
| Name   | Type   | Row_format | Rows     | Avg_row_length | Data_length |
+--------+--------+------------+----------+----------------+-------------+
| rt_lmp | InnoDB | Fixed      | 63213369 |             42 |  2691694592 |
+--------+--------+------------+----------+----------------+-------------+

A much longer row length and data_length than I expected (I know the
indexes take up more, but I figured data length would be more like 750 MB
instead of 2.5 GB.

What's taking up the extra space?

Thanks,
Ware

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
Row length surprisingWare Adams29 Apr
  • Re: Row length surprisingJeremy Zawodny2 May
    • Re: Row length surprisingWare Adams2 May
RE: Row length surprisingLeong Jern-Kuan30 Apr
  • RE: Row length surprisingWare Adams30 Apr