List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:May 22 2012 9:54am
Subject:Re: Reducing ibdata1 file size
View as plain text  
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
<kishore@stripped>wrote:

> 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.reindl@stripped
> >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 <anandkl@stripped>
> 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 <
> > >> kishore@stripped> 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.nanni@stripped
> > >>>> 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" <
> > kishore@stripped>
> > >>>> 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 <
> > vegivamp@stripped
> > >>>>>> wrote:
> > >>>>>
> > >>>>>> ----- Original Message -----
> > >>>>>>> From: "Manivannan S."
> <manivannan_s@stripped>
> > >>>>>>>
> > >>>>>>> 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
> >
> >
>

Thread
Reducing ibdata1 file sizeManivannan S .21 May
  • Re: Reducing ibdata1 file sizeClaudio Nanni21 May
  • Re: Reducing ibdata1 file sizeJohan De Meersman21 May
    • Re: Reducing ibdata1 file sizeKishore Vaishnav22 May
      • Re: Reducing ibdata1 file sizeClaudio Nanni22 May
        • Re: Reducing ibdata1 file sizeKishore Vaishnav22 May
          • Re: Reducing ibdata1 file sizeAnanda Kumar22 May
            • Re: Reducing ibdata1 file sizeKishore Vaishnav22 May
              • Re: Reducing ibdata1 file sizeReindl Harald22 May
                • Re: Reducing ibdata1 file sizeKishore Vaishnav22 May
                  • Re: Reducing ibdata1 file sizeAnanda Kumar22 May
                  • Re: Reducing ibdata1 file sizeReindl Harald22 May
                    • Re: Reducing ibdata1 file sizeJohan De Meersman22 May
                      • Re: Reducing ibdata1 file sizeReindl Harald22 May
                        • Re: Reducing ibdata1 file sizeJohan De Meersman22 May
                          • Re: Reducing ibdata1 file sizeReindl Harald22 May
                            • Re: Reducing ibdata1 file sizeJohan De Meersman22 May
                              • Re: Reducing ibdata1 file sizeReindl Harald22 May
                      • Re: Reducing ibdata1 file sizeBaron Schwartz22 May
              • Re: Reducing ibdata1 file sizeAnanda Kumar22 May
          • Re: Reducing ibdata1 file sizeClaudio Nanni22 May
  • RE: Reducing ibdata1 file sizeRick James22 May
Re: Reducing ibdata1 file sizeJohan De Meersman22 May
  • Re: Reducing ibdata1 file sizeAnanda Kumar22 May
    • Re: Reducing ibdata1 file sizeJohan De Meersman22 May
      • Re: Reducing ibdata1 file sizeAnanda Kumar22 May
        • Re: Reducing ibdata1 file sizeJohan De Meersman22 May
          • Re: Reducing ibdata1 file sizeReindl Harald22 May
            • Re: Reducing ibdata1 file sizeJohan De Meersman22 May
              • Re: Reducing ibdata1 file sizeAnanda Kumar22 May
                • Re: Reducing ibdata1 file sizeAnanda Kumar22 May
Re: Reducing ibdata1 file sizeJan Steinman22 May
  • Re: Reducing ibdata1 file sizeJohan De Meersman22 May
  • Re: Reducing ibdata1 file sizeClaudio Nanni22 May
    • RE: Reducing ibdata1 file sizeKay [DAS] Rozeboom22 May
    • Re: Reducing ibdata1 file sizeJan Steinman22 May