Saving space disk (innodb)
Hello guys, I have one monster database running on MySQL 4.0.17, using InnoDB: 270GB Oct 10 14:35 ibdata1 I've deleted a lot of register of then, and I've expected that the size can be decreased if 50% (135 GB) but the ibdata was the same value than before clean... How can I force to save this space? set-variable= innodb_buffer_pool_size=500M set-variable= innodb_additional_mem_pool_size=100M set-variable= innodb_log_files_in_group=5 set-variable= innodb_log_file_size=150M set-variable= innodb_log_buffer_size=8M set-variable= innodb_flush_log_at_trx_commit=1 set-variable= innodb_lock_wait_timeout=5 set-variable= innodb_data_home_dir=/dbms/mysql/bin-4.0.17/var set-variable= innodb_data_file_path=ibdata1:1000M:autoextend set-variable= innodb_log_group_home_dir=/dbms/mysql/bin-4.0.17/var Thanks! -- Tiago Cruz http://everlinux.com Linux User #282636 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving space disk (innodb)
Hi, Tiago Cruz wrote: Hello guys, I have one monster database running on MySQL 4.0.17, using InnoDB: 270GB Oct 10 14:35 ibdata1 I've deleted a lot of register of then, and I've expected that the size can be decreased if 50% (135 GB) but the ibdata was the same value than before clean... How can I force to save this space? You must dump your data to files, shut down MySQL, delete your current InnoDB tablespace and log files, reconfigure the server, restart MySQL and let InnoDB create new (empty) files. Then reload the data. You should probably save your current data and tablespace files until you are sure you complete this successfully. It's an annoying procedure but there is no other way. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Saving space disk (innodb)
OPTIMIZE TABLE should reclaim that space, but be aware that it could take a while to run (locking your table all the while) since it just maps to an ALTER TABLE statement which creates a new copy of the table. Depends on how big your tables are. Doc: http://dev.mysql.com/doc/refman/4.1/en/optimize-table.html -Dan -Original Message- From: Tiago Cruz [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 10, 2007 2:14 PM To: mysql@lists.mysql.com Subject: Saving space disk (innodb) Hello guys, I have one monster database running on MySQL 4.0.17, using InnoDB: 270GB Oct 10 14:35 ibdata1 I've deleted a lot of register of then, and I've expected that the size can be decreased if 50% (135 GB) but the ibdata was the same value than before clean... How can I force to save this space? set-variable= innodb_buffer_pool_size=500M set-variable= innodb_additional_mem_pool_size=100M set-variable= innodb_log_files_in_group=5 set-variable= innodb_log_file_size=150M set-variable= innodb_log_buffer_size=8M set-variable= innodb_flush_log_at_trx_commit=1 set-variable= innodb_lock_wait_timeout=5 set-variable= innodb_data_home_dir=/dbms/mysql/bin-4.0.17/var set-variable= innodb_data_file_path=ibdata1:1000M:autoextend set-variable= innodb_log_group_home_dir=/dbms/mysql/bin-4.0.17/var Thanks! -- Tiago Cruz http://everlinux.com Linux User #282636 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving space disk (innodb)
If you do what Baron suggests, you may want to set Innodb to create a file-per-table - that way, in the future, you could save space when tables are dropped, or you could recreate innodb tables individually to save space, not have to dump all your innodb tables at one time. On 10/10/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Tiago Cruz wrote: Hello guys, I have one monster database running on MySQL 4.0.17, using InnoDB: 270GB Oct 10 14:35 ibdata1 I've deleted a lot of register of then, and I've expected that the size can be decreased if 50% (135 GB) but the ibdata was the same value than before clean... How can I force to save this space? You must dump your data to files, shut down MySQL, delete your current InnoDB tablespace and log files, reconfigure the server, restart MySQL and let InnoDB create new (empty) files. Then reload the data. You should probably save your current data and tablespace files until you are sure you complete this successfully. It's an annoying procedure but there is no other way. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Andy Carlson --- Gamecube:$150,PSO:$50,Broadband Adapter: $35, Hunters License: $8.95/month, The feeling of seeing the red box with the item you want in it:Priceless.
Re: Saving space disk (innodb)
Dan Rogart wrote: OPTIMIZE TABLE should reclaim that space, but be aware that it could take a while to run (locking your table all the while) since it just maps to an ALTER TABLE statement which creates a new copy of the table. Depends on how big your tables are. Doc: http://dev.mysql.com/doc/refman/4.1/en/optimize-table.html He has InnoDB tables and that doesn't reclaim tablespace. He wants to get back disk space from his data files. As of 4.1.3, |OPTIMIZE TABLE| is mapped to |ALTER TABLE|, which rebuilds the table to update index statistics and free unused space in the clustered index But that just means he has empty space in his tablespace :) At least that is how I read it, so Baron's suggestion makes the most sense. Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving space disk (innodb)
Thank you guys!! I have a lot of MyISAM and a lot of InnoDB on this database. I did one little for to run one OPTIMIZE TABLE in each table that I have, on my database. If this step don't save enough disk space, I'll do the Baron suggestion. Thank you all! - Tiago Cruz On Wed, 2007-10-10 at 15:44 -0300, Eric Frazier wrote: He has InnoDB tables and that doesn't reclaim tablespace. He wants to get back disk space from his data files. As of 4.1.3, |OPTIMIZE TABLE| is mapped to |ALTER TABLE|, which rebuilds the table to update index statistics and free unused space in the clustered index But that just means he has empty space in his tablespace :) At least that is how I read it, so Baron's suggestion makes the most sense. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving space disk (innodb)
He's using 4.0, it's not an option in that version :-( Andrew Carlson wrote: If you do what Baron suggests, you may want to set Innodb to create a file-per-table - that way, in the future, you could save space when tables are dropped, or you could recreate innodb tables individually to save space, not have to dump all your innodb tables at one time. On 10/10/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Tiago Cruz wrote: Hello guys, I have one monster database running on MySQL 4.0.17, using InnoDB: 270GB Oct 10 14:35 ibdata1 I've deleted a lot of register of then, and I've expected that the size can be decreased if 50% (135 GB) but the ibdata was the same value than before clean... How can I force to save this space? You must dump your data to files, shut down MySQL, delete your current InnoDB tablespace and log files, reconfigure the server, restart MySQL and let InnoDB create new (empty) files. Then reload the data. You should probably save your current data and tablespace files until you are sure you complete this successfully. It's an annoying procedure but there is no other way. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz Xaprb LLC http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving space disk (innodb)
Hi Tiago, Tiago Cruz wrote: Thank you guys!! I have a lot of MyISAM and a lot of InnoDB on this database. I did one little for to run one OPTIMIZE TABLE in each table that I have, on my database. If this step don't save enough disk space, I'll do the Baron suggestion. It will not shrink your InnoDB files a single byte :-) If you're trying to shrink those, it's a waste of time. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving space disk (innodb)
At 01:24 PM 10/10/2007, Baron Schwartz wrote: Hi, Tiago Cruz wrote: Hello guys, I have one monster database running on MySQL 4.0.17, using InnoDB: 270GB Oct 10 14:35 ibdata1 I've deleted a lot of register of then, and I've expected that the size can be decreased if 50% (135 GB) but the ibdata was the same value than before clean... How can I force to save this space? You must dump your data to files, Why not change the table type to MyISAM instead of dumping to a file? shut down MySQL, delete your current InnoDB tablespace and log files, reconfigure the server, restart MySQL and let InnoDB create new (empty) files. Then reload the data. Then change the table type back to InnoDb. You should probably save your current data and tablespace files until you are sure you complete this successfully. Agreed. It's an annoying procedure but there is no other way. Yes it is a pain. As I understand it, most people would prefer root canal to this. :) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Saving space disk (innodb)
So, just to clarify: optimize table just defragments the index? Apologies, I misinterpreted the documentation then. Thanks, Dan -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 10, 2007 3:05 PM To: Tiago Cruz Cc: Eric Frazier; Dan Rogart; mysql@lists.mysql.com Subject: Re: Saving space disk (innodb) Hi Tiago, Tiago Cruz wrote: Thank you guys!! I have a lot of MyISAM and a lot of InnoDB on this database. I did one little for to run one OPTIMIZE TABLE in each table that I have, on my database. If this step don't save enough disk space, I'll do the Baron suggestion. It will not shrink your InnoDB files a single byte :-) If you're trying to shrink those, it's a waste of time. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving space disk (innodb)
Andrew Carlson wrote: If you do what Baron suggests, you may want to set Innodb to create a file-per-table - that way, in the future, you could save space when tables are dropped, or you could recreate innodb tables individually to save space, not have to dump all your innodb tables at one time. I think this is a fantastic idea. So you would - do your DB dump(horrible with hundreds of Gigs.) - reset your my.cnf setting to include: [mysqld] innodb_file_per_table - stop the db - kill off the existing tablespace files - restart the DB - recreate the database and import your dump. http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html So the only other question is what is the cost if any? It is a good idea because often there are just a few tables that get really big and this is a nice way to deal with them separately like you would with MyISAM. Eric On 10/10/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Tiago Cruz wrote: Hello guys, I have one monster database running on MySQL 4.0.17, using InnoDB: 270GB Oct 10 14:35 ibdata1 I've deleted a lot of register of then, and I've expected that the size can be decreased if 50% (135 GB) but the ibdata was the same value than before clean... How can I force to save this space? You must dump your data to files, shut down MySQL, delete your current InnoDB tablespace and log files, reconfigure the server, restart MySQL and let InnoDB create new (empty) files. Then reload the data. You should probably save your current data and tablespace files until you are sure you complete this successfully. It's an annoying procedure but there is no other way. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving space disk (innodb)
mos wrote: At 01:24 PM 10/10/2007, Baron Schwartz wrote: Hi, Tiago Cruz wrote: Hello guys, I have one monster database running on MySQL 4.0.17, using InnoDB: 270GB Oct 10 14:35 ibdata1 I've deleted a lot of register of then, and I've expected that the size can be decreased if 50% (135 GB) but the ibdata was the same value than before clean... How can I force to save this space? You must dump your data to files, Why not change the table type to MyISAM instead of dumping to a file? If you have a bunch of indexes on the table, you're creating the indexes on the MyISAM table too. It could be a lot more expensive than a dump and restore. Otherwise it sounds like a good idea. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving space disk (innodb)
Eric Frazier wrote: Andrew Carlson wrote: If you do what Baron suggests, you may want to set Innodb to create a file-per-table - that way, in the future, you could save space when tables are dropped, or you could recreate innodb tables individually to save space, not have to dump all your innodb tables at one time. I think this is a fantastic idea. So you would - do your DB dump(horrible with hundreds of Gigs.) - reset your my.cnf setting to include: [mysqld] innodb_file_per_table - stop the db - kill off the existing tablespace files - restart the DB - recreate the database and import your dump. http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html So the only other question is what is the cost if any? It is a good idea because often there are just a few tables that get really big and this is a nice way to deal with them separately like you would with MyISAM. You still can't get rid of the shared tablespace file completely; the separate tablespace files hold only the data and indexes. InnoDB stores the data dictionary, rollback segment etc in the main tablespace. Another cost is external fragmentation as opposed to internal. Admittedly, I do like file-per-table better. It's just not 100% upside. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving space disk (innodb)
At 02:40 PM 10/10/2007, Baron Schwartz wrote: mos wrote: At 01:24 PM 10/10/2007, Baron Schwartz wrote: Hi, Tiago Cruz wrote: Hello guys, I have one monster database running on MySQL 4.0.17, using InnoDB: 270GB Oct 10 14:35 ibdata1 I've deleted a lot of register of then, and I've expected that the size can be decreased if 50% (135 GB) but the ibdata was the same value than before clean... How can I force to save this space? You must dump your data to files, Why not change the table type to MyISAM instead of dumping to a file? If you have a bunch of indexes on the table, you're creating the indexes on the MyISAM table too. It could be a lot more expensive than a dump and restore. Otherwise it sounds like a good idea. Baron, Ok, then how about this: create table table2 select * from table1 limit 0; alter table table2 engine=myisam; insert into table2 select * from table1; Now you can count the rows in Table2 to make sure it agrees with Table1. You can also browse the data to make sure the column data is correct. I'm always a bit leary of dumping and reloading from a csv file because it could miss data and you wouldn't know which rows are missing. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
