Thanks to all for your detailed info. Hope Mysql community will add some solution for this in new release as to we moved from MyISAM to InooDB for performance purpose.
On Mon, Oct 3, 2011 at 2:00 AM, Reindl Harald <h.rei...@thelounge.net>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 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 <h.rei...@thelounge.net > >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 > >> > >> > > > > > > -- > > 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 > > -- Regards, Dhaval Jaiswal
