InterBase has an (alleged) 2GB Database size limit
by Paul Beach
Updated 30th September 2005
Firebird V1.0 and InterBase V6.5 onwards
This was an issue with InterBase Before V6.5 and previous, Where it was possible that if you didn't create an extended database file (by adding a new secondary file) then the 32-bit API I/O calls used by the database server would set the limit of a single database file at usually 2GB, when the limit was reached the database would corrupt itself by writing over the initial database pages. One of the first things we did in Firebird was change this so that if the limit was reached all writes would be prevented, although outstanding writes would be lost, this would make sure that even though the limit had been reached no corruption would occur. Then Firebird added Large Database File Support (see below). From Firebird V1.0 Large File Support was enabled automaticlly for Windows and then from Firebird V1.5, Large File Support was enabled automatically for other operating systems, meaning that you got 64bit I/O in all builds. i.e. prior to V1.5 you could get either 32bit I/O or 64bit I/O builds for Linux and other operating systems where appropriate.
Taken from the Firebird V1.0 Release Notes
Large Database File Support
The logical database can comprise a primary file and numerous secondary files (the standing record for a single FB/IB database is 980GB!). However, this enhancement concerns the size of a single physical database file.
The maximum file size limit involves three separate factors:
- the implementation (coding) of the FB/IB engine I/O functions
- operating system support for 64-bit file operations
- filesystem limits on file size
Engine support for 64-bit file I/O
All Firebird/IB versions prior to FB v1.0 (including IB v6.0) used both 32-bit API/OS calls and 32-bit (integer) file pointer structures. The engine's capability was thus designed to handle files with a maximum file size of 4Gb. In the Unix code the file pointer structures were defined as signed integers, thus limiting Unix and Linux implementations to file sizes of 2Gb.
By design, all versions of Win32 (Win9x/ME/NT/2000/XP/2003) provide full support for 64-bit pointer file operations, provided the right file pointer structure is passed. To add large file support for Firebird, it was necessary only to change the file pointer structure to LARGE_INTEGER and to modify the I/O calls themselves slightly.
For Linux, 64-bit file operations are supported with version 2.2 of glibc and above, using the new 2.4 kernel. Version 2.1.3 of glibc contained limited but broken support for large files. Red Hat Linux 7.1 ships with glibc 2.2.2-10 and has been tested to work with files over 2Gb (using the ext2 native Linux filesystem, which is the default filesystem in nearly all Linux distributions). Generally speaking, any recent Linux distribution that uses the 2.4 kernel, ext2 filesystem, and glibc 2.2 or higher, will handle files larger than the previous 32-bit/2Gb upper limit.
Because 64-bit large file support was not ubiquitous among Linux distributions when Firebird 1.0x was available (also true for some commercial Unix platforms), the Firebird Unix ports came in two flavours: 32-bit and 64-bit I/O. Allowing you to choose the version you know your platform can support.
The basic requirement is glibc 2.2 (+ support in core utilities) and the 2.4 kernel (for VFS). All these conditions are met in RH 7.1, MDK 8.0 and SuSE 7.2 and newer versions. But beware, some distributions have a choice to install old components! For example Mandrake 8.x allows installation of the 2.2 kernel in advanced setup.
MacOS X 10.0
Firebird does not work with MacOS X 10.0 due in part to 10.0's bug in 64 bit I/O support.
Although the engine can now support 64-bit operations, it is important to remember that not all filesystems support large files.
In the case of Win32, the following file size (not to be confused with partition/volume) limits apply:
- FAT16 for Win9x/ME, max file size = 2GB - 1byte
- FAT16 for WinNT/2000, max file size = 4GB - 1byte
- FAT32 for Win9x/ME/2000, max file size = 4GB - 1byte
- NTFS for WinNT/2000/XP/2003, max file size = 16,384GB - 1byte
For Linux and Unix flavors, because there are so many different filesystems, you should consult the following URL for the exact details: http://www.suse.de/~aj/linux_lfs.html
The good news is that all the major Linux distributions use the ext2 filesystem, which fully supports large files. It really boils down to: 1. Use kernel 2.4 and above 2. Use glibc 2.2 and above 3. Use ext2 filesystem, or other filesystem that supports the kernel's LFS (large file system) interface. 4. It should work on ReiserFS and Ext3
Firebird Before V1.0 and InterBase Before V6.5
Generically speaking (i.e. from a platform-independent point of view), InterBase documents a 2GB/file limit. As a consequence of using the overlapped I/O mechanism on Windows NT, the file offset is interpreted as an unsigned 32-bit quantity from the beginning of the file. This allows you to access 4GB file even though we didn't plan it this way. However, don't try anything beyond 4GB as InterBase zeroes the file offset high <64:32> bits.
In fact, InterBase will probably be happy to logically allocate pages beyond that 4GB limit but the resulting physical I/O will be modulo 4GB and likely canabalize real data in the database.
For Windows95, InterBase still uses SetFilePointer which allows only a 2GB file limit due to bidirectional seeks from the beginning, current and end of a file. For the brave souls that file copy between Windows NT and 95, keep in mind that any database file greater than 2GB will not be accessible on Windows95 or more precisely only the first 2GB will be accessible.
So, InterBase uses 32-bit file pointers internally, and many operating systems typically implement file pointers as signed data, which limits them to 2^31-1, or about 2GB. Thus InterBase supports only database files of 2GB or less. NT does allow larger files to exist.
But we do recommend breaking up the database into multiple files of 2GB's each so you do not not to risk surfacing idiosyncracies related to the sign bit of the file pointer.
If your database is growing such that the 2GB file size limit is within sight, you would be prudent to define secondary file(s) even if you aren't so close to reaching the limit. InterBase will automatically start using them when it becomes necessary. You can add the additional files to a fileset of a given database on the fly, without interrupting activity on the database.
Defining secondary files is easy to do; refer to the docs, or the examples below.
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.gdb’; ALTER DATABASE ADD FILE 'second.gdb' 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.gdb will begin on the page following the final page of first.gdb and will grow to 50,000 database pages. Then InterBase begins writing to third.gdb and dynamically increases the size as necessary.
CONNECT ’first.gdb’; ALTER DATABASE ADD FILE ’second.gdb’ LENGTH 50000 ADD FILE ’third.gdb’;
InterBase starts writing data to third.gdb once second.gdb file fills up. In the example above, second.gdb is 50,000 pages long, and begins following the original file. InterBase will begin filling the third.gdb file after second.gdb reaches 50,000 pages. Database pages are 1KB 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.
Backing up a multi-file database that exceeds the file size limitation, can be done using GBAK, and it will prompt you automatically to insert additional tape volumes.
Alternatively if you are using InterBase 5.x on Unix, you can use the gsplit utility to pipe input and output from gbak to multiple backup volumes. If you are using InterBase 5.x on Windows or NT download the fsplit utility (Miscellaneous) from this site which serves the same purpose.
If you are using InterBase 6.0, the syntax would be something like this example, where foo1.gdb on a server is backed up into multiple files in the /backup directory, where the first two backup files are limited to 500K. The last one expands as necessary.
gbak -b -user joe -pa blurf /foo1.gdb /backup/backup1.gbk 500k /backup/backup2.gbk 500k /backup/lastBackup.gbk
This example restores a multifile database from the /backup directory of to the /companydb directory. In this example, the first two files of the restored database are 500 pages long and the last file grows as needed.
gbak -r user -joe -pa blurf /backup/foo1.gbk /backup/foo2.gbk /backup/fooLast.gbk /companydb/foo1.gdb 500 /companydb/foo2.gdb 500 /companydb/fooLast.gdb
Regarding the theoretical maximum database size:
InterBase internally enumerates the multiple files with a 16-bit unsigned integers, therefore you may have up to 65,536 files. Assuming you choose file of the maximum supported size, you can theoretically address up to 2^(31+16)-1 bytes, or 131,072 terrabytes of data.