Multifile Databases
Go Up to Database Files
InterBase supports databases that span multiple files and multiple file systems. You can add additional files to the database without having to take it off line.
The Database Restore task in IBConsole and in the
gbak command-line utility permit you to create a multifile database. The only way to alter the file size allocation of an existing database is to back up and restore the database file.
Contents
Adding Database Files
You have the option of specifying the size of secondary files in either of two ways: specify the page on which each secondary file starts, or specify the length in database pages of each file. When you specify the size using the LENGTH keyword, do not specify the length of the final file. InterBase sizes the final file dynamically, as needed.
The following isql example adds files using STARTING AT syntax:
CONNECT ‘first.ib’; ALTER DATABASE ADD FILE 'second.ib' STARTING AT 50000;
Altering Database File Sizes
You cannot use ALTER DATABASE to split an existing database file. For example, if your existing database is 80,000 pages long and you issue the command above, InterBase starts the new database file at page 80,001. The only way to split an existing database file into smaller files is to back it up and restore it. When you restore a database, you are free to specify secondary file sizes at will, without reference to the original file sizes.
The following isql example adds a file using LENGTH syntax. second.ib will begin on the page following the final page of first.ib and will grow to 50,000 database pages. Then InterBase begins writing to
third.ib and dynamically increases the size as necessary.
CONNECT 'first.ib'; ALTER DATABASE ADD FILE 'second.ib' LENGTH 50000 ADD FILE 'third.ib';
InterBase starts writing data to third.ib only after second.ib file fills up. In the example above, second.ib is 50,000 pages long, and begins following the original file. InterBase will begin filling the third.ib file after second.ib reaches 50,000 pages. Database pages are 4KB each by default and have a maximum size of 8KB.
There is no guarantee that a given table resides entirely in one file or another. InterBase stores records based on available space within database files. Over time, records from a given table tend to spread over all the files in a multifile database.
Maximum Number of Files
InterBase allows up to 131,000 database files, including shadow files. Note that your operating system might have a lower limit on the number of simultaneous open files than the ibserver process can have.
Application Considerations
A multifile database is not the same thing as multiple single-file databases. The tables are all part of the same database they used to be in, but they can be stored across the multiple files. From the standpoint of your application, they are all part of the same database and are accessed exactly the same way they would be in a single-file database.
Your application does not need to know about any files except the first one. Any time your database operations access/write data in the secondary files, the InterBase software takes care of it without requiring any special programming from your application. The application attaches to the database by specifying the path of the first file of the database; applications do not change.
Reorganizing File Allocation
You can change the sizes of the files of a multifile database when using gbak to restore a database. If you need to move a multi-file database to a different disk or directory, use gbak to back up the database, then specify the new locations of all secondary files as you restore the database. See Performing backups and restores using the gbak command.
Any database in a production environment should include a definition for at least one secondary file, even if the current size of the database does not warrant a multifile database. Data tends to accumulate without bounds, and some day in the future your database might exceed your file system size, or the maximum file size of the operating system. By defining a secondary file, you specify what action InterBase takes when the database grows beyond these limits. This means that the database administrator is freed from monitoring the database as it approaches the file size limit.