MyISAM files size has increased (doubled)

Question

One of my table's file size (a .MYD file) has increased by about 100%. If I look into the data there is just a normal daily load during the last few days. What could have caused this file size increase?

myisamchk

As suggested by a user I tried sudo myisamchk -r tablename. The result of this operation was "Fixing index 1" "Fixing index 2" ... and the exact same file size.

Edit: After running the command a second time the file size decreased to the normal (half) size.

Possible duplicate of [Automated or regular backup of mysql data](http://stackoverflow.com/questions/38916163/automated-or-regular-backup-of-mysql-data) — e4c5, Mar 09 '17 at 14:13
@e4c5 - This question is not related to mysql backup. I will edit it to make it more clear — ESP32, Mar 09 '17 at 14:16
You've probably had a lot of rows deleted but the space hasn't been reclaimed. Try myisamchk https://dev.mysql.com/doc/refman/5.7/en/myisam-optimization.html — e4c5, Mar 09 '17 at 14:16

score 0 · Accepted Answer · answered Mar 09 '17 at 21:51

@e4c5 has the most likely answer. He is asking you to remove the deleted rows. MySQL will not reclaim the space from deleted rows. As more and more rows are deleted, the larger your .MYD becomes. The only way to reclaim the space is to rebuild the .MYD - either by recreating the table, using the optimize table command or running myisamchk -r.

The following command will rebuild the table.

optimize table $table_name;

The myisamchk -r command line approach works too - but be sure the table is locked to prevent users from accessing it (otherwise you run the very real risk of losing data). Be sure to flush table before using it again after running myisamchk repair or MySQL will detect the table has changed and run an unnecessary check against it. Of the three methods, myisamchk -r is the most prone to data lost (but still my preferred method- you just have to be careful and prepared for the worst).

score 0 · Answer 2 · answered Mar 14 '17 at 00:39

0

It sounds like you are replacing the entire table every day. Here's an optimal way to do it; no bloat, no OPTIMIZE:

CREATE TABLE new LIKE real;
populate `new`
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;

That way, you build a clean, new, table and swap it in. Note that the table is always available; that is, no downtime.

answered Mar 14 '17 at 00:39

Rick James

135,179
13
127
222

"populate", is that a valid statement in MySQL? – ddofborg Jan 30 '22 at 23:19
@ddofborg - True, not a valid statement. There are several ways to fill a table from outside data. `LOAD DATA INFILE` is a common one, but maybe `INSERT ... SELECT ...` or a program that reads or generates the data. What are you doing currently? – Rick James Jan 31 '22 at 00:45
It's `insert ... select ...`, just to copy the table. I posted the full query. – ddofborg Jan 31 '22 at 02:21

score 0 · Answer 3 · answered Jan 31 '22 at 02:21

Based on @rick-james's answer I looked further and found another thread. This does the trick... very fast and no downtime (if the table is not written during the run).

-- table_org is the name of the table to 'optimize'

CREATE TABLE `table_new` LIKE `table_org`;
-- disable keys for quick inserts
ALTER TABLE `table_new` DISABLE KEYS;

INSERT INTO `table_new`
SELECT * FROM `table_org`;

ALTER TABLE `table_new` ENABLE KEYS;

RENAME TABLE `table_org` TO `table_old`, `table_new` TO `table_org`;

DROP TABLE `table_old`;

-- is not needed, but just to have an up-to-date data
ANALYZE TABLE `table_org`;

MyISAM files size has increased (doubled)

3 Answers3