Overcoming MySQL limit of 2^32 rows in a MyISAM table

Question

I want to create a MyISAM table in MySQL (tried various versions of 5.0.x and 5.1.x) which allows more than 2^32 rows. The way to achieve this is to get MySQL to use 8-byte instead of 4-byte row pointers.

Here's a quote from the MySQL manual:

If you build MySQL with the --with-big-tables option, the row limitation is increased to 1.844E+19 rows. See Section 2.3.2, “Typical configure Options”. Binary distributions for Unix and Linux are built with this option.

Sounds simple, right? But I've tried various binary distributions for Linux x86_64, and also building MySQL from the source code with the "--with-big-tables" option. In every case I still can't get beyond the 2^32 limit. I create a table like thus:

CREATE TABLE big (col int) MAX_ROWS=1099511627776

And when I check the table status, it says:

Create_options: max_rows=4294967295

How do I escape 32-bit purgatory? Using InnoDB might solve the problem, but it performs a lot more slowly for the type of queries I'm running.

FYI, here's an important reference that didn't solve the problem:

http://jeremy.zawodny.com/blog/archives/000796.html

Answer 1

If you are compiling mysql using --with-big-tables option, the row limitation is increased to (2^32)^2. Unfortunately this is the highest limit :)

Answer 2

I worked this out for myself, and the answer is both simple and absurd.

If you leave out the MAX_ROWS setting when creating a MyISAM table, then the table doesn't suffer from the 2^32 limit on rows. Instead the maximum number of rows is determined by the pointer size, which is itself determined by the global variable myisam_data_pointer_size.

By default, the myisam_data_pointer_size is 6 on modern builds of MySQL running on Linux/Unix, leading to a limit of 2^48 rows on tables with fixed row width (or 2^48 bytes on tables with dynamic rows). But you can also change it using something like:

SET GLOBAL myisam_data_pointer_size=5;

And check it using:

SHOW VARIABLES LIKE 'myisam_data_pointer_size';

In a MyISAM table with fixed width rows, the maximum number of rows is equal to (2^(8* myisam_data_pointer_size))-1, given myisam_data_pointer_size at the time of table creation.