Arlindo Keep in mind that when looking at the real work done physically on a DB, all changes and handling are page-based. Not only that row is modified, but the adjacent rows in the very same page are rewritten too. There are some caching and smarter handlings, but its up to the database management system to even write a whole page just because of one row, if a transaction is definied to do so. About DELETE. There is a standard behavior that new developers (and some old ones) doesnt know. Rows are not physically deleted (just as the majority of the filesystems available today). They are logically marked as free. Check out the link below https://dev.mysql.com/doc/internals/en/innodb-page-structure.html. So, the "better performing and storage wise procedure" is to delete all the rows in a given page. This will mark the page as free. Some answers are done in the link below for what happens when specific cases happen.Beyond that, unfortunately you may need to go to C code reading for some stuff, but the guy who originally wrote InnoDB answered them https://www.percona.com/blog/2007/10/26/heikki-tuuri-innodb-answers-part-i/ I didnt find further info here https://mariadb.com/kb/en/library/xtradb-and-innodb/, but i suppose its the best place to add this info And lastly, go look over other RDBMs way of dealing with this stuff. You sure learn the tradeoffs that involves them https://hackernoon.com/showdown-mysql-8-vs-postgresql-10-3fe23be5c19e 2018-07-27 16:06 GMT-03:00 Arlindo Neto <neto.acs@gmail.com>:
Hi everyone.
I have some questions regarding InnoDB deletion process.
As far as I know, when we issue a DELETE command to a row stored in a InnoDB table, the row contents are stored in the UNDO Log, and the contents of the row are only deleted when the last transaction after the DELETE transaction has finished. But what actually happens to the row that was deleted? Is any disk space ever released to the OS, or are the contents simply erased? Defragmenting a table is only done through OPTIMIZE TABLE, or are there any automatic threads responsible for rearranging the rows? And if the are no such threads, how do we maintain tables which suffer from many deletions?
I hope I made myself clear. Thanks in advance,
Arlindo Neto.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp