|
Home > Archive > MySQL ODBC Connector > September 2005 > Avg row length is varying a lot from oracle to MySQL
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Avg row length is varying a lot from oracle to MySQL
|
|
| Sujay Koduri 2005-09-22, 8:24 pm |
| ------ _=_NextPart_001_01C5
BFAB.11D05978
Content-Type: text/plain
hi ,,
we are converting our oracle DB to MySQL DB. One problem i see is that the
abg row length in MySQL is much higher compared to that of Oracle.
In oracle it is around 180 bytes and in MySQL it is around 686 bytes. So as
a result, MySQL is taking more space to store the same number of records.
Can someone please explain me if this is the intended behaviour or i am
missing out something. I am also including the o/p of desc table_name of the
same table on both the databases.
This is a bit urgent. So any help is greatly appreciated.
ID NOT NULL VARCHAR2(50)
H0 NUMBER
H1 NUMBER
H2 NUMBER
H3 NUMBER
H4 NUMBER
H5 NUMBER
H6 NUMBER
H7 NUMBER
H8 NUMBER
H9 NUMBER
H10 NUMBER
H11 NUMBER
H12 NUMBER
H13 NUMBER
H14 NUMBER
H15 NUMBER
H16 NUMBER
H17 NUMBER
H18 NUMBER
H19 NUMBER
H20 NUMBER
H21 NUMBER
H22 NUMBER
H23 NUMBER
D1 NUMBER
D2 NUMBER
D3 NUMBER
D4 NUMBER
D5 NUMBER
D6 NUMBER
D7 NUMBER
D8 NUMBER
D9 NUMBER
D10 NUMBER
D11 NUMBER
D12 NUMBER
D13 NUMBER
D14 NUMBER
D15 NUMBER
D16 NUMBER
D17 NUMBER
D18 NUMBER
D19 NUMBER
D20 NUMBER
D21 NUMBER
D22 NUMBER
D23 NUMBER
D24 NUMBER
D25 NUMBER
D26 NUMBER
D27 NUMBER
D28 NUMBER
D29 NUMBER
D30 NUMBER
D31 NUMBER
D32 NUMBER
D33 NUMBER
D34 NUMBER
D35 NUMBER
D36 NUMBER
D37 NUMBER
D38 NUMBER
D39 NUMBER
D40 NUMBER
UPDATE_SECS NUMBER
B_UPDATE_SECS NUMBER
B1 NUMBER
B2 NUMBER
B3 NUMBER
B4 NUMBER
B5 NUMBER
B6 NUMBER
B7 NUMBER
B8 NUMBER
B9 NUMBER
B10 NUMBER
DATE_ADDED DATE
DATE_MODIFIED DATE
UPDATED DATE
ORIGINAL VARCHAR2(50)
COUNT NUMBER(10)
IPADDR VARCHAR2(16)
HI NUMBER
IM VARCHAR2(15)
ST VARCHAR2(20)
BS NUMBER
USERID NUMBER(10)
PAGE NUMBER
URL VARCHAR2(150)
DESCRIPTION VARCHAR2(100)
TAG VARCHAR2(4)
NH NUMBER
REFRESH NUMBER
POPULATE VARCHAR2(6)
LERY VARCHAR2(1)
LIST VARCHAR2(1)
LITE VARCHAR2(1)
STING_ID VARCHAR2(20)
YN VARCHAR2(1)
RY_ID VARCHAR2(9)
RATED VARCHAR2(1)
CREATED DATE
In mysql
+-----------------------+---------------------+------+-----+----------------
---+-------+
| Field | Type | Null | Key | Default
| Extra |
+-----------------------+---------------------+------+-----+----------------
---+-------+
| id | varchar(50) | | PRI | |
|
| H0 | tinyint(3) unsigned | YES | | NULL |
|
| H1 | tinyint(3) unsigned | YES | | NULL |
|
| H2 | tinyint(3) unsigned | YES | | NULL |
|
| H3 | tinyint(3) unsigned | YES | | NULL |
|
| H4 | tinyint(3) unsigned | YES | | NULL |
|
| H5 | tinyint(3) unsigned | YES | | NULL |
|
| H6 | tinyint(3) unsigned | YES | | NULL |
|
| H7 | tinyint(3) unsigned | YES | | NULL |
|
| H8 | tinyint(3) unsigned | YES | | NULL |
|
| H9 | tinyint(3) unsigned | YES | | NULL |
|
| H10 | tinyint(3) unsigned | YES | | NULL |
|
| H11 | tinyint(3) unsigned | YES | | NULL |
|
| H12 | tinyint(3) unsigned | YES | | NULL |
|
| H13 | tinyint(3) unsigned | YES | | NULL |
|
| H14 | tinyint(3) unsigned | YES | | NULL |
|
| H15 | tinyint(3) unsigned | YES | | NULL |
|
| H16 | tinyint(3) unsigned | YES | | NULL |
|
| H17 | tinyint(3) unsigned | YES | | NULL |
|
| H18 | tinyint(3) unsigned | YES | | NULL |
|
| H19 | tinyint(3) unsigned | YES | | NULL |
|
| H20 | tinyint(3) unsigned | YES | | NULL |
|
| H21 | tinyint(3) unsigned | YES | | NULL |
|
| H22 | tinyint(3) unsigned | YES | | NULL |
|
| H23 | tinyint(3) unsigned | YES | | NULL |
|
| D1 | tinyint(4) | YES | | NULL |
|
| D2 | tinyint(4) | YES | | NULL |
|
| D3 | tinyint(4) | YES | | NULL |
|
| D4 | tinyint(4) | YES | | NULL |
|
| D5 | tinyint(4) | YES | | NULL |
|
| D6 | tinyint(4) | YES | | NULL |
|
| D7 | tinyint(4) | YES | | NULL |
|
| D8 | tinyint(4) | YES | | NULL |
|
| D9 | tinyint(4) | YES | | NULL |
|
| D10 | tinyint(4) | YES | | NULL |
|
| D11 | tinyint(4) | YES | | NULL |
|
| D12 | tinyint(4) | YES | | NULL |
|
| D13 | tinyint(4) | YES | | NULL |
|
| D14 | tinyint(4) | YES | | NULL |
|
| D15 | tinyint(4) | YES | | NULL |
|
| D16 | tinyint(4) | YES | | NULL |
|
| D17 | tinyint(4) | YES | | NULL |
|
| D18 | tinyint(4) | YES | | NULL |
|
| D19 | tinyint(4) | YES | | NULL |
|
| D20 | tinyint(4) | YES | | NULL |
|
| D21 | tinyint(4) | YES | | NULL |
|
| D22 | tinyint(4) | YES | | NULL |
|
| D23 | tinyint(4) | YES | | NULL |
|
| D24 | tinyint(4) | YES | | NULL |
|
| D25 | tinyint(4) | YES | | NULL |
|
| D26 | tinyint(4) | YES | | NULL |
|
| D27 | tinyint(4) | YES | | NULL |
|
| D28 | tinyint(4) | YES | | NULL |
|
| D29 | tinyint(4) | YES | | NULL |
|
| D30 | tinyint(4) | YES | | NULL |
|
| D31 | tinyint(4) | YES | | NULL |
|
| D32 | tinyint(4) | YES | | NULL |
|
| D33 | tinyint(4) | YES | | NULL |
|
| D34 | tinyint(4) | YES | | NULL |
|
| D35 | tinyint(4) | YES | | NULL |
|
| D36 | tinyint(4) | YES | | NULL |
|
| D37 | tinyint(4) | YES | | NULL |
|
| D38 | tinyint(4) | YES | | NULL |
|
| D39 | tinyint(4) | YES | | NULL |
|
| D40 | tinyint(4) | YES | | NULL |
|
| UPDATE_SECS | int(10) unsigned | YES | | NULL |
|
| B_UPDATE_SECS | int(10) unsigned | YES | | NULL |
|
| B1 | tinyint(4) | YES | | NULL |
|
| B2 | tinyint(4) | YES | | NULL |
|
| B3 | tinyint(4) | YES | | NULL |
|
| B4 | tinyint(4) | YES | | NULL |
|
| B5 | tinyint(4) | YES | | NULL |
|
| B6 | tinyint(4) | YES | | NULL |
|
| B7 | tinyint(4) | YES | | NULL |
|
| B8 | tinyint(4) | YES | | NULL |
|
| B9 | tinyint(4) | YES | | NULL |
|
| B10 | tinyint(4) | YES | | NULL |
|
| DATE_ADDED | date | YES | | NULL
| |
| DATE_MODIFIED | date | YES | | NULL
| |
| UPDATED | timestamp | YES | |
CURRENT_TIMESTAMP | |
| ORIGINAL | varchar(50) | YES | | NULL
| |
| count | tinyint(3) unsigned | YES | | NULL
| |
| IPADDR | varchar(16) | YES | | NULL
| |
| HI | tinyint(4) | YES | | 0 |
|
| IM | varchar(10) | | | logo.gif
| |
| ST | varchar(10) | | | A
| |
| BS | tinyint(4) | YES | | 5
| |
| USERID | smallint(6) | YES | | NULL
| |
| PAGE | tinyint(4) | | | 0 |
|
| URL | varchar(150) | YES | | NULL
| |
| DESCRIPTION | varchar(100) | YES | | NULL
| |
| TAG | varchar(4) | YES | | NULL
| |
| NH | bigint(20) unsigned | | | 0 |
|
| REFRESH | int(10) unsigned | YES | | 0 |
|
| POPULATE | varchar(6) | YES | | NULL |
|
| LERY | char(1) | YES | | N
| |
| LIST | char(1) | YES | | N
| |
| LITE | char(1) | YES | | N |
|
| STING_ID | varchar(20) | YES | | NULL |
|
| YN | char(1) | YES | | Y |
|
| RY_ID | varchar(9) | YES | | NULL |
|
| RATED | char(1) | YES | | Y |
|
| CREATED | date | YES | | NULL
| |
+-----------------------+---------------------+------+-----+----------------
---+-------+
Thanks in advance
sujay
------ _=_NextPart_001_01C5
BFAB.11D05978--
| |
|
| > -----Original Message-----
> From: Sujay Koduri & #91;mailto:SujayK@an
dale.com]
> Sent: Thursday, September 22, 2005 15:23
> To: mysql@lists.mysql.com
> Subject: Avg row length is varying a lot from oracle to MySQL
>
>
> hi ,,
>
> we are converting our oracle DB to MySQL DB. One problem i
> see is that the
>
> abg row length in MySQL is much higher compared to that of Oracle.
>
> In oracle it is around 180 bytes and in MySQL it is around
> 686 bytes. So as
>
> a result, MySQL is taking more space to store the same number
> of records.
>
> Can someone please explain me if this is the intended
> behaviour or i am
>
> missing out something. I am also including the o/p of desc
> table_name of the
>
> same table on both the databases.
Probably do to the way the two database store data and how much space
they reserve for specific column types.
For a way to calculate row size see this link. Relize you must also
calculate the size of all indexes.
http://dev.mysql.com/doc/mysql/en/s...quirements.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Sujay Koduri 2005-09-22, 8:24 pm |
|
Each row in the table takes around 600 bytes, taking every thing into
consideration and assuming every field is used to its maximum bytes. But the
major portion of this 600 bytes are composed of varchar's (100 + 150 + 50 +
16 + 50 + 20 + 9..)
Out of these 400 bytes we generally use only 40 to 50 bytes. Most of them
are reserved for future uses. So strictly speaking even including the space
taken by the indexes, the avg length should not come more than 250 bytes.
sujay
-----Original Message-----
From: Jeff & #91;mailto:jsmforum@
optonline.net]
Sent: Friday, September 23, 2005 1:12 AM
To: mysql@lists.mysql.com
Subject: RE: Avg row length is varying a lot from oracle to MySQL
> -----Original Message-----
> From: Sujay Koduri & #91;mailto:SujayK@an
dale.com]
> Sent: Thursday, September 22, 2005 15:23
> To: mysql@lists.mysql.com
> Subject: Avg row length is varying a lot from oracle to MySQL
>
>
> hi ,,
>
> we are converting our oracle DB to MySQL DB. One problem i see is that
> the
>
> abg row length in MySQL is much higher compared to that of Oracle.
>
> In oracle it is around 180 bytes and in MySQL it is around
> 686 bytes. So as
>
> a result, MySQL is taking more space to store the same number of
> records.
>
> Can someone please explain me if this is the intended behaviour or i
> am
>
> missing out something. I am also including the o/p of desc table_name
> of the
>
> same table on both the databases.
Probably do to the way the two database store data and how much space they
reserve for specific column types.
For a way to calculate row size see this link. Relize you must also
calculate the size of all indexes.
http://dev.mysql.com/doc/mysql/en/s...quirements.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=sujayk@andale.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
|
> Each row in the table takes around 600 bytes, taking every
> thing into consideration and assuming every field is used to
> its maximum bytes. But the major portion of this 600 bytes
> are composed of varchar's (100 + 150 + 50 + 16 + 50 + 20 +
> 9..) Out of these 400 bytes we generally use only 40 to 50
> bytes. Most of them are reserved for future uses. So strictly
> speaking even including the space taken by the indexes, the
> avg length should not come more than 250 bytes.
>
If you have a varchar(50) but usually only use 10 in those fields MySQL
still counts the unused 40 for the total byte count of the row, so you
must count them.
An empty varchar(50) field still uses 50 bytes.
Also, I believe text and blob fields are always counted as 255 bytes
regardless of your settings.
Ordered indexes are 10bytes per column (in the index) per row. I
believe there is also some paging overhead so generally take your
calculated row size and multiply by 1.1.
> sujay
>
> -----Original Message-----
> From: Jeff & #91;mailto:jsmforum@
optonline.net]
> Sent: Friday, September 23, 2005 1:12 AM
> To: mysql@lists.mysql.com
> Subject: RE: Avg row length is varying a lot from oracle to MySQL
>
> see is that
> 686 bytes.
> table_name
>
> Probably do to the way the two database store data and how
> much space they reserve for specific column types.
>
> For a way to calculate row size see this link. Relize you
> must also calculate the size of all indexes.
>
http://dev.mysql.com/doc/mysql/en/s...quirements.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=sujayk@andale.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Michael Stassen 2005-09-26, 8:24 pm |
| Jeff wrote:
>
> If you have a varchar(50) but usually only use 10 in those fields MySQL
> still counts the unused 40 for the total byte count of the row, so you
> must count them.
>
> An empty varchar(50) field still uses 50 bytes.
No, it doesn't. Storage for a VARCHAR is L + 1, where L is length * bytes
per char. See the manual for details
<http://dev.mysql.com/doc/mysql/en/s...quirements.html>.
> Also, I believe text and blob fields are always counted as 255 bytes
> regardless of your settings.
No, it's L + 2.
> Ordered indexes are 10bytes per column (in the index) per row. I
> believe there is also some paging overhead so generally take your
> calculated row size and multiply by 1.1.
Where do you see that in the manual? I'm under the impression that index
size is related to the size of the column being indexed
<http://dev.mysql.com/doc/mysql/en/key-space.html>.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Michael Stassen 2005-09-26, 8:24 pm |
| Sujay Koduri wrote:
> we are converting our oracle DB to MySQL DB. One problem i see is that the
> abg row length in MySQL is much higher compared to that of Oracle. In
> oracle it is around 180 bytes and in MySQL it is around 686 bytes. So as a
> result, MySQL is taking more space to store the same number of records. Can
> someone please explain me if this is the intended behaviour or i am missing
> out something. I am also including the o/p of desc table_name of the same
> table on both the databases.
How are you measuring the size of a row in mysql? What makes you think it
is averaging 686 bytes?
Sujay Koduri also wrote:
> Each row in the table takes around 600 bytes, taking every thing into
> consideration and assuming every field is used to its maximum bytes. But the
> major portion of this 600 bytes are composed of varchar's (100 + 150 + 50 +
> 16 + 50 + 20 + 9..)
> Out of these 400 bytes we generally use only 40 to 50 bytes. Most of them
> are reserved for future uses. So strictly speaking even including the space
> taken by the indexes, the avg length should not come more than 250 bytes.
Umm, using about 50 bytes out of 400 in variable length columns saves about
350 bytes. 600 - 350 = 250, so you should expect about 250 bytes used
_before_ indexes. On the other hand, you say it's only 180 in Oracle, so
perhaps the estimate is off.
OK, looking at your column definitions, I see 118 bytes worth of fixed-width
columns, plus 11 to 436 bytes worth of varchar columns, yielding 129 bytes
per row with empty varchars, 554 bytes per row with full varchars. With 40
to 50 chars used in the varchars, that would be around 180 bytes per row,
just as in Oracle (not including any indexes). Of course, this is assuming
you are using 1-byte chars.
I can't imagine how that could take 686 bytes per row in mysql. It could
just be a failure of my imagination, but you haven't yet shown us how you
arrived at that number.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Sujay Koduri 2005-09-27, 3:23 am |
|
I am using the show table status command to find the average length of row
in a table. And it reported something about 686 bytes. But as I populated
more data, this number has substantially decreased to 484 bytes. But one
more thing I also learnt is the average row length returned by oracle is
just the avg length of each row in the data files ignoring the space
occupied by the indexes. But I think MySQL is giving this values taking the
space used by indexes into consideration.
So they are almost coming the same. :)
sujay
-----Original Message-----
From: Michael Stassen [mailto:Michael.Stassen@verizon.net]
Sent: Tuesday, September 27, 2005 3:59 AM
To: Sujay Koduri
Cc: mysql@lists.mysql.com
Subject: Re: Avg row length is varying a lot from oracle to MySQL
Sujay Koduri wrote:
> we are converting our oracle DB to MySQL DB. One problem i see is that
> the abg row length in MySQL is much higher compared to that of Oracle.
> In oracle it is around 180 bytes and in MySQL it is around 686 bytes.
> So as a result, MySQL is taking more space to store the same number of
> records. Can someone please explain me if this is the intended
> behaviour or i am missing out something. I am also including the o/p
> of desc table_name of the same table on both the databases.
How are you measuring the size of a row in mysql? What makes you think it
is averaging 686 bytes?
Sujay Koduri also wrote:
> Each row in the table takes around 600 bytes, taking every thing into
> consideration and assuming every field is used to its maximum bytes.
> But the major portion of this 600 bytes are composed of varchar's (100
> + 150 + 50 +
> 16 + 50 + 20 + 9..)
> Out of these 400 bytes we generally use only 40 to 50 bytes. Most of
> them are reserved for future uses. So strictly speaking even including
> the space taken by the indexes, the avg length should not come more than
250 bytes.
Umm, using about 50 bytes out of 400 in variable length columns saves about
350 bytes. 600 - 350 = 250, so you should expect about 250 bytes used
_before_ indexes. On the other hand, you say it's only 180 in Oracle, so
perhaps the estimate is off.
OK, looking at your column definitions, I see 118 bytes worth of fixed-width
columns, plus 11 to 436 bytes worth of varchar columns, yielding 129 bytes
per row with empty varchars, 554 bytes per row with full varchars. With 40
to 50 chars used in the varchars, that would be around 180 bytes per row,
just as in Oracle (not including any indexes). Of course, this is assuming
you are using 1-byte chars.
I can't imagine how that could take 686 bytes per row in mysql. It could
just be a failure of my imagination, but you haven't yet shown us how you
arrived at that number.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|