472,090 Members | 1,272 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,090 software developers and data experts.

Optimizing tables

I have big problem with mysql 4.0.18 . Because my database is growing
up, every night records that are older than some date are deleted. But
size of database on disk isn`t smaller after this operation. There are
some solutions to free space by OPTIMIZE TABLE command, but this
command when executes, does temporary copy of database. How can I free
memory and avoid doing temporary copy of database ( because I don`t
enough free space for this ).

Best Regards

Mar 24 '06 #1
1 1421
"coyote2002" <co********@interia.pl> wrote in message
news:11**********************@t31g2000cwb.googlegr oups.com...
How can I free
memory and avoid doing temporary copy of database ( because I don`t
enough free space for this ).

This is a common complaint about MySQL. See the comments in this bug:

The bug is unresolved at this time. The recommendation is to use ALTER
TABLE (even one that changes nothing like ALTER TABLE myTable TYPE=InnoDB
when the table is already InnoDB) to rebuild the table. But this still
doesn't shrink the file, it just defragments the space so subsequent updates
to the table can utilize the free space more efficiently.

AFAIK, the only way to shrink the files is to back up your tables (or entire
database), DROP them, and then restore them from the backups.

In the case of InnoDB, you may have to back up _all_ databases, manually
remove the "<datadir>/ibdata1" file, and then restore the databases. This
is because InnoDB stores all tables and indexes in that one file.

In the case of MyISAM, you can do this on a table-by-table basis, since each
table is stored in a separate file on disk. InnoDB has an option
"innodb_file_per_table" that you can enable to make it store tables

This is a big inconvenience when using MySQL, but several other RDBMS brands
suffer from similar limitations. It's hard to shrink a database file as an
online task. Depending on the quantity of data, it could take a long time
to do that, and it interrupts other clients' access to the database.

Bill K.
Mar 24 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by sameer | last post: by
35 posts views Thread by Thomas Matthews | last post: by
reply views Thread by sameer | last post: by
18 posts views Thread by amywolfie | last post: by
1 post views Thread by xpcer | last post: by
2 posts views Thread by suneelid2000 | last post: by
5 posts views Thread by John Rivers | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.