Re: Can Innodb reuse the deleted rows disk space?
On 7/28/06, Dan Nelson wrote: In the last episode (Jul 28), leo huang said: 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can Innodb reuse the deleted rows disk space?
In the last episode (Jul 29), Jochem van Dieten said: On 7/28/06, Dan Nelson wrote: In the last episode (Jul 28), leo huang said: 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 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can Innodb reuse the deleted rows disk space?
hi, 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 2006/7/27, Chris [EMAIL PROTECTED]: leo huang wrote: 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can Innodb reuse the deleted rows disk space?
leo huang wrote: hi, 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can Innodb reuse the deleted rows disk space?
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 2006/7/28, Chris [EMAIL PROTECTED]: leo huang wrote: hi, 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can Innodb reuse the deleted rows disk space?
In the last episode (Jul 28), leo huang said: 2006/7/28, Chris [EMAIL PROTECTED]: 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 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can Innodb reuse the deleted rows disk space?
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 2006/7/26, Chris [EMAIL PROTECTED]: leo huang wrote: 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can Innodb reuse the deleted rows disk space?
leo huang wrote: 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can Innodb reuse the deleted rows disk space?
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 2006/7/24, [EMAIL PROTECTED] [EMAIL PROTECTED]: 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 [EMAIL PROTECTED]: 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 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can Innodb reuse the deleted rows disk space?
leo huang wrote: 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can Innodb reuse the deleted rows disk space?
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 [EMAIL PROTECTED]: 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 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can Innodb reuse the deleted rows disk space?
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 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
