Note also that the documentation states that the data pointer could be as large as 8 bytes, but that is rather pointless as we will see later. Note that even MySQL versions prior to 5.0 could use more than 4 GB per file, but not by default. The default value of 6 bytes is good for 256TB of data in each single MYD file. If you change it, it will affect all newly created or altered tables, but not any existing tables. Like any default, it can of course be changed in your my.cnf file. This used to be 4 bytes up to MySQL 5.0, and is 6 bytes since and including MySQL 5.0. If you do not define a MAX_ROWS attribute at all, MyISAM will use a default number of bytes for the row pointer: myisam_data_pointer_size. Running an ALTER TABLE on a 4 GB table can take quite some time – it is not a good idea to get the MAX_ROWS value too small. Be warned, though: ALTER TABLE makes a copy of your data during the ALTER TABLE command, and that will take time. The addressable range is set in MyISAM with the table attribute MAX_ROWS: You can CREATE TABLE t … MAX_ROWS = x or you can later ALTER TABLE t MAX_ROWS=… and MyISAM will determine how many bytes will be needed to store a pointer to a row to be able to address the data. It is not useful to have a MYD file that is larger than the addressable range from an index, so MySQL prevents you from having such a file with the error message ‘ table full‘. In a DYNAMIC row format table, MYI files store byte offsets into the MYD file. These are converted into byte offsets within the MYD file by multiplying with the fixed row length.
In FIXED format, the MYI file refers to positions in the MYD file by using record numbers. In MyISAM, a table can have a FIXED or DYNAMIC row format (COMPRESSED also exists, but is not really relevant here). If it is on, each table gets its own file with an ibd extension if it is off, the actual tables are stored in a set of files most commonly called ibdataXXX, the size and number of which are freely configureable.Įvery database uses indices, and in every database the index needs to be able to address the data records. In InnoDB, we have two modes of operation, depending on innodb_file_per_table. In MyISAM, the limit, if it is ever reached, means that a single table is limited to that much data or index size (data is stored in one file, with a MYD extension, and indices are stored in another file, with a MYI extension). The limits listed in Nick’s table are file size limits, they are not database size limits.
We will soon see that the exact number is not really relevant.
In 64 bit systems and with modern file systems (NTFS in Windows and XFS on LVM2 in Linux, on a recent kernel), the operating imposed file size limit is multiple terabytes or petabytes, even. That’s kind of useless, because it is memory that makes databases fast. Not only does that limit your file size in Windows and also in certain ways in Linux, it will also limit the amount of system memory you can invest into MySQL buffer caches.
Second: You also never want to run a database system on a 32 bit operating system.
So the actual file size limit of FAT is kind of moot for the purpose of this discussion. Since seek operations are basically what a large database does all day, FAT is completely useless for this. That is, certain “seek” (backwards seek operations) operations become slower the larger a file is, because the file system has to position the file pointer by traversing the linked list of blocks in the FAT. In FAT, a file is a linked list of blocks in the FAT. But let’s start with file systems, anyway.įirst: You never want to run a database system on a FAT filesystem, ever. While every file system does have a maximum file size, this limitation is usually not relevant when it comes to MySQL maximum database size. He answers that with a list of maximum file sizes per file system type. In Maximum MySQL Database Size? Nick Duncan wants to find out what the maximum size of his MySQL database can possibly be.