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