Why using innodb_file_per_table?
Because it is easier to manage individual since it can be done at the file-level. This means that even if the server is down, you can still copy data by copying the table files whereas using a shared table-space means either copying everything which can be unnecessarily massive, or finding some way to get the server running to extract data (you really don’t want to manually extract the data with a hex-editor).
Someone warned that you cannot simply copy and paste
.ibd files from one server to another. This may be true, but it should not apply to backups on the same server (I am using the term backup here in the traditional sense of making a copy; i.e., not drastically changing the whole thing). Moreover,
ibdata1 is automatically recreated on startup (as seen in the delete
ibdata1 step of most “converting to file-per-table” guides). As such, you do not need to copy
ibdata1 in addition to your
.ibd files (and their corresponding
.frm, etc. files).
If trying to recover a lost table, it should be sufficient to copy its
.frm file, as well as
information_schema (which is much smaller than
ibdata1). That way, you can put them in a dummy server and extract your table without having to copy the whole, massive thing.
However, the claim for better performance is questionable. … With innodb_file_per_table, more disk I/O operations are needed; and this is significant in complicated JOINs and FOREIGN KEY constraints.
Not surprisingly, the performance will depend entirely on the specific database(s) in use. One person will have (even vastly) different results from another.
It is true that there will be more disk I/O operations with file-per-table, but only slightly more. Think about how the system works.
You will notice that when the server is running, you cannot move the data files because the server has open handles to them. This is because when it starts up, it opens them and leaves them open. It does not open and close them for each individual query.
As such, there is only some more I/O operations at the beginning, when the server starts up; not while it is running. Further, while each individual
.ibd file has its own separate overhead (file signatures, structures, etc.), they are cached in memory and not re-read for each query. Moreover, the same structures are read even with a shared table-space, so there is barely any (if any at all) more memory required.
Does innodb_file_per_table has an effect on a better performance of mysql?
Actually, if anything, the performance may in fact be worse.
When using a shared table-space, read and write operations can sometimes/often be combined so that the server reads a swatch of data from multiple tables in one go from
However, if the data is spread out amongst multiple files, then it has to perform a separate I/O operation for each one individually.
Of course this is again entirely dependent on the database in question; the real-world performance impact would depend on size, query frequency, and internal fragmentation of the shared table-space. Some people may notice a large difference while others may not see any impact at all.
Tablespace is shared on single ibdata; how dedicated tablespaces for separate tables can save disk space?
It does not. If anything, it increases disk usage some.
I don’t have a 60GB database to test with, but my “paltry” personal database which contains my WordPress installation and some a few small tables for personal use and development testing weighed in at ~30MB while using a shared table-space. After converting it to file-per-table, it bloated to ~85MB. Even by dropping everything and re-importing, it was still >60MB.
This increase is due to two factors:
The absolute minimum size for
ibdata1 is—for some reason—10MB, even if you have nothing but
information_schema stored in it.
With a shared table-space, only
ibdata1 has overhead like file signatures, meta-data, etc., but with per-table, each individual
.ibd file has all of that. This means that the total (even with a hypothetical <10MB
ibdata1) would be somewhat larger by at least:
GetTotalSizeofOverhead() * GetNumTables()
Obviously these are not going to be huge increases (unless you are using a host that limits your database size or storing them on a flash-drive, etc.), but they are increases nonetheless, and while by switching (every) table to file-per-table you can shrink
ibdata1 down to 10MB, the overall total will invariably be more than it was.