Re: how to shrink ibdata1

2011-10-03 Thread Andrew Moore
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

2011-10-02 Thread 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.


-- 

Regards,
Dhaval Jaiswal


Re: how to shrink ibdata1

2011-10-02 Thread Reindl Harald


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

2011-10-02 Thread 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




-- 
Regards,
Dhaval Jaiswal


Re: how to shrink ibdata1

2011-10-02 Thread Reindl Harald
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

2011-10-02 Thread Dhaval Jaiswal
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

2011-10-02 Thread Adarsh Sharma
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