List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:May 22 2012 9:59am
Subject:Re: Reducing ibdata1 file size
View as plain text  
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.reindl@stripped
> <mailto: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
> <mailto: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 <mailto: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 <mailto: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 <mailto: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 <mailto:vegivamp@stripped>
>     >>>>>> wrote:
>     >>>>>
>     >>>>>> ----- Original Message -----
>     >>>>>>> From: "Manivannan S."
> <manivannan_s@stripped <mailto: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
> 
> 

-- 

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


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
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