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.org>wrote: > 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 >>> >> > >>> >> > >>> >> >>> > >>> >> >> >
