Databases Reference
In-Depth Information
That creates a 3 GB tablespace in three files. Sometimes people wonder whether they
can use multiple files to spread load across drives, like this:
innodb_data_file_path = /disk1/ibdata1:1G;/disk2/ibdata2:1G;...
While that does indeed place the files in different directories, which represent different
drives in this example, InnoDB concatenates the files end-to-end. Thus, you usually
don't gain much this way. InnoDB will fill the first file, then the second when the first
is full, and so on; the load isn't really spread in the fashion you need for higher perfor-
mance. A RAID controller is a smarter way to spread load.
To allow the tablespace to grow if it runs out of space, you can make the last file
autoextend as follows:
...ibdata3:1G:autoextend
The default behavior is to create a single 10 MB autoextending file. If you make the file
autoextend, it's a good idea to place an upper limit on the tablespace's size to keep it
from growing very large, because once it grows, it doesn't shrink. For example, the
following example limits the autoextending file to 2 GB:
...ibdata3:1G:autoextend:max:2G
Managing a single tablespace can be a hassle, especially if it autoextends and you want
to reclaim the space (for this reason, we recommend disabling the autoextend feature,
or at least setting a reasonable cap on the space). The only way to reclaim space is to
dump your data, shut down MySQL, delete all the files, change the configuration,
restart, let InnoDB create new empty files, and restore your data. InnoDB is completely
unforgiving about its tablespace—you cannot simply remove files or change their sizes.
It will refuse to start if you corrupt its tablespace. It is likewise very strict about its log
files. If you're used to casually moving files around with MyISAM, take heed!
The
innodb_file_per_table
option lets you configure InnoDB to use one file per table
in MySQL 4.1 and later. It stores the data in the database directory as
tablename.ibd
files. This makes it easier to reclaim space when you drop a table, and it can be useful
for spreading tables across multiple disks. However, placing the data in multiple files
can actually result in more wasted space overall, because it trades internal fragmenta-
tion in the single InnoDB tablespace for wasted space in the
.ibd
files. This is more of
an issue for very small tables, because InnoDB's page size is 16 KB. Even if your table
has only 1 KB of data, it will still require at least 16 KB on disk.
Even if you enable the
innodb_file_per_table
option, you'll still need the main table-
space for the undo logs and other system data. It will be smaller if you're not storing
all the data in it, but it's still a good idea to disable autoextend, because you can't shrink
the file without reloading all your data.
Some people like to use
innodb_file_per_table
just because of the extra manageability
and visibility it gives you. For example, it's much faster to find a table's size by exam-
ining a single file than it is to use
SHOW TABLE STATUS
, which has to perform more com-
plex work to determine how many pages are allocated to a table.
