David,
If you want to decrease the size of the ibdata files, you will have to
restart the mysqld process. According to the manual
(http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html) the
only way is to:
1.
Use *mysqldump* to dump all your |InnoDB| tables.
2.
Stop the server.
3.
Remove all the existing tablespace files.
4.
Configure a new tablespace.
5.
Restart the server.
6.
Import the dump files.
If you only want to "free" the space taken up by the now-deleted rows,
and return this space to the innodb tablespace, then an ALTER TABLE /
OPTIMIZE TABLE is a way to do it, but understand that this will not
decrease the size of your ibdata files.
If you can't afford the table to be locked during either of the above
rebuild processes, the only other method I can suggest is to create a
second server and perform the maintenance there, then switch your
application over to that server and perform the maintenance on your
initial server. I frequently do this, and while it works for the
applications that I have, ymmv. I'd be glad to explain the process in
more depth if needed.
HTH!
Regards,
Devananda
David Israelsson wrote:
I have a really simple (two tables, one relation) but big (~70 GB)
innodb database containing rather dynamic data. After deleting lots
of records from the tables, the innodb data files still take the same
amount of disk space as before. What is the correct way of freeing
the disk space?
The general solution seems to be "alter table tablename type=innodb"
which completely rebuilds the table, but this is not acceptable
because:
a) It locks the table during the operation making it unavailable to
other clients.
b) It involves copying the table, taking twice the disk space during
the operation - disk space that I do not have.
Is there another way of doing this?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
