how to shrink ibdata1

--bcaec548640449e08f04ae56726f
Content-Type: text/plain; charset=ISO-8859-1

Hi All,

How to shrink the Mysql ibdata1 files.

The actual size of database is only hardly 10G, but ibdata1 is showing 73G
full. I did some googling and found the following procedure.

Do a mysqldump of all databases, procedures, triggers etc
Drop all databases
Stop mysql
Delete ibdata1 and ib_log files
Start mysql
Restore from dump

When you start MySQL in step 5 the ibdata1 and ib-log files will be
recreated.


Is this the only option with mysql? Can't we update the statistics of
database & reduce the file size.

I am currently using 5.0.X.


--

Regards,
Dhaval Jaiswal

--bcaec548640449e08f04ae56726f--

Re: how to shrink ibdata1

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



Am 02.10.2011 22:10, schrieb Dhaval Jaiswal:
> Hi All,
>=20
> How to shrink the Mysql ibdata1 files.
>=20
> The actual size of database is only hardly 10G, but ibdata1 is showing =
73G
> full. I did some googling and found the following procedure.
>=20
> Do a mysqldump of all databases, procedures, triggers etc
> Drop all databases
> Stop mysql
> Delete ibdata1 and ib_log files
> Start mysql
> Restore from dump
>=20
> When you start MySQL in step 5 the ibdata1 and ib-log files will be
> recreated.
>=20
>=20
> Is this the only option with mysql? Can't we update the statistics of
> database & reduce the file size.
>=20
> I am currently using 5.0.X.

"innodb_file_per_table" is your friend, but way too late
that should have been considered BEFORE the setup

now you have only the option dump your databases
reset the server
configure innodb_file_per_table
restore the backup


--------------enig573614413DE185DD959DF945
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.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk6Ix3QACgkQhmBjz394Ann8dgCfTpKkqZOn5xMEJHjUllQ8 jNEV
HHIAni8krDaFboSwjj1TGBDvpvqJJMo1
=qjax
-----END PGP SIGNATURE-----

--------------enig573614413DE185DD959DF945--

Re: how to shrink ibdata1

--20cf307cffea322a2804ae56aa1f
Content-Type: text/plain; charset=ISO-8859-1

Currently in my database only 5 tables are of innodb engine.

"innodb_file_per_table" will create each file per table is it ? what are the
other benefits of it.

If so than again i have to follow the dump & restore procedure.



On Mon, Oct 3, 2011 at 1:50 AM, Reindl Harald wrote:

>
>
> Am 02.10.2011 22:10, schrieb Dhaval Jaiswal:
> > Hi All,
> >
> > How to shrink the Mysql ibdata1 files.
> >
> > The actual size of database is only hardly 10G, but ibdata1 is showing
> 73G
> > full. I did some googling and found the following procedure.
> >
> > Do a mysqldump of all databases, procedures, triggers etc
> > Drop all databases
> > Stop mysql
> > Delete ibdata1 and ib_log files
> > Start mysql
> > Restore from dump
> >
> > When you start MySQL in step 5 the ibdata1 and ib-log files will be
> > recreated.
> >
> >
> > Is this the only option with mysql? Can't we update the statistics of
> > database & reduce the file size.
> >
> > I am currently using 5.0.X.
>
> "innodb_file_per_table" is your friend, but way too late
> that should have been considered BEFORE the setup
>
> now you have only the option dump your databases
> reset the server
> configure innodb_file_per_table
> restore the backup
>
>


--
Regards,
Dhaval Jaiswal

--20cf307cffea322a2804ae56aa1f--

Re: how to shrink ibdata1

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

yes and after that you can do "optimize table" like for MYISAM
"ibdata1", "ib_logfile0", "ib_logfile1" will still exists and
MUST NOT be removed but "ibdata1" will not grow endless

i do not know "other benefits" but they are enough

on the other hand - what are the benefits of having a dumb large
and endless growing file with no way to shrink?

[root@srv-rhsoft:~]$ ls /data/db/mysql-srv/dbmail/ | grep ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 cms1_config.ibd
-rw-rw---- 1 mysql mysql 64K 2011-10-02 04:00 cms1_global_cache.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 cms1_haupt.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 cms1_locks.ibd
-rw-rw---- 1 mysql mysql 64K 2011-10-02 00:06 cms1_meta.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 cms1_snippets.ibd
-rw-rw---- 1 mysql mysql 96K 2011-09-23 17:49 cms1_sub2.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 cms1_sub.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 cms1_user_group_permission=
s.ibd
-rw-rw---- 1 mysql mysql 64K 2011-10-02 00:43 cms1_user_login.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-24 17:51 cms1_user_modules.ibd
-rw-rw---- 1 mysql mysql 64K 2011-10-02 00:06 cms1_users.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_aliases_global.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_aliases.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_allowed_hosts.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_client_admins.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_clients.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 dbmail_acl.ibd
-rw-rw---- 1 mysql mysql 72K 2011-09-23 17:50 dbmail_aliases.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbmail_auto_notifications.=
ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbmail_auto_replies.ibd
-rw-rw---- 1 mysql mysql 72K 2011-09-23 17:50 dbmail_ccfield.ibd
-rw-rw---- 1 mysql mysql 216K 2011-10-02 18:31 dbmail_datefield.ibd
-rw-rw---- 1 mysql mysql 5,0M 2011-10-02 18:31 dbmail_envelope.ibd
-rw-rw---- 1 mysql mysql 448K 2011-10-02 18:31 dbmail_fromfield.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-30 11:56 dbmail_headername.ibd
-rw-rw---- 1 mysql mysql 11M 2011-10-02 18:31 dbmail_headervalue.ibd
-rw-rw---- 1 mysql mysql 72K 2011-09-23 17:50 dbmail_mailboxes.ibd
-rw-rw---- 1 mysql mysql 26M 2011-10-02 18:31 dbmail_messageblks.ibd
-rw-rw---- 1 mysql mysql 5,0M 2011-10-02 18:50 dbmail_messages.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbmail_pbsp.ibd
-rw-rw---- 1 mysql mysql 160K 2011-10-02 18:31 dbmail_physmessage.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-30 11:59 dbmail_referencesfield.ibd=

-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbmail_replycache.ibd
-rw-rw---- 1 mysql mysql 72K 2011-09-23 17:50 dbmail_replytofield.ibd
-rw-rw---- 1 mysql mysql 72K 2011-09-23 17:50 dbmail_sievescripts.ibd
-rw-rw---- 1 mysql mysql 360K 2011-10-02 18:31 dbmail_subjectfield.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbmail_subscription.ibd
-rw-rw---- 1 mysql mysql 376K 2011-10-02 18:31 dbmail_tofield.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbmail_usermap.ibd
-rw-rw---- 1 mysql mysql 64K 2011-10-02 18:50 dbmail_users.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_mta.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_recipient_relay.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_reply_groups.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_rewrite_domains.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_rewrite_senders.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_sender_relay.ibd
-rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_spamfilter.ibd
-rw-rw---- 1 mysql mysql 128K 2011-09-23 17:50 dbma_transports_error.ibd

Am 02.10.2011 22:25, schrieb Dhaval Jaiswal:
> Currently in my database only 5 tables are of innodb engine.
>=20
> "innodb_file_per_table" will create each file per table is it ? what ar=
e the
> other benefits of it.
>=20
> If so than again i have to follow the dump & restore procedure.
>=20
>=20
>=20
> On Mon, Oct 3, 2011 at 1:50 AM, Reindl Harald w=
rote:
>=20
>>
>>
>> Am 02.10.2011 22:10, schrieb Dhaval Jaiswal:
>>> Hi All,
>>>
>>> How to shrink the Mysql ibdata1 files.
>>>
>>> The actual size of database is only hardly 10G, but ibdata1 is showin=
g
>> 73G
>>> full. I did some googling and found the following procedure.
>>>
>>> Do a mysqldump of all databases, procedures, triggers etc
>>> Drop all databases
>>> Stop mysql
>>> Delete ibdata1 and ib_log files
>>> Start mysql
>>> Restore from dump
>>>
>>> When you start MySQL in step 5 the ibdata1 and ib-log files will be
>>> recreated.
>>>
>>>
>>> Is this the only option with mysql? Can't we update the statistics of=

>>> database & reduce the file size.
>>>
>>> I am currently using 5.0.X.
>>
>> "innodb_file_per_table" is your friend, but way too late
>> that should have been considered BEFORE the setup
>>
>> now you have only the option dump your databases
>> reset the server
>> configure innodb_file_per_table
>> restore the backup
>>
>>
>=20
>=20

--=20

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
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


--------------enigDEF9D338D054B85085B8E387
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.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk6IyfkACgkQhmBjz394AnlQYACfdkkwLnnt+MlAIBYSzRVO 5cxN
1YcAni2d8rzjFq3SheiLqCLVxR0LnPil
=ogPK
-----END PGP SIGNATURE-----

--------------enigDEF9D338D054B85085B8E387--

Re: how to shrink ibdata1

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

Thanks to all for your detailed info.

Hope Mysql community will add some solution for this in new release as to w=
e
moved from MyISAM to InooDB for performance purpose.


On Mon, Oct 3, 2011 at 2:00 AM, Reindl Harald wrote=
:

> yes and after that you can do "optimize table" like for MYISAM
> "ibdata1", "ib_logfile0", "ib_logfile1" will still exists and
> MUST NOT be removed but "ibdata1" will not grow endless
>
> i do not know "other benefits" but they are enough
>
> on the other hand - what are the benefits of having a dumb large
> and endless growing file with no way to shrink?
>
> [root@srv-rhsoft:~]$ ls /data/db/mysql-srv/dbmail/ | grep ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 cms1_config.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-10-02 04:00 cms1_global_cache.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 cms1_haupt.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 cms1_locks.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-10-02 00:06 cms1_meta.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 cms1_snippets.ibd
> -rw-rw---- 1 mysql mysql 96K 2011-09-23 17:49 cms1_sub2.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 cms1_sub.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49
> cms1_user_group_permissions.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-10-02 00:43 cms1_user_login.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-24 17:51 cms1_user_modules.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-10-02 00:06 cms1_users.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_aliases_global.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_aliases.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_allowed_hosts.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_client_admins.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_clients.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:49 dbmail_acl.ibd
> -rw-rw---- 1 mysql mysql 72K 2011-09-23 17:50 dbmail_aliases.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50
> dbmail_auto_notifications.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbmail_auto_replies.ibd
> -rw-rw---- 1 mysql mysql 72K 2011-09-23 17:50 dbmail_ccfield.ibd
> -rw-rw---- 1 mysql mysql 216K 2011-10-02 18:31 dbmail_datefield.ibd
> -rw-rw---- 1 mysql mysql 5,0M 2011-10-02 18:31 dbmail_envelope.ibd
> -rw-rw---- 1 mysql mysql 448K 2011-10-02 18:31 dbmail_fromfield.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-30 11:56 dbmail_headername.ibd
> -rw-rw---- 1 mysql mysql 11M 2011-10-02 18:31 dbmail_headervalue.ibd
> -rw-rw---- 1 mysql mysql 72K 2011-09-23 17:50 dbmail_mailboxes.ibd
> -rw-rw---- 1 mysql mysql 26M 2011-10-02 18:31 dbmail_messageblks.ibd
> -rw-rw---- 1 mysql mysql 5,0M 2011-10-02 18:50 dbmail_messages.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbmail_pbsp.ibd
> -rw-rw---- 1 mysql mysql 160K 2011-10-02 18:31 dbmail_physmessage.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-30 11:59 dbmail_referencesfield.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbmail_replycache.ibd
> -rw-rw---- 1 mysql mysql 72K 2011-09-23 17:50 dbmail_replytofield.ibd
> -rw-rw---- 1 mysql mysql 72K 2011-09-23 17:50 dbmail_sievescripts.ibd
> -rw-rw---- 1 mysql mysql 360K 2011-10-02 18:31 dbmail_subjectfield.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbmail_subscription.ibd
> -rw-rw---- 1 mysql mysql 376K 2011-10-02 18:31 dbmail_tofield.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbmail_usermap.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-10-02 18:50 dbmail_users.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_mta.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_recipient_relay.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_reply_groups.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_rewrite_domains.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_rewrite_senders.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_sender_relay.ibd
> -rw-rw---- 1 mysql mysql 64K 2011-09-23 17:50 dbma_spamfilter.ibd
> -rw-rw---- 1 mysql mysql 128K 2011-09-23 17:50 dbma_transports_error.ibd
>
> Am 02.10.2011 22:25, schrieb Dhaval Jaiswal:
> > Currently in my database only 5 tables are of innodb engine.
> >
> > "innodb_file_per_table" will create each file per table is it ? what ar=
e
> the
> > other benefits of it.
> >
> > If so than again i have to follow the dump & restore procedure.
> >
> >
> >
> > On Mon, Oct 3, 2011 at 1:50 AM, Reindl Harald > >wrote:
> >
> >>
> >>
> >> Am 02.10.2011 22:10, schrieb Dhaval Jaiswal:
> >>> Hi All,
> >>>
> >>> How to shrink the Mysql ibdata1 files.
> >>>
> >>> The actual size of database is only hardly 10G, but ibdata1 is showin=
g
> >> 73G
> >>> full. I did some googling and found the following procedure.
> >>>
> >>> Do a mysqldump of all databases, procedures, triggers etc
> >>> Drop all databases
> >>> Stop mysql
> >>> Delete ibdata1 and ib_log files
> >>> Start mysql
> >>> Restore from dump
> >>>
> >>> When you start MySQL in step 5 the ibdata1 and ib-log files will be
> >>> recreated.
> >>>
> >>>
> >>> Is this the only option with mysql? Can't we update the statistics of
> >>> database & reduce the file size.
> >>>
> >>> I am currently using 5.0.X.
> >>
> >> "innodb_file_per_table" is your friend, but way too late
> >> that should have been considered BEFORE the setup
> >>
> >> now you have only the option dump your databases
> >> reset the server
> >> configure innodb_file_per_table
> >> restore the backup
> >>
> >>
> >
> >
>
> --
>
> Mit besten Grüßen, Reindl Harald
> the lounge interactive design GmbH
> A-1060 Vienna, Hofmühlgasse 17
> CTO / software-development / cms-solutions
> 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
>
>


--=20
Regards,
Dhaval Jaiswal

--20cf307cffea292df104ae56e201--

Re: how to shrink ibdata1

--------------000707010409070002020501
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

innnodb_file per table creates ibdata files for each table and What to
do if some tables data are deleted frequently.
I have a innodb table which was truncated after 150GB in mysql database.


Reindl Harald wrote:
> Am 02.10.2011 22:10, schrieb Dhaval Jaiswal:
>
>> Hi All,
>>
>> How to shrink the Mysql ibdata1 files.
>>
>> The actual size of database is only hardly 10G, but ibdata1 is showing 73G
>> full. I did some googling and found the following procedure.
>>
>> Do a mysqldump of all databases, procedures, triggers etc
>> Drop all databases
>> Stop mysql
>> Delete ibdata1 and ib_log files
>> Start mysql
>> Restore from dump
>>
>> When you start MySQL in step 5 the ibdata1 and ib-log files will be
>> recreated.
>>
>>
>> Is this the only option with mysql? Can't we update the statistics of
>> database & reduce the file size.
>>
>> I am currently using 5.0.X.
>>
>
> "innodb_file_per_table" is your friend, but way too late
> that should have been considered BEFORE the setup
>
> now you have only the option dump your databases
> reset the server
> configure innodb_file_per_table
> restore the backup
>
>


--------------000707010409070002020501--

Re: how to shrink ibdata1

--000e0cd7199692cca704ae623dfb
Content-Type: text/plain; charset=ISO-8859-1

File per table is required if you want to implement compression via the
barracuda file format.
On 3 Oct 2011 06:39, "Adarsh Sharma" wrote:
> innnodb_file per table creates ibdata files for each table and What to
> do if some tables data are deleted frequently.
> I have a innodb table which was truncated after 150GB in mysql database.
>
>
> Reindl Harald wrote:
>> Am 02.10.2011 22:10, schrieb Dhaval Jaiswal:
>>
>>> Hi All,
>>>
>>> How to shrink the Mysql ibdata1 files.
>>>
>>> The actual size of database is only hardly 10G, but ibdata1 is showing
73G
>>> full. I did some googling and found the following procedure.
>>>
>>> Do a mysqldump of all databases, procedures, triggers etc
>>> Drop all databases
>>> Stop mysql
>>> Delete ibdata1 and ib_log files
>>> Start mysql
>>> Restore from dump
>>>
>>> When you start MySQL in step 5 the ibdata1 and ib-log files will be
>>> recreated.
>>>
>>>
>>> Is this the only option with mysql? Can't we update the statistics of
>>> database & reduce the file size.
>>>
>>> I am currently using 5.0.X.
>>>
>>
>> "innodb_file_per_table" is your friend, but way too late
>> that should have been considered BEFORE the setup
>>
>> now you have only the option dump your databases
>> reset the server
>> configure innodb_file_per_table
>> restore the backup
>>
>>
>

--000e0cd7199692cca704ae623dfb--