How to increase MySQL MyISAM Table Size Beyond 4GB

By Angsuman Chakraborty, Gaea News Network
Friday, April 10, 2009

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?


May 9, 2009: 9:10 pm

Let’s say I have a big table with numerous fields with only two interesting for the job;
do you have in your knowledge way to skip rows whil importing in order to remain under the max size.

will not be displayed