How to increase MySQL MyISAM Table Size Beyond 4GBBy 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: DynamicMax_data_length: 4294967295
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?