A significant gain you have with innodb_file_per_table is that of shrinking the
tablespaces.
You can do that with "OPTIMIZE TABLE <innodb-tbl>;"
You ibdata1 file should only contain metadata and some transaction logging info.
If your ibdata1 is gigantic, you have to do the following to shrink it:
01) In mysql, run "SELECT GROUP_CONCAT(DISTINCT table_schema SEPARATOR ' ')
FROM information_schema.tables where engine='InnoDB';"
02) In Linux, run "mysqldump -h... -u... -p... --routines --triggers
--databases [space-delimited list of dbs from step 1] > /root/InnoDBData.sql"
03) In mysql, run "SELECT DISTINCT CONCAT('DROP DATABASE ',table_schema,';')
FROM information_schema.tables where engine='InnoDB';"
04) In mysql, drop the databases specified in step 3
05) In Linux, run "service mysql stop"
06) In Linux, run "mv /var/lib/mysql/ibdata1 /var/lib/mysql/ibdata1.old"
07) In Linux, run "rm -f /var/lib/mysql/ib_logfile[01]"
08) In Linux, run "vi /etc/my.cnf" and make sure it has
'innodb_data_file_path=ibdata1:10M:autoextend' and 'innodb_file_per_table'.
09) In Linux, run "service mysql start" (This recreates ibdata1, ib_logfile0
and ib_logfile1)
10) In mysql, run "source /root/InnoDBData.sql" (This reloads all InnoDB data,
populates ibdata1 with metadata)
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
[email protected]
-----Original Message-----
From: Sebastien Moretti [mailto:[email protected]]
Sent: Thursday, May 28, 2009 2:38 AM
To: Baron Schwartz; [email protected]
Subject: Re: innodb_file_per_table cost
> Hi Sebastian,
>
> It depends. In general, no. In some filesystems and operating
> systems, it actually helps. I think you can base your decision on
> whether it makes server administration easier for you.
>
> Regards
> Baron
Thanks
It seems there are no clear thresholds between I/O access, the number of
innodb index files, their sizes, ...
>> Hi,
>>
>> Does the use of "innodb_file_per_table" option imply a performance cost ?
>> Compared to default: all InnoDB indexes are in ibdataX file(s).
>>
>> Thanks
--
Sébastien Moretti
--
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]
