A mysql table had 10.2Gb of data,
I circled a script that deleted old data that was no longer needed,
After deleting half of the data from this table, it still continues with 10.2Gb.
Consuming the HD from my server.
Home
How or what do I do to reduce the size of this table?
Table size does not decrease
3
asked by anonymous 27.01.2017 / 18:43
1 answer
6
When you delete a record, you are simply advising that the space is free.
It would be impractical for a DB to shorten the file in production, as this would mean rearranging all data internally (similarly to a disk defragmentation).
If you really need this, you somehow need to "reassemble" the table.
built-in solution for InnoDB and MyISAM
MySQL has 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 a bit between InnoDB and MyISAM. It does not work for other engines , keep this in mind.
- In InnoDB it reorganizes indices and table in the specific case you use one file per table (% with% individual).
-
MyISAM is already fully defragmented.
"Manual" solution for general or "controlled" use:
If it's a database that can be stopped for a while, one solution is simply to do a dump table and re-create it by importing the same data:
BACKUP AND TEST BACKUP BEFORE TRYING THIS!
mysqldump db_name t1 > dump.sql
mysql db_name < dump.sql
To do on all tables:
mysqldump --all-databases > dump.sql
mysql < dump.sql
If you need to keep the 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 one.
Be careful, in this case, you have to find a way to lock the original against writing, since someone can insert or modify a data while the INSERT is occurring. This second solution is best suited when you can block writing but need to keep reading.
27.01.2017 / 18:47