From: Reindl Harald
Date: May 22 2012 9:37am
Subject: Re: Reducing ibdata1 file size
List-Archive: http://lists.mysql.com/mysql/227482
Message-Id: <4FBB5E49.80203@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="------------enig18925EE5E2B91453C8B4BA19"

--------------enig18925EE5E2B91453C8B4BA19
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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 fi=
le
> per table.
>=20
> On Tue, May 22, 2012 at 2:56 PM, Ananda Kumar <anandkl@stripped> wrote=
:
>=20
>> 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=
om
>>>> wrote:
>>>
>>>> Kishore,
>>>> No, as already explained, it is not possible, Innodb datafiles *neve=
r*
>>>> shrink.
>>>>
>>>> Cheers
>>>>
>>>> Claudio
>>>> On May 22, 2012 10:05 AM, "Kishore Vaishnav" <kishore@stripped=
rg>
>>>> 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@tuxera=
=2Ebe
>>>>>> 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 datafi=
les
>>>>> (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
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>>
>=20

--=20

Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofm=FChlgasse 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


--------------enig18925EE5E2B91453C8B4BA19
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk+7XkkACgkQhmBjz394AnndRgCfQsXSXc9B0g7sfJRV/ef2ABMW
a3IAn1/HZ3qQiKqTqQU5XtVPcVVaop3S
=4IHD
-----END PGP SIGNATURE-----

--------------enig18925EE5E2B91453C8B4BA19--