Let’s get started.
Setting up a fixed InnoDB tablespace
When using the InnoDB storage engine of MySQL, the data is typically not stored in a per-database or per-table directory structure, but in several dedicated files, which collectively contain the so-called tablespace. By default (when installing MySQL using the configuration wizard) InnoDB is confi gured to have one small file to store data in, and this file grows as needed. While this is a very fl exible and economical confi guration to start with, this approach also has some drawbacks: there is no reserved space for your data, so you have to rely on free disk space every time your data grows. Also, if your database grows bigger, the file will grow to a size which makes it hard to handle—a dozen files of 1 GB each are typically easier to manage than one clumsy 12 GB file.
Large data files might, for example, cause problems if you try to put those files into an archive for backup or data transmission purposes. Even if the 2 GB limit is not present any more for the current file systems, many compression programs still have problems dealing with large files.
And finally, the constant adaptation of the file in InnoDB’s default configuration size will cause a (small, but existent) performance hit if your database grows.
The following recipe will show you how to define a fixed tablespace for your InnoDB installation, by which you can avoid these drawbacks of the InnoDB default configuration.
Getting ready
To install a fixed tablespace, you will have to reflect about some aspects: how much tablespace should be reserved for your database, and how to size the single data files which in sum constitute the tablespace.
Note that once your database completely allocates your tablespace, you will run into table full errors (error code 1114) when trying to add new data to your database.
Additionally, you have to make sure that your current InnoDB tablespace is completely empty. Ideally, you should set up the tablespace of a freshly installed MySQL instance, in which case this prerequisite is given.
To check whether any InnoDB tables exist in your database, execute the following statement and delete the given tables until the result is empty: SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables WHERE engine=”InnoDB”;
If your database already contains data stored in InnoDB tables that you do not want to lose, you will have to create a backup of your database and recover the data from it when you are done with the recipe. Please refer to the chapter Backing Up and Restoring MySQL Data for further information on this.
And finally, you have to make sure that the InnoDB data directory (as defined by the innodb_data_home_dir variable) features sufficient free disk space to store the InnoDB data files.
For the following example, we will use a fixed tablespace with a size of 500 MB and a maximal file size of 200 MB.
How to do it…
- Open the MySQL configuration file (my.ini or my.cnf) in a text editor.
- Identify the line starting with innodb_data_file_path in the [mysqld] section. If no such line exists, add the line to the file.
- Change the line innodb_data_file_path to read as follows:
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:100M
- Save the changed configuration file.
- Shut down your database instance (if running).
- Delete previous InnoDB data files (typically called ibdata1, ibdata2, and so on) from the directory defined by the innodb_data_home_dir variable.
- Delete previous InnoDB logfiles (named ib_logfile0, ib_logfile1, so on) from the directory defined by the innodb_log_group_home_dir variable.
If innodb_log_group_home_dir is not configured explicitly, it defaults to the datadir directory.
- Start your database.
- Wait for all data and log files to be created.
Depending on the size of your tablespace and the speed of your disk system, creation of InnoDB data fi les can take a significant amount of time (several minutes is not an uncommon time for larger installations). During this initialization sequence, MySQL is started but it will not accept any requests.
How it works…
Steps 1 through 4—and particularly 3—cover the actual change to be made to the MySQL configuration, which is necessary to adapt the InnoDB tablespace settings. The value of the innodb_data_file_path variable consists of a list of data file definitions that are separated by semicolons. Each data file definition is constructed of a fi le name and a file size with a colon as a separator. The size can be expressed as a plain numeric value, which defines the size of the data file in bytes. If the numeric value has a K, M, or G postfix, the number is interpreted as Kilobytes, Megabytes, or Gigabytes respectively. The list length is not limited to the three entries of our example; if you want to split a large tablespace into relatively small files, the list can easily contain dozens of data file definitions.
If your tablespace consists of more than 10 files, we propose naming the first nine files ibdata01 through ibdata09 (instead of ibdata1 and so forth; note the zero), so that the files are listed in a more consistent order when they are displayed in your file browser or command line interface.
Step 5 is prerequisite to the steps following after it, as deletion of vital InnoDB files while the system is still running is obviously not a good idea. In step 6, old data files are deleted to prevent collision with the new files. If InnoDB detects an existing file whose size differs from the size defined in the innodb_data_file_path variable, it will not initialize successfully. Hence, this step ensures that new, properly saved files can be created during the next MySQL start.
Note that deletion of the InnoDB data files is only suffi cient if all InnoDB tables were deleted previously (as discussed in the Getting ready section).
Alternatively, you could delete all *.frm files for InnoDB tables from the MySQL data directory, but we do not encourage this approach (clean deletion using DROP TABLE statements should be preferred over manual intervention in MySQL data directories whenever possible).
Step 7 is necessary to prevent InnoDB errors after the data files are created, as the InnoDB engine refuses to start if the log files are older than the tablespace files. With steps 8 and 9, the new settings take effect.
When starting the database for the first time after changes being made to the InnoDB tablespace configuration, take a look at the MySQL error log to make sure the settings were accepted and no errors have occurred.
The MySQL error log after the first start with the new settings will look similar to this:
InnoDB: The first specified data file E:MySQLInnoDBTestibdata1 did
not exist:
InnoDB: a new database to be created!
091115 21:35:56 InnoDB: Setting file E:MySQLInnoDBTestibdata1 size
to 200 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
...
InnoDB: Progress in MB: 100
091115 21:36:19 InnoDB: Log file .ib_logfile0 did not exist: new to
be created
InnoDB: Setting log file .ib_logfile0 size to 24 MB
InnoDB: Database physically writes the file full: wait...
...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
091115 21:36:22 InnoDB: Started; log sequence number 0 0
091115 21:36:22 [Note] C:Program FilesMySQLMySQL Server 5.1bin
mysqld: ready for connections.
Version: '5.1.31-community-log' socket: '' port: 3306 MySQL
Community Server (GPL)
There’s more…
If you already use a fixed tablespace, and you want to increase the available space, you can simply append additional files to your fixed tablespace by adding additional data file definitions to the current innodb_data_file_path variable setting. If you simply append additional files, you do not have to empty your tablespace first, but you can change the confi guration and simply restart your database. Nevertheless, as with all changes to the confi guration, we strongly encourage creating a backup of your database first.
Setting up an auto-extending InnoDB tablespace
The previous recipe demonstrates how to define a tablespace with a certain fixed size. While this provides maximum control and predictability, you have to block disk space based on the estimate of the maximum size required in the foreseeable future. As long as you store less data in your database than the reserved tablespace allows for, this basically means some disk space is wasted. This especially holds true if your setting does not allow for a separate file system exclusively for your MySQL instance, because then other applications compete for disk space as well. In these cases, a dynamic tablespace that starts with little space and grows as needed could be an alternative. The following recipe will show you how to achieve this.
Getting ready
When defining an auto-extending tablespace, you should first have an idea about the minimum tablespace requirements of your database, which will set the initial size of the tablespace. Furthermore, you have to decide whether you want to split your initial tablespace into files of a certain maximum size (for better file handling).
If the above settings are identical to the current settings and you only want to make your tablespace grow automatically if necessary, you will be able to keep your data. Otherwise, you have to empty your current InnoDB tablespace completely (please refer to the previous recipe Setting up a fixed InnoDB tablespace for details).
As with all major confi guration changes to your database, we strongly advise you to create a backup of your data first. If you have to empty your tablespace, you can use this backup to recover your data after the changes are completed. Again, please refer to the chapter Backing Up and Restoring MySQL Data for further information on this.
And as before, you have to make sure that there is enough disk space available in the innodb_data_home_dir directory—not only for the initial database size, but also for the anticipated growth of your database.
The recipe also requires you to shut down your database temporarily; so you have to make sure all clients are disconnected while performing the required steps to prevent conflicting access. As the recipe demands changes to your MySQL confi guration file (my.cnf or my.ini), you need write access to this file.
For the following example, we will use an auto-extending tablespace with an initial size of 100 MB and a file size of 50 MB.
How to do it…
- Open the MySQL configuration file (my.ini or my.cnf) in a text editor.
- Identify the line starting with innodb_data_file_path in the [mysqld] section. If no such line exists, add the line to the file.
- Change the line innodb_data_file_path to read as follows:
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
Note that no file defi nition except the last one must have the :autoextend option; you will run into errors otherwise.
- Save the changed confi guration file.
- Shut down your database instance (if running).
- Delete previous InnoDB data files (typically called ibdata1, ibdata2, and so on) from the directory defi ned by the innodb_data_home_dir variable.
- Delete previous InnoDB logfiles (named ib_logfile0, ib_logfile1, and so on) from the directory defined by the innodb_log_group_home_dir variable.
- Start your database.
- Wait for all data and log files to be created.
Depending on the size of your tablespace and the speed of your disk system, creation of InnoDB data files can take a signifi cant amount of time (several minutes is not an uncommon time for larger installations). During this initialization sequence, MySQL is started but will not accept any requests.
When starting the database for the first time after changes being made to the InnoDB tablespace configuration, take a look at the MySQL error log to make sure the settings were accepted and no errors have occurred.
If innodb_log_group_home_dir is not configured explicitly, it defaults to the datadir directory
How it works…
The above steps are basically identical to the steps of the previous recipe Setting up a fixed InnoDB tablespace, the only difference being the definition of the innodb_data_file_path variable. In this recipe, we create two files of 50 MB size, the last one having an additional :autoextend property.
If the innodb_data_file_path variable is not set explicitly, it defaults to the value ibdata1:10M:autoextend.
As data gets inserted into the database, parts of the tablespace will be allocated. As soon as the 100 MB of initial tablespace is not sufficient any more, the file ibdata2 will become larger to match the additional tablespace requirements.
Note that the :autoextend option causes the tablespace files to be extended automatically, but they are not automatically reduced in size again if the space requirements decrease. Please refer to the Decreasing InnoDB tablespace recipe for instructions on how to free unused tablespace.
There’s more…
The recipe only covers the basic aspects of auto-extending tablespaces; the following sections provide insight into some more advanced topics.
Making an existing tablespace auto-extensible
If you already have a database with live data in place and you want to change your current fixed configuration to use the auto-extension feature, you can simply add the :autoextend option to the last file definition.
Let us assume a current configuration like the following:
innodb_data_file_path=ibdata1:50M;ibdata2:50M
The respective configuration with auto-extension will look like this:
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
In this case, do not empty the InnoDB tablespace first, you can simply change the configuration file and restart your database, and you should be fine. As with all configuration changes, however, we strongly recommend to back up your database before editing these settings even in this case.
Controlling the steps of tablespace extension
The amount by which the size of the auto-extending tablespace file is increased is controlled by the innodb_autoextend_increment variable. The value of this variable defines the number of Megabytes by which the tablespace is enlarged. By default, 8 MB are added to the file if the current tablespace is no longer sufficient.
Limiting the size of an auto-extending tablespace
If you want to use an auto-extending tablespace, but also want to limit the maximum size your tablespace will grow to, you can add a maximum size for the auto-extended tablespace file by using the :autoextend:max:[size] option. The [size] portion is a placeholder for a size definition using the same notation as the size description for the tablespace file itself, which means a numeric value and an optional K, M, or G modifier (for sizes in Kilo-, Mega-, and Gigabytes). As an example, if you want to have a tiny initial tablespace of 10 MB, which is extended as needed, but with an upper limit of 2 GB, you would enter the following line to your MySQL configuration file:
innodb_data_file_path=ibdata1:10M:autoextend:max:2G
Note that if the maximum size is reached, you will run into errors when trying to add new data to your database.
Adding a new auto-extending data file
Imagine an auto-extending tablespace with an auto-extended file, which grew so large over time that you want to prevent the file from growing further and want to append a new auto-extending data file to the tablespace. You can do so using the following steps:
- Shut down your database instance.
- Look up the exact size of the auto-extended InnoDB data file (the last file in your current configuration).
- Put the exact size as the tablespace fi le size definition into the innodb_data_file_path configuration (number of bytes without any K, M, or G modifier), and add a new auto-extending data file.
- Restart your database.
As an example, if your current confi guration reads ibdata1:10M:autoextend and the ibdata1 file has an actual size of 44,040,192 bytes, change configuration to innodb_data_file_path=ibdata1:44040192;ibdata2:10M:autoextend:max:2G.
