How to increase MySQL MyISAM Table Size Beyond 4GB

When you started using MySQL, I am sure you never considered that it might have some limits on the maximum size of a table, did you?

Soon you may be facing the prospect of having tables over 4GB, the default limit imposed by MySQL. Fortunately MySQL provides a simple, but time-consuming, way to store data way beyond 4GB.

How to find maximum table size limit in MySQL?

show table status like 'table_name'

This command displays all settings associated with the table. A sample output:

           Name: table_name
           Type: MyISAM
     Row_format: Dynamic
Max_data_length: 4294967295

Index_length: 1024

How to increase maximum table size limit in MySQL?

Use alter table to set the max_rows to a large number say 200GB:

alter table weather max_rows = 200000000000 avg_row_length = 50;

This changes create option as follows:

Create_options: max_rows=4294967295 avg_row_length=50

There is still a limit on the maximum number of rows but not for maximum size of the table.

64 Bit Operating System

For 64bit OS like CentOS the limit is raised accordingly and so you don’t have to bother with any of the above. Another reason to switch to 64bit operating system, eh?