MyISAM max_rows greater than 4294967295?

[Tom D]

I'm running MySQL 4.1 under Linux. I need to have a MyISAM table with
more than 4G rows of data.

I've read the manual regarding the MAX_ROWS option on tables. This
table has a fixed row length, with 17 bytes per row.

MySQL simply won't set the ROW_LENGTH greater than 4G. When I use an
ALTER TABLE to set it higher, it acts as though it worked fine, but a SHOW
TABLE STATUS indicates that it is still limited to 4294967295.

I've been searching high and low on this, and the only things I'm
finding (for example in the '1.2.4. How Big MySQL Tables Can Be' section)
keep talking about file size limits of the OS. This isn't the issue. I
have a table that is at the 4G (rows) limit, and it's physical size on
disk is already 74GB. It appears that the addressing is being limited to
4 bytes.

Any ideas on what's imposing this limit? Thanks in advance.

Tom

[Bill Karwin]

Tom D wrote:

I'm running MySQL 4.1 under Linux. I need to have a MyISAM table with
more than 4G rows of data.
...It appears that the addressing is being limited to 4 bytes.

Yeah, I'd guess that this is the case, especially if the table is indexed.

Have you considered using InnoDB tables instead? The MAX_ROWS option is
specific to MyISAM. I can't quickly find a reference to any maximum
number of rows in InnoDB tables, but perhaps it is greater than 2^32.

Likewise, have you considered that your usage is outside the range of
functionality of MySQL? Apologies for suggesting this on a MySQL
newsgroup, but perhaps you should consider another RDBMS after
researching their row limits per table.

For instance, PostgreSQL claims that the maximum rows per table is
"unlimited. "
http://www.postgresql.org/docs/faqs.FAQ.html#4.4

Regards,
Bill K.

[Tom D]

On Thu, 21 Apr 2005 12:46:00 -0700, Bill Karwin wrote:

Tom D wrote:

I'm running MySQL 4.1 under Linux. I need to have a MyISAM table with
more than 4G rows of data.
...It appears that the addressing is being limited to 4 bytes.

Yeah, I'd guess that this is the case, especially if the table is indexed.

Have you considered using InnoDB tables instead? The MAX_ROWS option is
specific to MyISAM. I can't quickly find a reference to any maximum
number of rows in InnoDB tables, but perhaps it is greater than 2^32.

Thanks for the reply Bill. Actually I'm going to load these into
and InnoDB table. I can't find anything regarding the 4 billion
row limit on InnoDB either.

Here's what's really annoying. For the sake of the demo I need these
for I was going to just go with the 4 billion rows I was able to load,
but I wasn't able to index them, even after trimming out about 50 million
rows. myisamchk keeps telling me that index 1 has 'too many keys'. Index
one is a primary key using two integers from the table. The total number
of keys in the index would be a fraction of the total rows, so I just
simply don't get what's going on.

I'm just hoping that the InnoDB table will allow the existing records (a
bit less than 4G) with indexes. I plan on loading the InnoDB table using
'LOAD TABLE INFILE' commands against 1 million row chunks of data in tab
demimited files.

It's not fun I assure you. Thanks again!

Tom