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/mysql?unsub=gcdmg-mysql-2@m.gmane.org
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
On Wed, May 27, 2009 at 2:45 AM, Sebastien MORETTI
> 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=E9bastien Moretti
--=20
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
> 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/mysql?unsub=gcdmg-mysql-2@m.gmane.org
>-----Original Message-----
>From: Sebastien Moretti [mailto:Sebastien.Moretti@unil.ch]
>Sent: Thursday, May 28, 2009 2:38 AM
>To: Baron Schwartz; mysql@lists.mysql.com
>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, ...
>
[JS] I strongly suspect that MySQL, like any other random access, variable
record length scheme, would find it easier to manage the internal layout of
separate files. The rows would tend more to be of similar sizes, leading to
less obnoxious fragmentation, and the files themselves would be smaller.
>
>>> 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/mysql?unsub=jschwartz@the-
>infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
A significant gain you have with innodb_file_per_table is that of shrinking=
the tablespaces.
You can do that with "OPTIMIZE TABLE
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=3D'InnoDB';"
02) In Linux, run "mysqldump -h... -u... -p... --routines --triggers --data=
bases [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=3D'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_p=
ath=3Dibdata1:10M:autoextend' and 'innodb_file_per_table'.
09) In Linux, run "service mysql start" (This recreates ibdata1, ib_logfile=
0 and ib_logfile1)
10) In mysql, run "source /root/InnoDBData.sql" (This reloads all InnoDB da=
ta, 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
redwards@logicworks.net
-----Original Message-----
From: Sebastien Moretti [mailto:Sebastien.Moretti@unil.ch]=20
Sent: Thursday, May 28, 2009 2:38 AM
To: Baron Schwartz; mysql@lists.mysql.com
Subject: Re: innodb_file_per_table cost
> Hi Sebastian,
>=20
> 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.
>=20
> Regards
> Baron
Thanks
It seems there are no clear thresholds between I/O access, the number of=20
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
--=20
S=E9bastien Moretti
--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dredwards@logicworks=
..net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
> [JS] I strongly suspect that MySQL, like any other random access, variable
> record length scheme, would find it easier to manage the internal layout of
> separate files. The rows would tend more to be of similar sizes, leading to
> less obnoxious fragmentation, and the files themselves would be smaller.
Well, again -- it depends. Here's a good writeup:
http://yoshinorimatsunobu.blogspot.com/2009/05/overwriting-i s-much-faster-than_28.html
There's a lot of different things to think about, like which
filesystem you're using. InnoDB internally uses 16-kb pages so rows
from different tables aren't really intermingled, by the way.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org