I was doing some database sizing in MySQL 5.1.30 GA for memory tables. Generally I have used INFORMATION_SCHEMA.TABLES data_length,index_length as a reasonable guide.
However working with a MEMORY table, after deleting rows, the size did not decrease as expected. I deleted 10% of rows, and saw 0% reduction. This was confirmed by doing a subsequent ALTER where I saw the 10% reduction in memory size.
It requires more investigation, however I found these results unexpected and worthy of publishing.
mysql> select version(); +-----------+ | version() | +-----------+ | 5.1.30 | +-----------+ +-----------------+--------+------------+------------+----------------+-------------+-------------+------------+ | table_name | engine | row_format | table_rows | avg_row_length | total_mb | data_mb | index_mb | +-----------------+--------+------------+------------+----------------+-------------+-------------+------------+ | location_ex4 | MEMORY | Fixed | 1111000 | 45 | 59.68744659 | 51.16348267 | 8.52396393 | mysql> delete from location_ex4 limit 111000; Query OK, 111000 rows affected (0.16 sec) +-----------------+--------+------------+------------+----------------+-------------+-------------+------------+ | table_name | engine | row_format | table_rows | avg_row_length | total_mb | data_mb | index_mb | +-----------------+--------+------------+------------+----------------+-------------+-------------+------------+ | location_ex4 | MEMORY | Fixed | 1000000 | 45 | 59.68744659 | 51.16348267 | 8.52396393 | mysql> alter table location_ex4 engine=memory; Query OK, 1000000 rows affected (2.95 sec) Records: 1000000 Duplicates: 0 Warnings: 0 +-----------------+--------+------------+------------+----------------+-------------+-------------+------------+ | table_name | engine | row_format | table_rows | avg_row_length | total_mb | data_mb | index_mb | +-----------------+--------+------------+------------+----------------+-------------+-------------+------------+ | location_ex4 | MEMORY | Fixed | 1000000 | 45 | 53.75530243 | 45.97259521 | 7.78270721
If I remember correctly, OPTIMIZE SYNTAX will tell you that memory tables suck and need to be optimized regularly to shrink. Basically, this is because otherwise you will have a malloc-storm to grow and shrink and copy the data in the table as it is being modified.
I have not had a look at the code, but I hope that at least the OPTIMIZE is done in place.
This is documented in the manual under MEMORY TABLES at:
http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html
==
Memory used by a MEMORY table is not reclaimed if you delete individual rows from the table. Memory is only reclaimed when the entire table is deleted. Memory that was previously used for rows that have been deleted will be re-used for new rows only within the same table. To free up the memory used by rows that have been deleted you should use ALTER TABLE ENGINE=MEMORY to force a table rebuild.
To free all the memory used by a MEMORY table when you no longer require its contents, you should execute DELETE or TRUNCATE TABLE, or remove the table altogether using DROP TABLE.
==
Also OPTIMIZE TABLE does not work with MEMORY, you instead need to use the ALTER TABLE mentioned above (which doubles the memory in use for the table temporarily).
This behavior is documented – but I do remember having the same “aha!” moment when I first noticed it
See: http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html
“Memory used by a MEMORY table is not reclaimed if you delete individual rows from the table. Memory is only reclaimed when the entire table is deleted. Memory that was previously used for rows that have been deleted will be re-used for new rows only within the same table. To free up the memory used by rows that have been deleted you should use ALTER TABLE ENGINE=MEMORY to force a table rebuild.
To free all the memory used by a MEMORY table when you no longer require its contents, you should execute DELETE or TRUNCATE TABLE, or remove the table altogether using DROP TABLE.”
u must do “optimize table location_ex4″ before delete command to see any change at total_mb column.
when u delete some rows, the table is still alocating the same MB in HD unless u use optimize command.