Re: how to shrink ibdata1
File per table is required if you want to implement compression via the barracuda file format. On 3 Oct 2011 06:39, Adarsh Sharma adarsh.sha...@orkash.com 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
how to shrink ibdata1
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
Re: how to shrink ibdata1
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 signature.asc Description: OpenPGP digital signature
Re: how to shrink ibdata1
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.netwrote: 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
Re: how to shrink ibdata1
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.netwrote: 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
Re: how to shrink ibdata1
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.netwrote: 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
Re: how to shrink ibdata1
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