MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
The system tablespace is the storage area for the
InnoDB
data dictionary, the doublewrite buffer,
the change buffer, and undo logs. It may also contain table and
index data if tables are created in the system tablespace rather
than file-per-table tablespaces.
The system tablespace can have one or more data files. By default,
a single system tablespace data file, named
ibdata1
, is created in the data directory.
The size and number of system tablespace data files is defined by
the innodb_data_file_path
startup
option. For configuration information, see
System Tablespace Data File Configuration.
Additional information about the system tablespace is provided under the following topics in the section:
This section describes how to increase or decrease the size of the system tablespace.
The easiest way to increase the size of the system tablespace is
to configure it to be auto-extending. To do so, specify the
autoextend
attribute for the last data file
in the innodb_data_file_path
setting, and restart the server. For example:
innodb_data_file_path=ibdata1:10M:autoextend
When the autoextend
attribute is specified,
the data file automatically increases in size by 8MB increments
as space is required. The
innodb_autoextend_increment
variable controls the increment size.
You can also increase system tablespace size by adding another data file. To do so:
Stop the MySQL server.
If the last data file in the
innodb_data_file_path
setting is defined with the autoextend
attribute, remove it, and modify the size attribute to
reflect the current data file size. To determine the
appropriate data file size to specify, check your file
system for the file size, and round that value down to the
closest MB value, where a MB is equal to 1024 x 1024.
Append a new data file to the
innodb_data_file_path
setting, optionally specifying the
autoextend
attribute. The
autoextend
attribute can be specified
only for the last data file in the
innodb_data_file_path
setting.
Start the MySQL server.
For example, this tablespace has one auto-extending data file:
innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:10M:autoextend
Suppose that the data file has grown to 988MB over time. This is
the innodb_data_file_path
setting after modifying the size attribute to reflect the
current data file size, and after specifying a new 50MB
auto-extending data file:
innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
When adding a new data file, do not specify an existing file
name. InnoDB
creates and initializes the new
data file when you start the server.
You cannot increase the size of an existing system tablespace
data file by changing its size attribute. For example,
changing the
innodb_data_file_path
setting
from ibdata1:10M:autoextend
to
ibdata1:12M:autoextend
produces the
following error when starting the server:
[ERROR] [MY-012263] [InnoDB] The Auto-extending innodb_system data file './ibdata1' is of a different size 640 pages (rounded down to MB) than specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!
The error indicates that the existing data file size
(expressed in InnoDB
pages) is different
from the size specified in the configuration file. If you
encounter this error, restore the previous
innodb_data_file_path
setting, and refer to the system tablespace resizing
instructions.
InnoDB
page size is defined by the
innodb_page_size
variable.
The default is 16384 bytes.
You cannot remove a data file from the system tablespace. To decrease the system tablespace size, use this procedure:
Use mysqldump to dump all of your
InnoDB
tables, including
InnoDB
tables located in the
mysql
schema. Identify
InnoDB
tables in the
mysql
schema using the following query:
mysql> SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB';
+----------------------+
| table_name |
+----------------------+
| innodb_index_stats |
| innodb_table_stats |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
+----------------------+
5 rows in set (0.00 sec)
Stop the server.
Remove all of the existing tablespace files
(*.ibd
), including the
ibdata
and ib_log
files. Do not forget to remove *.ibd
files for tables located in the mysql
schema.
Remove any .frm
files for
InnoDB
tables.
Configure the data files for the new system tablespace. See System Tablespace Data File Configuration.
Restart the server.
Import the dump files.
If your databases only use the InnoDB
engine, it may be simpler to dump
all databases, stop the
server, remove all databases and InnoDB
log
files, restart the server, and import the dump files.
To avoid large system tablespaces, consider using file-per-table
tablespaces for your data. File-per-table tablespaces are the
default tablespace type and are used implicitly when creating an
InnoDB
table. Unlike the system tablespace,
disk space is returned to the operating system after truncating
or dropping a table created in a file-per-table tablespace. For
more information, see
Section 14.6.3.2, “File-Per-Table Tablespaces”.
You can use raw disk partitions as data files in the
InnoDB
system tablespace.
This technique enables nonbuffered I/O on Windows and on some
Linux and Unix systems without file system overhead. Perform
tests with and without raw partitions to verify whether this
change actually improves performance on your system.
When you use a raw disk partition, ensure that the user ID that
runs the MySQL server has read and write privileges for that
partition. For example, if you run the server as the
mysql
user, the partition must be readable
and writeable by mysql
. If you run the server
with the --memlock
option, the
server must be run as root
, so the partition
must be readable and writeable by root
.
The procedures described below involve option file modification. For additional information, see Section 4.2.2.2, “Using Option Files”.
When you create a new data file, specify the keyword
newraw
immediately after the data file
size for the
innodb_data_file_path
option. The partition must be at least as large as the size
that you specify. Note that 1MB in InnoDB
is 1024 × 1024 bytes, whereas 1MB in disk
specifications usually means 1,000,000 bytes.
[mysqld] innodb_data_home_dir= innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw
Restart the server. InnoDB
notices the
newraw
keyword and initializes the new
partition. However, do not create or change any
InnoDB
tables yet. Otherwise, when you
next restart the server, InnoDB
reinitializes the partition and your changes are lost. (As a
safety measure InnoDB
prevents users from
modifying data when any partition with
newraw
is specified.)
After InnoDB
has initialized the new
partition, stop the server, change newraw
in the data file specification to raw
:
[mysqld] innodb_data_home_dir= innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw
Restart the server. InnoDB
now permits
changes to be made.
On Windows systems, the same steps and accompanying guidelines
described for Linux and Unix systems apply except that the
innodb_data_file_path
setting
differs slightly on Windows.
When you create a new data file, specify the keyword
newraw
immediately after the data file
size for the
innodb_data_file_path
option:
[mysqld] innodb_data_home_dir= innodb_data_file_path=//./D::10Gnewraw
The //./
corresponds to the Windows
syntax of \\.\
for accessing physical
drives. In the example above, D:
is the
drive letter of the partition.
Restart the server. InnoDB
notices the
newraw
keyword and initializes the new
partition.
After InnoDB
has initialized the new
partition, stop the server, change newraw
in the data file specification to raw
:
[mysqld] innodb_data_home_dir= innodb_data_file_path=//./D::10Graw
Restart the server. InnoDB
now permits
changes to be made.