RE: Reusing ibdata1 space
Johan, I think you are right about this. The problem does not appear to be with the database at all, but with the undo log. Thanks for pointing me in the right direction. -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Tuesday, November 01, 2011 10:01 AM To: Rozeboom, Kay [DAS] Cc: mysql@lists.mysql.com Subject: Re: Reusing ibdata1 space - Original Message - From: Kay Rozeboom [DAS] kay.rozeb...@iowa.gov I realize that this would not return the unused space to the operating system. But would it return it to MySQL so that it could be re-used for subsequent inserts, instead of extending ibdata1 further? That should normally already happen. An occasional optimize table might help defragment the tablespace, but space from properly deleted records should be reused anyway (save for really small fragments). I recommend you keep track of the innodb free tablespace for a while, and see how that evolves - it should go relatively low before the tablespace expands on disk. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Reusing ibdata1 space
- Original Message - From: Nick Khamis sym...@gmail.com I should mention that we have deleted the ib_* files in the past. I hope that was an accident, because if you thought that was a good idea I'm sending someone over with the spiked cluebat. Luckily for you, the solution to that particular problem (in the cases where it's indeed caused by the delete of the ib_data files) is more deletes, and you're good at those :-p The tables show up in the listing because in the mysqldata/database directory there are .frm (table descriptor) files created for tables of all engines, even though those files are really artifacts from the MyISAM legacy. Thus, the server scans it, lists it, notices it's an InnoDB table and then fails to find it in the InnoDB data dictionary because you deleted the one it was in. Simply delete the .frm file for such tables, and they'll no longer show up. As for file_per_table, it's generally a good idea to set that to 1, yes. Be aware that you may need to tune other MySQL and/or OS level settings, too, for example max_open_files. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Reusing ibdata1 space
I wonder, if there could be any method to regain InnoDB space other than dump the whole database and reimport. Thanks, On Thu, Nov 10, 2011 at 12:44 AM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Nick Khamis sym...@gmail.com I should mention that we have deleted the ib_* files in the past. I hope that was an accident, because if you thought that was a good idea I'm sending someone over with the spiked cluebat. Luckily for you, the solution to that particular problem (in the cases where it's indeed caused by the delete of the ib_data files) is more deletes, and you're good at those :-p The tables show up in the listing because in the mysqldata/database directory there are .frm (table descriptor) files created for tables of all engines, even though those files are really artifacts from the MyISAM legacy. Thus, the server scans it, lists it, notices it's an InnoDB table and then fails to find it in the InnoDB data dictionary because you deleted the one it was in. Simply delete the .frm file for such tables, and they'll no longer show up. As for file_per_table, it's generally a good idea to set that to 1, yes. Be aware that you may need to tune other MySQL and/or OS level settings, too, for example max_open_files. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Reusing ibdata1 space
- Original Message - From: Prabhat Kumar aim.prab...@gmail.com I wonder, if there could be any method to regain InnoDB space other than dump the whole database and reimport. Very simple answer to that: no. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Reusing ibdata1 space
Hello Reindi, I don't mean to revisit an old post however, we are also using innodb and experiencing a rough start. Should we set innodb_file_per_table to 1? Also we expereince this scenario a lot: mysql show tables; +---+ | Tables_in_symax | +---+ | acc | ERROR 1146 (42S02): Table 'symax.acc' doesn't exist I should mention that we have deleted the ib_* files in the past. Is it ok if I post our config for a quick review? Thanks in Advance, Nick. On Tue, Nov 1, 2011 at 10:09 AM, Reindl Harald h.rei...@thelounge.net wrote: Am 01.11.2011 15:02, schrieb Rozeboom, Kay [DAS]: We are running MySQL 5.0.77, and using INNODB in production for the first time. The production database has a lot of inserts and deletes, and the shared ibdata1 file is continually growing. I understand that to return the unused space to the operating system, we must delete and recreate ibdata1 and its associated .frm files. I am wondering if we could do the following instead: 1) Let ibdata1 grow for a while. 2) Rebuild the tables periodically using this syntax: ALTER TABLE t1 ENGINE = InnoDB; this will not help as long you are not using innodb_file_per_table and if you would using it ibdata1 would not grow in my opinion innodb_file_per_table=0 is a dumb default and requires that people with too few expierience with mysql/innodb would much more carefully read documentations as they usually do -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Reusing ibdata1 space
Hello Reindl, I just noticed that I misspelled your name. Sorry about that! Cheers, Nick. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Reusing ibdata1 space
Thanks to everyone who replied to my question. Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Reusing ibdata1 space
We are running MySQL 5.0.77, and using INNODB in production for the first time. The production database has a lot of inserts and deletes, and the shared ibdata1 file is continually growing. I understand that to return the unused space to the operating system, we must delete and recreate ibdata1 and its associated .frm files. I am wondering if we could do the following instead: 1) Let ibdata1 grow for a while. 2) Rebuild the tables periodically using this syntax: ALTER TABLE t1 ENGINE = InnoDB; I realize that this would not return the unused space to the operating system. But would it return it to MySQL so that it could be re-used for subsequent inserts, instead of extending ibdata1 further? Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov
Re: Reusing ibdata1 space
Am 01.11.2011 15:02, schrieb Rozeboom, Kay [DAS]: We are running MySQL 5.0.77, and using INNODB in production for the first time. The production database has a lot of inserts and deletes, and the shared ibdata1 file is continually growing. I understand that to return the unused space to the operating system, we must delete and recreate ibdata1 and its associated .frm files. I am wondering if we could do the following instead: 1) Let ibdata1 grow for a while. 2) Rebuild the tables periodically using this syntax: ALTER TABLE t1 ENGINE = InnoDB; this will not help as long you are not using innodb_file_per_table and if you would using it ibdata1 would not grow in my opinion innodb_file_per_table=0 is a dumb default and requires that people with too few expierience with mysql/innodb would much more carefully read documentations as they usually do signature.asc Description: OpenPGP digital signature
Re: Reusing ibdata1 space
Kay, There's no way to regain InnoDB space. I can suggest some techniques but no magic. 1. dump the whole database and reimport 2. setup a brand new slave ,sync and switch to it Cheers Claudio 2011/11/1 Rozeboom, Kay [DAS] kay.rozeb...@iowa.gov We are running MySQL 5.0.77, and using INNODB in production for the first time. The production database has a lot of inserts and deletes, and the shared ibdata1 file is continually growing. I understand that to return the unused space to the operating system, we must delete and recreate ibdata1 and its associated .frm files. I am wondering if we could do the following instead: 1) Let ibdata1 grow for a while. 2) Rebuild the tables periodically using this syntax: ALTER TABLE t1 ENGINE = InnoDB; I realize that this would not return the unused space to the operating system. But would it return it to MySQL so that it could be re-used for subsequent inserts, instead of extending ibdata1 further? Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov -- Claudio
Re: Reusing ibdata1 space
- Original Message - From: Kay Rozeboom [DAS] kay.rozeb...@iowa.gov I realize that this would not return the unused space to the operating system. But would it return it to MySQL so that it could be re-used for subsequent inserts, instead of extending ibdata1 further? That should normally already happen. An occasional optimize table might help defragment the tablespace, but space from properly deleted records should be reused anyway (save for really small fragments). I recommend you keep track of the innodb free tablespace for a while, and see how that evolves - it should go relatively low before the tablespace expands on disk. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
