How To Store InnoDB Data In One File Per Table

How To Store InnoDB Data In One File Per Table

To achieve faster performance of MySQL server with InnoDB engine and for I/O optimization one can choose file-per-table mode.

InnoDB storage engine stores all data for tables in a common tablespace. It is possible to configure InnoDB to store data for each table in separate files.

How to do it:

♦ Add the following line to the “mysqld” section of my.cnf:

 

innodb_file_per_table

 

♦ Restart database instance

Be aware that this setting will only be applied to the newly created databases.

If you would like to have file-per-table mode for all existing databases, it is better to contact our support team as it is complicated to perform task. They can do that without any problems.


Now, you can map tables to different physical disks or back up and recover certain tables on the file level. If you delete data from a table, the file size will not reduce automatically.
As each table uses its own tablespace, this means that the space that gets freed by deleting from one table cannot be reused by another table, as is the case for the “classical” shared InnoDB tablespace.


    • Related Articles

    • How to run manual file system check

      How to run manual file system check Category: Cloud Servers, Recovery Interface, VPS.net dashboard, VPS.net features &nbsp If server is not coming up after “Startup” button is pressed, and you see one of the following messages in server console in ...

    • MySQL table marked as crashed and should be repaired

      MySQL table marked as crashed and should be repaired Category: Recovery Interface &nbsp If you are getting the following error message: Table ‘jos151_session’ is marked as crashed and should be repaired this means mysql service was terminated while ...

    • How to Find Path to php.ini File

      In many instances, locating the active php.ini file is very simple. Run the following command to view the instances of php.ini, including “Loaded Configuration File”: php.ini # php --ini Example output: # php --ini Configuration File (php.ini) Path: ...

    • Content Delivery Network

      Our Content Delivery Network (CDN) product is here. What is a CDN? And why should I care? How much does it cost? Is there a time limit on the PAYG bandwidth? What CDN services do you offer? Can I use it with multiple sites? What file formats are ...

    • How To Use Shoutcast

      A single install of a shoutcast server has been installed, simply login as the shoutcast user either by setting the password (passwd shoutcast) or switching user (su shoutcast) and in /home/shoutcast begin configuring sc_serv.conf to get your first ...