3
A mysql table had 10.2Gb of data,
I ran a script that deleted old data that was no longer needed,
After erasing half of the data from this table, it still continues with 10.2Gb.
Consuming my server hard drive.
How or what I do to reduce the size of this table ?
6
When you delete a record, you are simply warning that that space is free.
It would be impractical for a DB to keep shortening the file in production as this would mean rearranging all the data internally (similarly to a disk defragmentation).
If you really need it, somehow you need to "reassemble" the table.
Mysql has the OPTIMIZE TABLE, which, among other things, reorganizes the data internally:
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ...
It works for most uses, and the behavior changes somewhat between Innodb and Myisam. It doesn’t work for others Engines, keep this in mind.
In Innodb it rearranges indexes and table in the specific case you use a file per table (.ibd individual).
Complete defragmentation is already done in Myisam.
If it is a bank that can be stopped for an instant, one solution is simply to make a dump of the table and recreate it by importing the same data:
BACK UP AND TEST BACKUP BEFORE TRYING THIS!
mysqldump db_name t1 > dump.sql
mysql db_name < dump.sql
To do in all tables:
mysqldump --all-databases > dump.sql
mysql < dump.sql
If you need to keep DB in production, an intermediate solution is to create a table with the same structure, and use a
SELECT * FROM tabela INSERT tabela2
and at the end, rename the tables, then discard the old.
The precaution, in this second case, is that you need to find a way to lock the original against writing, because someone can insert or modify a data while INSERT is taking place. This second solution is best suited when you can block writing but need to keep reading.
Browser other questions tagged mysql
You are not signed in. Login or sign up in order to post.
The database does not reallocate memory when you delete a record.
– LucaoA