maria-discuss team mailing list archive

[Alexandre hadjinlian guerra <alexhguerra@xxxxxxxxx>]

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@xxxxxxxxx>:

> 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>