MySQL ibdata File Size Limit

Usually MySQL database size is limited by underlying operating system, not by MySQL internal limit. (In Linux 2.4+, ext3 file system can support up to 4TB)

In the beginning, MySQL sets the initial size of InnoDB data file. After the data reaches the limit, there are two options:

1. Stop to write data

2. Write data by increasing data file size automatically

The option is set by a variable, innodb_data_file_path, which is in /etc/my.cnf

For example,

innodb_data_file_path = ibdata1:2G:autoextend


This says the initial data file name is ibdata1 and the initial size is 2G. When the data size reaches 2G, it will extend the file automatically by the prescribed size. The increased size is 8MB by default and decided by “innodb-autoextend-increment.”

Without the autoextend option, it will generate an error when the size beyond 2G.

Another option is max:max_file_size. This works as a hard-limit to restrict the operation of autoextend so the data file cannot be auto-extended beyond max_file_size.


For example,

innodb_data_file_path = ibdata1:2G:autoextend:max:20G


Here, MySQL starts with 2G data file and auto-extend the data file up to 20G. But it cannot extend more than 20G.

The location (path) of InnoDB data file is decided by innodb_data_home_dir. If innodb_data_home_dir is not specified, MySQL will use the default directory location specified by datadir variable in my.cnf.


The following shows the part of my.cnf file that is used for NexOSS 5.15.x version.

# For InnoDB storage engine

# InnoDB data directory
# The deault is "./" which means MySQL data directory.
# innodb_data_home_dir = ./

# InooDB data files must be able to hold data and indexes
# But 2G may be a limit for X86
innodb_data_file_path = ibdata1:2000M:autoextend


The above configuration shows that the database file name is “ibdata1” at MySQL home directory and the size is 2G (2000M) with auto-extending option.

Add a Comment

Your email address will not be published. Required fields are marked *