Increase maximum table space in MySQL

For those of us that are still forced to used 32bit MySQL, there is a table size limit of 4GB by default (even though the file size limit on those systems is 4TB on ext3 – 2TB on NTFS).  Here is what to do to lift that limit:

ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

You have to specify only for tables with or columns; in this case, MySQL can’t optimize the space required based only on the number of rows.

If you want to make the default larger, then you can set the mysqld engine parameter in (or whereever your MySQL server config file is). The setting of 7 will allow 256TB table size.

References:

1. MySQL 5.0 Reference Manual
2. Overcoming MySQL’s 4GB Limit
3. Vbulletin Community Forum