List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:October 2 2011 8:30pm
Subject:Re: how to shrink ibdata1
View as plain text  
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.reindl@stripped>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


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
Thread
how to shrink ibdata1Dhaval Jaiswal2 Oct
  • Re: how to shrink ibdata1Reindl Harald2 Oct
    • Re: how to shrink ibdata1Dhaval Jaiswal2 Oct
      • Re: how to shrink ibdata1Reindl Harald2 Oct
        • Re: how to shrink ibdata1Dhaval Jaiswal2 Oct
    • Re: how to shrink ibdata1Adarsh Sharma3 Oct
      • Re: how to shrink ibdata1Andrew Moore3 Oct