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


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