I've read several occasions that whenever you remove a row within an InnoDB table in MySQL, its space isn't used again, if you make lots of Card inserts right into a table after which periodically Remove some rows the table uses increasingly more space on disk, as though the rows weren't erased whatsoever.
Lately I have learned though the space occupied by erased rows is re-used only after a little transactions are complete as well as then - not fully. I'm now confused.
Can someone please understand this in my experience? I have to perform a large amount of Card inserts into an InnoDB table after which every X minutes I have to Remove records which are a lot more than Y minutes old. Have i got an issue of ever-growing InnoDB table here, or perhaps is it paranoia?
It's paranoia :)
DB's don't grow in dimensions unnecessarily, however for performance issues space isn't freed either.
What you've heard most most likely is when you remove records that space isn't returned towards the Operating-system. Rather, it's stored being an empty space for that DB to re-use later on.
The reason being:
- DB will need some HD space in order to save its data whether it does not have space, it reserves some empty space in the beginning.
- Whenever you place a brand new row, a bit of that space can be used.
- Advertising media are from free space, a brand new block is reserved, and so forth.
- Now, whenever you remove some rows, to be able to prevent arranging increasingly more blocks, its space is stored free but never returned towards the Operating-system, so technology-not only again later with no necessity of arranging new blocks.
As you can tell, space is re-used, but never returned. That's the main factor for your question.
This might hint at you want to do:
Don't empty a table with Remove FROM or TRUNCATE
Draining a sizable table using Remove FROM or TRUNCATE is slow on InnoDB. The reason being for procedures InnoDB needs to process each row on the table, and because of its transactional set it up first creates each remove action towards the transaction log then is applicable it towards the actual table. For better performance otherwise restricted to foreign secrets, use DROP TABLE then CREATE TABLE to empty a sizable table.
in innodb, there's no practical method of clearing in the space.
- use per table ibdata file, which will allow you to remove record copy the data to a different table and remove old table, thus recuperating records.
- use mysqldump and whole plenty of
receipe to wash in the whole
server. Check following:
Many of these techniques become not practical when you're using huge tables(during my situation they're a lot more than 250GB) and also you must have them removing records to higher performance.
You'll have to seriously think, whether you've enough space in your harddisk to do among the above function (during my situation I don't think 1TB is sufficient for those these actions)
with Innotab table (and mysql itself) the choice are fairly limited if have serious database size.