- 14.3 The InnoDB Storage Engine
- 14.3.1 InnoDB as the Default MySQL Storage Engine
- 14.3.2 Configuring InnoDB
- 14.3.3 Using Per-Table Tablespaces
- 14.3.4 InnoDB Startup Options and System Variables
- 14.3.5 Creating and Using InnoDB Tables
- 14.3.6 Adding, Removing, or Resizing InnoDB Data and Log Files
- 14.3.7 Backing Up and Recovering an InnoDB Database
- 14.3.8 Moving or Copying InnoDB Tables to Another Machine
- 14.3.9 The InnoDB Transaction Model and Locking
- 14.3.10 InnoDB Multi-Versioning
- 14.3.11 InnoDB Table and Index Structures
- 14.3.12 InnoDB Disk I/O and File Space Management
- 14.3.13 InnoDB Error Handling
- 14.3.14 InnoDB Performance Tuning and Troubleshooting
- 14.3.15 Limits on InnoDB Tables
This section describes what you can do when your
InnoDB tablespace runs out of room or when you
want to change the size of the log files.
The easiest way to increase the size of the
InnoDB tablespace is to configure it from the
beginning to be auto-extending. Specify the
autoextend attribute for the last data file in
the tablespace definition. Then InnoDB increases
the size of that file automatically in 8MB increments when it runs
out of space. The increment size can be changed by setting the value
of the innodb_autoextend_increment
system variable, which is measured in MB.
Alternatively, you can increase the size of your tablespace by
adding another data file. To do this, you have to shut down the
MySQL server, change the tablespace configuration to add a new data
file to the end of
innodb_data_file_path, and start
the server again.
If your last data file was defined with the keyword
autoextend, the procedure for reconfiguring the
tablespace must take into account the size to which the last data
file has grown. Obtain the size of the data file, round it down to
the closest multiple of 1024 × 1024 bytes (= 1MB), and specify
the rounded size explicitly in
innodb_data_file_path. Then you can
add another data file. Remember that only the last data file in the
innodb_data_file_path can be
specified as auto-extending.
As an example, assume that the tablespace has just one
auto-extending data file ibdata1:
innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:12M:autoextend
Suppose that this data file, over time, has grown to 988MB. Here is the configuration line after modifying the original data file to not be auto-extending and adding another auto-extending data file:
innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
When you add a new file to the tablespace configuration, make sure
that it does not exist. InnoDB will create and
initialize the file when you restart the server.
Currently, you cannot remove a data file from the tablespace. To decrease the size of your tablespace, use this procedure:
Use mysqldump to dump all your
InnoDBtables.Stop the server.
Remove all the existing tablespace files, including the
ibdataandib_logfiles. If you want to keep a backup copy of the information, then copy all theib*files to another location before the removing the files in your MySQL installation.Remove any
.frmfiles forInnoDBtables.Configure a new tablespace.
Restart the server.
Import the dump files.
If you want to change the number or the size of your
InnoDB log files, use the following instructions.
The procedure to use depends on the value of
innodb_fast_shutdown:
If
innodb_fast_shutdownis not set to 2: Stop the MySQL server and make sure that it shuts down without errors (to ensure that there is no information for outstanding transactions in the log). Copy the old log files into a safe place in case something went wrong during the shutdown and you need them to recover the tablespace. Delete the old log files from the log file directory, editmy.cnfto change the log file configuration, and start the MySQL server again. mysqld sees that noInnoDBlog files exist at startup and creates new ones.If
innodb_fast_shutdownis set to 2: Setinnodb_fast_shutdownto 1:mysql>
SET GLOBAL innodb_fast_shutdown = 1;Then follow the instructions in the previous item.
User Comments
It is absurd that there is not a builtin function to compact the ibdata file to reclaim unused space to the OS. *ALWAYS* use innodb_file_per_table.
Shouldn't the innodb_fast_shutdown value set to 0 be the one that's advised before changing innodb logs ?
Since it does full insert buffer merge before shutting down it would be safer way to change innodb_log_file_size and/or innodb_log_files_in_group, am I wrong ?
Add your own comment.