Re: Reducing ibdata1 file size
Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- 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
Re: Reducing ibdata1 file size
Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- 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
Re: Reducing ibdata1 file size
Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.comwrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- 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
Re: Reducing ibdata1 file size
do u have one file per table or just one system tablespace datafile. On Tue, May 22, 2012 at 2:20 PM, Kishore Vaishnav kish...@railsfactory.orgwrote: Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.com wrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- 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
Re: Reducing ibdata1 file size
Right now one tablespace datafile. But does it matters if i have one file per table. *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 2:56 PM, Ananda Kumar anan...@gmail.com wrote: do u have one file per table or just one system tablespace datafile. On Tue, May 22, 2012 at 2:20 PM, Kishore Vaishnav kish...@railsfactory.org wrote: Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.com wrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- 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
Re: Reducing ibdata1 file size
Changes, you have a lot of changes. You change the equivalent of 1 gb data a day. Every change is similar to an insert and every delete does not free any disk space. No way to reduce this rate unless you change the app logic. More info needed to provide a hint. Claudio On May 22, 2012 10:50 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.comwrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- 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
Re: Reducing ibdata1 file size
as multiple answered, yes it matters! there is no way to reduce the size of a single tablespace with file per table you can shrink the files with optimize table tblname which is in fact a ALTER TABLE without real changes Am 22.05.2012 11:28, schrieb Kishore Vaishnav: Right now one tablespace datafile. But does it matters if i have one file per table. On Tue, May 22, 2012 at 2:56 PM, Ananda Kumar anan...@gmail.com wrote: do u have one file per table or just one system tablespace datafile. On Tue, May 22, 2012 at 2:20 PM, Kishore Vaishnav kish...@railsfactory.org wrote: Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.com wrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- 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 -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm signature.asc Description: OpenPGP digital signature
Re: Reducing ibdata1 file size
Hi Reindl Harald, Does this means that if we have a single tablespace with file per table and doing the optimization will reduce the size of the datafile size ? If yes, then why this not possible on the datafile (one single file) too ? * * *thanks regards,* *__* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 3:07 PM, Reindl Harald h.rei...@thelounge.netwrote: as multiple answered, yes it matters! there is no way to reduce the size of a single tablespace with file per table you can shrink the files with optimize table tblname which is in fact a ALTER TABLE without real changes Am 22.05.2012 11:28, schrieb Kishore Vaishnav: Right now one tablespace datafile. But does it matters if i have one file per table. On Tue, May 22, 2012 at 2:56 PM, Ananda Kumar anan...@gmail.com wrote: do u have one file per table or just one system tablespace datafile. On Tue, May 22, 2012 at 2:20 PM, Kishore Vaishnav kish...@railsfactory.org wrote: Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.com wrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- 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 -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm
Re: Reducing ibdata1 file size
Yes, Looks like there is lot of fragmentation. Is this production or dev/qa. You need to take a dump, drop database, make changes to parameter file to have file per table and restore the dump, so that each table will have its own .idb files, and you can easily manage the space. regards anandkl On Tue, May 22, 2012 at 2:58 PM, Kishore Vaishnav kish...@railsfactory.orgwrote: Right now one tablespace datafile. But does it matters if i have one file per table. *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 2:56 PM, Ananda Kumar anan...@gmail.com wrote: do u have one file per table or just one system tablespace datafile. On Tue, May 22, 2012 at 2:20 PM, Kishore Vaishnav kish...@railsfactory.org wrote: Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.com wrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- 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
Re: Reducing ibdata1 file size
single file will not release the space to OS, it will continue to hold the space got from drop or truncate and used only for the database On Tue, May 22, 2012 at 3:20 PM, Kishore Vaishnav kish...@railsfactory.orgwrote: Hi Reindl Harald, Does this means that if we have a single tablespace with file per table and doing the optimization will reduce the size of the datafile size ? If yes, then why this not possible on the datafile (one single file) too ? * * *thanks regards,* *__* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 3:07 PM, Reindl Harald h.rei...@thelounge.net wrote: as multiple answered, yes it matters! there is no way to reduce the size of a single tablespace with file per table you can shrink the files with optimize table tblname which is in fact a ALTER TABLE without real changes Am 22.05.2012 11:28, schrieb Kishore Vaishnav: Right now one tablespace datafile. But does it matters if i have one file per table. On Tue, May 22, 2012 at 2:56 PM, Ananda Kumar anan...@gmail.com wrote: do u have one file per table or just one system tablespace datafile. On Tue, May 22, 2012 at 2:20 PM, Kishore Vaishnav kish...@railsfactory.org wrote: Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.com wrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- 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 -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm
Re: Reducing ibdata1 file size
because this is simply the way InnoDB is implemented as multiple said the default of a single table space is idiotic in my opinion, but however this is well known over years google: mysql innodb reduce datafiles Am 22.05.2012 11:50, schrieb Kishore Vaishnav: Does this means that if we have a single tablespace with file per table and doing the optimization will reduce the size of the datafile size ? If yes, then why this not possible on the datafile (one single file) too ? On Tue, May 22, 2012 at 3:07 PM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: as multiple answered, yes it matters! there is no way to reduce the size of a single tablespace with file per table you can shrink the files with optimize table tblname which is in fact a ALTER TABLE without real changes Am 22.05.2012 11:28, schrieb Kishore Vaishnav: Right now one tablespace datafile. But does it matters if i have one file per table. On Tue, May 22, 2012 at 2:56 PM, Ananda Kumar anan...@gmail.com mailto:anan...@gmail.com wrote: do u have one file per table or just one system tablespace datafile. On Tue, May 22, 2012 at 2:20 PM, Kishore Vaishnav kish...@railsfactory.org mailto:kish...@railsfactory.org wrote: Thanks for the reply, but in my case the datafile is growing 1 GB per day with only 1 DB (apart from mysql / information_schema / test) and the size of the DB is just 600MB, where records get updated / deleted / added and on an average it maintains 600MB only. Now the datafile is increased to 30GB from the past 30 days, do you have any idea how to reduce this ? Also just wondering what does the datafile contains actually and why can't it gets decreased ? *thanks regards, __* Kishore Kumar Vaishnav * * On Tue, May 22, 2012 at 1:40 PM, Claudio Nanni claudio.na...@gmail.com mailto:claudio.na...@gmail.com wrote: Kishore, No, as already explained, it is not possible, Innodb datafiles *never* shrink. Cheers Claudio On May 22, 2012 10:05 AM, Kishore Vaishnav kish...@railsfactory.org mailto:kish...@railsfactory.org wrote: Hi, I understand that if I set the innodb_file_per_table then once the table is drop the datafile will also be lost. But is there a way where I truncate the table and the datafile shrinks itself ? *thanks regards, __* Kishore Kumar Vaishnav * * On Mon, May 21, 2012 at 6:43 PM, Johan De Meersman vegiv...@tuxera.be mailto:vegiv...@tuxera.be wrote: - Original Message - From: Manivannan S. manivanna...@spanservices.com mailto:manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- 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 -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm -- Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / CISO / Software-Development p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm signature.asc Description: OpenPGP digital signature
Re: Reducing ibdata1 file size
- Original Message - From: Pothanaboyina Trimurthy skd.trimur...@gmail.com hi sir, Please keep the list in CC, others may benefit from your questions, too. can we see any performance related improvements if we use innodb_file_per_table other than using a single ibdatafile for all innodb databases. Please let me know the difference sir. No, that shouldn't yield any particular performance benefit, as far as I'm aware. -- 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
Re: Reducing ibdata1 file size
- Original Message - From: Reindl Harald h.rei...@thelounge.net as multiple said the default of a single table space is idiotic in my opinion, but however this is well known over years I suppose there's a certain logic to favouring one-shot allocation and never giving up free space, in that it reduces on-disk fragmentation. -- 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
Re: Reducing ibdata1 file size
yes, there some new features you can use to improve performance. If you are using mysql 5.5 and above, with files per table, you can enable BARACUDA file format, which in turn provides data compression and dynamic row format, which will reduce IO. For more benefits read the doc On Tue, May 22, 2012 at 4:45 PM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Pothanaboyina Trimurthy skd.trimur...@gmail.com hi sir, Please keep the list in CC, others may benefit from your questions, too. can we see any performance related improvements if we use innodb_file_per_table other than using a single ibdatafile for all innodb databases. Please let me know the difference sir. No, that shouldn't yield any particular performance benefit, as far as I'm aware. -- 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
Re: Reducing ibdata1 file size
Am 22.05.2012 13:19, schrieb Johan De Meersman: - Original Message - From: Reindl Harald h.rei...@thelounge.net as multiple said the default of a single table space is idiotic in my opinion, but however this is well known over years I suppose there's a certain logic to favouring one-shot allocation and never giving up free space, in that it reduces on-disk fragmentation. well but for what price? the problem is the DEFAULT users with enough knowledge could easy change the default currently what is happening is that mostly every beginner is caught in the trap with single-table-space and hearing from innodb_file_per_table after is is way too late __ BTW: From: Pothanaboyina Trimurthy skd.trimur...@gmail.com hi sir, Please keep the list in CC, others may benefit from your questions, too NO - do NOT set any CC send lists to a mailing-list ONLY to the list-address with this idiotic list as cc you are breaking reply-to-list button for every user with a well working mail-client signature.asc Description: OpenPGP digital signature
Re: Reducing ibdata1 file size
In regards to why the file grows large, you may wish to read some of the posts on the MySQL Performance Blog, which has quite a bit of information on this, such as http://www.mysqlperformanceblog.com/2010/06/10/reasons-for-run-away-main-innodb-tablespace/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
- Original Message - From: Ananda Kumar anan...@gmail.com yes, there some new features you can use to improve performance. If you are using mysql 5.5 and above, with files per table, you can enable BARACUDA file format, which in turn provides data compression and dynamic row format, which will reduce IO. True, but Barracuda isn't limited to file-per-table, is it? Also, while it's true that compression will lighten your I/O load, do not forget that it does so at the cost of additional CPU load. There's no such thing as a free lunch :-) -- 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: Reducing ibdata1 file size
yes, Barracuda is limited to FILE_PER_TABLE. Yes, true there is CPU cost, but very less. To gain some you have to loss some. On Tue, May 22, 2012 at 5:07 PM, Johan De Meersman vegiv...@tuxera.bewrote: -- *From: *Ananda Kumar anan...@gmail.com yes, there some new features you can use to improve performance. If you are using mysql 5.5 and above, with files per table, you can enable BARACUDA file format, which in turn provides data compression and dynamic row format, which will reduce IO. True, but Barracuda isn't limited to file-per-table, is it? Also, while it's true that compression will lighten your I/O load, do not forget that it does so at the cost of additional CPU load. There's no such thing as a free lunch :-) -- 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: Reducing ibdata1 file size
- Original Message - From: Reindl Harald h.rei...@thelounge.net Subject: Re: Reducing ibdata1 file size well but for what price? the problem is the DEFAULT users with enough knowledge could easy change the default currently what is happening is that mostly every beginner is caught in the trap with single-table-space and hearing from innodb_file_per_table after is is way too late All true, but I would argue that enterprise-suitable defaults are a good thing - shame so many of the other defaults aren't :-) And yes, it is problematic for beginners, but so is having beginners manage production sites, isn't it? -- 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
Re: Reducing ibdata1 file size
Am 22.05.2012 13:40, schrieb Johan De Meersman: - Original Message - From: Reindl Harald h.rei...@thelounge.net Subject: Re: Reducing ibdata1 file size well but for what price? the problem is the DEFAULT users with enough knowledge could easy change the default currently what is happening is that mostly every beginner is caught in the trap with single-table-space and hearing from innodb_file_per_table after is is way too late All true, but I would argue that enterprise-suitable defaults are a good thing shame so many of the other defaults aren't :-) 95% of mysqld-installations have no problem with innodb_file_per_table so DEFAULTS should not be for 5% and as said in another reply to this thread you need innodb_file_per_table for compression and no, most servers these days are NOT cpu-bound most are IO-bound especially in enterprise environments with shared storage (SAN) And yes, it is problematic for beginners, but so is having beginners manage production sites, isn't it? not only for beginners very few people expecting that a datastorage is only growing and growing with no painless way to release all the wasted space if you make tests with real big data and drop them this has nothing to do with enterprise or beginners it is a surprising behavior and defaults should minimize surprises signature.asc Description: OpenPGP digital signature
Re: Reducing ibdata1 file size
- Original Message - From: Ananda Kumar anan...@gmail.com yes, Barracuda is limited to FILE_PER_TABLE. Ah, I didn't realise that. Thanks :-) Yes, true there is CPU cost, but very less. To gain some you have to loss some. I've only got it enabled on a single environment, but enabling it added about 20% of a single vcore to the CPU usage. Very visible (and not problematic) because that system isn't CPU-bound :-) Converting an existing table to the compressed format did shoot the CPU through the roof, though. See http://www.tuxera.be/filestore/vefeuraxinie/mysql-cpu-year.png for an interesting graph. -- 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: Reducing ibdata1 file size
Am 22.05.2012 13:52, schrieb Johan De Meersman: - Original Message - From: Ananda Kumar anan...@gmail.com yes, Barracuda is limited to FILE_PER_TABLE. Ah, I didn't realise that. Thanks :-) Yes, true there is CPU cost, but very less. To gain some you have to loss some. I've only got it enabled on a single environment, but enabling it added about 20% of a single vcore to the CPU usage. Very visible (and not problematic) because that system isn't CPU-bound :-) Converting an existing table to the compressed format did shoot the CPU through the roof, though. See http://www.tuxera.be/filestore/vefeuraxinie/mysql-cpu-year.png for an interesting graph interesting because i have here a dbmail-server with no CPU load and innodb with compression enabled since 2009 (innodb plugin in the past) [--] Data in InnoDB tables: 6G (Tables: 49) [--] Up for: 5d 0h 44m 10s (455M q [1K qps], 50K conn, TX: 36B, RX: 13B) [--] Reads / Writes: 90% / 10% [--] Total buffers: 4.1G global + 1.2M per thread (500 max threads) [OK] Maximum possible memory usage: 4.7G (54% of installed RAM) [OK] Slow queries: 0% (3/455M) [OK] Highest usage of available connections: 18% (93/500) [OK] Key buffer size / total MyISAM indexes: 128.0M/76.4M [OK] Key buffer hit rate: 98.6% (40M cached / 559K reads) signature.asc Description: OpenPGP digital signature
Re: Reducing ibdata1 file size
- Original Message - From: Reindl Harald h.rei...@thelounge.net 95% of mysqld-installations have no problem with innodb_file_per_table so DEFAULTS should not be for 5% There is no problem, and there is better practice - and if your system is I/O bound it makes sense to minimize on-disk fragmentation. However, given the need for file-per-table to benefit from compression, I admit that it might make sense to change the default now. Would you say compression should be enabled by default, too? If you're aiming at diskbound systems, I'd think that it might make sense, too. it is a surprising behavior and defaults should minimize surprises Changing the default is a surprise, no? :-) Also, everything is a surprise if you don't read the fine manual. -- 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
Re: Reducing ibdata1 file size
Am 22.05.2012 13:59, schrieb Johan De Meersman: - Original Message - From: Reindl Harald h.rei...@thelounge.net 95% of mysqld-installations have no problem with innodb_file_per_table so DEFAULTS should not be for 5% There is no problem, and there is better practice and if your system is I/O bound it makes sense to minimize on-disk fragmentation. many systems are not CPU-bound nor IO-bound because they are well designed for their load However, given the need for file-per-table to benefit from compression I admit that it might make sense to change the default now. yes, one reason more Would you say compression should be enabled by default, too? no because with sane defaults (file_per_table) you can do this with one command, with the current defaults only with many work and downtime to switch If you're aiming at diskbound systems, I'd think that it might make sense, too no, defaults should be as flexible as possible and not optimized for special workloads it is a surprising behavior and defaults should minimize surprises Changing the default is a surprise, no? :-) no problem in a minor update 5.5.0 was a good moment 5.6.0 is the next good one Also, everything is a surprise if you don't read the fine manual please do not tell us that you have seen any single option of any software you are using BEFORE beause your systems would still be in theoretical stages a default causing the user to dump out all his data, switch it and pray while re-import is running and system is down is a worst-case default signature.asc Description: OpenPGP digital signature
Re: Reducing ibdata1 file size
- Original Message - From: Reindl Harald h.rei...@thelounge.net interesting because i have here a dbmail-server with no CPU load and innodb with compression enabled since 2009 (innodb plugin in the past) Ah, this is a mixed-use server that also receives data from several Cacti installs. [--] Data in InnoDB tables: 6G (Tables: 49) [--] Data in InnoDB tables: 17G (Tables: 276) [--] Up for: 5d 0h 44m 10s (455M q [1K qps], 50K conn, TX: 36B, RX: 13B) [--] Up for: 11d 23h 27m 20s (200M q [193.511 qps], 8M conn, TX: 132B, RX: 35B) [--] Reads / Writes: 90% / 10% [--] Reads / Writes: 18% / 82% I guess it's reasonable that I get a lot more CPU overhead from compression, as you get a lot of reads from decompressed blocks in the cache :-) -- 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
Re: Reducing ibdata1 file size
Is you system READ intensive or WRITE intensive. If you have enable compression for WRITE intensive data, then CPU cost will be more. On Tue, May 22, 2012 at 5:41 PM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Reindl Harald h.rei...@thelounge.net interesting because i have here a dbmail-server with no CPU load and innodb with compression enabled since 2009 (innodb plugin in the past) Ah, this is a mixed-use server that also receives data from several Cacti installs. [--] Data in InnoDB tables: 6G (Tables: 49) [--] Data in InnoDB tables: 17G (Tables: 276) [--] Up for: 5d 0h 44m 10s (455M q [1K qps], 50K conn, TX: 36B, RX: 13B) [--] Up for: 11d 23h 27m 20s (200M q [193.511 qps], 8M conn, TX: 132B, RX: 35B) [--] Reads / Writes: 90% / 10% [--] Reads / Writes: 18% / 82% I guess it's reasonable that I get a lot more CPU overhead from compression, as you get a lot of reads from decompressed blocks in the cache :-) -- 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
Re: Reducing ibdata1 file size
or it could be that your buffer size is too small, as mysql is spending lot of CPU time for compress and uncompressing On Tue, May 22, 2012 at 5:45 PM, Ananda Kumar anan...@gmail.com wrote: Is you system READ intensive or WRITE intensive. If you have enable compression for WRITE intensive data, then CPU cost will be more. On Tue, May 22, 2012 at 5:41 PM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Reindl Harald h.rei...@thelounge.net interesting because i have here a dbmail-server with no CPU load and innodb with compression enabled since 2009 (innodb plugin in the past) Ah, this is a mixed-use server that also receives data from several Cacti installs. [--] Data in InnoDB tables: 6G (Tables: 49) [--] Data in InnoDB tables: 17G (Tables: 276) [--] Up for: 5d 0h 44m 10s (455M q [1K qps], 50K conn, TX: 36B, RX: 13B) [--] Up for: 11d 23h 27m 20s (200M q [193.511 qps], 8M conn, TX: 132B, RX: 35B) [--] Reads / Writes: 90% / 10% [--] Reads / Writes: 18% / 82% I guess it's reasonable that I get a lot more CPU overhead from compression, as you get a lot of reads from decompressed blocks in the cache :-) -- 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
Re: Reducing ibdata1 file size
From: Claudio Nanni claudio.na...@gmail.com No, as already explained, it is not possible, Innodb datafiles *never* shrink. That's been the common wisdom for a long time. However, this just popped up on my RSS reader. I haven't even looked at it, let alone tried it. I'm interested in what the experts think... Getting rid of huge ibdata file, no dump required: You have been told (guilty as charged), that the only way to get rid of the huge InnoDB tablespace file (commonly named ibdata1), when moving to innodb_file_per_table, is to do a logical dump of your data, completely erase everything, then import the dump. http://code.openark.org/blog/mysql/getting-rid-of-huge-ibdata-file-no-dump-required Four multinational companies control over seventy percent of fluid milk sales in the U.S... These giants have grown through debt-fueld acquisitions and mergers and by keeping payments to dairy farmers as low as possible. -- Ron Schmid Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
- Original Message - From: Jan Steinman j...@bytesmiths.com That's been the common wisdom for a long time. However, this just popped up on my RSS reader. I haven't even looked at it, let alone tried it. In brief: convert all your tables to myisam, delete ibdatafile during a restart, convert tables back to compressed innodb. My first thought is mentioned, but will be adressed in next post: you lose your relations. I gotta run now, but I wonder what other incompatibilities between the engines one might run into. For simple databases this would probably work, though. -- 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
Re: Reducing ibdata1 file size
Jan, that's not common wisdom, Innodb datafiles ***never*** shrink, that in the blog from 22th of May is a workaround, one of the many. If you ask my my favourite is to use a stand by instance and work on that. Claudio 2012/5/22 Jan Steinman j...@bytesmiths.com From: Claudio Nanni claudio.na...@gmail.com No, as already explained, it is not possible, Innodb datafiles *never* shrink. That's been the common wisdom for a long time. However, this just popped up on my RSS reader. I haven't even looked at it, let alone tried it. I'm interested in what the experts think... Getting rid of huge ibdata file, no dump required: You have been told (guilty as charged), that the only way to get rid of the huge InnoDB tablespace file (commonly named ibdata1), when moving to innodb_file_per_table, is to do a logical dump of your data, completely erase everything, then import the dump. http://code.openark.org/blog/mysql/getting-rid-of-huge-ibdata-file-no-dump-required Four multinational companies control over seventy percent of fluid milk sales in the U.S... These giants have grown through debt-fueld acquisitions and mergers and by keeping payments to dairy farmers as low as possible. -- Ron Schmid Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
RE: Reducing ibdata1 file size
Despite the conventional wisdom, converting to innodb_file_per_table will not necessarily help you. It depends on your situation. If most of your growth is in a single table, you will only have transferred the problem from the ibdata1 file to a new file. The ibdata1 file may also continue to grow, since innodb uses it for several kinds of temporary storage such as the insert buffer and the undo logs (AKA rollback segment). 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
Re: Reducing ibdata1 file size
Okay, my mistake. I should write precisely when communicating with precise people. :-) What I meant was, dumping and importing is the common knowledge way of virtually shrinking innodb files. So, now that I've conceded the meta-argument, what do you think of the linked procedure for reducing innodb files? On 22 May 12, at 06:40, Claudio Nanni wrote: Jan, that's not common wisdom, Innodb datafiles ***never*** shrink, that in the blog from 22th of May is a workaround, one of the many. If you ask my my favourite is to use a stand by instance and work on that. Claudio 2012/5/22 Jan Steinman j...@bytesmiths.com From: Claudio Nanni claudio.na...@gmail.com No, as already explained, it is not possible, Innodb datafiles *never* shrink. That's been the common wisdom for a long time. However, this just popped up on my RSS reader. I haven't even looked at it, let alone tried it. I'm interested in what the experts think... Getting rid of huge ibdata file, no dump required: You have been told (guilty as charged), that the only way to get rid of the huge InnoDB tablespace file (commonly named ibdata1), when moving to innodb_file_per_table, is to do a logical dump of your data, completely erase everything, then import the dump. http://code.openark.org/blog/mysql/getting-rid-of-huge-ibdata-file-no-dump-required Four multinational companies control over seventy percent of fluid milk sales in the U.S... These giants have grown through debt-fueld acquisitions and mergers and by keeping payments to dairy farmers as low as possible. -- Ron Schmid Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio No man is so foolish but he may sometimes give another good counsel, and no man so wise that he may not easily err if he takes no other counsel than his own. He that is taught only by himself has a fool for a master. -- Ben Johnson Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Reducing ibdata1 file size
To shrink ibdata1: 1. Dump everything 2. Stop mysql 3. change to innodb_file_per_table = 1 4. change allocation in my.cnf (my.ini) to something smaller, say 50M,AUTOEXTEND 5. remove (rm / delete) ibdata1 6. restart mysql 7. reload data Step 3: innodb_file_per_table gives you more fine-grained control -- you can rebuild (ALTER TABLE) individual tables, thereby giving back unused space to the OS. Step 4: ibdata1 is still needed, but will probably not grow nearly as much once you have file_per_table. -Original Message- From: Manivannan S. [mailto:manivanna...@spanservices.com] Sent: Monday, May 21, 2012 6:04 AM To: mysql@lists.mysql.com Subject: Reducing ibdata1 file size Hi , I am trying to reduce the ibdata1 data file in MySQL. In MySQL data directory the ibdata1 data file is always increasing whenever I am creating a new database and inserting some data into database. If I drop the existing database, the table structures only dropped from the server but data still exist in the ibdata1 data file. How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. Do you have any idea how to solve this problem. Thanks for any feedback. Thanks Manivannan S DISCLAIMER: This email message and all attachments are confidential and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, you are notified that any dissemination, distribution or copying of this email is strictly prohibited. If you have received this email in error, please notify us immediately by return email or to mailad...@spanservices.com and destroy the original message. Opinions, conclusions and other information in this message that do not relate to the official business of SPAN, shall be understood to be neither given nor endorsed by SPAN. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reducing ibdata1 file size
Manivannan, There is no way to reduce the InnoDB main tablespace. You can get rid (=regain disk space) of InnoDB tablespaces only if you have innodb_file_per_table setting, which allows you to get disk space back if you drop the table. The best option you have to free your current shared tablespace is to do a sql dump of the whole instance and reimport into a new one (if you need instructions on how to run multiple mysql instances on the same server just let me know) and then discard the old instance. Cheers Claudio 2012/5/21 Manivannan S. manivanna...@spanservices.com Hi , I am trying to reduce the ibdata1 data file in MySQL. In MySQL data directory the ibdata1 data file is always increasing whenever I am creating a new database and inserting some data into database. If I drop the existing database, the table structures only dropped from the server but data still exist in the ibdata1 data file. How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. Do you have any idea how to solve this problem. Thanks for any feedback. Thanks Manivannan S DISCLAIMER: This email message and all attachments are confidential and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, you are notified that any dissemination, distribution or copying of this email is strictly prohibited. If you have received this email in error, please notify us immediately by return email or to mailad...@spanservices.com and destroy the original message. Opinions, conclusions and other information in this message that do not relate to the official business of SPAN, shall be understood to be neither given nor endorsed by SPAN. -- Claudio
Re: Reducing ibdata1 file size
- Original Message - From: Manivannan S. manivanna...@spanservices.com How to reduce the ibdata1 file size in both LINUX and WINDOWS machine. This is by design - you cannot reduce it, nor can you remove added datafiles. If you want to shrink the ibdata files, you must stop all connections to the server, take a full backup, stop the server, remove the datafiles (and maybe change the config), restart the server (will take time to recreate emtpy datafiles) and then import the backup. For new tables, you can turn on the option innodb_file_per_table - then every (new) table gets it's own datafile; and when you drop the table, that datafile also gets deleted. -- 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
