removing ibdata1 if some/all tables are not InnoDB?

[Carl Brewer]

Hello,

I'm stuck with a rapidly decreasing amount of available disk space and
a requirement to keep a lid on the size of our databases. We're
using MySQL 4.1.12 as bundled with RHEL ES 4. We do a lot of
transactions keeping short term track of webserver sessions, which
we don't need to keep logs of for very long.

I have a number of databases, almost all of which are using MyISAM or
HEAP, and one database using InnoDB. As such (or at least, as I
understand it) we have a ibdata1 file that will grow forever and
AFAIK there's no way to stop it growing forever for as long
as we have that InnoDB database. Am I correct? I'm no MySQL
guru, my parsing of TFM and googling around and finding bug and feature
requests for ibdata1 purging suggests that this is the case.

If so, if I drop the InnoDB database, stop mysqld, delete (UNIX
filesystem) the imdata1 file, restart mysqld and import a
(modified to be MyISAM) dumped copy of the InnoDB database,
will that work without damaging anything and then not leave me
with another infinatly growing imdata1 file?

Am I correct in assuming that InnoDB databases are meant
for sites where disk space is not ever likely to be an
issue, and MyISAM is a more suitable database engine for
our much tighter disk space situation? I may have missed
a section of the doco that discusses why one would choose an
engine over another?

Thanks for any advice,

Carl
--
=======================
Vivitec Pty. Ltd.
Suite 6, 51-55 City Rd.
Southbank, 3006.
Ph. [phone]
Fax [phone]
=======================

[Chander Ganesan]

You can stop the auto-growth of your ibdata1 file, and add additional
ibdata files (as needed) on different disks/partitions. However, you
cannot currently "shrink" the file without some work..

Check out the MySQL documentation for innodb_data_file_path (that is the
config setting that you would use in the my.cnf file to set things up).

You'll have to find out the size in MB of your current file (ls -lh)
when you do this (if you want to start a new innodb data file on a
separate disk, etc.), since in my experience MySQL will complain if you
specify the size of the file incorrectly.

InnoDB is great when you have a lot of transactions going on, need
rollback capability (batch operations that should either succeed as a
whole or fail as a whole) - or you need ACID compliance. MyISAM is fast
for lookups, but requires a table lock to be acquired for updates, and
most inserts (except in certain cases) - so its fast for lookups, but
not as good for updates. Each have their own distinct advantages...
HEAP is good when you don't care if your data sticks around, and you
need fast access to it (such as web cookies...)

As far as purging - you'd be best off doing an export, trash your InnoDB
tables, and then import .
--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC 27560
http://www.otg-nc.com
Phone: [phone]
Fax: [phone]
Expert MySQL Training

Post by Carl Brewer
Hello,
I'm stuck with a rapidly decreasing amount of available disk space and
a requirement to keep a lid on the size of our databases. We're
using MySQL 4.1.12 as bundled with RHEL ES 4. We do a lot of
transactions keeping short term track of webserver sessions, which
we don't need to keep logs of for very long.
I have a number of databases, almost all of which are using MyISAM or
HEAP, and one database using InnoDB. As such (or at least, as I
understand it) we have a ibdata1 file that will grow forever and
AFAIK there's no way to stop it growing forever for as long
as we have that InnoDB database. Am I correct? I'm no MySQL
guru, my parsing of TFM and googling around and finding bug and feature
requests for ibdata1 purging suggests that this is the case.
If so, if I drop the InnoDB database, stop mysqld, delete (UNIX
filesystem) the imdata1 file, restart mysqld and import a
(modified to be MyISAM) dumped copy of the InnoDB database,
will that work without damaging anything and then not leave me
with another infinatly growing imdata1 file?
Am I correct in assuming that InnoDB databases are meant
for sites where disk space is not ever likely to be an
issue, and MyISAM is a more suitable database engine for
our much tighter disk space situation? I may have missed
a section of the doco that discusses why one would choose an
engine over another?
Thanks for any advice,
Carl

[Alex]

HI Carl,

The ibdata file growth can be stopped by removing the autoextend
keyword in the my.cnf file.

In your my.cnf file the entry might be

innodb_data_file_path = ibdata1:256M:autoextend

If you want to stop the growth of that file and add another file then this
is what you want to do.

1. Stop the mysql server
2. Get the size of the ibdata1 file in MB (Lets say its 5600MB in size)
3. edit the my.cnf file and replace

innodb_data_file_path = ibdata1:256M:autoextend

with

innodb_data_file_path = ibdata1:5600M;ibdata2:256M:autoextend

4. Start the server.

This will stop that file from growing and a new file will be added that
can pushed on to a different disk and symlinked into the ibdata directory.

Data growth is a problem in all table types. Even if you migrate to MyISAM
you need space.

See whether there is log_bin turned on the server. If so there might be
lots of bin log files that you can do a cleanup on. Bin logs occupy a
great deal of space.

Thanx
Alex,
MySQL DBA
Yahoo!

Post by Carl Brewer
Hello,
I'm stuck with a rapidly decreasing amount of available disk space and
a requirement to keep a lid on the size of our databases. We're
using MySQL 4.1.12 as bundled with RHEL ES 4. We do a lot of
transactions keeping short term track of webserver sessions, which
we don't need to keep logs of for very long.
I have a number of databases, almost all of which are using MyISAM or
HEAP, and one database using InnoDB. As such (or at least, as I
understand it) we have a ibdata1 file that will grow forever and
AFAIK there's no way to stop it growing forever for as long
as we have that InnoDB database. Am I correct? I'm no MySQL
guru, my parsing of TFM and googling around and finding bug and feature
requests for ibdata1 purging suggests that this is the case.
If so, if I drop the InnoDB database, stop mysqld, delete (UNIX
filesystem) the imdata1 file, restart mysqld and import a
(modified to be MyISAM) dumped copy of the InnoDB database,
will that work without damaging anything and then not leave me
with another infinatly growing imdata1 file?
Am I correct in assuming that InnoDB databases are meant
for sites where disk space is not ever likely to be an
issue, and MyISAM is a more suitable database engine for
our much tighter disk space situation? I may have missed
a section of the doco that discusses why one would choose an
engine over another?
Thanks for any advice,
Carl

[Heikki Tuuri]

Carl,

InnoDB does purge deleted rows from the ibdata files. Certain PostgreSQL
advocates have been spreading a claim that InnoDB would not do that, but the
claim is false.

If your ibdata file keeps growing indefinitely, please check with SHOW
INNODB STATUS that you do commit all your transactions. If a transaction
stays open for months, then the purge cannot remove deleted rows.

If you convert ALL your tables from InnoDB to MyISAM, then you can remove
the ibdata files and ib_logfiles. If you put skip-innodb to my.cnf, then
those files will not be created again.

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php


----- Original Message -----
From: "Alex" <***@yahoo-inc.com>
Newsgroups: mailing.database.myodbc
Sent: Tuesday, January 03, 2006 6:55 AM
Subject: Re: removing ibdata1 if some/all tables are not InnoDB?

Post by Alex
HI Carl,
The ibdata file growth can be stopped by removing the autoextend
keyword in the my.cnf file.
In your my.cnf file the entry might be
innodb_data_file_path = ibdata1:256M:autoextend
If you want to stop the growth of that file and add another file then this
is what you want to do.
1. Stop the mysql server
2. Get the size of the ibdata1 file in MB (Lets say its 5600MB in size)
3. edit the my.cnf file and replace
innodb_data_file_path = ibdata1:256M:autoextend
with
innodb_data_file_path = ibdata1:5600M;ibdata2:256M:autoextend
4. Start the server.
This will stop that file from growing and a new file will be added that
can pushed on to a different disk and symlinked into the ibdata directory.
Data growth is a problem in all table types. Even if you migrate to MyISAM
you need space.
See whether there is log_bin turned on the server. If so there might be
lots of bin log files that you can do a cleanup on. Bin logs occupy a
great deal of space.
Thanx
Alex,
MySQL DBA
Yahoo!

Post by Carl Brewer
Hello,
I'm stuck with a rapidly decreasing amount of available disk space and
a requirement to keep a lid on the size of our databases. We're
using MySQL 4.1.12 as bundled with RHEL ES 4. We do a lot of
transactions keeping short term track of webserver sessions, which
we don't need to keep logs of for very long.
I have a number of databases, almost all of which are using MyISAM or
HEAP, and one database using InnoDB. As such (or at least, as I
understand it) we have a ibdata1 file that will grow forever and
AFAIK there's no way to stop it growing forever for as long
as we have that InnoDB database. Am I correct? I'm no MySQL
guru, my parsing of TFM and googling around and finding bug and feature
requests for ibdata1 purging suggests that this is the case.
If so, if I drop the InnoDB database, stop mysqld, delete (UNIX
filesystem) the imdata1 file, restart mysqld and import a
(modified to be MyISAM) dumped copy of the InnoDB database,
will that work without damaging anything and then not leave me
with another infinatly growing imdata1 file?
Am I correct in assuming that InnoDB databases are meant
for sites where disk space is not ever likely to be an
issue, and MyISAM is a more suitable database engine for
our much tighter disk space situation? I may have missed
a section of the doco that discusses why one would choose an
engine over another?
Thanks for any advice,
Carl

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

[Remigiusz Sokołowski]

Post by Heikki Tuuri
Carl,
InnoDB does purge deleted rows from the ibdata files. Certain
PostgreSQL advocates have been spreading a claim that InnoDB would not
do that, but the claim is false.

Could You explain more about reusing space previously taken by deleted
rows? Is this concept similar to Oracle's concepts in this regard ?

Regards
Remigusz

[Chander Ganesan]

Post by Heikki Tuuri
InnoDB does purge deleted rows from the ibdata files. Certain
PostgreSQL advocates have been spreading a claim that InnoDB would not
do that, but the claim is false.
If your ibdata file keeps growing indefinitely, please check with SHOW
INNODB STATUS that you do commit all your transactions. If a
transaction stays open for months, then the purge cannot remove
deleted rows.
If you convert ALL your tables from InnoDB to MyISAM, then you can
remove the ibdata files and ib_logfiles. If you put skip-innodb to
my.cnf, then those files will not be created again.

I don't think there is a question as to whether or not InnoDB will purge
data and re-use space, the question is whether or not the ibdata files
will be "shrunk" when that space is purged.

My understanding (and experience) has always shown that ibdata files -
while they may purge and re-use unused space, will not shrink themselves
based on the actual space usage. Is that not correct?

I.e., if I have 100 MB of table data, and say - delete 6 tables (which
would result in InnoDB recovering all that space), that results in only
10MB of space being "used", the file will be re-sized to 10MB - or
something smaller than 100 MB.

The practical example would be if I were to accidentally add 1GB of data
to my InnoDB tablespace, and then remove it. Would my total ibdata file
sizes total less than 1GB of space (now I'm just using 100MB)?

If that were the case it would be a simple matter of switching to
'tablespace per table', migrating the data to the individual tables
(which would shrink the ibdata files), re-structuring the ibdata files
(to use other partitions, etc.) an the moving the data back into the
tablespace.

Thanks
--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC 27560
Phone: [phone]
http://www.otg-nc.com

Post by Heikki Tuuri
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM tables
http://www.innodb.com/order.php
Newsgroups: mailing.database.myodbc
Sent: Tuesday, January 03, 2006 6:55 AM
Subject: Re: removing ibdata1 if some/all tables are not InnoDB?

Post by Alex
HI Carl,
The ibdata file growth can be stopped by removing the autoextend
keyword in the my.cnf file.
In your my.cnf file the entry might be
innodb_data_file_path = ibdata1:256M:autoextend
If you want to stop the growth of that file and add another file then this
is what you want to do.
1. Stop the mysql server
2. Get the size of the ibdata1 file in MB (Lets say its 5600MB in size)
3. edit the my.cnf file and replace
innodb_data_file_path = ibdata1:256M:autoextend
with
innodb_data_file_path = ibdata1:5600M;ibdata2:256M:autoextend
4. Start the server.
This will stop that file from growing and a new file will be added that
can pushed on to a different disk and symlinked into the ibdata directory.
Data growth is a problem in all table types. Even if you migrate to MyISAM
you need space.
See whether there is log_bin turned on the server. If so there might be
lots of bin log files that you can do a cleanup on. Bin logs occupy a
great deal of space.
Thanx
Alex,
MySQL DBA
Yahoo!

Post by Carl Brewer
Hello,
I'm stuck with a rapidly decreasing amount of available disk space and
a requirement to keep a lid on the size of our databases. We're
using MySQL 4.1.12 as bundled with RHEL ES 4. We do a lot of
transactions keeping short term track of webserver sessions, which
we don't need to keep logs of for very long.
I have a number of databases, almost all of which are using MyISAM or
HEAP, and one database using InnoDB. As such (or at least, as I
understand it) we have a ibdata1 file that will grow forever and
AFAIK there's no way to stop it growing forever for as long
as we have that InnoDB database. Am I correct? I'm no MySQL
guru, my parsing of TFM and googling around and finding bug and feature
requests for ibdata1 purging suggests that this is the case.
If so, if I drop the InnoDB database, stop mysqld, delete (UNIX
filesystem) the imdata1 file, restart mysqld and import a
(modified to be MyISAM) dumped copy of the InnoDB database,
will that work without damaging anything and then not leave me
with another infinatly growing imdata1 file?
Am I correct in assuming that InnoDB databases are meant
for sites where disk space is not ever likely to be an
issue, and MyISAM is a more suitable database engine for
our much tighter disk space situation? I may have missed
a section of the doco that discusses why one would choose an
engine over another?
Thanks for any advice,
Carl

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql