Mailing List
Home
Forum Home
MySQL General - General MySQL discussion
MySQL++ - Programming with the C++ API to MySQL
MaxDB - Everything about MaxDB, formerly known as SAP DB
ODBC - ODBC with the MySQL Connector/ODBC driver
MySQL on Win32 - Runing MySQL on Windows 9x/Me/NT/2000/XP
Java Help - Mostly related to the MySQL Connector/J driver
Perl - Perl support for MySQL with DBI and DBD::mysql
GUI - MySQL GUI Tools
Announcement
Subjects
• mysql openssl Question
• ERROR 1045: Access denied for user: 'root@localhost ' (Using
password: NO)
• Update one field with more fields from another table
• Getting Identity after INSERT
• ERROR 2002: Can 't connect to local MySQL server through socket
• mysql test 4 1 fails with the gis test
• MySQL Cluster Software
• Downgrade Mysql from 4 to 3 23
• Mysql 4 0 Oracle Stored Procedure Trigger Conversion
• Can 't access mysql after kernel upgrade
• Executing MySQL Commands From Within C Program
• Comparing and writing out BLOBS
• Preventing Duplicate Entries
• FULLTEXT query format question
• Strange behavior, Table Level Permission
• Does the binary log enabling affect the MySQL performances?
• mysql:it 's a db not a dbms how it 's possible?!
• mysql have same function mthod as Oracle decode()
 
Saving space disk (innodb)

Saving space disk (innodb)

2007-10-10       - By Baron Schwartz

 Back
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/mysql?unsub=mysql@(protected)