Can Innodb reuse the deleted rows disk space?

[leo huang]

Hi, all

I know the Innodb use MVCC to achieve very high concurrency. Can
Innodb reuse the deleted rows disk space? I have an database which
have many update operation. If Innodb can't reuse the space of deleted
rows, I worry about that MySQL will exhaust our disk space very
quickly.

Any recommend will be welcome!


Regards,
Leo Huang

[d***@sifycorp.com]

Hi,

Try using the optimize table tablename ,but this will keep the data accordingly,but really if it is a disk space constraint you can go with re-org process in which you will have to get a down time for mysql db.Process is something like .
Dump all the Innodb tables drop the existing innodb tables and shutdown mysql, clear the Innodb log-space as ibdata1 & indata2 & iblogfile0 & iblogfile1 and also the redo logs of the innodb.
Then start the mysql this will create innodb logs 1 & innodb2 as what u have mentioned in ur cnf file and import the dump .
In this case u can able to reduce the space usage of innodb.
Try this it might help u out.


With Regards
Dilipkumar

Post by leo huang
Hi, all
I know the Innodb use MVCC to achieve very high concurrency. Can
Innodb reuse the deleted rows disk space? I have an database which
have many update operation. If Innodb can\'t reuse the space of deleted
rows, I worry about that MySQL will exhaust our disk space very
quickly.
Any recommend will be welcome!
Regards,
Leo Huang
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

[leo huang]

hi, Dilipkumar

Thank you very much!

I think I know the fact: The Innodb can't reuse the deleted rows' disk
space. And a solution is: dump the data; shutdown mysql; delete the
files; restart mysql; import the data.

Regards,
Leo Huang

Post by d***@sifycorp.com
Hi,
Try using the optimize table tablename ,but this will keep the data accordingly,but really if it is a disk space constraint you can go with re-org process in which you will have to get a down time for mysql db.Process is something like .
Dump all the Innodb tables drop the existing innodb tables and shutdown mysql, clear the Innodb log-space as ibdata1 & indata2 & iblogfile0 & iblogfile1 and also the redo logs of the innodb.
Then start the mysql this will create innodb logs 1 & innodb2 as what u have mentioned in ur cnf file and import the dump .
In this case u can able to reduce the space usage of innodb.
Try this it might help u out.
With Regards
Dilipkumar

Post by leo huang
Hi, all
I know the Innodb use MVCC to achieve very high concurrency. Can
Innodb reuse the deleted rows disk space? I have an database which
have many update operation. If Innodb can\'t reuse the space of deleted
rows, I worry about that MySQL will exhaust our disk space very
quickly.
Any recommend will be welcome!
Regards,
Leo Huang
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

[Chris]

Post by leo huang
hi, Dilipkumar
Thank you very much!
I think I know the fact: The Innodb can't reuse the deleted rows' disk
space. And a solution is: dump the data; shutdown mysql; delete the
files; restart mysql; import the data.

InnoDB does re-use the space inside the database, it's the logfiles that
are growing. The logs are needed in case you need to replay transactions.


I suggest you read this page:

http://dev.mysql.com/doc/refman/5.1/en/adding-and-removing.html

and this page:

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html

Specify 2-3 entries in the innodb_data_file_path and mysql should (if
I'm reading it properly) rotate between the files and keep size under
control.

[leo huang]

hi, Chris

Thank you for your advice!

I know that Innodb use the logfiles circularly. Can Innodb re-use the
deleted rows' disk space in tablespace?

Regards,
Leo Huang

Post by Chris

Post by leo huang
hi, Dilipkumar
Thank you very much!
I think I know the fact: The Innodb can't reuse the deleted rows' disk
space. And a solution is: dump the data; shutdown mysql; delete the
files; restart mysql; import the data.

InnoDB does re-use the space inside the database, it's the logfiles that
are growing. The logs are needed in case you need to replay transactions.
http://dev.mysql.com/doc/refman/5.1/en/adding-and-removing.html
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html
Specify 2-3 entries in the innodb_data_file_path and mysql should (if
I'm reading it properly) rotate between the files and keep size under
control.

[Chris]

Post by leo huang
hi, Chris
Thank you for your advice!
I know that Innodb use the logfiles circularly. Can Innodb re-use the
deleted rows' disk space in tablespace?

I'm sure it will, what makes you think it won't?

You might need an 'optimize table' or something to see a reduction in
the on disk file size but mysql will reclaim that space as it needs to.

[leo huang]

hi, Chris

Post by Chris
I'm sure it will, what makes you think it won't?

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?

Regards,
Leo Huang

Post by Chris

Post by leo huang
hi, Chris
Thank you for your advice!
I know that Innodb use the logfiles circularly. Can Innodb re-use the
deleted rows' disk space in tablespace?

I'm sure it will, what makes you think it won't?
You might need an 'optimize table' or something to see a reduction in
the on disk file size but mysql will reclaim that space as it needs to.

[Chris]

Post by leo huang
hi, Chris

Post by Chris
I'm sure it will, what makes you think it won't?

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

[leo huang]

hi, Chris

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.

Regards,
Leo Huang

Post by Chris

Post by leo huang
hi, Chris

Post by Chris
I'm sure it will, what makes you think it won't?

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

[Dan Nelson]

Post by leo huang

Post by Chris

Post by leo huang
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.

[Jochem van Dieten]

Post by Dan Nelson

Post by leo huang
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

After all transactions that were started before the transaction that
did the delete committed have either been committed or rolled back.

Jochem

[Dan Nelson]

Post by Jochem van Dieten

Post by Dan Nelson

Post by leo huang
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

After all transactions that were started before the transaction that
did the delete committed have either been committed or rolled back.

Ouch.

[leo huang]

hi, Dan Nelson, Jochem van Dieten, and Chris

Thx!

I think I understand it after your replies.

Regards,
Leo Huang

Post by Dan Nelson

Post by Jochem van Dieten

Post by Dan Nelson

Post by leo huang
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

After all transactions that were started before the transaction that
did the delete committed have either been committed or rolled back.

Ouch.
--
Dan Nelson