List:General Discussion« Previous MessageNext Message »
From:Kishore Vaishnav Date:May 22 2012 9:50am
Subject:Re: Reducing ibdata1 file size
View as plain text  
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