InnoDB doubles size when converting from MyIsam

--0016e6d784589e39580473673ceb
Content-Type: text/plain; charset=ISO-8859-1

Hello,

I have a database with about 1000 tables and 150GB. I have done a simple
"for f in $(cat tables); do mysql -uuser -ppassword database_name -e "alter
table $f engine=InnoDB;" ; done

I took about 3 hours and everything went fine, but I just realised that the
same database in another server in myisam is about 150GB and in the server
where I converted it to innodb it's about 350GB.

Is that normal?

Thanks in advance
A

Ps: I asked in the InnoDB list, but it has no traffic.

--0016e6d784589e39580473673ceb--

Re: InnoDB doubles size when converting from MyIsam

In the last episode (Sep 12), Arthur Meeks Meeks said:
> I have a database with about 1000 tables and 150GB. I have done a simple
> "for f in $(cat tables); do mysql -uuser -ppassword database_name -e
> "alter table $f engine=InnoDB;" ; done
>
> I took about 3 hours and everything went fine, but I just realised that
> the same database in another server in myisam is about 150GB and in the
> server where I converted it to innodb it's about 350GB.
>
> Is that normal?

Yes; innodb tables will require quite a bit more space than myisam, because
the table data itself is stored as an index (which means each page will be
between 50% and 94% full), and because each row needs extra space to hold
transaction and locking info.

http://dev.mysql.com/doc/refman/5.1/en/dynamic-format.html

http://dev.mysql.com/doc/refman/5.1/en/innodb-physical-struc ture.html
http://dev.mysql.com/doc/refman/5.1/en/innodb-physical-recor d.html

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: InnoDB doubles size when converting from MyIsam

--0016e6d58a8015efcc04736fd8f0
Content-Type: text/plain; charset=ISO-8859-1

2009/9/13 Dan Nelson

> In the last episode (Sep 12), Arthur Meeks Meeks said:
> > I have a database with about 1000 tables and 150GB. I have done a simple
> > "for f in $(cat tables); do mysql -uuser -ppassword database_name -e
> > "alter table $f engine=InnoDB;" ; done
> >
> > I took about 3 hours and everything went fine, but I just realised that
> > the same database in another server in myisam is about 150GB and in the
> > server where I converted it to innodb it's about 350GB.
> >
> > Is that normal?
>
> Yes; innodb tables will require quite a bit more space than myisam, because
> the table data itself is stored as an index (which means each page will be
> between 50% and 94% full), and because each row needs extra space to hold
> transaction and locking info.
>
> http://dev.mysql.com/doc/refman/5.1/en/dynamic-format.html
>
> http://dev.mysql.com/doc/refman/5.1/en/innodb-physical-struc ture.html
> http://dev.mysql.com/doc/refman/5.1/en/innodb-physical-recor d.html


Thanks a lot Dan, now it makes sense.

A

--0016e6d58a8015efcc04736fd8f0--