Re: Apparently enough max_heap_table_size but "table is full"

[Ken Kakihara]

Thank you Rick for your post.

For the 2x10e9, I meant rows. And the character set is latin1, not utf8.

I tested with 2x10e6, 2x10e7 records and the b-tree index could be created in both
of the cases. But when it comes to 2x10e8 records, the table is full error occurred.

The increase in the memory usage monitered using the "top" utility was less
than 10GB in the case of 2x10e7 records. So the allocation of 1TB should be
enough to support 2x10e8 and 2x10e9 records with b-tree index if the memory
usage has linear relationship with the number of records in the table.

I haven't tested changing VARCHAR to CHAR, as I couldn't minimize the
size of for this column.

I wanted to keep a very large table and its indexes to be always on the RAM,
that is the reason for using MEMORY engine, and there is no updates and
insert to these tables, so the use of MEMORY engine seemed to be the
best choice. Would using the MyISAM or InnoDB be a better choice?