List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:July 28 2006 4:42pm
Subject:Re: Can Innodb reuse the deleted rows disk space?
View as plain text  
In the last episode (Jul 28), leo huang said:
> 2006/7/28, Chris <dmagick@stripped>:
> >leo huang wrote:
> >> Because some paper say that when the row is deleted or update,
> >> Innodb just make a mark that the row is deleted and it didn't
> >> delete the rows. I can't find more information about the re-use
> >> tablespace. Can you give me more?
> >
> >That's the way MVCC works. If you need full acid/transaction
> >support, that's the only way it can do it (postgresql works exactly
> >the same way). It can't just delete the row because you might roll
> >back the transaction and it will have to undo that delete, or other
> >transactions might be using it for whatever purpose.
> >
> >http://dev.mysql.com/doc/refman/5.1/en/innodb-multi-versioning.html
> >http://dev.mysql.com/doc/refman/5.1/en/file-space-management.html
>
> So,  the deleted rows' disk space in tablespace can't re-use when I
> use Innodb, can it? And the tablespace is growing when we update the
> tables, even the amount of rows do not increase.

It can be re-used after the transaction has been committed, but if
there weren't enough deleted rows to cause a b-tree compaction, that
free space can only be used by another row near the same parimary key
value.  InnoDB tables aren't like MyISAM tables, where a row can be
stored any place in the .MYI file.  In InnoDB, the entire table is a
large b-tree index and each index block holds a small range of key
values.  

-- 
	Dan Nelson
	dnelson@stripped
Thread
Can Innodb reuse the deleted rows disk space?leo huang24 Jul
Re: Can Innodb reuse the deleted rows disk space?dilipkumar_parikh24 Jul
  • Re: Can Innodb reuse the deleted rows disk space?leo huang26 Jul
    • Re: Can Innodb reuse the deleted rows disk space?Chris26 Jul
      • Re: Can Innodb reuse the deleted rows disk space?leo huang26 Jul
        • Re: Can Innodb reuse the deleted rows disk space?Chris27 Jul
          • Re: Can Innodb reuse the deleted rows disk space?leo huang28 Jul
            • Re: Can Innodb reuse the deleted rows disk space?Chris28 Jul
              • Re: Can Innodb reuse the deleted rows disk space?leo huang28 Jul
                • Re: Can Innodb reuse the deleted rows disk space?Dan Nelson28 Jul
                  • Re: Can Innodb reuse the deleted rows disk space?Jochem van Dieten29 Jul
                    • Re: Can Innodb reuse the deleted rows disk space?Dan Nelson29 Jul
                      • Re: Can Innodb reuse the deleted rows disk space?leo huang1 Aug